Re: Problems raising exceptions when using a cursor

On Wed, 12 Jun 2002 17:51:10 -0600, "Aaron Jaque" <aaron.jaque_at_cybersurf.net> wrote:

>I'm having problems raising a NO_DATA_FOUND exception when using a cursor.
>This is an example of my code.
>This is in the package spec:
>
>TYPE ACCOUNT_REC IS RECORD(
> ACCOUNT_ID NUMBER(10, 0),
> FIRST_NAME VARCHAR2(100),
> LAST_NAME VARCHAR2(100),
> PHONE_NUM VARCHAR2(40),
> EMAIL VARCHAR2(100),
> PROVINCE_ID NUMBER(10, 0),
>);
>TYPE ACCOUNT_CURSOR IS REF CURSOR RETURN ACCOUNT_REC;
>
>This is in the package body:
>
>PROCEDURE GET_ACCOUNT_INFO(ACCOUNTID_IN INTEGER, ACCOUNTDATA_OUT OUT
>ACCOUNT_CURSOR)
>IS
>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
> RAISE_APPLICATION_ERROR(-20221, 'NO ACCOUNT INFO FOUND');
>END;
>
>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.
>
>Thanks in advance,
>Aaron
>
>

An open cursor doesn't FETCH. The NO_DATA_FOUND exception will be only set after a FETCH. Check your PL/SQL manual.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jun 13 2002 - 00:09:35 CDT