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

You are using an implicit cursor (basically, you didn't define the cursor before you used it) in this example. If you would decide to stay with the implicit cursor, use the Exception NO_DATA_FOUND. Someone mentioned using %NOTFOUND, but I believe this is only for explicit cursors.

i.e.

Exception

    When NO_DATA_FOUND

                return -1;

soybean <soybean_at_hushmail.com> wrote in message news:37be15e7.589304444_at_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 - 15:33:48 CDT