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

No rows returned may be acceptable, but you'll need to tell this to Oracle explicitly. The program is reacting to an error condition of no data found and terminating.

You'll want to enclose your select statements in a BEGIN- END block and put in an exception handler for no data found.

refer to "Exception Handling" in your PL/SQL reference.

-Pat Ryan
 Chair - Puget Sound Oracle Users Group  mailto:pat_at_halcyon.com
 http://www.halcyon.com/pat

soybean (soybean_at_hushmail.com) 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 Wed Aug 25 1999 - 17:42:01 CDT