Oracle 10g retrieves all selected columns and data type from a single select statement

Question

I want to get all selected columns from a single select statement without executing execution. A query can use a join or a join, then a query can consist of more than just one table, so I can’t just use a statement

select column_name, data_type from user_tab_cols where table_name = 'my_table_name' 

to get the result.

for instance

input:

 select db1.* from (select dept_code, vital_signs, units, log_time from table1 where dept_code = '111' union select 'hl06' as dept_code, vital_signs, units, log_time from table2 where isdefault = 1 and vital_signs not in (select vital_signs from Ward_Vs_Vital_Signs where dept_code = '111')) db1, table3 db2 where db1.vital_signs = db2.vital_signs(+) order by db2.serial_no 

exit:

 column_name | data_type ------------------------- dept_code | VARCHAR vital_signs | NUMBER units | VARCHAR log_time | DATE 

My question is: how can I parse the query and ask the database to tell me the columns and data types that will be returned without executing the statement? Thanks!

PS: I am using Oracle 10g.

Answer 1

There is no easy way to do this using a single select statement, however you can explore the dbms_sql ( DOC ) approach.

  14:32:26 SYSTEM@dwal > ed Wrote file S:\spool\dwal\BUFFER_SYSTEM_329.sql 1 DECLARE 2 c NUMBER; 3 d NUMBER; 4 col_cnt INTEGER; 5 rec_tab DBMS_SQL.DESC_TAB; 6 col_num NUMBER; 7 PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS 8 BEGIN 9 DBMS_OUTPUT.NEW_LINE; 10 DBMS_OUTPUT.PUT_LINE('col_name = ' || rec.col_name); 11 DBMS_OUTPUT.PUT_LINE('col_type = ' || rec.col_type); 12 END; 13 BEGIN 14 c := DBMS_SQL.OPEN_CURSOR; 15 DBMS_SQL.PARSE(c, 'SELECT dummy, 12345 num, sysdate dt FROM dual', DBMS_SQL.NATIVE); 16 d := DBMS_SQL.EXECUTE(c); 17 DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab); 18 col_num := rec_tab.first; 19 IF (col_num IS NOT NULL) THEN 20 for i in 1 .. col_cnt LOOP 21 print_rec(rec_tab(i)); 22 END LOOP; 23 END IF; 24 DBMS_SQL.CLOSE_CURSOR(c); 25* END; 14:32:46 SYSTEM@dwal > / col_name = DUMMY col_type = 1 col_name = NUM col_type = 2 col_name = DT col_type = 12 PL/SQL procedure successfully completed. 

Note the col_type numeric value above - this is a type code that matches the actual data type. The mapping is described here: http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements001.htm#i54330

Answer 2

I believe that there is no way to find the data type in the query. Instead, you should make a create table as or create view as query with a query and then use desc or some data dictionaries, such as user_tab_cols.

Answer 3

In Java, you can use PreparedStatement and ResultSetMetaData, which it provides as follows:

 PreparedStatement ps = conn.prepareStatement(yourSqlStatement); ResultSetMetaData rsm = ps.getMetaData(); for(int i = 0; i<rsm.getColumnCount();i++) { int index = i+1; System.out.println(rsm.getColumnName(index) + ": " + rsm.getColumnTypeName(index)); } 

Answer 4

Some questions like this have been asked before. In any case, I can post the answer. Maybe something like this:

 SELECT column_name, data_type FROM user_tab_cols WHERE table_name = 'your_table_name'