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
– Parth Soni, Commented Jan 9, 2013 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
– mpettis, Commented Jan 10, 2013 at 2:06

score 14 · Accepted Answer · 2013-01-09 20:21:38Z

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 9, 2013 at 20:21

answered Jan 9, 2013 at 20:04

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

mpettis Over a year ago

Thanks! I'm getting the error: "java.sql.SQLException: statement handle not executed: getMetaData". Upon googling, I found: stackoverflow.com/questions/9207073/… . The driver I am using is ojdbc5.jar', and I am thinking that the getMetaData' call is not supported with this.

user330315 Over a year ago

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

mpettis Over a year ago

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.

user330315 Over a year ago

Again: the 5 or 6 does not denote the driver version! ojdbc6.jar could very well be the same driver version as ojdbc5.jar

Frank Schmitt · Accepted Answer · 2013-01-09 20:10:11Z

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 9, 2013 at 20:10

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

2 Comments

mpettis Over a year ago

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 <your query here> section isn't actually executed and discarded due to the 'where 1=0' section. So, is it true that the <your query here> won't actually retrieve results and get them discarded in the outer 'where' statement?

mpettis Over a year ago

I'm taking this solution because it seems to work independent of the underlying jdbc driver. Thank you Frank and everybody who answered!

Vikram · Accepted Answer · 2013-01-09 20:23:27Z

-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 9, 2013 at 20:23

answered Jan 9, 2013 at 20:10

Vikram

4,1828 gold badges47 silver badges71 bronze badges

3 Comments

user330315 Over a year ago

Oracle has no view named SYSOBJECTS and it wouldn't return the columns of a JOIN anyway.

mpettis Over a year ago

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!

Vikram Over a year ago

yw @mpettis! got it now :-)

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

3 Comments

Linked

Related