Re: Dynamic Call to PL/SQL stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 Aug 1998 00:53:38 GMT
Message-ID: <35c9accc.2822007@192.86.155.100>

A copy of this was sent to dov_at_healthstreet.com (if that email address didn't require changing) On Tue, 04 Aug 1998 21:36:10 GMT, you wrote:

>hello,
>
>Is there a way to call a PL/SQL procedure dynamically i.e. storing the called
>procedure name in a variable and usingthe variable in the CALL instead of
>explicit procedure name ?
>
>Thanks
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

dbms_sql will do this. for example, if you have a procedure like:

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );
    dbms_output.put_line( dbms_sql.last_row_id );

exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/

then you can:

SQL> begin

         execute_immediate( 'begin SomeProcedure; end;' );
     end;
     /

you can bind inputs/outputs to this as well...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Aug 04 1998 - 19:53:38 CDT