problem with specifying INTO inside execute immediate [message #513800] |
Wed, 29 June 2011 08:20 |
|
daudiam
Messages: 51 Registered: June 2011
|
Member |
|
|
The following code works
set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id from employees where first_name=:ab' into a using 'Donald' ;
dbms_output.put_line(a);
end;
but this one doesn't
set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id into :1 from employees where first_name=:2' using a,'Donald';
dbms_output.put_line(a);
end;
Am I not allowed to specify a bind variable with an into clause inside execute immediate ?
|
|
|
|
|
Re: problem with specifying INTO inside execute immediate [message #513807 is a reply to message #513800] |
Wed, 29 June 2011 08:33 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Maybe you should realize the difference between SQL and PL/SQL.
SELECT is SQL statement
SELECT INTO is PL/SQL statement
It is described in PL/SQL User's Guide and Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
For 10gR2, the chapter about dynamic SQL is placed here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#i14500 Quote:Except for multi-row queries, the dynamic string can contain any SQL statement or any PL/SQL block. So, it means that PL/SQL statement has to be enclosed in BEGIN END block. declare
a int;
begin
execute immediate 'begin'
||' select employee_id into :1'
||' from employees'
||' where first_name=:2;'
||' end;'
using out a,'Donald';
dbms_output.put_line(a);
end; However, in this case, static SQL is the best option.
[Edit: Appended the end of PL/SQL block as well. Just dislike treating one-line SQL commands.]
[Updated on: Wed, 29 June 2011 08:36] Report message to a moderator
|
|
|
|
|
Re: problem with specifying INTO inside execute immediate [message #513824 is a reply to message #513819] |
Wed, 29 June 2011 09:39 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
That is nice, however you should consult documentation firstly.
1. Primarily, SELECT INTO is PL/SQL statement, so you have to use it in PL/SQL context (BEGIN END block). Not sure about which 'locally-defined variable' are you talking; I used it on both sides with one variable, although with a lot of unnecessary context switching.
2. Examples 7-5 and 7-6 in documentation link I posted (they are bulks, but if that UPDATEs would affect only one row, you could use it without BULK COLLECT. Or you could use it with any other SQL statement - INSERT or DELETE - containing RETURNING INTO clause)
|
|
|
|
|
|