Re: procedure parameter:

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 18 Jun 1999 16:43:14 GMT
Message-ID: <37777686.17533491@newshost.us.oracle.com>

A copy of this was sent to "Achille Carette" <act_at_bluegate.be> (if that email address didn't require changing) On Fri, 18 Jun 1999 18:26:39 +0200, you wrote:

>In PL/SQL, Is there some possibility to pass a procedure as a procedure
>parameter, like:
>
>PROCEDURE CALLING (pCalledProcedure PROCEDURE);
>
>
>

No, there is nothing like a procedure type, however, there is dynamic sql. Using that, you can pass the name of a procedure to execute and have the other procedure dynamically run it. for example:

SQL> create or replace procedure proca
  2 as
  3 begin
  4 dbms_output.put_line( 'procedure a' );   5 end;
  6 /

Procedure created.

SQL> create or replace procedure procb
  2 as
  3 begin
  4 dbms_output.put_line( 'procedure b' );   5 end;
  6 /

Procedure created.

SQL> create or replace procedure run_a_procedure( p_procname in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor, 'begin '||p_procname ||';end;',
  7                                     dbms_sql.native );
  8      rows_processed := dbms_sql.execute(exec_cursor);
  9      dbms_sql.close_cursor( exec_cursor );

 10 end;
 11 /

Procedure created.

SQL> 
SQL> 
SQL> set serveroutput on
SQL> exec run_a_procedure( 'proca' );

procedure a

PL/SQL procedure successfully completed.

SQL> exec run_a_procedure( 'procb' );
procedure b

PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

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 Received on Fri Jun 18 1999 - 11:43:14 CDT