How can I find out the field type of my query

Question

I want to know the type of my query field.

At the moment, only one option comes to mind - create table table_name as select trunc (systimestamp) from dual;

And then I can look at the data type of the table column and find out that it is a DATE type.

Are there more elegant solutions?

Maybe there is some analogue of DESCRIBE - DESCRIBE (SELECT trunc (systimestamp) FROM dual); or something like that

Possible duplicate of [How do I get column datatype in Oracle with PL-SQL with low privileges?](https://stackoverflow.com/questions/2339053/how-do-i-get-column-datatype-in-oracle-with-pl-sql-with-low-privileges) — Cid, Jun 03 '19 at 07:51
You can check [this](https://stackoverflow.com/questions/22962114/get-data-type-of-field-in-select-statement-in-oracle/22962307) too — Cid, Jun 03 '19 at 07:52
have a look at the anstwers here: https://stackoverflow.com/questions/17134293/how-to-view-the-type-of-a-variable-in-pl-sql — hotfix, Jun 03 '19 at 07:52

Kaushik Nayak · Accepted Answer · 2019-06-03T15:08:17.527

This can be achieved using the utilities available in DBMS_SQL package. You don't have to create a table. Use any query you wish to describe as the argument to this generic procedure.

Procedure

 CREATE OR REPLACE PROCEDURE desc_query (p_query VARCHAR2) AS
     v_static_rc SYS_REFCURSOR;
    v_desc      DBMS_SQL.DESC_TAB;
    v_cols      BINARY_INTEGER;
    v_cursor    BINARY_INTEGER;
    v_datatype VARCHAR2(40) ;
 BEGIN

    OPEN v_static_rc FOR  p_query;

    v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(v_static_rc);

    DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);

    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    dbms_output.put_line(rpad('COL',3)
 || ' ' || RPAD('COLUMN_NAME',15) ||'DATA TYPE' );
    FOR i IN 1 .. v_cols LOOP
       DBMS_OUTPUT.PUT( rpad(i,3) || ' ' || RPAD(v_desc(i).col_name,15));

 select 
   decode(v_desc(i).col_type, 1,
              decode(v_desc(i).col_charsetform, 2, 'NVARCHAR2',
                                    'VARCHAR2'),
    2, decode(v_desc(i).col_scale, null, 
                   decode(v_desc(i).col_precision, null,
                                    'NUMBER', 'FLOAT'), 'NUMBER'),
    8, 'LONG',
    9, decode(v_desc(i).col_charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
    12, 'DATE',
    23, 'RAW', 24, 'LONG RAW',
    69, 'ROWID',
    96, decode(v_desc(i).col_charsetform, 2, 'NCHAR', 'CHAR'),
    100, 'BINARY_FLOAT',
    101, 'BINARY_DOUBLE',
    105, 'MLSLABEL',
    106, 'MLSLABEL',
    111, 'REF',
    112, decode(v_desc(i).col_charsetform, 2, 'NCLOB', 'CLOB'),
    113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
    121, 'USER_TYPE',
    122, 'USER_TYPE',
    123, 'USER_TYPE',
    178, 'TIME(' ||v_desc(i).col_scale|| ')',
    179, 'TIME(' ||v_desc(i).col_scale|| ')' || ' WITH TIME ZONE',
    180, 'TIMESTAMP(' ||v_desc(i).col_scale|| ')',
    181, 'TIMESTAMP(' ||v_desc(i).col_scale|| ')'||' WITH TIME ZONE',
    231, 'TIMESTAMP(' ||v_desc(i).col_scale|| ')'||' WITH LOCAL TIME ZONE',
    182, 'INTERVAL YEAR(' ||v_desc(i).col_precision||') TO MONTH',
    183, 'INTERVAL DAY(' ||v_desc(i).col_precision||') TO SECOND(' ||
          v_desc(i).col_scale || ')',
    208, 'UROWID',
    'UNDEFINED') type_name into v_datatype  from dual;

     dbms_output.put_line(v_datatype);

  END LOOP;
  EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR OCCURED: '|| SQLERRM);
END;
/

Now, describe any query by executing the procedure like this (or using EXEC ) :

BEGIN
     desc_query('SELECT 1 as id, CAST ( ''STR'' as VARCHAR2(10) ) as str ,
     SYSTIMESTAMP as tstamp,trunc (SYSTIMESTAMP) as trunc_tstamp 
     from dual'
     );
END;
/

Result

COL COLUMN_NAME    DATA TYPE
1   ID             NUMBER
2   STR            VARCHAR2
3   TSTAMP         TIMESTAMP(6) WITH TIME ZONE
4   TRUNC_TSTAMP   DATE

DEMO

How can I find out the field type of my query

1 Answers1