?
Solved

GET SEQUENCE NAME FOR A TABLE.

Posted on 2004-11-16
8
Medium Priority
?
9,409 Views
Last Modified: 2013-12-11
hello everyone.
i have a question regarding to get the sequence name pertaining to a table.
say for example i have a table "Employee" with columns "eid",ename,ecity.
if eid is the primary key and i made it a auto increment sequence with name of sequence as "eid_seq"

how would i get the sequence name eid_seq using a query.

in general,if  i pass in a tablename and tableowner i should get sequence name for that table.

i hvae tried using user_dependencies and to get triggers and associate triggers with sequences.but my problem was what if you have more than 1 trigger?? how would you deal that??

is there any other way to do it?

any help is appreciated.
thanks
Pgnath.
0
Comment
Question by:pgnath
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
ishando earned 400 total points
ID: 12599394
There is no direct relationship between a table and a sequence built into the database. Any relationship that exists between them is created by you.

One approach would be to have a standard naming convention for you sequences, e.g. <colname>_seq or <tab>_<col>_seq so that you can easily generate the sequence name programatically if you need to.

An alternative would be to add a comment to the sequenced column, which is the name of the associated sequence, and then you can retrieve the sequence name from the all_col_comments view.

0
 

Author Comment

by:pgnath
ID: 12599440
hi ishando
i do have the naming conventions ,and i did think of the process.but i was thinking if there is any generic way to do this.i do appreciate your suggestion.

as i said there is other way to approach to use user_dependency and get triggers associated and the find the sequence using the trigger.

infact here is my query
SELECT REFERENCED_NAME into tblSeq1
FROM USER_DEPENDENCIES
WHERE NAME = (SELECT NAME FROM USER_DEPENDENCIES A,USER_TRIGGERS B
WHERE TYPE = 'TRIGGER' and B.TRIGGERING_EVENT='INSERT' AND REFERENCED_OWNER = tblOwner AND REFERENCED_NAME = tblName)
AND REFERENCED_OWNER = tblOwner AND REFERENCED_TYPE = 'SEQUENCE';

this wont work if u have more than 1 trigger in a table.so u need to loop through then find out sequence associated.

thanks
Pgnath.
0
 
LVL 15

Expert Comment

by:ishando
ID: 12599933
but do you have multiple sequences per table?

if not then you can simply do:

SELECT distinct REFERENCED_NAME into tblSeq1
FROM USER_DEPENDENCIES
....


0
 
LVL 4

Assisted Solution

by:alexnuijten
alexnuijten earned 400 total points
ID: 12783271
Why would you want to do it a "generic" way? There are going to be exceptions whatever you can come up with in a generic way.

Simply create a before insert row trigger to fill your Primary Key with the next-value from the sequence and you're set. You can make a template for this type of trigger and use this over and over whenever you create a new table. You can also create a "Generator" to create the trigger for you..

Alex
0
 

Author Comment

by:pgnath
ID: 13046112
i didnt get any defnitive answer for my question.
ok i would share teh points for the two people who gave their efforts
thanks
pgnath
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question