Problems raising exceptions with Cursors

From: Aaron Jaque <aaron.jaque_at_cybersurf.net>
Date: Wed, 12 Jun 2002 17:43:29 -0600
Message-ID: <3d07db99$1_2@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:43:29 CDT