CODEWITHSUNDEEP
sqloracle-database
Error_2646
Error_2646

Reputation: 3791

Return data type of expression in Oracle

Is there a way to get the data type of an expression in Oracle?

For example, in Teradata I can write 

SELECT TYPE(4 + 4);

and get Integer. But the same doesn't work in Oracle

SELECT TYPE(4 + 4)
  FROM dual;

Returns an error. I know I can look at the table description, but this way would come in handy if it's possible.

Upvotes: 6

Views: 4842

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

One way is to materialize it:

CREATE TABLE test_temp AS
SELECT 4 + 4 AS result
FROM dual
WHERE 1=2;

SELECT *
FROM ALL_TAB_COLS
WHERE table_name = 'TEST_TEMP';

DROP TABLE test_temp PURGE;

DBFiddle Demo

If you have TOAD for Oracle you could use:

Editor -> Describe(Parse) Select Query

or highlight query and press: CTRL+F9

From Toad for Oracle 12.11 - Getting Started Guide:

Use the Describe (Parse) Select Query function to see what columns would be returned if the query were executed. This is useful for tuning a LONG query before it is executed.

Upvotes: 4

user5683823
user5683823

Reputation:

There is, unfortunately, no TYPE() function in Oracle.

One approach is to use the DUMP function, as in:

select dump(4+4) from dual;

DUMP(4+4)         
------------------
Typ=2 Len=2: 193,9

This shows the data type as 2. Then you need to check the documentation to see what that means. For example:

https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#BABCGCHG

Here you can see that Code = 2 corresponds to data type NUMBER.

Still a pain in the butt, but at least there is a way.

Alas, Oracle doesn't seem to offer a table (code, data_type) in the db, but you can build one yourself - then you can combine it with DUMP so you can always get the data type in a single SELECT.

Upvotes: 10

The Impaler
The Impaler

Reputation: 48780

Just add to @Iad2025's response, that I think is good, some drivers offer you metadata of the "resultset" of a query.

This metadata is theoretically available in any JDBC driver, so you can retrieve it right after the "prepare statement" operation without actually executing the query.

However -- and this is a big one -- not all drivers actually implement this functionality. You would need to test your case and see.

Upvotes: 1

Related Questions