Re: how do you call procedures dynamically

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/02/11
Message-ID: <5dqe85$3fk@shadow.CSUFresno.EDU>#1/1

In article <3300C26E.54A4_at_citicorp.com>, Simon Spencer <Simon.spencer_at_citicorp.com> wrote:
>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.

You can do it with DBMS_SQL. Thomas Kyte gave a neat little example just a week ago:

  Subject:      Re: Execute DDL from PL/SQL?
  From:         tkyte_at_us.oracle.com (Thomas J. Kyte)
  Date:         1997/02/04

  Message-Id: <32f6a0ae.4381299_at_nntp.mediasoft.net>   Newsgroups: comp.databases.oracle.misc

(I looked it up in Dejanews: http://www.dejanews.com )

Using his Execute_Immediate function, I created the following script. Using this script, I'm sure you can proceed toward your goal. Note the 'Begin Procedure_Name; End;' string used to execute the procedure. Also, my little procedure doesn't return the number of rows processed. I believe selects or Updates used in DBMS_SQL will do that, but not a procedure.

Steve Cosner
Try my test data tool at http://members.aol.com/stevec5088 (It uses dbms_sql, by the way)

---

create table tmpt (A VARCHAR2(5),B VARCHAR2(5));
insert into tmpt values('A','B');
insert into tmpt values('B','C');

select * from tmpt;

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;
.
/
show errors function execute_immediate

create or replace
procedure tmpp is begin
  update tmpt set A='Z';
end;
.
/
show errors procedure tmpp

variable N number
begin
  :N := execute_immediate('begin tmpp;end;'); end;
.
/

print N
select * from tmpt;
drop table tmpt;
drop procedure tmpp;
drop function execute_immediate; Received on Tue Feb 11 1997 - 00:00:00 CST