I have a procedure that does a validation and inserts a record in a table. The procedure is breaking right after the INSERT statement when I try the following code:
EXECUTE IMMEDIATE V_SOME_STRNG || ' returning SOME_ID into :NEW_ID' returning into V_TRGT_ID;
I am trying to execute my INSERT statement which is stored in V_SOME_STRNG and assign the new record's ID to V_TRGT_ID. However, I am running into the following error:
ORA-00933: SQL command not properly ended
Any thoughts?
You don't need to repeat the returning into part, you need a using clause for your bind variable:
EXECUTE IMMEDIATE V_SOME_STRNG || ' returning SOME_ID into :NEW_ID' using out V_TRGT_ID;
Demo using a basic trigger to provide the ID:
create table t42 (some_id number, dummy varchar2(1));
create sequence s42 start with 42;
create trigger tr42 before insert on t42 for each row
begin
:new.some_id := s42.nextval;
end;
/
set serveroutput on
declare
v_some_strng varchar2(200) := 'insert into t42 (dummy) values (''X'')';
v_trgt_id number;
begin
EXECUTE IMMEDIATE V_SOME_STRNG || ' returning SOME_ID into :NEW_ID' using out V_TRGT_ID;
dbms_output.put_line('Returned ID: ' || v_trgt_id);
end;
/
which shows:
Returned ID: 42
PL/SQL procedure successfully completed.
You can only use returning into with the insert .. values ... pattern, not with insert ... select ...; so for instance changing the code above to use;
v_some_strng varchar2(200) := 'insert into t42 (dummy) select ''X'' from dual';
will get the error you originally reported:
ORA-00933: SQL command not properly ended
ORA-06512: at line 6
EXECUTE a SELECT statement after my INSERT statement to get the required value; but as a requirement, I have to use this method.
v_some_strng, or somewhere else in the block; or it's a version issue. I tested this in 11gR2; which version are you using?While you don't need to use returning into part, the OP problem most likely results from an error in the not shown content of the V_SOME_STRNG variable. Because you definitely can use returning into with execute immediate. Here is an example strait from the documentation:
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
I stress the point again: it works. So if you have any troubles here check you dynamically generated SQL statement more thoroughly.
My test_queries table consist of 2 columns:fid and query_text.I want insert new row. And I return fid I inserted because I use it next question. But the code give me error .
select max(a.fid) into max_fid from test_queries a; execute immediate 'insert into test_queries values (:1,:2) returning fid into :a' using max_fid+1,query_text,c;
