Returning into clause for an INSERT from a SELECT statement

Hi,
I have the attached insert which creates a new audit_template. How do I get the NEW AUDIT_TEMPLATE_ID?

I tried putting:
returning AUDIT_TEMPLATE_ID into lNewTemplateID ;

get this error:
ORA-06550: line 21, column 4:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored1. DECLARE
2.     lNewTemplateID NUMBER;
3.      pCloneTemplateId NUMBER:=205;
4. BEGIN
 
without the
returning AUDIT_TEMPLATE_ID into lNewTemplateID ;
it inserts great!


DECLARE
    lNewTemplateID NUMBER;
     pCloneTemplateId NUMBER:=205;
BEGIN
INSERT INTO AUDIT_TEMPLATE (
   TITLE, LINE_OF_BUSINESS, 
   ACTIVE, RESPONSE_TYPE_1, 
   RESPONSE_TYPE_2, RESPONSE_TYPE_3, FILE_NBR_DISP, 
   CLIENT_DISP, CLAIMANT_DISP, OFFICE_DISP, 
   EXAMINER_DISP, SUPERVISOR_DISP, NURSE_DISP, 
   NURSE_SUPERV_DISP, TEMPL_CLIENT,cloned_from) 
select 
   TITLE||' (Copy)', LINE_OF_BUSINESS, 
   ACTIVE, RESPONSE_TYPE_1, 
   RESPONSE_TYPE_2, RESPONSE_TYPE_3, FILE_NBR_DISP, 
   CLIENT_DISP, CLAIMANT_DISP, OFFICE_DISP, 
   EXAMINER_DISP, SUPERVISOR_DISP, NURSE_DISP, 
   NURSE_SUPERV_DISP, TEMPL_CLIENT,AUDIT_TEMPLATE_ID 
   from AUDIT_TEMPLATE 
   where AUDIT_TEMPLATE_ID = pCloneTemplateId
returning AUDIT_TEMPLATE_ID into lNewTemplateID ;
 
htp.prn('Old ID['|| pCloneTemplateId||']');
htp.prn('New ID['|| lNewTemplateID||']');
END;

Open in new window

LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GGuzdziolConnect With a Mentor Commented:
You can fetch the row you want to insert into local variables (i.e. instead of insert into ... select ... you put select ... into ...) and then issue INSERT INTO ... VALUES ... RETURNING.
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
You can't use RETURNING in combination with a subquery on an insert, only with a VALUES clause
0
 
GGuzdziolCommented:
You cannot use returning clause if you use insert into ... select .... You can use it only for insert into ... values ....

See this:
SQL> create table bla (id number, value number);
 
Table created.
 
SQL> var x number;
SQL> insert into bla values (1, 2) returning id into :x;
 
1 row created.
 
SQL> insert into bla select 1, 2 from dual returning id into :x;
insert into bla select 1, 2 from dual returning id into :x
                                                *
ERROR at line 1:
ORA-00933: SQL command not properly ended

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BILL CarlisleAPEX DeveloperAuthor Commented:
What is a solution for getting it?
0
 
KICUSekConnect With a Mentor Commented:
I believe You can BULK COLLECT into collection.
0
 
Shaju KumbalathConnect With a Mentor Deputy General Manager - ITCommented:

DECLARE
lNewTemplateID NUMBER;

TYPE ARRAY IS TABLE OF shaju1%ROWTYPE;
l_data ARRAY;
pCloneTemplateId NUMBER:=205;
BEGIN
 
select AUDIT_TEMPLATE_ID,
TITLE||' (Copy)', LINE_OF_BUSINESS,
ACTIVE, RESPONSE_TYPE_1,
RESPONSE_TYPE_2, RESPONSE_TYPE_3, FILE_NBR_DISP,
CLIENT_DISP, CLAIMANT_DISP, OFFICE_DISP,
EXAMINER_DISP, SUPERVISOR_DISP, NURSE_DISP,
NURSE_SUPERV_DISP, TEMPL_CLIENT,AUDIT_TEMPLATE_ID into l_data
from AUDIT_TEMPLATE
where AUDIT_TEMPLATE_ID = pCloneTemplateId
 
INSERT INTO AUDIT_TEMPLATE
values l_data(1)
returning AUDIT_TEMPLATE_ID into lNewTemplateID ;

htp.prn('Old ID['|| pCloneTemplateId||']');
htp.prn('New ID['|| lNewTemplateID||']');
END;
 
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.