Re: Help from Oracle GURUS

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/03
Message-ID: <336c8ed1.1457025@newshost>#1/1

On 2 May 1997 22:43:30 GMT, "Panuccio Antonello" <panuccio_at_arena.sci.univr.it> wrote:

>Can you help me solving this little problem : How can I execute a
>procedure stored in a table field ?
>Thanks.

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 Sat May 03 1997 - 00:00:00 CDT