Call a stored procedure with another in Oracle

Question

Hi, does anyone know of a way, or even if its possible to call a stored procedure from within another? And 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;

Thanks

Answer 1

Sure, you just call it from within the SP, there's no special syntax.

Ex:

   PROCEDURE some_sp
   AS
   BEGIN
      some_other_sp('parm1', 10, 20.42);
   END;

If the procedure is in a different schema than the one the executing procedure is in, you need to prefix it with schema name.

   PROCEDURE some_sp
   AS
   BEGIN
      other_schema.some_other_sp('parm1', 10, 20.42);
   END;

Answer 2

@Michael Lockwood - you don't need to use the keyword "CALL" anywhere. You just need to mention the procedure call directly.

That is

Begin
   proc1(input1, input2);
end;
/

instead of

Begin
   call proc1(input1, input2);
end;
/

Answer 3

To invoke the procedure from the SQLPlus command line, try one of these:

CALL test_sp_1();
EXEC test_sp_1

Answer 4

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>