Re: Problems raising exceptions with Cursors

"Aaron Jaque" <aaron.jaque_at_cybersurf.net> wrote in message news:<3d07db99$1_2_at_news.cybersurf.net>...
> 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

The no_data_found error does not apply to an explicit cursor like your example is using. If you performed a select into statement then it must return one and only one row. The no_data_found exception would apply. If you were not returning a row set I would tell you to check the %rowcount cursor attribute after your fetch. With a result set the fetch is going to occur at the caller so I believe you would need to do your checking there.

HTH -- Mark D Powell -- Received on Thu Jun 13 2002 - 09:14:31 CDT