Re: How To Trap No Rows Returned From Select Statement

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 05 Jul 2004 20:22:25 -0700
Message-ID: <1089084180.553222@yasure>

Chris Val wrote:
> 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

Because at the point where it executes it is meaningless. How could anything not be found when nothing yet has happened?

CREATE OR REPLACE PROCEDURE PrintMethod ( MethodName VARCHAR2 ) IS

CURSOR MyCursor IS
SELECT *
FROM USER_SOURCE
WHERE NAME = UPPER(TRIM(MethodName));

i PLS_INTEGER;

BEGIN
   SELECT COUNT(*)
   INTO i
   FROM user_source
   WHERE name = UPPER(TRIM(MethodName));

   IF i = 0 THEN

      DBMS_OUTPUT.PUT_LINE('Oop''s - ....');    ELSE

     FOR MyRow IN MyCursor LOOP
       DBMS_OUTPUT.PUT_LINE( 'Line ....');
     END LOOP;

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

But why reinvent the wheel? Take a look at the DBMS_METADATA built-in package.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Jul 05 2004 - 22:22:25 CDT