how do you call procedures dynamically

From: Simon Spencer <Simon.Spencer_at_citicorp.com>
Date: 1997/02/11
Message-ID: <3300C26E.54A4@citicorp.com>#1/1

I am looking for a way to call a procedure dynamically (ie, not knowing the name of the procedure at compile time). I have tried quite a few methods but without any luck, I believe that the DBMS_SQL package should allow me to achieve my goal, but I am stuffed if i know how to get it to work.

My first attempt went along the lines of

  procedure callIt is
    myVariable varchar2(1000) := 'callThisProcedure';   begin
    myVariable; -- a vein attempt to run the procedure "callThisProcedure"
  end;

Below is my latest attempt to get this thing working. Any suggestions?

    procedure callIt is

        handle1 integer;
        alteredRows integer;
    begin
        handle1 := DBMS_SQL.OPEN_CURSOR;

-- have tried this both with an without the bind variable. Someone
suggested that a select
-- procedurename from dual would work, but not for me.

        DBMS_SQL.PARSE (handle1, 'select :proc from dual', DBMS_SQL.V7);
        DBMS_SQL.BIND_VARIABLE(handle1, ':proc',
'theProcedureIWantToCall);
        alteredRows := DBMS_SQL.EXECUTE(handle1);
        DBMS_SQL.CLOSE_CURSOR(handle1);
    exception
        when others then
            DBMS_SQL.CLOSE_CURSOR(handle1);

    end;

---

Simon Spencer

Vice President                         Work:  203-961-6140
IPD, Operations and Technology         Fax:   203-961-6163
Emerging Technologies Group            Cell: 
100 First Stamford Place               Email: simon.spencer_at_citicorp.com

7th Floor, Stamford CT.
06902 USA

---

Received on Tue Feb 11 1997 - 00:00:00 CST