Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate
Execute Immediate [message #37219] Thu, 24 January 2002 20:38 Go to next message
Eric
Messages: 78
Registered: April 1999
Member
I am using a dynamic sql statement, inside a procedure, to return a sequence # into a variable., using the execute immediate block and I can not get this section of code to work.

create or replace procedure( input variables) as

block_to_execute varchar2(300) := 'begin select :1' || '_seq.nextval into '|| ':2' ||' from dual; end;';

--Where :1 is a bind input var that will hold the
--sequence name, which I will have to append an '_seq'
--to complete, and :2 is a bind output var to hold the
--output which is a number.

begin

execute immediate block_to_execute
using in pi_cmnt_tbl_nm, --This is an input var
out lv_seq; --This is an output var

end;
Re: Execute Immediate [message #37221 is a reply to message #37219] Fri, 25 January 2002 00:47 Go to previous messageGo to next message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
I think there is some syntax err .
execute immediate
'select '||:1||'_seq.nextval from '|| dual into :2;

But you know dual not allowed in this context .
Re: Execute Immediate [message #37234 is a reply to message #37219] Fri, 25 January 2002 08:33 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No need for a begin/end block.

The 'into' is not part of the string, but instead a separate clause similar to 'using'.

You cannot use bind variables for object names (sequence name), only values.

create or replace procedure p_get_next_sequence_value
  (p_sequence in varchar2, p_value out number)
as
begin
  execute immediate 'select ' || p_sequence || '_seq.nextval from dual'
    into p_value;
end;
/
Previous Topic: I have a question about procedure
Next Topic: urgent..how do i return the resultset or cursor from oracle stored procedure
Goto Forum:
  


Current Time: Tue Jul 07 04:03:21 CDT 2020