I am using two cursors. The first is just an implicit one that opens a record that exist a table and works fine as shown here. Found first record is printed.


SELECT <cols>
INTO RET_REC
FROM table1
WHERE key = I_key;


IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('FOUND first record');
END IF;



Then I check for the same key in another table where that key does not yet exist.


OPEN OVR FOR
SELECT *
FROM table2
WHERE key = I_key;

FETCH OVR INTO OVD_NDC;

IF NOT OVR%FOUND THEN
DBMS_OUTPUT.PUT_LINE('NOT FOUND ');
INSERT_RECORD := TRUE;
END IF;


According to the documentation this means that the code will jump to the exception block. But this does not happen. I don't know how a cursor ties itself to the DATA_NOT_FOUND exception. It appears that the block I currenlty have only refers to the implicit cursor since the exception block does not run. Yet the line NOT FOUND is printed indicating that the ref cursor did not return data as I expected. Shouldn't the exception block run?

EXCEPTION


WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('SHOULD CREATE RECORD');
if(INSERT_RECORD ) then
-- insert record
end if;

Not knowing which cursor would trigger this I set the INSERT_RECORD flag to true to place a record into the second table. Yet since this is not running checking that flag is useless.


What do I need to make sure that when the ref cursor causes the DATA_NOT_FOUND event that I can capture it?
Thanks in advance.