Home » SQL & PL/SQL » SQL & PL/SQL » Creating sequences for each table in schema???
Creating sequences for each table in schema??? [message #186735] Wed, 09 August 2006 04:54 Go to next message
Messages: 30
Registered: June 2006
I need to write a stored procedure which should match the followings:

- It will Create sequences for each of the table in the schema, owned by the user and excluding , with the same name appended with _seq

- sequence number must start with the existing primary key's maximum value+1 in the table, where pK must be a number

- If there is no primary key, or PK is not number type in the table then the sequence must not be created for this tbale.

If the procedure is executed in the scott schema and EMP and DEPT table encounters then the following sequences will be created

EMP_SEQ strating from the maximum emopno+1
DEPT_SEQ starting from the maximim deptno+1

Thanx in Advance.
Re: Creating sequences for each table in schema??? [message #186741 is a reply to message #186735] Wed, 09 August 2006 05:16 Go to previous message
Messages: 21127
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why would you ever wanted to bother with something like that? What's wrong with one sequence for entire schema? I guess it will be used for some kind of ID purposes; if so, it really shouldn't matter "which value it has", but only "is it unique" (and yes, it will be).

If you insist on this, write a PL/SQL procedure; loop through all tables and primary key columns, find its maximum value and dynamically create a sequence.

Are you going to use sequence(s) in database triggers? You'll have to pay attention which sequence you use. Be careful while copy-pasting existing trigger code into another; you might end with using the same sequence for more than one table (but your requirement prohibits that).

Really, why wouldn't you have one sequence?
Previous Topic: Timestamp date format conversion
Next Topic: SQL query rewrite, dimensions/datawarehouse with business objects
Goto Forum:

Current Time: Thu Jul 20 15:47:09 CDT 2017

Total time taken to generate the page: 0.12091 seconds