Re: NO_DATA_FOUND EXCEPTION

From: Bruce Pihlamae <pihlab_at_cbr.hhcs.gov.au>
Date: 1995/05/15
Message-ID: <1995May15.094549.1_at_cbr.hhcs.gov.au>#1/1

In article <3otfvo$mi7_at_atglab10.atglab.bls.com>, Jay.Cappy1_at_bridge.bst.bls.com writes:

> 
> In article <3o7usl$345_at_pubxfer3.news.psi.net>, 
> <Scott_Mattes_at_NAVSEALOG.I-Net.com> writes:

>> Path:

> atglab.bls.com!gatech!news.mathworks.com!europa.chnt.gtegsc.com!salliemae!uunet
> !in1.uu.net!psinntp!pubxfer.news.psi.net!usenet

>> From: Scott_Mattes_at_NAVSEALOG.I-Net.com (Scott Mattes)
>> Newsgroups: comp.databases.oracle
>> Subject: Re: NO_DATA_FOUND EXCEPTION
>> Date: Wed, 03 May 1995 13:03:49 GMT
>> Organization: PSI Public Usenet Link
>> Lines: 27
>> Message-ID: <3o7usl$345_at_pubxfer3.news.psi.net>
>> References: <3nbutc$4sp_at_news.annex.com>
>> NNTP-Posting-Host: port7.fredericksburg.va.pub-ip.psi.net
>> X-Newsreader: Forte Free Agent v0.46
>>
>> sanctus2_at_annex.com () wrote:
>>
>> >In PL/SQL, if I do a SELECT INTO or a FETCH that returns 0 records, the
>> >NO_DATA_FOUND EXCEPTION is raised. That's all well and good but what do
>> >I do with it? If I write code to only do something if I RETRIEVE
>> >records, why can't I ignore the NO_DATA_FOUND? It ends up generating an
>> >error in my trigger and rolls back my transaction.
 

>> >I want to be able to retrieve no records WITHOUT erroring out my trigger.
 

>> >I'd appreciate any help on this issue.
>> >Thanx....
 

>> >Jack Schwartz
>> >Systems Analyst
>> >Great Western Bank
>>
>> Being new to all this I may be missing something, but from what I have
>> read and what you say you want to do I would code an exception handler
>> that basicly ignored that error so that my block could continue on
>> (the pl/sql book I have says that I would use 'when others' and put a
>> do nothing type of pl/sql command in it's body and then after it was
>> done execution would return to the line of code just after the one
>> that caused the exception).
>>
>>
>>

> You have to create an exception within the procedure that has the select into 
> clause.  Read chapter 5 in the PL/SQL User guide and Reference Version 2.0.
> 
> An example to check referential integrity follows:
> 
> 	BEGIN
> 		SELECT rownum
> 		INTO found_a_row
> 		FROM XYZ_table
> 		WHERE ...;
> 
> 	EXCEPTION
> 		WHEN NO_DATA_FOUND THEN
> 			found_a_row := 0;
> 	END;
> 
> 	BEGIN
> 		IF found_a_row = 0 THEN
> 
> 			SELECT rownum
> 			INTO found_a_row
> 			FROM abc_table
> 			WHERE ...;
> 		END IF;
> 
> 	EXCEPTION
> 		WHEN NO_DATA_FOUND THEN
> 
> 			SELECT error_message
> 			into err_msg
> 			FROM user_errors     -- set up error table
> 			WHERE error_number = error_num;
> 
> 			err_msg := 'your error message';
> 
> 			raise_application_error(error_num, err_msg);
> 
> 	END;
> 

If you code an EXPLICIT cursor OPEN...FETCH...CLOSE then you will find that the NO_DATA_FOUND EXCEPTION is NOT raised and you can check cursor%FOUND or cursor%NOTFOUND and proceed from there on.  

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras

Received on Mon May 15 1995 - 00:00:00 CEST