This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 1, 2006 6:11 PM by SamB RSS

INSERT Statement with RETURNING option

324137 Newbie
Currently Being Moderated
Hello Everyone,

What I am trying to accomplish is to duplicate some records in a table based on a condition. I have included a small subset of the program below.

To accomplish this, I am using an INSERT statement with the returning option.

I am inserting a new record in a table and generates the primary key from a sequence. However, I need the value of that primary key after the record is inserted, so that I can update other tables. I thought that the INSERT with RETURNING option could do the job.

But I can't figure out the syntax of the RETURNING option. The code I have below for the INSERT is returning an error "Statement not properly ended" at a different line when I add the INSERT statement. Here is the program subset with the INSERT statement. It looks quite obvious to me but I don't know what I am doing wrong or what I am missing.

Thanks for any help you can provide.

DECLARE
nEmployee_Id NUMBER(16);
CURSOR cuEmployee is SELECT employee.*, ROWID FROM Employee
reg_code = 'C' AND workprovince IN('NU','YT','AB','MB','NT','SK');

BEGIN
FOR REC in cuEmployee LOOP
EXIT WHEN cuEmployee%NotFound;
BEGIN
INSERT INTO Employee (Employee_Id, Employee_Name, reg_code)
(SELECT employee_seq.nextval, Employee_Name, 'PN'
FROM Employee WHERE Employee_Id = rec.Employee_ID AND reg_code = rec.reg_code)
RETURNING Employee_Id INTO nEmployee_Id;
END IF;
-- I need to use the nEmployee_Id of the new record here
--COMMIT;
END;
END LOOP;
END;
/
  • 1. Re: INSERT Statement with RETURNING option
    jeneesh Guru
    Currently Being Moderated
    sorry

    Message was edited by:
    jeneesh
  • 2. Re: INSERT Statement with RETURNING option
    RobvanWijk Oracle ACE
    Currently Being Moderated
    Denis,

    You are inserting multiple records with the insert...select... statement, so it is returning multiple ID's. Use an array and use the syntax RETURNING employee_id BULK COLLECT INTO <array>.

    Regards,
    Rob.
  • 3. Re: INSERT Statement with RETURNING option
    436317 Newbie
    Currently Being Moderated
    Hi Denis,
    when you're sure you get only one employee you can use employee_seq.curval
    as value for Employee-Id.
  • 4. Re: INSERT Statement with RETURNING option
    438877 Explorer
    Currently Being Moderated
    You are inserting multiple records with the insert...select... statement, so it is >>returning multiple ID's. Use an array and use the syntax RETURNING >>employee_id BULK COLLECT INTO <array>.
    Unfortunately RETURNING INTO can't be used when INSERT INTO ... SELECT FROM statement is executing.

    http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_913a.htm#2063932
    SQL> create table th(id number);

    Table created.

    SQL> insert into th select rownum from dict;

    616 rows created.

    SQL> declare
      2    type tht is table of number index by pls_integer;
      3    th_t tht;
      4  begin
      5   
      6    update th set id = id
      7    returning id bulk collect into th_t;
      8 
      9  end;
    10  /

    PL/SQL procedure successfully completed.

    SQL> declare
      2    type tht is table of number index by pls_integer;
      3    th_t tht;
      4  begin
      5   
      6    insert into th select rownum from dict
      7    returning id bulk collect into th_t;
      8 
      9  end;
    10  /
      returning id bulk collect into th_t;
                *
    ERROR at line 7:
    ORA-06550: line 7, column 13:
    PL/SQL: ORA-00933: SQL command not properly ended
    ORA-06550: line 6, column 3:
    PL/SQL: SQL Statement ignored
    But you can use SELECT ... BULK COLLECT INTO <<array>>
    and FORALL statement here.

    Rgds.
  • 5. Re: INSERT Statement with RETURNING option
    RobvanWijk Oracle ACE
    Currently Being Moderated
    Ah yes, of course, it works only with forall.
    Thanks for pointing that out, dnikiforov.

    Regards,
    Rob.
  • 6. Re: INSERT Statement with RETURNING option
    324137 Newbie
    Currently Being Moderated
    Thanks to everyone for all the replies.

    But why are you saying that I am inserting multiple rows ?

    The WHERE part 'WHERE Employee_Id = rec.Employee_ID AND reg_code = rec.reg_code' returns only one row.

    I may end up using the curval if I can run that script without anyone connected to the database and be sure curval will be the right value but I am disappointed that the returning clause can't be used with an insert based on a subquery, especially when it returns only one row. I have looked at the doc again and coudn't find that restriction on the returning option. It must be hidden somewhere ???

    I am not familiar with the FORALL but will try to see if I can use that.

    Thanks again.





    INSERT INTO Employee (Employee_Id, Employee_Name, reg_code)
    (SELECT employee_seq.nextval, Employee_Name, 'PN'
    FROM Employee WHERE Employee_Id = rec.Employee_ID AND reg_code = rec.reg_code)
    RETURNING Employee_Id INTO nEmployee_Id;
  • 7. Re: INSERT Statement with RETURNING option
    John Spencer Oracle ACE
    Currently Being Moderated
    I'm not really sure why you are doing this, it appears that you are duplicating all the employees that meet the criteria into region PN. Depending on what else you are doing here, it seems to me that it could be done as a single update statement.
    INSERT INTO employee (employee_id, employee_name, reg_code)
    SELECT employee_seq.nextval, emp_rec.employee_name, 'PN'
    FROM employee
    WHERE reg_code = 'C' AND
          workprovince IN('NU','YT','AB','MB','NT','SK')
    However, if you really do need the loop for some reason, and I would think really hard about whether you do, then I would code it more like:
    DECLARE
       l_employee_id NUMBER;
    BEGIN
       FOR emp_rec IN (SELECT employee.*, ROWID FROM employee
                       WHERE reg_code = 'C' AND
                             workprovince IN('NU','YT','AB','MB','NT','SK')) LOOP
          INSERT INTO employee (employee_id, employee_name, reg_code)
          VALUES(employee_seq.nextval, emp_rec.employee_name, 'PN')
          RETURNING employee_id INTO l_employee_id;
          -- use l_employee_id here
       END LOOP;
    END;
    HTH
    John
  • 8. Re: INSERT Statement with RETURNING option
    RobvanWijk Oracle ACE
    Currently Being Moderated
    But why are you saying that I am inserting multiple rows ?
    You used the INSERT ... SELECT syntax, which is used to insert multiple rows at once
    The WHERE part 'WHERE Employee_Id = rec.Employee_ID
    AND reg_code = rec.reg_code' returns only one row.
    Yes, you are right that the "WHERE Employee_Id = rec.Employee_ID" part suggest that only one row is inserted. But then you should use the INSERT ... VALUES ... syntax. In which case you can use the RETURNING clause as it is right now.

    And <sequence>.currval can be used as well.

    Regards,
    Rob.
  • 9. Re: INSERT Statement with RETURNING option
    Justin Cave Oracle ACE
    Currently Being Moderated
    sequence_name.currval returns the last value of the sequence that was given to the current session. Another session could be constantly getting new sequence values at the same time but currval would still return the correct value for your session.

    Justin
  • 10. Re: INSERT Statement with RETURNING option
    APC Oracle ACE
    Currently Being Moderated
    But why are you saying that I am inserting multiple rows ?

    The WHERE part 'WHERE Employee_Id = rec.Employee_ID AND reg_code =
    rec.reg_code' returns only one row.
    yes but the database doesn't know that it. You are using the INSERT syntax for inserting multiple rows and that doesn't permit RETURNING INTO a non-array variable.

    Of course, quite why you are using the INSERT ... SELECT syntax is beyond me, as you have already got all that info in the active set.

    Cheers, APC
  • 11. Re: INSERT Statement with RETURNING option
    324137 Newbie
    Currently Being Moderated
    Thanks again everyone.

    Yes, I think I need to loop because I am changing the value of some columns which are involved in a foreign key relationship. Therefore, if I update the records without looping and creating the parent records on the fly, I will receive the "Foreign key not found" type of error. DBA will not let me disable/enable the foreign key constraints for the time the program is run and that would require the database to be shut-down to other users.

    So, I have to come up with a different solution, which I think is the looping approach. Unless I can do a sequence of UPDATE's and have Oracle check the integrity of the data only after all my update's are completed (cause it will crash on the first update when it can't find the parent records) ?

    Anyway, thank you much to all of you. The curval or John's example should work for me.
  • 12. Re: INSERT Statement with RETURNING option
    324137 Newbie
    Currently Being Moderated
    I think that I am having concentration problems lately. :)

    I have tried to give a simplify example previously of what I am trying to accomplish but obviously it didn't work. Therefore, John, your example would not work for my actual case.

    So, I will put a subset of the actual code here and show what I am trying to accomplish. Needless to say that the curval solution will work, but it looked easier to me using the returning clause. Maybe I need to create an array with one element to have it work ? Don't know, I'll check and figure out all of that later on but now I must apply a working solution. My head is spinning and have a hard time thinking clearly.

    But here is the actual code (or part of it):

    DECLARE
    Local_waterway_nbr NUMBER(16);
    CURSOR cuDemande is SELECT demande.*, ROWID FROM Demande
    WHERE reg_code = 'C' AND workprovince IN('NU','YT','AB','MB','NT','SK');

    BEGIN
    FOR REC in cuDemande LOOP
    EXIT WHEN cuDemande%NotFound;
    BEGIN
    SELECT local_waterway_id INTO local_waterway_nbr FROM CM001_Local_Waterway WHERE Local_waterway_id =
    rec.local_waterway_id AND reg_code = 'PN';
    IF SQL%NOTFOUND THEN
    -- Duplicate that record for PN region
    INSERT INTO CM001_Local_Waterway (Region_Cd, Local_Waterway_Name_Txt)
    SELECT 'PN', Local_Waterway_Name_Txt
    FROM CM001_Local_Waterway WHERE local_waterway_id = rec.local_waterway_id AND reg_code = rec.reg_code
    RETURNING Local_Waterway_id INTO Local_Waterway_nbr;
    END IF;
    UPDATE Demande SET reg_code = 'PN', Local_Waterway_ID = Local_Waterway_Nbr WHERE ROWID = rec.ROWID;
    --COMMIT;
    END;
    END LOOP;
    END;
    /


    So, I am updating the column "reg_code" to move the records from one region to another. However, due to the way the database was designed, reg_code is part of the primary key of some tables to which the "demande" table is related.

    The relation goes like this:

    Demande Table
    ===========
    - Reg_code
    - Local_Waterway_Id
    - Date_Received
    - Latitude
    - Longitude
    - .....


    CM001_Local_Waterway table
    ======================
    - Local_Waterway_Id
    - Region_Cd (primary key)
    - Local_Waterway_Name_txt


    So, to move the records in "Demande" from one region to another, I need to change the reg_code column value. But unless I create another record within the parent table (CM001_Local_Waterway) with the new region code and a new key, I will get the "foreign key not found" error. And I have many related tables to change like that.

    I hope I explained it properly. I wish there was an easier solution to do this then the way I did it but as long as it works, that'll be just fine with me.

    Thank you.

    p.s. Anyway to put an html tag to keep the original code spacing indentation ?


    UPDATED
    =======

    Forgot to add (getting so much more complicated):

    - Local_Waterway_Id is generated from a sequence using a PRE-INSERT database trigger.

    Message was edited by:
    Denis Demers
  • 13. Re: INSERT Statement with RETURNING option
    45878 Newbie
    Currently Being Moderated
    If this is an option, you can defer the constraints then "un-defer" them. Found this in the Administrator's Guide:
    Set All Constraints Deferred
    Within the application being used to manipulate the data, you must set all constraints
    deferred before you actually begin processing any data. Use the following DML
    statement to set all deferrable constraints deferred:

    SET CONSTRAINTS ALL DEFERRED;

    Note: The SET CONSTRAINTS statement applies only to the
    current transaction. The defaults specified when you create a
    constraint remain as long as the constraint exists. The ALTER
    SESSION SET CONSTRAINTS statement applies for the current
    session only.

    Check the Commit (Optional)
    You can check for constraint violations before committing by issuing the SET
    CONSTRAINTS ALL IMMEDIATE statement just before issuing the COMMIT. If there
    are any problems with a constraint, this statement fails and the constraint causing the
    error is identified. If you commit while constraints are violated, the transaction is
    rolled back and you receive an error message.
  • 14. Re: INSERT Statement with RETURNING option
    William Robertson Oracle ACE
    Currently Being Moderated
    > FOR REC in cuDemande LOOP
    EXIT WHEN cuDemande%NotFound;

    A Cursor FOR loop already exits when no further rows are found, so the above EXIT WHEN line will never be true.
    SELECT local_waterway_id INTO local_waterway_nbr FROM CM001_Local_Waterway WHERE Local_waterway_id =
    rec.local_waterway_id AND reg_code = 'PN';
    IF SQL%NOTFOUND THEN

    If a SELECT INTO finds no rows, a NO_DATA_FOUND exception will be raised, and so the IF condition will never be raised.
1 2 Previous Next