Home » SQL & PL/SQL » SQL & PL/SQL » Relation between sequence and column (not the autoincrement problem)
Relation between sequence and column (not the autoincrement problem) [message #193272] Fri, 15 September 2006 09:29 Go to next message
Xeneize
Messages: 6
Registered: September 2006
Junior Member
Hi, I need to make a function, which receive
1. Table name
2. Column name

And it give me the sequence name of the sequence that i have made for autoincrement that field (i did it with a trigger).

In postgres i can have that relation, I did't find the way to solve this in oracle.
Re: Relation between sequence and column (not the autoincrement problem) [message #193276 is a reply to message #193272] Fri, 15 September 2006 09:39 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
there is nothing built in to do this. you would need to write code that would look for triggers on the table (before insert, update triggers, for each row only), then parse out the trigger code, looking for the sequence used to set the value of :new.column. this could be quite difficult to automate, since not everyone writes trigger code the same way, and you'd really be writing a pl/sql and sql parser.

hate to say it, but I think you're up the creek
Re: Relation between sequence and column (not the autoincrement problem) [message #193292 is a reply to message #193276] Fri, 15 September 2006 10:15 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

Few minutes before Tom Kyte gave a answer to this
http://asktom.oracle.com/pls/ask/f?p=4950:8:10139059792190785289::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:72258229973679


Regards
Re: Relation between sequence and column (not the autoincrement problem) [message #193301 is a reply to message #193292] Fri, 15 September 2006 10:35 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
unfortunately, even using dba_dependencies to find the referenced sequence is not 100% accurate. some people might put the code that uses the sequence into a procedure/package, and call that from the trigger (especially anyone that listens to fuerenstien). some people don't understand when/why to use dynamic sql, so they might reference the sequence with exec immed in the trigger, so it won't show as a reference. and then there's the people that populate more than just the single pk via a sequence from within the trigger, so you won't know which referenced sequence is the one you want. the only way to know for sure would be to build a parser.

that said, if you KNOW that all of your pk's are populated via sequences in the trigger itself (no nested calls), and there is only on sequence used per trigger, and there's no dynamic sql (you must have tighter development standards/review than I've ever seen), then find the name of the before insert/update for each row trigger, and check dba_dependencies for referenced sequences.

good luck
Re: Relation between sequence and column (not the autoincrement problem) [message #193326 is a reply to message #193272] Fri, 15 September 2006 13:00 Go to previous message
Xeneize
Messages: 6
Registered: September 2006
Junior Member
Hi, thankz for the answers, the link to tom's page was a reply to my question there.
In my framework (i use this function get_sequence in php) and because of the design of my database i can use the dba_dependencies, so, one problem less in my migration proccess from Postgres to Oracle.
Previous Topic: select statement help!
Next Topic: Need a SQL Expert to solve my SQL dilema
Goto Forum:
  


Current Time: Sat Dec 10 03:26:02 CST 2016

Total time taken to generate the page: 0.05602 seconds