How to Call One Stored Procedure from Another in Oracle?

How to Execute Stored Procedures Within Other Stored Procedures in Oracle

Introduction:

Calling a stored procedure from within another is a valuable technique in Oracle, enabling developers to daisy-chain database operations for complex scenarios.

Solution:

To invoke a stored procedure within another, employ one of the following methods:

1. CALL Statement:

CALL <procedure_name>([arguments]);</procedure_name>

This method uses the SQL keyword CALL and requires parentheses, even for procedures without arguments.

Example:

CALL test_sp_1();

2. Anonymous PL/SQL Block:

BEGIN
  <procedure_name>([arguments]);
END;</procedure_name>

PL/SQL blocks allow direct invocation of procedures.

Example:

BEGIN
  test_sp_1;
END;

3. EXEC Command (SQL*Plus Only):

EXEC <procedure_name>([arguments]);</procedure_name>

This command is a shortcut for an anonymous PL/SQL block.

Additional Considerations:

• Ensure that the called procedure exists and has the necessary privileges.
• If arguments are passed, their data types must match those specified in the called procedure.
• Errors encountered during procedure execution will be propagated to the calling procedure.

Example Code:

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE test_sp AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Test works');
END;

CREATE OR REPLACE PROCEDURE test_sp_1 AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Testing');
    test_sp;
END;

CALL test_sp_1();

Output:

Testing
Test works

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