Re: Problems raising exceptions when using a cursor

In article <3d07dd66$1_1_at_news.cybersurf.net>,  "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

I admit I'm no great shakes at PL/SQL, but don't you have to declare ACCOUNTID_IN as "IN"?).

-- 
The underscore character does not belong in my address. You know the drill...
***
Anyone sufficiently smart enough to configure and use USEnet for research should
be smart enough to Read The Freakin' Documentation!