Can One Oracle Stored Procedure Call Another?

Calling a Stored Procedure Within Another in Oracle

Problem:

It is possible to call a stored procedure from within another one in Oracle? If so, how can you accomplish this?

Example 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;

Solution:

The test code provided successfully creates and initializes the stored procedures. However, the issue lies in the final line, which attempts to invoke the procedures but fails.

There are three ways to invoke stored procedures in SQL*Plus:

1. CALL: Requires parentheses, even for procedures without arguments.

CALL test_sp_1();

2. Anonymous PL/SQL Block: Allows inline execution of code.

BEGIN
    test_sp_1;
END;
/

3. EXEC: A SQL*Plus command that is a shortcut for an anonymous block.

EXEC test_sp_1

Example:

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.

The above is the detailed content of Can One Oracle Stored Procedure Call Another?. For more information, please follow other related articles on the PHP Chinese website!