Home » SQL & PL/SQL » SQL & PL/SQL » problem with specifying INTO inside execute immediate (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod, Ubuntu 11.04)
problem with specifying INTO inside execute immediate [message #513800] Wed, 29 June 2011 08:20 Go to next message
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 #513802 is a reply to message #513800] Wed, 29 June 2011 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
No. 1st example is what the documentation says you should do.
Re: problem with specifying INTO inside execute immediate [message #513803 is a reply to message #513800] Wed, 29 June 2011 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'Donald' is not a variable it is a constant.

But the correct code should be:
declare
  a int;
begin
  select employee_id into a from employees where first_name='Donald';
  dbms_output.put_line(a);
end;


There is no need to use EXECUTE IMMEDIATE.

Regards
Michel
Re: problem with specifying INTO inside execute immediate [message #513807 is a reply to message #513800] Wed, 29 June 2011 08:33 Go to previous messageGo to next message
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 #513819 is a reply to message #513803] Wed, 29 June 2011 09:17 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
Thanks. I understand that there is no need for execute immediate, but I am just trying out various combinations to get a hold of its usage.

1. We can't use a bind variable with INTO inside an EXECUTE IMMEDIATE ? Either the INTO has to be outside or if inside, the INTO should be associated with a locally-defined variable
2. Can u give me a simple example for using RETURNING INTO with EXECUTE IMMEDIATE. Can we supply bind variables with RETURNING INTO inside EXECUTE IMMEDIATE ?
Re: problem with specifying INTO inside execute immediate [message #513820 is a reply to message #513819] Wed, 29 June 2011 09:20 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You really should try reading the docs. The bit Flyboy linked to above descibes all this with examples.
Re: problem with specifying INTO inside execute immediate [message #513824 is a reply to message #513819] Wed, 29 June 2011 09:39 Go to previous messageGo to next message
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)
Re: problem with specifying INTO inside execute immediate [message #513828 is a reply to message #513824] Wed, 29 June 2011 10:30 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
Thanks. I really do read the docs before posting but in this case, the RETURNING example skipped me and as flyboy pointed, I forgot that SELECT INTO is a Pl/SQL construct. But I couldn't understand one thing : RETURNING INTO is also PL/SQL not SQL, so when it is put in the query, we don't put it inside a BEGIN and END block like we put SELECT INTO when using it in EXECUTE IMMEDIATE.
Re: problem with specifying INTO inside execute immediate [message #513830 is a reply to message #513828] Wed, 29 June 2011 10:51 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
daudiam wrote on Wed, 29 June 2011 17:30
But I couldn't understand one thing : RETURNING INTO is also PL/SQL not SQL

Wrong, it is both SQL and PL/SQL (I was able to find it even in documentation for version 8.1.7). It seems you skipped much more. Anyway, SQL Reference book for 10gR2 version states it clearly here:
INSERT: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698
UPDATE: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2067715
DELETE: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8005.htm#i2117787
Concentrate on RETURNING_CLAUSE, or RETURNING keyword itself.
Re: problem with specifying INTO inside execute immediate [message #513894 is a reply to message #513830] Thu, 30 June 2011 00:05 Go to previous message
daudiam
Messages: 51
Registered: June 2011
Member
Thanks.
Previous Topic: what would be the out parameter in pl sql procedure in this case?
Next Topic: ORA-00900: invalid SQL statement
Goto Forum:
  


Current Time: Wed Apr 24 06:31:39 CDT 2024