Re: RE: help with dynamic pl/sql

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" <NDATFM_at_labor.state.ny.us>
> Date: 2003/07/18 Fri AM 11:59:32 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: NDATFM_at_labor.state.ny.us
>
> 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: ListGuru_at_fatcity.com (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
Received on Fri Jul 18 2003 - 11:24:12 CDT