Re: dynamically invoking a stored procedure.

Instead of using 'execute procecure-name ' try using this:

DBMS_SQL.PARSE( cid, 'BEGIN '||PROC_NAME||' END;', DBMS_SQL.NATIVE );

Hope that helps

Graham

M8trx wrote:

> 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
Received on Fri Mar 03 2000 - 00:00:00 CST