How Can I Call One Oracle Stored Procedure from Within Another?

Calling Stored Procedures from Within Others in Oracle

In Oracle, one might encounter a scenario where it is necessary to invoke one stored procedure from within another. This can be achieved using various methods, which we will explore in this article.

Test Case

Consider the following 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;

Problem and Solution

The issue with the given code lies in the last line, which attempts to directly invoke the stored procedures. This can be resolved by utilizing one of the following three methods:

1. Call Syntax

Oracle's SQL Reference defines the syntax for calling stored procedures as follows:

CALL <procedure_name>(<argument_list>);

Enclose the procedure name in parentheses, even when no arguments are passed.

CALL test_sp_1();

2. Anonymous PL/SQL Block

In an anonymous PL/SQL block, one can call stored procedures without explicitly naming them.

BEGIN
    test_sp_1;
END;
/

3. Exec Command (SQL*Plus)

SQL*Plus provides the exec command as a shortcut for anonymous blocks. It executes the specified stored procedure as if it were part of an anonymous block.

EXEC test_sp_1;

Example

Here is a fully working example:

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(); -- Direct call
exec test_sp_1; -- SQL*Plus shortcut
BEGIN test_sp_1; END; -- Anonymous PL/SQL block

Output:

Testing
Test works
Testing
Test works
Testing
Test works

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