CODEWITHSUNDEEP
javasqloracle-databasejdbc
Laxmikant Dange
Laxmikant Dange

Reputation: 7688

List of columns in sql query

I have a query using various joins, and I just need the list of columns which are returned by this query. I done it in java, by asking only one row with rownum=1 and getting column name for value.The problem is if there is no data returned by that query.

For ex.

select * from something

and if there is any data returning by this query then it will return col1,col2,col3. But if there is no data returned by this query, then it will throw error.

What I need is

Is there any way that I can run 

desc (select * from something)

or similar to get list of columns returned by query.

It can be in sql or JAVA. Both methods are acceptable.

In my application, user passes the query, and I can add wrapper to the query but I cant modify it totally.

The flow of application is

query from user -> execute by java and get one row -> return list of columns in the result set.

Upvotes: 4

Views: 780

Answers (3)

karim mohsen
karim mohsen

Reputation: 2254

The Oracle equivalent for information_schema.COLUMNS is USER_TAB_COLS for tables owned by the current user, ALL_TAB_COLS or DBA_TAB_COLS for tables owned by all users.

Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.

Providing the schema/username would be of use if you want to query ALL_TAB_COLS or DBA_TAB_COLS for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:

String sqlStr= "

   SELECT column_name

   FROM   all_tab_cols

   WHERE  table_name = 'users'

   AND owner = ' || +_db+ || '

   AND column_name NOT IN ( 'password', 'version', 'id' )
   "

Note that with this approach, you risk SQL injection.

Upvotes: 0

Keval
Keval

Reputation: 1859

you can use ResultSetMetaData of resultset

for example :

 ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2"); 
 ResultSetMetaData rsmd = rs.getMetaData();  
 int countOfColumns = rsmd.getColumnCount();
 for(int i = 1; i <= countOfColumns ; i++  )
 System.out.println(rsmd.getColumnName(i));

Upvotes: 6

davegreen100
davegreen100

Reputation: 2115

you could maybe convert your query to a view, you can then see the columns in the view by querying user_tab_columns

select * from user_tab_columns

Upvotes: 0

Related Questions