Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01002: fetch out of sequence
ORA-01002: fetch out of sequence [message #259449] Wed, 15 August 2007 11:42 Go to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
 /*
        Package to select point spec information from the TS database.

        Note:   Commented lines below in the procedure can be removed for stand alone
                testing purposes.
  */

  PROCEDURE getValidPointSpec
                (
                  IN_DVC_TID_NEID         IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.DVC_TID_NEID%TYPE,
                  IN_MP_SLOT              IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_SLOT%TYPE,
                  IN_MP_PORT              IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_PORT%TYPE,
                  IN_MP_SUBCHANNEL        IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_SUBCHANNEL%TYPE,
                  IN_MP_SHELF             IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_SHELF%TYPE,
                  VALID_POINT_SPEC_VAR      IN OUT VALID_POINT_SPEC_TYP,
                  OUT_ERROR_CODE          IN OUT NUMBER,
                  OUT_ROWCOUNT            IN OUT NUMBER
                );

END pkg_valid_point_spec;
/
show errors

CREATE OR REPLACE PACKAGE BODY pkg_valid_point_spec AS

  PROCEDURE getValidPointSpec
  (
                  IN_DVC_TID_NEID         IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.DVC_TID_NEID%TYPE,
                  IN_MP_SLOT              IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_SLOT%TYPE,
                  IN_MP_PORT              IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_PORT%TYPE,
                  IN_MP_SUBCHANNEL        IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_SUBCHANNEL%TYPE,
                  IN_MP_SHELF             IN     VW_DVC_MP_CCIRC_CROUTE_CUST_NC.MP_SHELF%TYPE,
                  VALID_POINT_SPEC_VAR      IN OUT VALID_POINT_SPEC_TYP,
                  OUT_ERROR_CODE          IN OUT NUMBER,
                  OUT_ROWCOUNT            IN OUT NUMBER
                )
        AS

                VALID_POINT_SPEC_TYPE       VALID_POINT_SPEC_RECORD;
                VALID_POINT_SPEC_REC        VALID_POINT_SPEC_TYPE%TYPE;
                WRK_KEY_INFO            VARCHAR2 (250);
                WRK_SQLCODE             NUMBER;
                WRK_ROWCOUNT            NUMBER;
                ERR_MSG                 VARCHAR2 (300);
                v_ErrorSeq              NUMBER;

        BEGIN

           SELECT (
                   ' DVC_TID_NEID: '   || IN_DVC_TID_NEID       ||
                   ' MP_SLOT: '        || IN_MP_SLOT            ||
                   ' MP_PORT: '        || IN_MP_PORT            ||
                   ' MP_SUBCHANNEL: '  || IN_MP_SUBCHANNEL      ||
                   ' MP_SHELF: '       || IN_MP_SHELF
                  )
             INTO    WRK_KEY_INFO FROM DUAL;

     --   SP_SET_TRANS('READ', WRK_SQLCODE);

-- *************************************
-- 1.  dvc
-- *************************************
   IF   IN_DVC_TID_NEID  IS NOT NULL    AND
        IN_MP_SLOT       IS NULL        AND
        IN_MP_PORT       IS NULL        AND
        IN_MP_SUBCHANNEL IS NULL        AND
        IN_MP_SHELF      IS NULL        THEN

       OPEN VALID_POINT_SPEC_VAR FOR
        SELECT  DISTINCT MP_AID,
                        DVC_TID_NEID,
                        NETWORK_TYPE,
                        MP_AISLE,
                        MP_RACK_FRAME,
                        MP_SHELF,
                        MP_SLOT,
                        MP_PORT,
                        MP_SUBCHANNEL,
                        MP_DIRECTION,
                        MP_SPEED,
                        MP_SLOT_TYPE,
                        MP_DEPLOYMENT_STATUS,
                        MP_AID_GROUP,
                        MP_IF_INDEX,
                        MP_SRC,
                        CUST_NAME,
                        CCIRC_DEPLOYMENT_STATUS,
                        CCIRC_COMMITTED_INFO_RATE,
                        CCIRC_TYPE,
                        CCIRC_CUST_BILLING_ID,
                        CCIRC_SRC,
                        CCIRC_ID
          FROM  VW_DVC_MP_CCIRC_CROUTE_CUST_NC
         WHERE  DVC_TID_NEID =  IN_DVC_TID_NEID         AND
                CCIRC_DEPLOYMENT_STATUS = 'T'          AND
                CUST_NAME <> 'UNKNOWN';

       FETCH VALID_POINT_SPEC_VAR INTO VALID_POINT_SPEC_REC;
       OUT_ERROR_CODE := SQLCODE;
       OUT_ROWCOUNT := VALID_POINT_SPEC_VAR%ROWCOUNT;
   END IF;


-- *************************************
-- 4.  dvc_tid_neid, slot, port, channnel
-- *************************************
   IF   IN_DVC_TID_NEID  IS NOT NULL    AND
        IN_MP_SLOT       IS NOT NULL    AND
        IN_MP_PORT       IS NOT NULL    AND
        IN_MP_SUBCHANNEL IS NOT NULL    AND
        IN_MP_SHELF      IS NULL        THEN
       OPEN VALID_POINT_SPEC_VAR FOR
        SELECT  DISTINCT MP_AID,                                                                                                        
DVC_TID_NEID,  
NETWORK_TYPE,    
MP_RACK_FRAME,
MP_SHELF,
MP_SLOT,
MP_PORT,
MP_SUBCHANNEL,
MP_DIRECTION,
MP_SPEED,
MP_SLOT_TYPE,
MP_DEPLOYMENT_STATUS,
MP_AID_GROUP,
MP_IF_INDEX,
MP_SRC,
CUST_NAME,
CCIRC_DEPLOYMENT_STATUS,
CCIRC_COMMITTED_INFO_RATE,
CCIRC_TYPE,
CCIRC_CUST_BILLING_ID,
CCIRC_SRC,
CCIRC_ID
 FROM  VW_DVC_MP_CCIRC_CROUTE_CUST_NC
         WHERE  DVC_TID_NEID  = IN_DVC_TID_NEID         AND
                MP_SLOT       = IN_MP_SLOT              AND
                MP_PORT       = IN_MP_PORT              AND
                MP_SUBCHANNEL = IN_MP_SUBCHANNEL        AND
                CCIRC_DEPLOYMENT_STATUS = 'T'          AND
                CUST_NAME <> 'UNKNOWN';
        FETCH VALID_POINT_SPEC_VAR INTO VALID_POINT_SPEC_REC;
        OUT_ERROR_CODE := SQLCODE;
        OUT_ROWCOUNT := VALID_POINT_SPEC_VAR%ROWCOUNT;
   END IF;
-- *************************************


In the above package which there are 9 if(statements) its working fine for 7 but not for 2.

when i excute the package it throws this error
SQL> execute pkg_valid_point_spec.getValidPointSpec('S_FTLD2', '14', '1', '1.148', null, :c1, :ec, :rc);

PL/SQL procedure successfully completed.


SQL> 
print :ec
print :rc
print :c1
SQL> ERROR:
ORA-01002: fetch out of sequence
no rows selected


Any help would be appreciated.
Thanks
Re: ORA-01002: fetch out of sequence [message #259459 is a reply to message #259449] Wed, 15 August 2007 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course a REAL execution should be more helpful.
With variable declaration and setting.

Regards
Michel
Re: ORA-01002: fetch out of sequence [message #259472 is a reply to message #259459] Wed, 15 August 2007 13:06 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Michel heres the testing criteria
execute pkg_valid_point_spec.getValidPointSpec('S_FTLD2', null, null, null, null, :c1, :ec, :rc);
print :ec
print :rc
print :c1

spool off
spool log/tpkg_valid_point_spec_2.log
execute pkg_valid_point_spec.getValidPointSpec('S_FTLD2', '14', null, null, null, :c1, :ec, :rc);
print :ec
print :rc
print :c1

spool off
spool log/tpkg_valid_point_spec_3.log
execute pkg_valid_point_spec.getValidPointSpec('S_FTLD2', '14', '1', null, null, :c1, :ec, :rc);
print :ec
print :rc
print :c1

spool off
spool log/tpkg_valid_point_spec_4.log
#4  execute pkg_valid_point_spec.getValidPointSpec('S_FTLD2', '14', '1', '1.148', null, :c1, :ec, :rc);
print :ec
print :rc
print :c1

spool off
spool log/tpkg_valid_point_spec_5.log
execute pkg_valid_point_spec.getValidPointSpec('C2DT801', null, null, null, '1', :c1, :ec, :rc);
print :ec
print :rc
print :c1

spool off
spool log/tpkg_valid_point_spec_6.log
execute pkg_valid_point_spec.getValidPointSpec('C2DT801', '7', null, null, '1', :c1, :ec, :rc);
print :ec
print :rc
print :c1

spool off
spool log/tpkg_valid_point_spec_7.log
#7  execute pkg_valid_point_spec.getValidPointSpec('C2DT801', '7', '3', null, '1', :c1, :ec, :rc);
print :ec
print :rc
print :c1

spool off
spool log/tpkg_valid_point_spec_8.log
execute pkg_valid_point_spec.getValidPointSpec('C2DT801', '7', '3', 'UNKNOWN', '1', :c1, :ec, :rc);
print :ec
print :rc
print :c1
spool off
spool log/tpkg_valid_point_spec_9.log
execute pkg_valid_point_spec.getValidPointSpec('123', null, null, null, null, :c1, :ec, :rc);
print :ec
print :rc


In the above criteria #4 and #7 throws the same error while remaining runs succefully.


And one more thing same package runs successfully without any problem on the othe BOX.

[Updated on: Wed, 15 August 2007 13:08]

Report message to a moderator

Re: ORA-01002: fetch out of sequence [message #259481 is a reply to message #259472] Wed, 15 August 2007 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I wanted to see the ouput.
I wanted to see how variable are declared.

Regards
Michel

[Updated on: Wed, 15 August 2007 13:15]

Report message to a moderator

Re: ORA-01002: fetch out of sequence [message #259483 is a reply to message #259481] Wed, 15 August 2007 13:17 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
SQL>  var c1 refcursor
SQL> var rc number
SQL> var ec number
SQL>  execute pkg_valid_point_spec.getValidPointSpec('S_FTLD2', '14', '1', '1.148', null, :c1, :ec, :rc);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.85
SQL> print :ec

         0

SQL> print :rc

         0

SQL> print :c1
ERROR:
ORA-01002: fetch out of sequence



no rows selected

Elapsed: 00:00:00.00
SQL>

[Updated on: Wed, 15 August 2007 13:18]

Report message to a moderator

Re: ORA-01002: fetch out of sequence [message #259485 is a reply to message #259483] Wed, 15 August 2007 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already got all the rows that can return the cursor, so you try to fetch after the last one and thus the error.

Regards
Michel
Re: ORA-01002: fetch out of sequence [message #259491 is a reply to message #259485] Wed, 15 August 2007 13:32 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
If We close VALID_POINT_SPEC_VAR in every IF after FETCH ,would that be suitable?
Re: ORA-01002: fetch out of sequence [message #259499 is a reply to message #259491] Wed, 15 August 2007 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you close the cursor, it is useless to have it as a parameter.

Regards
Michel
Re: ORA-01002: fetch out of sequence [message #259519 is a reply to message #259499] Wed, 15 August 2007 16:06 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Thanks Michel Wink Thumbs Up
Re: ORA-01002: fetch out of sequence [message #259814 is a reply to message #259519] Thu, 16 August 2007 10:42 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Problem has been solved.
Actually problem was with the table data.
In the above test criteria the value 1.148 was not existed in the table thats why it throws the error we checked it with data which was available in table it doesn't generate any error.
Previous Topic: group_by conditions
Next Topic: SQL/PLSQL
Goto Forum:
  


Current Time: Sat Dec 10 11:04:38 CST 2016

Total time taken to generate the page: 0.08995 seconds