BILL Carlisle
asked on
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!
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;
You can't use RETURNING in combination with a subquery on an insert, only with a VALUES clause
You cannot use returning clause if you use insert into ... select .... You can use it only for insert into ... values ....
See this:
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
ASKER
What is a solution for getting it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.