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)?

@mpettis: I've updated my answer... Did you find your solution in that? — Parth Soni, Jan 09 '13 at 21:03
@codeMaker : Thanks for the answer... I've commented on your answer below. Short is that I'm not getting 'getMetaData' to work, and I'm not sure my DB driver works with that call... — mpettis, Jan 10 '13 at 02:06

score 14 · Answer 1 · 2013-01-09T20:21:38.607

14

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

edited Jan 09 '13 at 20:21

answered Jan 09 '13 at 20:04

Thanks! I'm getting the error: "java.sql.SQLException: statement handle not executed: getMetaData". Upon googling, I found: http://stackoverflow.com/questions/9207073/column-names-for-an-ad-hoc-sql . The driver I am using is `ojdbc5.jar', and I am thinking that the `getMetaData' call is not supported with this. – mpettis Jan 10 '13 at 02:00
@mpettis: then try a newer driver. As I said, with driver version 11.2.0.3 it worked for me. The "5" in the filename name does *not* indicate the driver's version. It's the Java version for which the driver is intended (are you really still using Java 5?). You can find the driver's version by looking into the manifest file (inside the .jar) – Jan 10 '13 at 07:16
Thanks! I'll get a new driver (using Java6), but this is the standard jdbc driver distributed at my company, so I'll lobby for having a newer one installed (ojdbc6.jar). I wasn't convinced that a new driver would do it because upon googling I saw that this functionality worked well for other people with ojdbc5.jar, so I assumed that it would work with me unless I had some other issue I didn't know about. This solution was the one I was trying to make work because it seems the most programatically stable, and doesn't rely on a trick. But as it stands, it didn't work. – mpettis Jan 10 '13 at 19:00
1

Again: the 5 or 6 does **not** denote the driver version! ojdbc6.jar could very well be the same driver version as ojdbc5.jar – Jan 10 '13 at 19:03

score 2 · Accepted Answer · answered Jan 09 '13 at 20:10

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

answered Jan 09 '13 at 20:10

Frank Schmitt

30,195
12
73
107

This works, and I've only tested this on small, cheap queries. Not being familiar with Oracle's query analyzer, I just want to confirm that the section isn't actually executed and discarded due to the 'where 1=0' section. So, is it true that the won't actually retrieve results and get them discarded in the outer 'where' statement? – mpettis Jan 10 '13 at 02:36
I'm taking this solution because it seems to work independent of the underlying jdbc driver. Thank you Frank and everybody who answered! – mpettis Jan 10 '13 at 02:50

Vikram · Answer 3 · 2013-01-09T20:23:27.280

-1

    SELECT
      COLUMN_NAME
   FROM
      ALL_TAB_COLUMNS
   WHERE
      TABLE_NAME ='tableName';

is probably what you meant.. however it is still a query...just that instead of querying application tables you are querying special tables

same with answers that request metadata

edited Jan 09 '13 at 20:23

answered Jan 09 '13 at 20:10

Vikram

4,162
8
43
65

Oracle has no view named `SYSOBJECTS` and it wouldn't return the columns of a JOIN anyway. – Jan 09 '13 at 20:12
Thanks Vikram. However, a_horse_with_no_name is correct -- this only works if I want to get column names from an actual table, but I want to get them from the result set of a general query, where result set column names might be made up on the fly or might come from multiple tables. Thank you for responding though! – mpettis Jan 10 '13 at 02:40
yw @mpettis! got it now :-) – Vikram Jan 10 '13 at 16:07

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

3 Answers3