Re: what's going on with SELECT INTO in PL/SQL?

You need to read up on pl/sql error handling. If no data is returned by the select, pl/sql throws an exception. The solution is to place each SELECT statement inside its own block. Try the following version of your function. I think the syntax is correct. I didn't compile it though.

Jonathan

CREATE OR REPLACE FUNCTION select_test ( in_isbn_id number)
RETURN VARCHAR2 iS

	l_isbn DBO.ISBN%ROWTYPE;
	s_isbn DBO.ISBN_STAGE%ROWTYPE;
	error_flag number;

BEGIN
	--init the error flag
	error_flag := 0;

	BEGIN	
	    SELECT *
		INTO l_isbn
		FROM DBO.ISBN
		WHERE ISBN_ID=in_isbn_id;
	EXCEPTION
	   WHEN OTHER THEN
		error_flag := 1;		
	END;

 	BEGIN
	   SELECT *
		INTO s_isbn
		FROM DBO.ISBN_STAGE
		WHERE ISBN_ID=in_isbn_id;
	EXCEPTION
	  WHEN OTHER THEN
		error_flag :=1;
	END;

	if error_flag = 1 then
		RETURN 'hello';
	else
		RETURN 'failure';
	endif;

END select_test;
/

On Thu, 19 Aug 1999 14:34:22 GMT, soybean_at_hushmail.com (soybean) wrote:

>I am working on a trigger that needs to do a select from a different
>table then the one that the trigger functions on. But I can't really
>figure out how to deal with the case where the select returns no rows,
>which is acceptable.
>
>I wrote this function to try to test the functionality of selects in
>pl/sql, but it's not helping much. The first thing that I can't figure
>out is why this function returns 'hello' if both selects succeed
>otherwise it returns nothing. How do I check for the success of the
>select? Why does it ever return nothing? what is s_isbn after a failed
>select into?
>
>CREATE OR REPLACE FUNCTION select_test (
>in_isbn_id number)
>RETURN VARCHAR2 iS
>
> l_isbn DBO.ISBN%ROWTYPE;
> s_isbn DBO.ISBN_STAGE%ROWTYPE;
>BEGIN
>
> SELECT *
> INTO l_isbn
> FROM DBO.ISBN
> WHERE ISBN_ID=in_isbn_id;
>
> SELECT *
> INTO s_isbn
> FROM DBO.ISBN_STAGE
> WHERE ISBN_ID=in_isbn_id;
>
> RETURN 'hello';
>
>END select_test;
>/
>
>I'd appreciate any insight anyone can offer me.
> Thanks
> Soy
Received on Thu Aug 19 1999 - 22:44:28 CDT