Re: LOOP question

From: Kenneth C Stahl <BlueSax_at_Unforgettable.com>
Date: Thu, 16 Sep 1999 07:37:56 -0400
Message-ID: <37E0D694.C571A3F0@Unforgettable.com>

Iancrozier wrote:

> I have code something like:
> DECLARE
>
> v_column number;
>
> CURSOR a IS SELECT column FROM table;
>
> BEGIN
>
> OPEN a;
>
> LOOP
>
> FETCH a INTO v_column;
>
> mesg := (RPAD(vcolumn,12));
>
> UTL_FILE.PUTF(file_name,'%s\n',mesg);
>
> UTL_FILE.PUT_LINE(file_name,' . ');
>
> UTL_FILE.FFLUSH(file_name);
>
> EXIT WHEN A%NOTFOUND;
>
> END LOOP;
>
> END;
>
> -- End of code
>
> When I look into file_name, the last row is always in there TWICE!
>
> What am I doing wrong? TIA
>
> Ian Crozier

You have a logic problem which others have pointed out, but the real solution is to code the loop properly with a cursor FOR loop as follows:

DECLARE
    v_column number;
    file_handle utl_file.file_type;

    CURSOR A IS
    SELECT column
    FROM table;

BEGIN
   file_handle := utl_file.fopen('myfile','r');     For I in A Loop

        mesg := Rpad(i.column);
        utl_file.put_line(file_handle,mesg);
        utl_file.fflush(file_handle);

    End Loop;
End;

By the way, you'd never pass a file name to put_line or fflush.

---

---

---

---

---

---

---

Received on Thu Sep 16 1999 - 06:37:56 CDT