CODEWITHSUNDEEP
oracle-databasestored-procedures
Xaphann
Xaphann

Reputation: 3677

Oracle stored procured calling another stored procured and returning a cursor

I need to have a stored procedure parse input and based on the results call another stored procedure. The result needs to return a cursor.

My procedure is something like this:

CREATE OR REPLACE PROCEDURE TestProceuder1 (userData VARCHAR2) IS
    cl SYS_REFCURSOR;
BEGIN
    --Amazing Logic
    OPEN c1 FOR SELECT 'results' AS Results FROM dual;
    dbms_sql.return_result(c1);
END;

Then the main do something like this:

CREATE OR REPLACE PROCEDURE MainProcedure (userData VARCHAR2) IS
    c1 SYS_REFCURSOR;
BEGIN
    --Amazing logic picking the correct procedure
    OPEN c1 FOR TestProceuder1(userData);    
        dbms_sql.return_result(c1);
END;

However, this returns an error of

PLS-00222: no function with name 'TESTPROCEUDER1' exists in this scope

Why is it trying to call it as a function when TestProceuder1 is a stored procedure?

Upvotes: 0

Views: 130

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

If only MainProcedure will be called by the client, and the split-out procedures will not be called directly, then the handling for those can use standard PL/SQL mechanisms instead of dbms_sql.return_result:

CREATE OR REPLACE PROCEDURE TestProcedure1 (userData VARCHAR2, c1 OUT SYS_REFCURSOR) IS
BEGIN
    --Amazing Logic
    OPEN c1 FOR SELECT 'results' AS Results FROM dual;
END;
/

Or you could use a function instead of an OUT parameter:

CREATE OR REPLACE FUNCTION TestFunction1 (userData VARCHAR2)
RETURN SYS_REFCURSOR IS
    c1 SYS_REFCURSOR;
BEGIN
    --Amazing Logic
    OPEN c1 FOR SELECT 'results' AS Results FROM dual;
    RETURN c1;
END;
/

Then the main procedure calls those, and only that needs to use the result mechanism:

CREATE OR REPLACE PROCEDURE MainProcedure (userData VARCHAR2) IS
    c1 SYS_REFCURSOR;
BEGIN
    --Amazing logic picking the correct procedure
    TestProcedure1(userData, c1);
    dbms_sql.return_result(c1);
    -- or function
    c1 := TestFunction1(userData);
    dbms_sql.return_result(c1);
END;
/

db<>fiddle

It would probably make sense to put all of the procedures into a package, and only expose the main one; db<>fiddle.

Upvotes: 2

Related Questions