returning output parameter from Oracle stored proc

Question

I am trying to set the value of the output parameter thirdPartyId, but I am getting an error saying missing or invalid option at the set thirdPartyId statement.

PROCEDURE usp_insert_user(  userType VARCHAR2,
                                logonId VARCHAR2,
                                title VARCHAR2,
                                firstName VARCHAR2,
                                middleName VARCHAR2,
                                lastName VARCHAR2,
                                comments VARCHAR2,
                                thirdPartyId OUT number) AS
      begin
        set thirdPartyId := select max(third_party_id) + 1 from third_party_user_temp;
        insert into THIRD_PARTY_USER_TEMP
            (Third_party_id,web_logon_id,prsn_type_cd,prsn_title_nm,
            prsn_first_nm,prsn_mdl_nm,prsn_last_nm,addtnl_third_party_txt)
            VALUES(thirdPartyId,logonId,upper(userType),title,
            firstName,middleName,lastName,comments)
        ;
    end usp_insert_user;

What is the right way to do this?

Thanks!

Update: Is this any safer?

insert into THIRD_PARTY_USER_TEMP
        (Third_party_id,web_logon_id,prsn_type_cd,prsn_title_nm,
        prsn_first_nm,prsn_mdl_nm,prsn_last_nm,addtnl_third_party_txt)
        VALUES((select max(third_party_id) + 1 from third_party_user_temp),logonId,upper(userType),title,
        firstName,middleName,lastName,comments)
        returning third_party_id into thirdPartyId

Alex Poole · Accepted Answer · 2011-03-01T13:02:35.190

3

You can do this:

select max(third_party_id) + 1 into thirdPartyId from third_party_user_temp;

This may be subject to contention problems if two people can run this at the same time - both could end up with the same new thirdPartyId. You can look at sequences to avoid this.

If you define a sequence called, say, thirdPartyIdSeq, you can do this:

PROCEDURE usp_insert_user(  userType VARCHAR2,
                                logonId VARCHAR2,
                                title VARCHAR2,
                                firstName VARCHAR2,
                                middleName VARCHAR2,
                                lastName VARCHAR2,
                                comments VARCHAR2,
                                thirdPartyId OUT number) AS
      begin
        insert into THIRD_PARTY_USER_TEMP
            (Third_party_id,web_logon_id,prsn_type_cd,prsn_title_nm,
            prsn_first_nm,prsn_mdl_nm,prsn_last_nm,addtnl_third_party_txt)
            VALUES(third_party_id_seq.nextval,logonId,upper(userType),title,
            firstName,middleName,lastName,comments)
        returning third_party_id into thirdPartyId;
    end usp_insert_user;

This uses the sequence to generate the next ID, and the returning clause populates your OUT parameter.

You can also avoid the procedure and do the ID generation in a trigger, again using the sequence. There are a lot of examples of this approach around, not least on this site.

CREATE OR REPLACE TRIGGER third_party_user_temp_bi
BEFORE INSERT ON third_party_user_temp
FOR EACH ROW
BEGIN
    SELECT thirdPartyIdSeq.NEXTVAL
    INTO   :new.third_party_id
    FROM   dual;
END;
/

Your insert then doesn't need to specify the ID to be used.

edited Mar 01 '11 at 13:02

answered Mar 01 '11 at 12:16

Alex Poole

183,384
11
179
318

is there a way that I can lock the table at the beginning of the stored proc and unlock it at the end to avoid this? I think even if I used a indentity of some sort for the id I would have to return the new ID and I could run into the same issue, where if two people inserted a record and then it returned the id for both they would get the same id... – kralco626 Mar 01 '11 at 12:18
Explicit locking probably isn't something you want to dive into. Sequences are the way to go with this. – Alex Poole Mar 01 '11 at 12:20
sequences are oracles version of identities? – kralco626 Mar 01 '11 at 12:21
As far as I understand what you mean by identities, then sort of. Oracle doesn't have auto-incrementing IDs for column values built in. A sequence is a counter that increments and won't return the same value to two sessions, so it can be used to generate the equivalent of an auto-incrementing ID, but requires a bit of code (usually in a trigger) to assign the next value to a column. – Alex Poole Mar 01 '11 at 12:30
Oracle makes me sad :(. Anyways, I'm updating my question. is the new way any safer? – kralco626 Mar 01 '11 at 12:34
haha, did I update my question and you update your answer with the same idea at the same time? Well sorta the same idea with using the returning clause... – kralco626 Mar 01 '11 at 12:37
OK. so i created a seq. Now if i enter a bunch of records without using the seq, and then go to use the seq when I execute this stored proc, is the seq going to know that I inserted the other records? Or am I going to be recieving a number that could already be in the table? – kralco626 Mar 01 '11 at 12:54
Your update, using the returning clause but still with the max+1 calculation, is not any safer, no. It has the same potential race condition from two users executing at the same moment. (Well, I suppose you might have very slightly reduced the amount of time the contention may exist, but that's not going to be much comfort when it does finally break). – Alex Poole Mar 01 '11 at 12:55
No, the sequence won't automatically adjust itself if you enter records manually without using it. This is why doing it in a trigger is better, particularly if there's more than one way to insert a record. As long as the trigger is enabled this can't happen. – Alex Poole Mar 01 '11 at 12:56
O.K. thanks for this information, you have been very helpful! – kralco626 Mar 01 '11 at 12:59
Updated with a sample trigger to do this. – Alex Poole Mar 01 '11 at 13:03
Hi, sequence and a before insert trigger should be your aproach.[http://jen.fluxcapacitor.net/geek/autoincr.html](http://jen.fluxcapacitor.net/geek/autoincr.html) – lkdg Mar 01 '11 at 13:04
What if I had the situation where the primary key of a second table was the third_party_id and third_party_seq_no. Where third_party_seq_no was the number of third_party_id records there was in the table. so there would be records like (1,1);(1,2);(1,3);(2,1);(3,1);(4,1);(4,2)... how do i safly determine the value of the third_party_seq_no? – kralco626 Mar 01 '11 at 13:47

returning output parameter from Oracle stored proc

1 Answers1