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
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'
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. :)
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