How To Trap No Rows Returned From Select Statement

From: Chris Val <chrisval_at_bigpond.com.au>
Date: 5 Jul 2004 18:47:56 -0700
Message-ID: <118880b0.0407051747.1e9cbdb7@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 Received on Mon Jul 05 2004 - 20:47:56 CDT