• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4600
  • Last Modified:

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

0
BILL Carlisle
Asked:
BILL Carlisle
3 Solutions
 
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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
What is a solution for getting it?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GGuzdziolCommented:
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
 
KICUSekCommented:
I believe You can BULK COLLECT into collection.
0
 
Shaju KumbalathDeputy 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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now