2

I am developing a Online Registration System using JSP and Oracle where I need to give every successful registrant a unique registration number sequentially. For this I want to adopt the NEXTVAL facility of Oracle, but I am in a dilemma for which policy I would adopt.

Policy 1: First I will store the NEXTVAL of a sequence in the following way. 

      select seq_form.nextval slno from dual;

Then I will store the slno into a variable say 

      int slno;

Now I will use the slno for in the insert query when the user finally submits the form, like 

    insert into members(registration_no, name,...) values(slno, name, ...);

Here the registration_no is primary key.

Policy 2: In my second policy, I will run the insert the query first 

  insert into members(registration_no, name,...) values(seq_form.nextval, name, ...);

fetch the last inserted ID like 

   select seq_form.currval slno from dual;

And then store the same in some variable say

   int slno;

And use the same to show it to the registrant. Now I can't come to a conclusion which is better in terms of safety and efficiency. Here, I must make it clear that, in both the cases, my intension is to give the user a unique sequential number after successful submission of the form and by safety I meant to say that the user should get the ID respect to his/her own web session. Please help me.

samlancer
  • 141
  • 2
  • 13
  • How about using RETURNING clause? If you are on 12c, you could simply use the Identity column. I already answered a similar question here http://stackoverflow.com/a/28472515/3989608 – Lalit Kumar B Apr 07 '15 at 05:47

3 Answers3

2

I suggest you do it slightly differently:

  1. Create a BEFORE INSERT trigger on your MEMBERS table. Set REGISTRATION_NO column to SEQ_FORM.NEXTVAL in the trigger:

    CREATE OR REPLACE TRIGGER MEMBERS_BI BEFORE INSERT ON MEMBERS FOR EACH ROW BEGIN :NEW.REGISTRATION_NO := SEQ_FORM.NEXTVAL; END MEMBERS_BI;

  2. Do NOT put REGISTRATION_NO into the column list in your INSERT statement - it will be set by the trigger so there's no need to supply any value for it:

  3. Use the RETURNING clause as part of the INSERT statement to get back the value put into REGISTRATION_NO by the trigger:

    INSERT INTO MEMBERS (NAME, ...) VALUES ('Fred', ...) RETURNING REGISTRATION_NO INTO some_parameter

halfer
  • 19,824
  • 17
  • 99
  • 186
Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
1

If you are using oracle 12, you can use identity column. Then use RETURNING to get auto-generated value back.

vav
  • 4,584
  • 2
  • 19
  • 39
0

Go with the policy 2. Because you cant always be sure that the insert query will be successful. If the insert fails, your oracle sequence has been rolled forward and you lose a sequence.

it is a better idea to insert and then later fetch it into a variable.

Varun Rao
  • 781
  • 1
  • 10
  • 31
  • It doesn't matter if you lose a value; you have a theoretical limit of 1 duodecillion - 1... – Ben Apr 07 '15 at 07:40