Re: procedure name as a variable

On Wed, 20 May 1998 22:02:02 -0600, "Juan" <jgarza_at_tdhca.state.tx.us> wrote:

>Is there anyway to call a procedure where the name
>of the procedure is in a variable. (like a pointer
>or something).
>
>example:
>procedure Test1 is
>v_procedure_name varchar2(20);
>begin
>v_procedure_name := 'Test2';
>v_procedure_name;
>end;

Hi Juan,

You can use dynamic SQL to execute anonymous PL/SQL blocks. Try something like this:

PROCEDURE Test1 IS

   v_procedure_name VARCHAR2(20);
   stmt VARCHAR2(2000);
   hndl INTEGER;
   exec INTEGER;
BEGIN
   v_procedure_name := 'Test2';

   stmt := 'BEGIN ' || v_procedure_name || '; END;';    hndl := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(hndl, stmt, DBMS_SQL.NATIVE);    exec := DBMS_SQL.EXECUTE(hndl);
   DBMS_SQL.CLOSE_CURSOR(hndl);

   EXCEPTION WHEN others THEN

      IF DBMS_SQL.IS_OPEN(hndl) THEN
         DBMS_SQL.CLOSE_CURSOR(hndl);
      END IF;
      RAISE;

END; Note that if your procedure has OUT parameters, or if you want to call functions this way, you could use the procedures DBMS_SQL.DEFINE_VARIABLE and DBMS_SQL.VARIABLE_VALUE to get access to them from the calling procedure.

HTH,
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Thu May 21 1998 - 06:45:54 CDT