Re: Problems raising exceptions with Cursors

"Aaron Jaque" <aaron.jaque_at_cybersurf.net> writes:

> I'm having problems raising a NO_DATA_FOUND exception when using a cursor.
> TYPE ACCOUNT_CURSOR IS REF CURSOR RETURN ACCOUNT_REC;
> BEGIN
> OPEN ACCOUNTDATA_OUT FOR
> SELECT ACCOUNT_ID, FIRST_NAME, LAST_NAME, PHONE_NUM,
> EMAIL_ADDRESS, PROVINCE_ID
> FROM CLIENT_PROFILES
> WHERE ACCOUNT_ID = ACCOUNTID_IN;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> Can anyone tell me why the NO_DATA_FOUND exception is not being raised when
> no records are returned by the select statement, and if there is a way to
> raise an exception in this situation? I've also tried to use the different
> cursor attributes such as %NOTFOUND and %ROWCOUNT but these don't seem to
> help either.
>

I think you may have a conceptual error regarding what is happening where. Your above prceedure is 'defining' an explicit cursor. Errors such as NO_DATA_FOUND etc only occur at the point when the cursor attempts to retrieve data - for example, if your definition was being used with an implicit cursor you might get the error because it is actually trying to retrieve data at the point where the cursor definition is encountered. Here, you are only specifying the definition - the cursor still needs to be opened and data fetched.

With explicit cursors, you usually use the %NOTFOUND, %FOUND cursor variables to test for data within some form of loop or use a cursor FOR loop - but at the point where you are retrieving data - in this case, probably within the block which calls your package procedure that returns the ref cursor - this is where you would probably want to put your exception handling elements which will check for NO_DATA_FOUND (though i would probgably just do an initial fetch before the loop and throw an application error if n%NOTFOUND was true after the first fetch or something like that.

Tim Received on Sat Jun 15 2002 - 23:38:12 CDT