| Need help on how to deal with sequence number [message #38349] |
Fri, 12 April 2002 14:01  |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
Hello everyone,
I need insert data through the web. I have two tables (surgery, test). What I need to do is to have the user input data into the surgery table then the test table. In surgery table I have a primary key column (surgeryid), which is a sequence number. After the user input data into the first table, he need input data into the test table, in the test table, there is a foreign key column which reference to the surgeryid column in the surgery table. My question is how can I get surgeryid from the first table and insert it into the second table. In my insert statement to the test table, I tried to use suid.currval but I got an error message that told me the suid.curr had not been defined in the current session. Do somebody have an answer? Thanks in advance.
|
|
|
|
| Re: Need help on how to deal with sequence number [message #38350 is a reply to message #38349] |
Fri, 12 April 2002 14:31  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
One option would be to use the RETURNING clause on your surgery insert to return the value generated by the sequence. You can hold this value in a variable and then use it for your test insert.
insert into surgery (surgeryid, ...)
values (suid.nextval, ...) -- or maybe the sequence is handled by a trigger
returning surgeryid into v_surgeryid;
As far as using suid.currval, you can reference it in the test insert as long as that insert occurs in the same session (i.e., the session is persistant across the surgery and test inserts).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
