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

From: soybean <soybean_at_hushmail.com>
Date: Thu, 19 Aug 1999 14:34:22 GMT
Message-ID: <37be15e7.589304444@news.supernews.com>

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 - 09:34:22 CDT