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

In article <37bd0bed.586750082_at_news.supernews.com>,   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;
> /
>

The best way to handle the problem of not finding a row on a select into statement is probably to enclose the offending SQL in a begin/end block with an exception claude to trap the error.

  begin
  select some_column
    into v_some_variable
    from some_table
   where some_other_column = v_select_value;   exception
    when no_data_found then null;
  end;

I hope this helps.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 19 1999 - 10:24:18 CDT