i should have seen that one... error looks like it wasnt seeing the variable.
If you do a google search for the online journal 'Oracle Professional'. Steve Fuerstein has an article where he shows you how to do method 4 dynamic sql with execute immediate instead of dbms_sql. Method 4 is when you do a select and you dont know anythign about the query. Not even the number and types of columns. DBMS_SQL is a nuissance. You can sign up for free for 30 days and I think that is one of the articles they give away for free. Its very good. Then again everything by that guy is. your basically turning a dynamic sql into dynamic pl/sql since this type of dynamic sql does not work. Its rather cleaver. > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > Date: 2003/07/18 Fri AM 11:59:32 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: help with dynamic pl/sql > > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
