Re: NO_DATA_FOUND EXCEPTION

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;