Returning just column names of ResultSet without actually performing the query (Oracle and Java)

Question

I'm wondering if there is a way to return the column names of a results set (Oracle database, in Java) that would be generated IF I actually executed a query. For example, say I had SQL looking something like this:

select * from <complex table join>;

Is there a way to send this query to oracle and have it tell me just what the column names are on the result set it will return WITHOUT actually performing the query (because it is expensive)?

Answer 1

I think using a PreparedStatement could 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 that fails you could simply append a where 1=0 to the query and execute it then. At least it will not return all the rows then (possibly also using Statement.setMaxRows() as well, just to be sure.

A final (yet pretty complicated) option would be to use dbms_sql to open, prepare and describe the statement. See the manual for details: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sql.htm

Answer 2

You could try wrapping the query in an outer select and adding where 1=0 to prevent it from fetching any rows:

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