Re: PL/SQL Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 05 Nov 1999 15:57:10 -0500
Message-ID: <JEQjOLSLmhsb+y6DabhGZpPSz3j=@4ax.com>

A copy of this was sent to amerar_at_ci.chi.il.us (if that email address didn't require changing) On Fri, 05 Nov 1999 20:18:34 GMT, you wrote:

>
>
>
>Hello,
>
>This code is giving me an invalid cursor. What is wrong with it? It is
>going down on the CLOSE statement.......
>
>
>CURSOR revanal_strts_cursor IS
> select * from tx_street_sanitation;
>
>BEGIN
> UTL_FILE_HANDLE1 := UTL_FILE.FOPEN(UTL_PATH,UTL_STRTS_FILENAME,'w');
>
> FOR v_revanal_strts_curs_rec IN revanal_strts_cursor
> LOOP
> v_strts_rec_read := v_strts_rec_read + 1;
> v_strts_batch_amt := v_strts_batch_amt +
> TO_NUMBER(NVL(v_revanal_strts_curs_rec.amt_cash,0)) +
> TO_NUMBER(NVL(v_revanal_strts_curs_rec.amt_check,0)) +
> TO_NUMBER(NVL(v_revanal_strts_curs_rec.amt_credit,0));
> END LOOP;
> UTL_FILE.PUT_LINE(UTL_FILE_HANDLE1,
> 'BH' || '99 ' ||
> TO_CHAR(sysdate,'DD') ||
> LPAD(SUBSTR(TO_CHAR(v_strts_rec_read),7,4),4,'0') ||
> LPAD(TO_CHAR(v_strts_batch_amt),10,'0') ||
> TO_CHAR(sysdate,'MMDDYY') ||
> LPAD(' ',53,' '));
>
> CLOSE revanal_strts_cursor;

the cursor is already close for you and you do not need to reopen it. Cursor for loops do the implicit OPEN/CLOSE automagically like so:

  1 declare
  2 cursor c1 is select ename from emp;   3 begin

  4     for x in c1 loop        
  5             null;
  6     end loop;
  7     for x in c1 loop
  8             null;
  9     end loop;

 10* end;
 11 /

PL/SQL procedure successfully completed.

You only need to OPEN/CLOSE them in an explicit loop such as:

  1 declare

  2     cursor c1 is select ename from emp;
  3     l_ename varchar2(25);
  4  begin
  5     open c1;
  6     loop
  7             fetch c1 into l_ename;
  8             exit when c1%notfound;
  9     end loop;
 10     close c1;

 11* end;
 12 /

PL/SQL procedure successfully completed.

> OPEN revanal_strts_cursor;
>
> FOR v_revanal_strts_curs_rec IN revanal_strts_cursor
> LOOP
> BEGIN
> .
> .
> .
> .
>
>Anyone see anything?
>
>Thanks,
>
>Arthur
>amerar_at_ci.chi.il.is
>
>ps. Please cc copy to e-mail
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 05 1999 - 14:57:10 CST