Skip to Main Content
  • Questions

  • Column metadata for Queries "in flight"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Duke.

Asked: June 06, 2016 - 2:39 pm UTC

Last updated: June 08, 2016 - 12:47 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I have "read-only privileges" on a production Oracle 12c database. I can't create views or tables, even locally in my own schema. That's fine except I'd like to be able to see the column names and data-types for various queries that I'm developing.

I often need to take existing production queries (say a multi-table joins, various derived columns, etc). Obviously I have to store the query as a script, spool the results to a flat file. However I'd like to get same information available as on xxx_TAB_COLUMNS: the number of columns, data types, names of columns. Can that be obtained from the production database somehow?

NOTE: Because I do have access to a dev database, I can use the COPY command to create a table, but the real query is hundreds of lines long, so I'd have to do some manipulation to do so. I also looked at generating the XML schema (DBMS_XMLSCHEMA.GENERATESCHEMA), but it seems to just read the xxx_TAB_COLUMNS rather than parsing a query. Is it possible to get the same info directly from the production database query?

(Somewhat silly example):

PROD> create table t ( t NUMBER );
create table t ( t NUMBER )
*
ERROR at line 1:
ORA-01031: insufficient privileges


Elapsed: 00:00:00.03
PROD> ed
Wrote file c:/users/DGANOTE/sqlplusedit.sql

  1  select owner, ageism, count(*) from (
  2  select all_views.owner
  3       , view_name
  4       , created
  5       , case when created > add_months(trunc(sysdate),-2)
  6              then 'recent' else 'older'
  7          end ageism
  8   from all_views
  9   join all_objects
10     on view_name = object_name
11    and all_views.owner = all_objects.owner
12   where all_views.owner like 'SYS%'
13* ) group by owner, ageism
PROD> /

OWNER                               AGEISM     COUNT(*)
----------------------------------- ------ ------------
SYSTEM                              older            14
SYS                                 older         5,760
SYS                                 recent           16

Elapsed: 00:00:01.22


and Connor said...

You can use DBMS_SQL to parse the query (you just change what is in l_query, or pass it as &1 in your SQL script).

The 'case' statement is approximate, but should be enough to work with.


SQL> set serverout on
SQL> declare
  2        l_cur   int default dbms_sql.open_cursor;
  3        l_desc  dbms_sql.desc_tab;
  4        l_ncols int;
  5        l_query long := 'select * from scott.emp';
  6      begin
  7        dbms_sql.parse(l_cur,  l_query,dbms_sql.native);
  8        dbms_sql.describe_columns(l_cur, l_ncols, l_desc);
  9        dbms_sql.close_cursor(l_cur);
 10        for i in 1..l_ncols loop
 11          dbms_output.put_line('---------------------------');
 12          dbms_output.put_line(
 13                 case
 14                    when l_desc(i).col_type = 1 then 'VARCHAR2'
 15                    when l_desc(i).col_type = 2 then 'NUMBER'
 16                    when l_desc(i).col_type = 8 then 'LONG'
 17                    when l_desc(i).col_type =  9 then 'VARCHAR'
 18                    when l_desc(i).col_type =  12 then 'DATE'
 19                    when l_desc(i).col_type =  23 then 'RAW'
 20                    when l_desc(i).col_type =  69 then 'ROWID'
 21                    when l_desc(i).col_type =  96 then 'CHAR'
 22                    when l_desc(i).col_type =  100 then 'BINARY_FLOAT'
 23                    when l_desc(i).col_type =  101 then 'BINARY_DOUBLE'
 24                    when l_desc(i).col_type =  105 then 'MLSLABEL'
 25                    when l_desc(i).col_type =  106 then 'MLSLABEL'
 26                    when l_desc(i).col_type =  112 then  'CLOB'
 27                    when l_desc(i).col_type =  113 then 'BLOB'
 28                    when l_desc(i).col_type =  114 then 'BFILE'
 29                    when l_desc(i).col_type =  115 then 'CFILE'
 30                    when l_desc(i).col_type =  178 then 'TIME'
 31                    when l_desc(i).col_type =  179 then 'TIME WITH TIME ZONE'
 32                    when l_desc(i).col_type =  180 then 'TIMESTAMP'
 33                    when l_desc(i).col_type =  181 then 'TIMESTAMP WITH TIME ZONE'
 34                    when l_desc(i).col_type =  231 then 'TIMESTAMP WITH LOCAL TIME ZONE'
 35                    when l_desc(i).col_type =  182 then 'INTERVAL YEAR TO MONTH'
 36                    when l_desc(i).col_type =  183 then 'INTERVAL DAY TO SECOND'
 37                    else 'OTHER'
 38                  end
 39          );
 40          dbms_output.put_line(l_desc(i).col_name);
 41          dbms_output.put_line(l_desc(i).col_max_len);
 42          dbms_output.put_line(l_desc(i).col_precision);
 43          dbms_output.put_line(l_desc(i).col_scale);
 44       end loop;
 45     end;
 46  /
