Problems raising exceptions when using a cursor

From: Aaron Jaque <aaron.jaque_at_cybersurf.net>
Date: Wed, 12 Jun 2002 17:51:10 -0600
Message-ID: <3d07dd66$1_1@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 Received on Wed Jun 12 2002 - 18:51:10 CDT