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;
This question is related to
sql
oracle
stored-procedures
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;
/
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;
To invoke the procedure from the SQLPlus command line, try one of these:
CALL test_sp_1();
EXEC test_sp_1
Source: Stackoverflow.com