Home » SQL & PL/SQL » SQL & PL/SQL » Error during fetching data in cursor
icon9.gif  Error during fetching data in cursor [message #186577] Tue, 08 August 2006 10:03 Go to next message
itsshardul
Messages: 3
Registered: August 2006
Junior Member
In a batch that I run everyday, I have a cursor which takes a ref. no. as input parameter and same is used for cursor's query(and nothing else).

Sometimes the batch fails(please note sometimes not always); the error comes for -- IF <cursor>%NOTFOUND.
However when I run the cursor query separately, I get the record Mad . There is no delete DML on the referred table.

Please help me with this!!
Re: Error during fetching data in cursor [message #186589 is a reply to message #186577] Tue, 08 August 2006 11:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Could you provide just a bit more information? A look at the actual code might help.
Re: Error during fetching data in cursor [message #186757 is a reply to message #186589] Wed, 09 August 2006 06:49 Go to previous messageGo to next message
itsshardul
Messages: 3
Registered: August 2006
Junior Member
---------------------------------------------------------
CURSOR acc_info (p_acc tms_account.ac_no%TYPE)
IS
SELECT a.*
FROM tms_account a
WHERE ac_no = p_acc
and status= 'O';
---------------------------------------------------------

Above is a cursor defined in the package definition.

Inside the package body the following code is there:
-------------------------------------------------------------
FUNCTION get_details(pkg_acc IN OUT tms_account%ROWTYPE, ercode IN OUT VARCHAR2, ermsg IN OUT VARCHAR2)
RETURN BOOLEAN IS
BEGIN

OPEN cr_sttm_cust_acc(pkg_acc.ac_no);

FETCH cr_tms_acc
INTO pkg_acc;

IF cr_tms_acc%NOTFOUND THEN
CLOSE cr_tms_acc;

ercode := '0001';
ermsg := p_acc;
RETURN FALSE;
END IF;

CLOSE cr_tms_acc;

RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
ercode := '0002';
ermsg := SQLERRM;
RETURN FALSE;
END;
------------------------------------------------------------

Finally I get the ercode as '0001' and ermsg as the p_acc. Hope the above code suffices.

Regards
Re: Error during fetching data in cursor [message #186763 is a reply to message #186757] Wed, 09 August 2006 07:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
errcode and errmsg are IN out, so they could maintain their values from a previous call. I suppose they should be defined as OUT-only.
(pkg_acc should be IN-only, as you don't change it)

You say there is no process deleting records. Could it be that a record is inserted, or (status) updated between the time the batch runs and you check ?
Re: Error during fetching data in cursor [message #186781 is a reply to message #186763] Wed, 09 August 2006 08:24 Go to previous messageGo to next message
itsshardul
Messages: 3
Registered: August 2006
Junior Member
Frank,

To answer the concerns mentioned by you:

The ercode '0001' is only used here and nowhere else; Possible that still it may get the above value. But the function returns 'false'; that's more worrisome to me. Also you see that when function return false, only two values for ercode are possible '0001' / '0002'.

On entering this function, only pkg_acc.ac_no is populated. The function serves to fetch the whole record, corresponding to a particular ac_no. Hence it is made IN OUT.

As far as the status of any record goes, there is a check before and after the batch(which encloses the below function) is run. The batch process will be skipped if the status <> 'O'; in this case the below function will not even be touched. Also the batch itself does not modify any record in the concerned table.

The surprising part is this function runs successfully 9 out of 10 times.


Re: Error during fetching data in cursor [message #186783 is a reply to message #186781] Wed, 09 August 2006 08:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

On entering this function, only pkg_acc.ac_no is populated. The function serves to fetch the whole record, corresponding to a particular ac_no. Hence it is made IN OUT.

Ah sure. Should have seen that.

Where did you declare the p_acc that you assign to errmsg??
Could it be that this is a package global that keeps a previous value? (it is not declared in your function)
Previous Topic: n00b View question!
Next Topic: Ora10g MVs & QR
Goto Forum:
  


Current Time: Sun Dec 11 02:36:19 CST 2016

Total time taken to generate the page: 0.07776 seconds