I'd like to see that. Do you have the link?
According to Tom Kyte, you cannot do type 4 with execute immediate.
Jared
<[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/18/2003 10:24 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: 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" <[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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
