Re: How To Trap No Rows Returned From Select Statement

chrisval_at_bigpond.com.au (Chris Val) wrote in message news:<118880b0.0407051747.1e9cbdb7_at_posting.google.com>...
> Hi all.
>
> I am having trouble working out how to trap whether
> my select statement returned any rows - This seems
> to be more difficult with implicit cursors, and I
> been able to figure it out :-).
>
> I would like to trap for the user entering the wrong name,
> and displaying an appropriate message as shown, hopefully:
>
> The procedure has just enough code to demonstrate the problem:
>
> CREATE OR REPLACE PROCEDURE PrintMethod ( MethodName VARCHAR2 ) IS
>
> MyException EXCEPTION;
>
> CURSOR MyCursor IS SELECT * FROM USER_SOURCE
> WHERE NAME = UPPER( TRIM( MethodName ) );
>
> BEGIN
>
> -- I would like to enter the exception handler if 'MethodName'
> -- was not found in the select statement above, otherwise, if
> -- rows were returned by the select statement, then display them.
> IF SQL%NOTFOUND THEN
> RAISE MyException;
> END IF;
>
> DBMS_OUTPUT.ENABLE( 50000 );
>
> FOR MyRow IN MyCursor LOOP
> DBMS_OUTPUT.PUT_LINE( 'Line ' || MyCursor%ROWCOUNT || ': ' || MyRow.TEXT );
> END LOOP;
>
> EXCEPTION
> WHEN MyException THEN
> DBMS_OUTPUT.PUT_LINE( 'Oop''s - That name could not be found ... ' );
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE( 'Oop''s - Unknown Error Raised ... ' );
> END;
> /
>
> ...but, when the name is not found, all I get is the message:
> PL/SQL procedure successfully completed.
>
> I would like to have my exception handler invoked, so
> I can display my message, and if possible, also remove
> the "PL/SQL procedure successfully completed." message
> from following it.
>
> For Example:
> -- Looking for method named 'GetNumber'
>
> SQL> EXEC PrintMethod( 'GetNumber' );
> -- Prints out all the lines of 'GetNumber', as expected.
>
> -- Note now the bad name passed in on purpose...
> SQL> EXEC PrintMethod( 'GetBumber' );
> -- Prints out:
> PL/SQL procedure successfully completed.'
>
> Why does SQL%NOTFOUND not return true ?, and how do I fix it ?
>
> Any help appreciated.
>
> Thanks.
> Chris Val

SQL%NOTFOUND doesn't work because you have not performed any SQL at that point in the program. You can do this:

CREATE OR REPLACE PROCEDURE PrintMethod ( MethodName VARCHAR2 ) IS

  l_found BOOLEAN := FALSE;
  MyException EXCEPTION;

  CURSOR MyCursor IS SELECT * FROM USER_SOURCE

                  WHERE NAME = UPPER( TRIM( MethodName ) )
                  ORDER BY line;

BEGIN    DBMS_OUTPUT.ENABLE( 50000 );    FOR MyRow IN MyCursor LOOP

       DBMS_OUTPUT.PUT_LINE( 'Line ' || MyCursor%ROWCOUNT || ': ' || MyRow.TEXT );

       l_found := TRUE;
   END LOOP;        IF NOT l_found THEN

       RAISE MyException;
   END IF; EXCEPTION
   WHEN MyException THEN

        DBMS_OUTPUT.PUT_LINE( 'Oop''s - That name could not be found ... ' );

   WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( 'Oop''s - Unknown Error Raised ... ' ); END;
/

(I took the liberty of adding a useful ORDER BY clause to the query). Received on Tue Jul 06 2004 - 07:09:58 CDT