Call a stored procedure with another in Oracle

Question

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; 

Answer 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: call, 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.

CALL test_sp_1(); 

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;

Full example:

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>