Re: DYNAMIC PROCEDURE CALL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/01
Message-ID: <3367e3d5.851033@newshost>#1/1

On Tue, 29 Apr 1997 12:15:25 +0200, Rizzoli Michele <rizzoli_at_sodalia.it> wrote:

>How can I store a procedure name in a Variable and then call it?
>i.e.:
>
>name := 'proc' || variable || '1';
>Now in name I have the name of my procedure.
>How can I call it in another procedure?
>
>Please Help me.
>E-mail me.
>Thanks
>
>--
>E-mail : mrizzoli_at_inf.unitn.it

I use a small routine like:

create or replace

function execute_immediate( stmt in varchar2 )

return number

as

    exec_cursor integer default dbms_sql.open_cursor;

    rows_processed number default 0;

begin

    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );

    rows_processed := dbms_sql.execute(exec_cursor);

    dbms_sql.close_cursor( exec_cursor );

    return rows_processed;

exception

    when others then

      if dbms_sql.is_open(exec_cursor) then

        dbms_sql.close_cursor(exec_cursor);

      end if;

      raise;

end;

/

It can do creates/inserts/updates/deletes/anonymous blocks as such:

  l_name := 'proc' || l_variable || '1';   l_status := execute_immediate( 'begin ' || l_name || '; end;' );

Bear in mind, roles are not active in pl/sql. You will either need to own the above procedure contained in l_name OR you will have had execute granted directly to you OR you have the execute any procedure privelege granted to you...

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 01 1997 - 00:00:00 CDT