| Execute Immediate [message #37219] |
Thu, 24 January 2002 20:38  |
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 #37234 is a reply to message #37219] |
Fri, 25 January 2002 08:33  |
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;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
