How do I get list of fields participates in SQL for Oracle?

Question

I am writing a small application that user can type in the SQL and get the result. I noticed that my application cannot deal with XMLTYPE columns (I am using ADO). Is it possible to “ask” oracle what are the fields that participate in the select part of the SQL, so I can replace XMLTYPE fields with “getclobval()” function on the fly?

Answer 1

You can determine the datatype of a column by querying the *_TAB_COLS dictionary views. However, these are based on a table, not a query. To do this "on-the-fly" will be very difficult, as you'd need to parse the incoming query (in your code), figure out which table each column in the query belongs to and issue the following:

select data_type 
from   all_tab_cols
where  table_name = :table
and    column_name = :column;

Then update the query as appropriate and submit that for execution. This will be very difficult to write and very likely to be slow.

You can do what you want by creating a view over tables with XMLTYPEs, doing the conversion in the view and getting users to execute queries against the view instead of the underlying tables:

create table tab (x xmltype, y integer);

create or replace view tab_v as
  select t.x.getClobVal() x, y 
  from   tab t;

select table_name, data_type from all_tab_cols
where  column_name = 'X'
and    table_name like 'TAB%';

TABLE_NAME                     DATA_TYPE                                                                                                
------------------------------ --------
TAB                            XMLTYPE                                                                                                    
TAB_V                          CLOB