Re: dynamically invoking a stored procedure.

In case you are using Oracle 8i, try the Native Dynamic SQL rather than using the dbms_sql package. It is simpler and faster too.

M8trx <m8trx_at_aol.com> wrote in article
<20000303113413.01940.00000404_at_ng-dc1.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
>
Received on Sat Mar 04 2000 - 00:00:00 CST