Posted By: Anonymous
Does anyone know of a way, or even if its possible, to call a stored procedure from within another? If so, how would you do it?
Here is my test code:
SET SERVEROUTPUT ON; DROP PROCEDURE test_sp_1; DROP PROCEDURE test_sp; CREATE PROCEDURE test_sp AS BEGIN DBMS_OUTPUT.PUT_LINE('Test works'); END; / CREATE PROCEDURE test_sp_1 AS BEGIN DBMS_OUTPUT.PUT_LINE('Testing'); test_sp; END; / CALL test_sp_1;
Your stored procedures work as coded. The problem is with the last line, it is unable to invoke either of your stored procedures.
Three choices in SQL*Plus are:
exec, and an anoymous PL/SQL block.
call appears to be a SQL keyword, and is documented in the SQL Reference. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4008.htm#BABDEHHG The syntax diagram indicates that parentesis are required, even when no arguments are passed to the call routine.
An anonymous PL/SQL block is PL/SQL that is not inside a named procedure, function, trigger, etc. It can be used to call your procedure.
BEGIN test_sp_1; END; /
Exec is a SQL*Plus command that is a shortcut for the above anonymous block.
EXEC <procedure_name> will be passed to the DB server as
BEGIN <procedure_name>; END;
SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE test_sp 2 AS 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('Test works'); 5 END; 6 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE test_sp_1 2 AS 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('Testing'); 5 test_sp; 6 END; 7 / Procedure created. SQL> CALL test_sp_1(); Testing Test works Call completed. SQL> exec test_sp_1 Testing Test works PL/SQL procedure successfully completed. SQL> begin 2 test_sp_1; 3 end; 4 / Testing Test works PL/SQL procedure successfully completed. SQL>