How to resolve 'ORA-01002: fetch out of sequence' error?-CodePudding

I am facing 'ORA-01002: fetch out of sequence' error as a value for 'P_RESPONSE' cursor. It doesn't matter if select query returns any records, error is there in the output every single time. Please note that 'REQUESTSUBMITTERS.USERNAME' is a primary key.

PFB Stored Procedure.

create or replace PROCEDURE GET_REQUESTSUBMITTER (
    P_USERNAME         IN   REQUESTSUBMITTERS.USERNAME%TYPE,
    P_RESPONSE         OUT  SYS_REFCURSOR,
    SPRESULT           OUT  VARCHAR2,
    SPRESPONSECODE     OUT  VARCHAR2,
    SPRESPONSEMESSAGE  OUT  VARCHAR2
)
AS
    L_REQUESTSUBMITTER  REQUESTSUBMITTERS%ROWTYPE;
BEGIN
    OPEN P_RESPONSE FOR
    SELECT USERNAME, GEN, FIRSTNAME, LASTNAME, EMAILID, OFFICELOCATION, TITLE, MANAGER, DEPARTMENT
        FROM REQUESTSUBMITTERS 
        WHERE UPPER(USERNAME)=UPPER(P_USERNAME);
    LOOP
        EXIT WHEN P_RESPONSE%NOTFOUND;
        FETCH P_RESPONSE INTO L_REQUESTSUBMITTER;  
    END LOOP;
    IF P_RESPONSE%ROWCOUNT=0 THEN
        OPEN P_RESPONSE FOR
            SELECT 'NA' AS USERNAME FROM DUAL;
        SPRESULT:='NOK';
        SPRESPONSECODE:='GETREQSUBMTR-002';
        SPRESPONSEMESSAGE:=CONCAT('SUBMITTER RECORD NOT FOUND FOR USER - ', P_USERNAME);
    ELSE
        SPRESULT:='OK';
        SPRESPONSECODE:='GETREQSUBMTR-001';
        SPRESPONSEMESSAGE:=CONCAT('SUBMITTER RECORD FOUND FOR USER - ', P_USERNAME);
    END IF;

END GET_REQUESTSUBMITTER;

CodePudding user response：

I believe your EXIT statement should be after the FETCH statement inside the loop, else you'll loop past the end.

Don't forget to CLOSE the cursor.

CodePudding user response：

Do the following changes and see if your procedure works

    create or replace PROCEDURE GET_REQUESTSUBMITTER (
        P_USERNAME         IN   REQUESTSUBMITTERS.USERNAME%TYPE,
        P_RESPONSE         OUT  SYS_REFCURSOR,
        SPRESULT           OUT  VARCHAR2,
        SPRESPONSECODE     OUT  VARCHAR2,
        SPRESPONSEMESSAGE  OUT  VARCHAR2
    )
    AS
        L_REQUESTSUBMITTER  REQUESTSUBMITTERS%ROWTYPE;
    L_ROW_CNT NUMBER;
    BEGIN
        OPEN P_RESPONSE FOR
        SELECT USERNAME, GEN, FIRSTNAME, LASTNAME, EMAILID, OFFICELOCATION, TITLE, MANAGER, DEPARTMENT
            FROM REQUESTSUBMITTERS 
            WHERE UPPER(USERNAME)=UPPER(P_USERNAME);
        LOOP
            FETCH P_RESPONSE INTO L_REQUESTSUBMITTER; 
            EXIT WHEN P_RESPONSE%NOTFOUND;  
        END LOOP;
        L_ROW_CNT:= P_RESPONSE%ROWCOUNT;  
        CLOSE P_RESPONSE;    
        IF L_ROW_CNT=0 THEN
            OPEN P_RESPONSE FOR
                SELECT 'NA' AS USERNAME FROM DUAL;
            SPRESULT:='NOK';
            SPRESPONSECODE:='GETREQSUBMTR-002';
            SPRESPONSEMESSAGE:=CONCAT('SUBMITTER RECORD NOT FOUND FOR USER - ', P_USERNAME);
        CLOSE P_RESPONSE;  
        ELSE
            SPRESULT:='OK';
            SPRESPONSECODE:='GETREQSUBMTR-001';
            SPRESPONSEMESSAGE:=CONCAT('SUBMITTER RECORD FOUND FOR USER - ', P_USERNAME);
        END IF;

    END GET_REQUESTSUBMITTER;