46

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;
cr0ss
  • 877
  • 5
  • 20
electricsheep
  • 5,114
  • 9
  • 37
  • 41

5 Answers5

51

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>
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67
20

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;
dcp
  • 54,410
  • 22
  • 144
  • 164
  • Thanks for that however when I try to run that using CALL sp_1; I get the following error. Error starting at line 21 in command: CALL test_sp_1 Error report: SQL Error: ORA-06576: not a valid function or procedure name 06576. 00000 - "not a valid function or procedure name" *Cause: Could not find a function (if an INTO clause was present) or a procedure (if the statement did not have an INTO clause) to call. *Action: Change the statement to invoke a function or procedure – electricsheep Aug 05 '10 at 13:55
  • @Michael Lockwood - Can you post your code in your original question? Also, if you are calling a function using a tool like SQL*PLus, you need to assign the return value to a variable. – dcp Aug 05 '10 at 13:57
  • 1
    @ML: why are you still trying to use CALL when CALL doesn't work (is not a valid PL/SQL statement) and dcp has shown you how to do it correctly? – Tony Andrews Aug 05 '10 at 14:04
  • 1
    Thanks for this! I was stuck trying to precede the procedure name with `EXEC` or `EXECUTE`, as (I think) would be necessary to invoke the procedure independently -- but was getting an error. I didn't realize that inside another procedure, I could just call the procedure by name, without a preceding command. – Joe DeRose Jun 16 '15 at 15:03
  • Is it possible to use results from called stored procedure? (select statement) – FrenkyB Nov 04 '21 at 05:36
  • @FrenkyB - That's really a completely different question. But in general, you can pass a parameter by reference to the stored procedure if you intend to use its value in the calling procedure/function. In Oracle, you pass a parameter by reference by specifying that it's an "OUT" parameter. If you are looking to get the results of a select statement returned to your procedure, then check out ref cursors. See https://stackoverflow.com/questions/31587292/what-is-pass-by-reference-in-oracle/31588096 for examples of passing by reference. – dcp Nov 04 '21 at 14:16
5

@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;
/
Tom H
  • 46,766
  • 14
  • 87
  • 128
Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
  • Looking at his code, and the revision history to the question, I don't see where he is using call within PL/SQL. The call is outside of any PL/SQL, and an attempt to test the stored procedures. – Shannon Severance Aug 05 '10 at 20:48
3

Calling one procedure from another procedure:

One for a normal procedure: 

CREATE OR REPLACE SP_1() AS 
BEGIN
/*  BODY */
END SP_1;

Calling procedure SP_1 from SP_2:

CREATE OR REPLACE SP_2() AS
BEGIN
/* CALL PROCEDURE SP_1 */
SP_1();
END SP_2;

Call a procedure with REFCURSOR or output cursor:

CREATE OR REPLACE SP_1
(
oCurSp1 OUT SYS_REFCURSOR
) AS
BEGIN
/*BODY */
END SP_1;

Call the procedure SP_1 which will return the REFCURSOR as an output parameter 

CREATE OR REPLACE SP_2 
(
oCurSp2 OUT SYS_REFCURSOR
) AS `enter code here`
BEGIN
/* CALL PROCEDURE SP_1 WITH REF CURSOR AS OUTPUT PARAMETER */
SP_1(oCurSp2);
END SP_2;
jonsca
  • 10,218
  • 26
  • 54
  • 62
Nirav savla
  • 41
  • 2
3

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

CALL test_sp_1();
EXEC test_sp_1
Dave Costa
  • 47,262
  • 8
  • 56
  • 72