Call a stored procedure with another in Oracle

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 ONSQL> 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();TestingTest worksCall completed.SQL> exec test_sp_1TestingTest worksPL/SQL procedure successfully completed.SQL> begin  2      test_sp_1;  3  end;  4  /TestingTest worksPL/SQL procedure successfully completed.SQL> 

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;

@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;/