---------------------------
NUMBER
EMPNO
22
4
0
---------------------------
VARCHAR2
ENAME
10
0
0
---------------------------
VARCHAR2
JOB
9
0
0
---------------------------
NUMBER
MGR
22
4
0
---------------------------
DATE
HIREDATE
7
0
0
---------------------------
NUMBER
SAL
22
7
2
---------------------------
NUMBER
COMM
22
7
2
---------------------------
NUMBER
DEPTNO
22
2
0

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

That's fly, Connor!

Duke Ganote, June 07, 2016 - 1:12 pm UTC

I can see the implied data type on the derived column, etc.:

declare
   l_cur   int default dbms_sql.open_cursor;
   l_desc  dbms_sql.desc_tab;
   l_ncols int;
   l_query long := q'{select all_views.owner
                           , view_name
                           , created
                           , case when created > add_months(trunc(sysdate),-2)
                                 then 'recent' else 'older'
                             end ageism
                        from all_views
                        join all_objects
                          on view_name = object_name
                         and all_views.owner = all_objects.owner
                       where all_views.owner like 'SYS%'  }';
 begin
   dbms_sql.parse(l_cur,  l_query,dbms_sql.native);
   dbms_sql.describe_columns(l_cur, l_ncols, l_desc);
   dbms_sql.close_cursor(l_cur);
   dbms_output.put_line(CHR(13));
   dbms_output.put_line('DESC my query result');
   dbms_output.put_line(CHR(13));
   dbms_output.put_line('Name                               Type');
   dbms_output.put_line('--------------------------------   --------------------');
   for i in 1..l_ncols loop
     dbms_output.put_line(
          rpad(l_desc(i).col_name,35)||
            case l_desc(i).col_type
                 when   1 then 'VARCHAR2'
                 when   2 then 'NUMBER'
                 when   8 then 'LONG'
                 when   9 then 'VARCHAR'
                 when  12 then 'DATE'
                 when  23 then 'RAW'
                 when  69 then 'ROWID'
                 when  96 then 'CHAR'
                 when 100 then 'BINARY_FLOAT'
                 when 101 then 'BINARY_DOUBLE'
                 when 105 then 'MLSLABEL'
                 when 106 then 'MLSLABEL'
                 when 112 then 'CLOB'
                 when 113 then 'BLOB'
                 when 114 then 'BFILE'
                 when 115 then 'CFILE'
                 when 178 then 'TIME'
                 when 179 then 'TIME WITH TIME ZONE'
                 when 180 then 'TIMESTAMP'
                 when 181 then 'TIMESTAMP WITH TIME ZONE'
                 when 231 then 'TIMESTAMP WITH LOCAL TIME'
                 when 182 then 'INTERVAL YEAR TO MONTH'
                 when 183 then 'INTERVAL DAY TO SECOND'
               else 'OTHER'
             end
        ||case when l_desc(i).col_type IN ( 1, 9, 96 )
               then '('||l_desc(i).col_max_len||')'
           end
       );
  end loop;
end;
/


DESC my query result

Name                               Type
--------------------------------   --------------------
OWNER                              VARCHAR2(128)
VIEW_NAME                          VARCHAR2(128)
CREATED                            DATE
AGEISM                             VARCHAR2(6)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06


Thank you!

Connor McDonald
June 08, 2016 - 12:47 am UTC

btw, if you want the precise datatype listings, just check out the DDL for dba_tab_cols, and keep drilling down, and you can get the definitive definition, but I figured this would suffice.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here