Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How To Trap No Rows Returned From Select Statement

Re: How To Trap No Rows Returned From Select Statement

From: Gabriele Lamon <g.lamon.UGO_at_mcg-econ.it>
Date: Tue, 6 Jul 2004 09:24:25 +0200
Message-ID: <ccdk3b$38s$1@carabinieri.cs.interbusiness.it>

"Chris Val" <chrisval_at_bigpond.com.au> ha scritto nel messaggio news:118880b0.0407051747.1e9cbdb7_at_posting.google.com...

> CREATE OR REPLACE PROCEDURE PrintMethod ( MethodName VARCHAR2 ) IS
>
> MyException EXCEPTION;
>
> CURSOR MyCursor IS SELECT * FROM USER_SOURCE
> WHERE NAME = UPPER( TRIM( MethodName ) );
>

     i INT;
> 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.
but above you have only "DECLARED" the cursor, no SQL operation has been executed

> IF SQL%NOTFOUND THEN
> RAISE MyException;
> END IF;

this doesn't work because you have to "OPEN" and "FETCH" the cursor to know if you have almost a row returned.

>
> DBMS_OUTPUT.ENABLE( 50000 );
>

        i:=0;
> FOR MyRow IN MyCursor LOOP
> DBMS_OUTPUT.PUT_LINE( 'Line ' || MyCursor%ROWCOUNT || ': ' ||
MyRow.TEXT );

            i := i + 1;
> END LOOP;
    IF i = 0 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;
> /
>

> Any help appreciated.

> Thanks.

Ni hao,

> Chris Val

Lelle.

P.S.: Sorry for my english Received on Tue Jul 06 2004 - 02:24:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US