From: m8trx@aol.com (M8trx)
Subject: dynamically invoking a stored procedure.
Date: 2000/03/03
Message-ID: <20000303113413.01940.00000404@ng-dc1.aol.com>#1/1
Organization: AOL http://www.aol.com
Newsgroups: comp.databases.oracle.server
X-Admin: news@aol.com



does anyone know how to dynamically invoke a stored procedure passed as a
parameter to another stored procedure?  i tried using dbms_sql, but i am
getting "ORA-00900: invalid SQL statement ".

the code is listed below:

PROCEDURE EXECUTE_PROC( PROC_NAME VARCHAR2 )

IS

	cid INTEGER;
	dummyid INTEGER;
BEGIN
	cid := DBMS_SQL.OPEN_CURSOR;
	DBMS_SQL.PARSE( cid, 'EXECUTE ' || PROC_NAME, DBMS_SQL.NATIVE );
	dummyid := DBMS_SQL.EXECUTE( cid );
	DBMS_SQL.CLOSE_CURSOR( cid );

EXCEPTION

	WHEN others THEN
	  htp.htmlOpen;
	  htp.bodyOpen(  cattributes => ' bgcolor="#FFFFFF"' );
	  htp.para;
	  htp.para;
	  htp.print( 'Error in executing => execute ' || PROC_NAME );
	  htp.print( 'SQLERRM:  ' || SQLERRM );
	  htp.para;
	  htp.para;
	  htp.bodyClose;
	  htp.htmlClose;
  
END;

thanks for the help in advance.

manuel alfonso