problem with 'EXECUTE IMMEDIATE'

[JBrooke]

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

[sambavan]

Just give the statment without the exec (try)


Sam

[JBrooke]

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

[sambavan]

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