Re: help with select into

From: Brad Worsfold <bworsfold_at_vic.lgs.ca>
Date: 1998/01/16
Message-ID: <01bd2214$7a1070b0$0faf22cf@cerulean>#1/1

Hi Laura.

If you are doing this in a PL/SQL block you can do it a couple of ways:

1. Wrap your statement in an error handler

		BEGIN
			BEGIN
 				Select	match_prod_id into new_prod_id_out
 				from	product_up_cross_sell
 				where	 product_up-cross_sell.base_prod_id = prod_id_in
 				and	product_up_cross_sell.match_type = 'U';

			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					<logic>
				WHEN OTHERS THEN
					<logic>
			END;

			BEGIN

				<Next Statement>

			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					<logic>
				WHEN OTHERS THEN
					<logic>
			END;
		EXCEPTION
			WHEN OTHERS THEN
				<logic>
			
		END;


	2) Embedding your select in a for loop and setting a flag.

	3) Creating a cursor out of your select statement.  Caution, you do not

get an error if no data is returned, you need to test for this in another fashion.

Good luck!

Brad

Laura Bellini <laura.bellini_at_compaq.com]> wrote in article <01bd2109$13d1c9e0$863612ac_at_pw48>...
> Hi.
> I'm trying to do a fairly simple select into within a stored procedure,
 but
> I'm having difficulty getting the statement or statement thereafter to
> handle the situation when no data is returned.
>
> Select match_prod_id
> into new_prod_id_out
> from product_up_cross_sell
> where product_up-cross_sell.base_prod_id = prod_id_in
> and product_up_cross_sell.match_type = 'U'
>
> Prod_id_in is an incoming parameter.
> If there is no row returned, this is fine - new_prod_id_out will be
> 'empty'.
> What I want, however, is to populate new_prod_id_out with another
> variable's value if this select statement returns no rows.
>
> I DO NOT want the procedure to hit an exception and end. All I want to
 do
> is fill in this variable with a value, and continue.
>
> Does anyone know how to do this?
>
> thanks.
> Laura Bellini
> laura_bellini_at_compaq.com
>
Received on Fri Jan 16 1998 - 00:00:00 CST