Home » SQL & PL/SQL » SQL & PL/SQL » User Defined errors
User Defined errors [message #259815] Thu, 16 August 2007 10:46 Go to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
i get the error
ORA-01002: fetch out of sequence

when data is not available in table for that i defined user defined_exception like this

v_excep EXCEPTION;
 PRAGMA EXCEPTION_INIT (v_excep, -1002)<<<<<<is this code is correct;



and i called it in EXCEPTION area like this

EXCEPTION
WHEN v_excep THEN
   DBMS_OUTPUT.PUT_LINE (' No Data Found ');


But when i execute the package this exception does execute i means i get the same error

ORA-01002: fetch out of sequence



Thanks in advance.

[Updated on: Thu, 16 August 2007 10:47]

Report message to a moderator

Re: User Defined errors [message #259818 is a reply to message #259815] Thu, 16 August 2007 10:55 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It works fine for me, so you must have put your exception handler in the wrong place or something.

declare
    cursor cdum is select dummy from dual for update of dummy;
    v_x varchar2(10);
    v_excep  EXCEPTION;
    PRAGMA EXCEPTION_INIT(v_excep, -1002);
begin
    open cdum;
    commit;
    fetch cdum into v_x;
    close cdum;
EXCEPTION
WHEN v_excep THEN
   DBMS_OUTPUT.PUT_LINE (' No Data Found ');
end;	


You seem to be confusing this error, which is an error caused by bad coding, with the general no data found condition. You should not be trying to capture this error. The fact that you are getting it indicates that your code is badly written and you should be trying to sort that out rather than convert it into a no_data_found exception (which it isn't).

Re: User Defined errors [message #259821 is a reply to message #259818] Thu, 16 August 2007 11:07 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Thanks for feed back these packages are pre written many years back.
heres where i put the exception code.

--   SP_END_TRANS('COMMIT', WRK_SQLCODE);

        EXCEPTION
WHEN v_excep THEN
   DBMS_OUTPUT.PUT_LINE (' No Data Found ');
    --       WHEN OTHERS THEN
     --           SP_END_TRANS('COMMIT', WRK_SQLCODE);
    ---            PKG_ERROR_HANDLER.HandleAll(TRUE);
    ---            PKG_ERROR_HANDLER.HandleAll(FALSE);
    ---            PKG_ERROR_HANDLER.StoreStacks
   ---                     ('pkg_valid_point_spec.getValidPointSpec', WRK_KEY_INFO,
   ---                      v_ErrorSeq, TRUE);
   ---             PKG_ERROR_HANDLER.PrintStacks
   ---                     ('pkg_valid_point_spec.getValidPointSpec', v_ErrorSeq);
                OUT_ERROR_CODE  := SQLCODE;

END getValidPointSpec;

END pkg_valid_point_spec;
/
SHOW ERRORS


Correct me if i'm wrong
Thanks
Re: User Defined errors [message #259825 is a reply to message #259821] Thu, 16 August 2007 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're surley wrong somewhere in other part of your code as it works as Cthulhu showed it.

Regards
Michel
Re: User Defined errors [message #259834 is a reply to message #259825] Thu, 16 August 2007 11:26 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Here's mine package and i defined it at the bottom of code.

CREATE  OR REPLACE PACKAGE pkg_valid_point_spec AS
.
.
.
PROCEDURE getValidPointSpec
(
.
.
END pkg_valid_point_spec;
/
show errors

CREATE OR REPLACE PACKAGE BODY pkg_valid_point_spec AS

PROCEDURE getValidPointSpec
(
.
.
)
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;
v_excep EXCEPTION;
   PRAGMA_EXCEPTION_INIT (v_excep, -1002);

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);

*************************************
8.  dvc_tid_neid, shelf, slot, port, subchannel
*************************************
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 NOT NULL    THEN
OPEN VALID_POINT_SPEC_VAR FOR
SELECT  DISTINCT MP_AID,
DVC_TID_NEID,
.
.
CCIRC_ID
FROM  VW_DVC_MP_CCIRC_CROUTE_CUST_NC
WHERE  DVC_TID_NEID  =  IN_DVC_TID_NEID         AND
MP_SHELF      =  IN_MP_SHELF             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;


SP_END_TRANS('COMMIT', WRK_SQLCODE);

EXCEPTION <<<<<<<<<<<<<<<<<<<<<<<<<<exceptionnnnn
WHEN v_excep THEN
   DBMS_OUTPUT.PUT_LINE (' No Data Found ');
WHEN OTHERS THEN
SP_END_TRANS('COMMIT', WRK_SQLCODE);
---            PKG_ERROR_HANDLER.HandleAll(TRUE);
---            PKG_ERROR_HANDLER.HandleAll(FALSE);
---            PKG_ERROR_HANDLER.StoreStacks
---                     ('pkg_valid_point_spec.getValidPointSpec', WRK_KEY_INFO,
---                      v_ErrorSeq, TRUE);
---             PKG_ERROR_HANDLER.PrintStacks
---                     ('pkg_valid_point_spec.getValidPointSpec', v_ErrorSeq);
OUT_ERROR_CODE  := SQLCODE;

END getValidPointSpec;

END pkg_valid_point_spec;
/
SHOW ERRORS

[Updated on: Thu, 16 August 2007 13:31]

Report message to a moderator

Re: User Defined errors [message #259859 is a reply to message #259834] Thu, 16 August 2007 13:14 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
No Help yet Rolling Eyes
Re: User Defined errors [message #259867 is a reply to message #259859] Thu, 16 August 2007 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too long code for me.
Remove all parts till you don't the error then you have the root.

Regards
Michel
Re: User Defined errors [message #259869 is a reply to message #259867] Thu, 16 August 2007 13:25 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Its run successfully
SQL> @$TS_DB/pkg_valid_point_spec.sql

Synonym dropped.


Package created.

No errors.

Package body created.

No errors.

Synonym created.


Grant succeeded.


Grant succeeded.


Grant succeeded.

SQL>


I remove some of the code from package.

[Updated on: Thu, 16 August 2007 13:26]

Report message to a moderator

Re: User Defined errors [message #259909 is a reply to message #259859] Thu, 16 August 2007 17:06 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
DreamzZ wrote on Thu, 16 August 2007 12:14
No Help yet Rolling Eyes

Re: User Defined errors [message #259954 is a reply to message #259909] Thu, 16 August 2007 23:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you use ref-cursors? Why not use a normal cursor? Is it because you oversimplified your code-example?
If so, did you add the exception-handler to the procedure (are its caller) that FETCHES the rows, not the one that defines the ref-cursor?

By the way, don't do select <constant> into <variable> from dual like you do in your first statement. Do a <variable> := <constant> instead.

Also, don't forget to close your ref-cursor.
These last two points are not related to your error.
Re: User Defined errors [message #259971 is a reply to message #259909] Fri, 17 August 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DreamzZ wrote on Fri, 17 August 2007 00:06
DreamzZ wrote on Thu, 16 August 2007 12:14
No Help yet Rolling Eyes



Nobody owes you an answer and surely not in less than 4 hours.
Do like others: wait!

Regards
Michel
Re: User Defined errors [message #260172 is a reply to message #259971] Fri, 17 August 2007 11:34 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Thanks Michel and Frank
Previous Topic: SELECT...FOR UPDATE
Next Topic: Query Help
Goto Forum:
  


Current Time: Sat Dec 10 01:30:57 CST 2016

Total time taken to generate the page: 0.09802 seconds