RE: help with dynamic pl/sql

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 18 Jul 2003 11:01:14 -0400
Message-Id: <25956.338463@fatcity.com>

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-----
From: rgaffuri_at_cox.net [mailto:rgaffuri_at_cox.net] Sent: Friday, July 18, 2003 11:15 AM
To: Multiple recipients of list ORACLE-L Subject: help with dynamic pl/sql

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 Received on Fri Jul 18 2003 - 10:01:14 CDT