Get data type of field in select statement in ORACLE

Question

Can I get data types of each column I selected instead of the values  using a select statement   FOR EXAMPLE   SELECT a name  a surname  b ordernum  FROM customer a JOIN orders b ON a id   b id   and result should be like this  name      NVARCHAR 100  surname   NVARCHAR 100  ordernum  INTEGER   or it can be in row like this  it isn t important   name               surname        ordernum NVARCHAR 100     NVARCHAR 100      INTEGER   Thanks

User · Answer

I found a not-very-intuitive way to do this by using DUMP()

SELECT DUMP(A.NAME), 
       DUMP(A.surname), 
       DUMP(B.ordernum) 
FROM   customer A 
       JOIN orders B 
         ON A.id = B.id

It will return something like:

'Typ=1 Len=2: 0,48' for each column.

Type=1 means VARCHAR2/NVARCHAR2
Type=2 means NUMBER/FLOAT
Type=12 means DATE, etc.

You can refer to this oracle doc for information Datatype Code
or this for a simple mapping Oracle Type Code Mappings

User · Answer

Also  if you have Toad for Oracle  you can highlight the statement and press CTRL   F9 and you ll get a nice view of column and their datatypes

User · Answer

You can query the all tab columns view in the database   SELECT  table name  column name  data type  data length FROM all tab columns where table name    CUSTOMER

User · Answer

you can use the DBMS SQL DESCRIBE COLUMNS2      SET SERVEROUTPUT ON  DECLARE     STMT CLOB      CUR NUMBER      COLCNT NUMBER      IDX NUMBER      COLDESC DBMS SQL DESC TAB2  BEGIN     CUR    DBMS SQL OPEN CURSOR      STMT     SELECT  object name   to char object id   created FROM    DBA OBJECTS where rownum lt 10        SYS DBMS SQL PARSE CUR  STMT  DBMS SQL NATIVE       DBMS SQL DESCRIBE COLUMNS2 CUR  COLCNT  COLDESC       DBMS OUTPUT PUT LINE  Statement       STMT       FOR IDX IN 1    COLCNT     LOOP         CASE COLDESC IDX  col type         WHEN 2 THEN             DBMS OUTPUT PUT LINE        TO CHAR IDX        NUMBER            WHEN 12 THEN             DBMS OUTPUT PUT LINE        TO CHAR IDX        DATE            WHEN 180 THEN             DBMS OUTPUT PUT LINE        TO CHAR IDX        TIMESTAMP            WHEN 1 THEN             DBMS OUTPUT PUT LINE        TO CHAR IDX        VARCHAR         COLDESC IDX  col max len           WHEN 9 THEN             DBMS OUTPUT PUT LINE        TO CHAR IDX        VARCHAR2            -- Insert more cases if you need them         ELSE             DBMS OUTPUT PUT LINE        TO CHAR IDX        OTHERS       TO CHAR COLDESC IDX  col type                   END CASE      END LOOP      SYS DBMS SQL CLOSE CURSOR CUR   EXCEPTION      WHEN OTHERS THEN         DBMS OUTPUT PUT LINE SQLERRM SQLCODE               DBMS UTILITY FORMAT ERROR BACKTRACE           SYS DBMS SQL CLOSE CURSOR CUR   END      full example in the below url  https   www ibm com support knowledgecenter sk SSEPGG 9 7 0 com ibm db2 luw sql rtn doc doc r0055146 html

User · Answer

I came into the same situation  As a workaround  I just created a view  If you have privileges  and described it and dropped it later

User · Answer

I usually create a view and use the DESC command   CREATE VIEW tmp view AS  SELECT        a name       a surname       b ordernum  FROM customer a   JOIN orders b     ON a id   b id   Then  the DESC command will show the type of each field   DESC tmp view

User · Answer

If you don t have privileges to create a view in Oracle  a  hack  around it to use MS Access  -   In MS Access  create a pass through query with your sql  but add where clause to just select 1 record   create a select query from the view  very important   selecting all    then create a make table from the select query  When this runs it will create a table with one record  all the data types should  match  oracle  i e  Passthrough --  Select --  MakeTable --  Table  I am sure there are other better ways  but if you have limited tools and privileges this will work

[sql] Get data type of field in select statement in ORACLE

Examples related to sql

Examples related to oracle

Examples related to function

Examples related to types