Re: Determining the specific sequence number generated on an insert statement

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 08 Dec 1998 20:13:09 GMT
Message-ID: <36728550.24451960@inet16.us.oracle.com>

On 8 Dec 1998 18:00:56 GMT, lpoon_at_Glue.umd.edu (Leon Poon) wrote:

<snip>

>CREATE TRIGGER AB_trigger INSTEAD OF INSERT ON ab_view
>FOR EACH ROW
>DECLARE
> aid number;
>BEGIN
> INSERT INTO A (A_column2) values (:new.A_column2);
>
> /* How do I find out what the exact value of A_ID is
> in the above insert statement? */
>
> aid := A_seq.currval;

This will work just fine. Currval will be the last nextval that your session retrieved. It won't matter if two inserts are done at the same time.

>
> /* This value might not correspond to the exact value
> in the above insert statement if this is a highly
> concurrent system, no? */
>
> INSERT INTO B (A_ID,B_column2) values (aid, :new.B_Column2);
> /* It is assumed that B_ID is generated using another sequnece B_seq */
>
>END;
>
>
>So, what is the best way to ensure that the value inserted into
>B.A_ID corresponds exactly to the value A.A_ID generated using
>a sequence in the first INSERT statement?
>
>Any comments or suggestsions would be greatly appreciated.
>If possible, please cc your reply to lpoon_at_isr.umd.edu.

eg.

• In session 1 I do

SQL> select my_seq.nextval from dual;

   NEXTVAL

---

         8

• In session 2 I do

SQL> declare
  2 n number;
  3 begin
  4 for i in 1 .. 100 loop
  5 select my_seq.nextval into n from dual;   6 end loop;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL> select my_seq.currval from dual;

   CURRVAL

---

       108

• Back in session 1

SQL> select my_seq.currval from dual;

   CURRVAL

---

         8

As you can see, they both have a different currval. Oracle will keep track of each sessions copy of its currval, even for the same sequence. If session 1 was to nextval it would get 109.

SQL> select my_seq.nextval from dual;

   NEXTVAL

---

       109

So what you are doing will work, even in a highly current system.

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Dec 08 1998 - 14:13:09 CST