CODEWITHSUNDEEP
sqloracle-databaseplsql
thusith.92
thusith.92

Reputation: 306

Get metadata of an Oracle SQL query

Is there any way in oracle to get the metadata of a running SQL query? As in if my query is:

SELECT fname,
       lname,
       age,
       salary
FROM emp_tab ;

I want to get the metadata of each column returned in the result set (ie: age = NUMBER, lname = VARCAR2(25) etc; ) which is defined in the DB.

Upvotes: 1

Views: 3524

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

SQL> column column_name format a35;
SQL> column data_type format a20;
SQL> column data_default format a15;
SQL> select column_name, data_type||decode(data_type,'NUMBER',nvl2(data_precision,'('||data_precision||nvl2(data_scale,','||data_scale,'')||')',''),'('|| data_length||')') data_type, data_default
       from user_tab_columns 
      where table_name = upper('&i_table_name'); -- EMP_TAB

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

One way is to use DBMS_SQL.DESCRIBE_COLUMNS:

Table:

CREATE TABLE emp_tab (fname VARCHAR2(25),
       lname VARCHAR2(25),
       age NUMBER,
       salary NUMBER
);

Query:

DECLARE
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   cols DBMS_SQL.DESC_TAB;
   ncols PLS_INTEGER;
BEGIN
   DBMS_SQL.PARSE(cur, 'SELECT fname,lname, age,  salary FROM emp_tab'
                 , DBMS_SQL.NATIVE);
   DBMS_SQL.DESCRIBE_COLUMNS (cur, ncols, cols);
   FOR i IN 1 .. ncols   LOOP
      DBMS_OUTPUT.PUT_LINE ('Column name:'|| cols(i).col_name || ' Column type:' 
           || cols(i).col_type || ' Max column length:' || cols(i).col_max_len);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

Output:

Column name:FNAME Column type:1 Max column length:25
Column name:LNAME Column type:1 Max column length:25
Column name:AGE Column type:2 Max column length:22
Column name:SALARY Column type:2 Max column length:22

Upvotes: 2

Related Questions