Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: OPEN cursor FOR SELECT... Question
On Sun, 09 Nov 1997 14:25:39 -0500, Dan Cunningham <dcunning_at_wam.umd.edu> wrote:
>Hello there,
> I have a question concerning testing a select that is used in opening a
>cursor.
>
> If I have the following:
> OPEN c_return FOR
> SELECT CompanyName, AccountNum
> FROM AccountsTable
> WHERE AccountNum = term;
> can I use the SQL%... attributes (specifically, ROWCOUNT and
>NOTFOUND) and/or catch the exception NO_DATA_FOUND for the select
>statement?
>
Yes and no. You can't use SQL% (since you are using a named cursor) and the exception isn't raised (NO_DATA_FOUND isn't raised for FETCH, select ... into ... raises no data found). but you can reference
c_return%notfound
c_return%rowcount
consider:
1 declare
2 type curs is ref cursor; 3 c_return curs; 4 l_ename varchar(25); 5 begin 6 open c_return for select ename from emp where rownum < 5; 7 loop 8 fetch c_return into l_ename; 9 if ( c_return%notfound ) 10 then 11 dbms_output.put_line( 'Not Found' ); 12 exit; 13 end if; 14 dbms_output.put_line( c_return%rowcount || ' ' || l_ename ); 15 end loop; 16 close c_return;
SQL> /
1 ALLEN
2 WARD
3 JONES
4 MARTIN
Not Found
SQL>
>Thank you,
>Daniel Cunningham
>dcunning_at_umdstu.umd.edu
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Nov 09 1997 - 00:00:00 CST
![]() |
![]() |