Re: Problems raising exceptions when using a cursor

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

why do so many people believe:

o a select that returns no rows is an error o an update of zero records is an error
o a delete of zero records is an error
o an insert of zero records is an error

None of them are.

A explicit cursor like that with OPEN/CLOSE -- will NEVER throw NO_DATA_FOUND. the only thing that throws that exception is a SELECT INTO. Only with a SELECT INTO is "zero rows returned" an error.

The client that recieves this ref cursor will be responsible for FETCHING the data from this cursor. They are the only ones that can decide if after failing to fetch a single row -- that means "error". You have to actually FETCH a record -- test the cursor attribute "cursor_name%found" -- and then decide what you want to do.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp