This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes In Oracle, write SELECT query that will return metadata information for the oracle table's columns? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "In Oracle, write SELECT query that will return metadata information for the oracle table Watch "In Oracle, write SELECT query that will return metadata information for the oracle table New topic
Author

In Oracle, write SELECT query that will return metadata information for the oracle table's columns?

Hitesh Patel Patel
Greenhorn

Joined: Jul 23, 2009
Posts: 27
I have been searching on internet about as mentioned in the PROBLEM SUMMARY but no luck.

I like to know how to write SELECT query that will return metadata information for the oracle table's columns?

I will pass TABLE name as a parameter and then SELECT query should return following metadata information.

1) COLUMN NAME
2) COLUMN TYPE (number or varcahar2 etc...)
3) Identity (true / false if column is primary key & identity sequence is set on it)

Can you please help on this ASAP you can?
Thanks in advance.

Hitesh Patel
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
Check out the data dictionary views all_tables,all_tab_cols and all_constraints. Do make sure that you have SELECT privilege on the data dictionary objects (alternatively you can use dba_tables, dba_tab_cols and dba_constraints data dictionary views).
Hitesh Patel Patel
Greenhorn

Joined: Jul 23, 2009
Posts: 27
Hi Sudhir,

Thanks for your reply and I even tried below way. The only thing I am not able to get it "How to know particular table column got identity / sequence on it?"

SELECT cols.table_name, cols.column_name, cols.position
, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE lower(cols.table_name) = 'status'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Can you please help?

Thanks,
Hitesh
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
I don't think its possible to tie the sequence to a particular column in Oracle. Which means, using a query on data dictionary views, you cannot determine whether a particular column gets its value from sequence or not.
Hitesh Patel Patel
Greenhorn

Joined: Jul 23, 2009
Posts: 27
Thanks again Sudhir.

That's fine but is there any other alternative?
I mean what I have to achieve is : one of my class method is inserting data into oracle table AND I have to skip column that got identity/sequence on it.

I know in SQL SERVER when you query metadata it just returns like as below.
For example: there is a USERS table and its primary key (user_id) has the identity set so then when we query metadata it will return something like.
-> column_name = user_id
-> type_name = numeric identity

so then by identity word I can skip in my class method.
I am just looking for same thing in oracle.

Can you still suggest something?

Thanks - Hitesh
Hitesh Patel Patel
Greenhorn

Joined: Jul 23, 2009
Posts: 27
I forgot to write that class method INSERT logic has the dynamic SQL script so that is can be shared among all tables.
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
In oracle there is nothing on the column metadata that says whether it will be incremented using SEQUENCE or not. The only way for you to achieve your goal is to define a sequence to be used across all the tables in your application, then read the metadata from the DB, use sequence.nextval for the columns which are defined as primary keys or have a unique constraint defined on the columns.

Or have a before insert trigger on all the tables, and populate the primary key/unique constraint columns using sequence in the trigger.
Hitesh Patel Patel
Greenhorn

Joined: Jul 23, 2009
Posts: 27
Thanks for your reply.
Well, I already have sequence and before trigger for every table requires IDENTITY column.

Let me reiterate, by querying metadata I like to find out which column of the table will be incremented automatically by the oracle so I can skip it in my class method logic.
And that is the reason before I do actual INSERT DDL I like to find all columns metadata information like (column name, column type (identity or not) etc..) for the specific table.

Thanks,
Hitesh
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
Sudheer Bhat wrote:In Oracle there is nothing on the column metadata that says whether it will be incremented using SEQUENCE or not.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: In Oracle, write SELECT query that will return metadata information for the oracle table's columns?
 
Similar Threads
java.sql.SQLException: Invalid column index
FORWARD_ONLY Resultset
Problem in displaying result set value
why do I get this exception
Where does information about columns get stored??