Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How To Trap No Rows Returned From Select Statement
"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