R,
This would work. You need the package to return the result back into - note
the global v_into variable at the package spec level. I don't think it
would work the way you had set it up originally because the inner exec
immediate would not find the v_into variable.
Good Luck!
PS - WHY do you need to do the inner exec immediate???
CREATE OR REPLACE PACKAGE Tomtest AS
PROCEDURE proc_main;
v_into NUMBER;
END ;
/
CREATE OR REPLACE PACKAGE BODY Tomtest
AS
PROCEDURE proc_main IS
v_variable VARCHAR2(20) := 'varchar2(20);';
v_myString VARCHAR(500);
BEGIN
v_myString := 'declare ' ||
' v_into number; ' ||
' BEGIN ' ||
' EXECUTE IMMEDIATE ' ||
'''' || ' SELECT 1 FROM dual ' || '''' ||
' INTO tomtest.v_into ' || '; ' ||
' END; ';
dbms_output.put_line(v_mystring);
EXECUTE IMMEDIATE v_myString;
dbms_output.put_line(v_into);
END;
END;
/
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Friday, July 18, 2003 11:15 AM
To: Multiple recipients of list ORACLE-L
Im playing around with it to figure out how to use it. Any idea what Im
doing wrong? I think its the quotes, but I cant figure out where and Im not
sure. I want to do the two execute immediates, because you do method 4
pl/sql that way(you dont know how many columns you want in an execute
immediate). So I need to leave that in so I can learn how to do it.
1 declare
2 v_variable varchar2(20) := 'varchar2(20);';
3 v_into number;
4 v_myString VARCHAR(500);
5 begin
6 v_myString := 'BEGIN
7 execute immediate
8 '' select 1 from dual into '||''''||v_into||
9 ' end;';
10 execute immediate v_myString;
11* end;
SQLPLUS>/
declare
*
ERROR at line 1:
ORA-06550: line 3, column 50:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
. ( * @ % & = - + ; < / > at in mod not rem return returning
<an exponent (**)> <> or != or ~= >= <= <> and or like
between into using is null is not || is dangling
The symbol ";" was substituted for "END" to continue.
ORA-06512: at line 10
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
