Re: PL/SQL cursor question

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: Wed, 08 Jul 1998 02:08:44 GMT
Message-ID: <6nukb3$9g5@bgtnsc03.worldnet.att.net>

mOn 7 Jul 1998 22:43:06 GMT, iancrozier_at_aol.com (Iancrozier) wrote:

>How can I stop the program from bombing just because no rows are selected for
>this select statement.
>I would like to place a value in a variable when no rows are found, instead
>of having the program abort.

Embed the offending statement in a block of its own. Something like this:

DECLARE

	data_found	boolean;
	my_var		char;
BEGIN
	--Execute a SELECT, and set the flag depending
	--on whether it succeeds or fails.
	BEGIN
		SELECT x into my_var from dual;

		--If we get here, the select worked
		data_found := true;
	EXCEPTION
		--Control comes to this exception handler
		--if an error  occurs in this block.
		WHEN no_data_found THEN
			data_found := false;
		WHEN OTHERS THEN
			--Somethingg really bad happened.
			--Raise the exception to the next level
			raise;
	END;
	--Now you can take appropriate action depending
	--on whether or not data was found.
	if data_found then
		dbms_output.put_line('data found');
	else
		dbms_output.put_line('no data found');
	end if;

END; The above may not be 100% syntactically correct, but it should give you the idea. Read up in the manuals about exception handling. Also, feel free to post more questions, email me, etc.

regards,

Jonathan Received on Tue Jul 07 1998 - 21:08:44 CDT