Cursor returns NO DATA FOUND Exception.

do not forget to compile your package code after adding those
debug messages before you run it otherwise we will not
get to see those debug messages.

Hi,

i did try adding debug messages and thats how i reached the conclusion that the exception arrives just before C_AMF...

like i said above, the problem is,this cursor loops for certain parameters passed to it.. but once it receives a particular set of parameters which coz it to return no rows, it raises an exception of NO DATA FOUND

Cheers,
Rosh

can you paste the code with your debug messages ?

Once you get to reproduce the problem in sqlplus you'll solve it easily: Copy/paste the cursor to sqlplus, substitute the binds by the actual parameters that cause it to fail. My bet is that you are calling some plsql function within the SELECT statement, and the function fails with that error when invoked with those parameters.

Hi,

i have debug messages before Cursor Loop C_AMF and inside the loop...
i get the debug messages for BOTH before and inside the loop...
BUT FOR A CERTAIN COMBINATION OF PARAMETERS PASSED TO C_AMF, i get NO_DATA_FOUND

When i query the C_AMF cusror SQL with these parameters, i get no records... which means no data found... but as we all know, using a cursor-based loop should not raise an exception.

i do have other procedures being called withing cursor loop C_AMF, but im sure for that combination of parameters Oracle cannot enter the loop, hence no question of those procedures returning an exception...

i cannot paste the entire procedure here... its briefly 5000 lines and wont make too much sense...

anything else i need to look into??

im not yet in the fire fighthing stage,coz this exception hasnt popped up at the clients place... but wanna get armed before that happens...

NickPrice0001:
i did try exception handling before ur post but i got compilation errors, will try again,
where do i put my END LOOP in ur code below???
BEGIN
-- Your FOR loop and associated code would go here
EXCEPTION
    WHEN NO_DATA_FOUND
        -- Exit the procedure, with appropriate values for any OUT arguments.
END;



thanx for all the posts guys... im sure ill reach a solution soon... keep posting...

Cheers,
Rosh

can you just paste that chunk of code where you have added
a debug message before the loop and inside the loop ? i am not
asking for the whole code to be pasted

How do we infer that cursor loop for certain combination
is raising that...Do you get the debug message which should be your
first line inside the loop before any code gets executed ..

Also add a debug message at the last line of the code inside the loop
and see whether you are getting that...

Thanks

Diaroshan,

You'd put the end loop inside the Begin / Exception block.  Here's a trivial example:

DECLARE
CURSOR c_test (al_value NUMBER) IS
        SELECT al_value FROM dual WHERE 1 = 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before Cursor');
FOR c_test_rec IN c_test(5) LOOP
        DBMS_OUTPUT.PUT_LINE('In Cursor');
END LOOP;
DBMS_OUTPUT.PUT_LINE('After cursor');
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('No data found exception');
        WHEN OTHERS THEN
                RAISE;
END;

However, this trivial example does not cause the NO_DATA_FOUND exception, and as nav_kum_v is saying, it's not possible for this particular error to occur using a cursor like this.
(I thought I'd managed to replicate it, but I can't do it today, so it must have been a mistake)