Get data type of field in select statement in ORACLE

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

sql oracle function types

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'

sql oracle function types

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

CodeHunter