Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Hitesh Patel Patel
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sudheer Bhat wrote:In Oracle there is nothing on the column metadata that says whether it will be incremented using SEQUENCE or not.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic