Q
Manage Learn to apply best practices and optimize your operations.

How to implement a ref cursor called in a stored procedure

Expert Brian Peasland explains how and when to implement a ref cursor called in a stored procedure.

I read your answer on calling the stored procedure from another procedure. On the base of this, I want to call another procedure, which has some cursor (output) parameters. The way I am doing it is as follows:

CREATE OR REPLACE PROCEDURE DEVELOPER.SPGETPORTLETSANDINSTANCES(
USERID IN NUMBER,
CR_PORTLETS OUT SYS_REFCURSOR ,
CR_PORTLETINSTANCES OUT SYS_REFCURSOR
)
AS

BEGIN

    
    DEVELOPER.SPGETPORTLETS(CR_PORTLETS);
    
    DEVELOPER.SPGETPORTLETINSTANCES(USERID, CR_PORTLETINSTANCES);

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END SPGETPORTLETSANDINSTANCES;
/

When I run this procedure, it gives the following procedure:
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor

Can you tell me what the problem with this is?

The following has a nice example of how to implement a ref cursor called in a stored procedure:

How to Use the SYS_REFCURSOR and REF CURSOR in the Oracle Stored Procedure and Function

Otherwise, you will probably have to diagnose the ORA-604 error. I answered this question previously as can be seen here:

Getting ORA-00604 error when connecting to database user

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close