Re: How Can i call a procedure that the name is in a table?

A copy of this was sent to "Antonio Figueiredo" <acfigueiredo_at_net.sapo.pt> (if that email address didn't require changing) On Sun, 28 Nov 1999 16:42:00 -0000, you wrote:

>I need to call procedures or functions that they are
>stored in a table, after i make the select what can i do to call them is
>PL/SQL?
>
>Any one that reply i thanks
>

Using dynamic sql. In Oracle8.0 and before it could look like this:

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 );

begin
  for x in ( select proc_name from T ) loop     execute_immediate( 'begin ' || x.proc_name || '; end;' );   end loop;
end;
/

In Oracle8i, release 8.1 and up, it would be more easily:

begin

   for x in ( select proc_name from T ) loop      EXECUTE IMMEDIATE 'begin ' || x.proc_name || '; end;';    end loop;
end;
/

(no need for the execute_immediate procedure in 8i)

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

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