problem with 'EXECUTE IMMEDIATE'

Gurus,

I'm trying to execute a stored procedure from within a different stored procedure. It's not working, so I'm trying to get the syntax right by debugging with an anonymous PL/SQL block. Results are:

SQL> declare
2 p_sequence number;
3 sql_stmt varchar2(100);
4 begin
5 sql_stmt := 'PROC_MPS_WO_PROJ2';
6 EXECUTE IMMEDIATE 'exec '||sql_stmt;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

A simple query ('SELECT...') works fine, but for some reason the dynamic SQL doesn't seem to like 'EXEC'. Any ideas appreciated. Thanks.

Joe

Just give the statment without the exec (try)


Sam

Thanks, Sam. Just coding the name of the procedure with its input variables solved the problem - EXECUTE IMMEDIATE was not even necessary.

Thats correct you would use execute immediate for DDL operations like create/ALTER/MODIFY and etc. Not for the procedures

procedures and packages you can just call

package.procedure(input1);
or
procedure(input1);

Sam