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
junaidsystems
Messages: 30
Registered: June 2006
Member
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.


Example:
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
Littlefoot
Messages: 20901
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 Dec 08 20:07:30 CST 2016

Total time taken to generate the page: 0.09247 seconds