Home » SQL & PL/SQL » SQL & PL/SQL » How to find UTL_FILE end of file
How to find UTL_FILE end of file [message #27205] Wed, 03 September 2003 07:32 Go to next message
Sunil
Messages: 132
Registered: September 1999
Senior Member
Hi,
I am using UTL_FILE to read a flat file, for which I am using LOOP, END LOOP,
However, on reaching last record, after that when it tries to fetch record, not finding anything it generates no-dat-found error. could we put some condition in UTL_FILE like when EOF then exit. ?
Re: How to find UTL_FILE end of file [message #27206 is a reply to message #27205] Wed, 03 September 2003 07:54 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
quote from Sunil:
----------------------------------------------------------------------
Hi,
I am using UTL_FILE to read a flat file, for which I am using LOOP, END LOOP,
However, on reaching last record, after that when it tries to fetch record, not finding anything it generates no-dat-found error. could we put some condition in UTL_FILE like when EOF then exit. ? 

----------------------------------------------------------------------
Sure, just nest a BEGIN/EXCEPTION/END within your LOOP, and EXIT it when you hit the NO_DATA_FOUND:
DECLARE
    l_input         UTL_FILE.FILE_TYPE;
    l_buffer        VARCHAR2(1000);
BEGIN
    l_input := UTL_FILE.FOPEN('/tmp','my_filename.txt','r');
    LOOP
        BEGIN
            UTL_FILE.GET_LINE(l_input,l_buffer);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
        <i>do something with l_buffer here...</i>
        ...
    END LOOP;

    UTL_FILE.FCLOSE(l_input);    
END;
Hope this helps,

A.
Re: How to find UTL_FILE end of file [message #27220 is a reply to message #27206] Wed, 03 September 2003 21:54 Go to previous message
Sunil
Messages: 132
Registered: September 1999
Senior Member
Thanks Art,
That was exactly what i was looking for. Thanks
Previous Topic: # of records in 3 tables
Next Topic: The number of a row inside a table (without ROWNUM)
Goto Forum:
  


Current Time: Tue Aug 19 04:04:12 CDT 2025