Re: Dynamic PL/SQL and Stored Procedures

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/03/27
Message-ID: <4jacse$b9_at_inet-nntp-gw-1.us.oracle.com>#1/1

ghp_at_infosoft.be (Gerard H. Pille) wrote:

>In article <31579DF9.3817_at_mcimail.com>, Ernie Svehla
>(esvehla_at_mcimail.com) says...
>!>
>!>Hi Everyone,
>!>
>!>I am trying to find out how I can dynamically invoke a stored procedure
>!>through the use of the DBMS_SQL package. Does anyone have any example
>!code or
>!>suggestions? Looking in the Steven Feuerstein text I only found
>!discussion on
>!>how to execute inserts, updates, selects, etc.
>!>
>!>Thanks in advance!
>!>
>!>Ernie Svehla
 

>I don't know the 'Steven Feuerstein text', but I suppose that if you take
>one of his examples and replace the 'select ...' by an
>'execute proc_whatever(param_this, param_that);'
>you should be OK. No?
 

>--
>Kind reGards
> \ / |
> X |
> / \ s
> Gerard

Close. You could replace it with
"begin proc_whatever(param_this,param_that); end;"

The following little procedure makes invoking arb. statements pretty easy. You can call

execute_immediate( 'alter user scott identified by lion' );

or

execute_immediate( 'begin foo(5); end;' );

and so on....

BTW, in case you didn't know, you will need to have priveleges granted directly to the developer of the procedure. For example, for the above alter user command to have worked, the ALTER USER privelege must be granted to the developer, not a role the developer has granted to them.

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;
/
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Wed Mar 27 1996 - 00:00:00 CET