Re: OPEN cursor FOR SELECT... Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/09
Message-ID: <34661d28.199091218@newshost>#1/1

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;

 17* end;

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  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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