Returning only ResultSet column names without querying (Oracle and Java)

Question

I am wondering if there is a way to return the column names of the result set (Oracle database in Java) that will be generated if I really executed the query. For example, let's say that SQL looked something like this:

select * from <complex table join>; 

Is there a way to send this request to oracle and tell me that it is the column names that are in the result set, it will return WITHOUT the actual execution of the request (because it is expensive)?

Answer 1

You can try to wrap the request in an external select and add where 1 = 0 so that it does not retrieve rows:

  SELECT * from ( <your query here> ) WHERE 1=0 

Answer 2

I think using PreparedStatement might work:

 PreparedStatement stmt = connection.prepareStatement("select ..."); ResultSetMetaData meta = stmt.getMetaData(); for (int col=0; col < meta.getColumnCount(); col++) { System.out.println("Column: " + meta.getColumnName(col + 1)); } 

(Edit): I tried this with Oracle 11.2 and driver version 11.2.0.3, and it works.

If this fails, you can simply add where 1=0 to the request and execute it. At the very least, it will not return all rows then (possibly also using Statement.setMaxRows() , just to be sure.

The last (but rather complicated) option would be to use dbms_sql to open, prepare and describe the instruction. See the manual for more details: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sql.htm

Answer 3

  SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME ='tableName'; 

probably what you had in mind .. however this is still a query ... just, instead of querying application tables, you are querying special tables

same with answers that request metadata