Skip to Main Content
  • Questions

  • ORA-29284: file read error for a few lines

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rachit.

Asked: November 06, 2017 - 10:07 pm UTC

Last updated: March 12, 2025 - 8:17 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hi Experts,

Thanks for taking the time out to ready my Question.

I am receiving a file from a third party as a flat file, with different lines of different lengths. The first two characters of each line represents what data that line will have. (Please let me know how can I share the file with you). A line can have the following types with the lengths specified. 

FH-277
MH-424
TR-1598
PR-1465
LR-1872
MT-323
FT-321<code>

An ideal file will be in the following format. There can be multiple TR, PR and LR lines. 

<code>FH
   MH
      TR1
         PR11
            LR111
            LR112
            LR113
      TR2
         PR21
            LR211
            LR212
         PR22
            LR221
            LR222
   MT
FT


Each line may have * or + or - symbols in them.

FH04092012ADMIN100040                                                                                                                                                                                                                                                                
MH********55681075T111                                              TRAVEL CARD                                       100161111                                                                                                                                                                                                                                                                                                         
TR************0921031420120000124717052073640733148761TESTID49                      US TEST MERCHANT RETAIL       612-9737304         MN 55415     840+000000000000001.000313201205999MISCELLANEOUS AND SPECIAL                                 0DTS2596001437847                       +000000000000000.00+000000000000001.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
PRDTS2596001437847008201340010242011Y03222012P+000000000000001.00-000000000000002.00+000000000000001.00************0921031420120000124717052073640733148761840+000000000000001.0003132012                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
MT********55681075+000000000000008.00000000001400000000120000000012                                                                                                                                                                                                                                                                
FT04092012ADMIN1+000000000000008.00000000001400000000120000000012


I am reading the file using UTL_FILE line by line, using the code below. 

DECLARE
   fileHandler    UTL_FILE.FILE_TYPE;
   lineData       CLOB;
   lineType       VARCHAR2(2);
   excep          EXCEPTION;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Begin');
   fileHandler := UTL_FILE.FOPEN('DIRECTORY','FILENAME','R');
   DBMS_OUTPUT.PUT_LINE('File Opened');
   IF UTL_FILE.IS_OPEN(fileHandler)
      THEN
      DBMS_OUTPUT.PUT_LINE('File is open');
      LOOP
         BEGIN
            DBMS_OUTPUT.PUT_LINE('----');
            lineData := NULL;
            UTL_FILE.GET_LINE(fileHandler, lineData);
            DBMS_OUTPUT.PUT_LINE('Line Read');
            IF lineData IS NULL
               THEN
               DBMS_OUTPUT.PUT_LINE('Null');
               EXIT;
            END IF;
            DBMS_OUTPUT.PUT_LINE('Here');
            lineType := SUBSTR(lineData,1,2);
            DBMS_OUTPUT.PUT_LINE('lineType : '||lineType);
            IF lineType = '  '
               THEN
               DBMS_OUTPUT.PUT_LINE(SUBSTR(lineData,1,50));
               RAISE excep;
            END IF;
            IF lineType = 'FH'
               THEN
               dbms_output.put_line('FH');
            ELSIF lineType = 'MH'
               THEN
               dbms_output.put_line('MH');
            ELSIF lineType = 'TR'
               THEN
               dbms_output.put_line('TR');
            ELSIF lineType = 'PR'
               THEN
               dbms_output.put_line('PR');
            ELSIF lineType = 'LR'
               THEN
               dbms_output.put_line('LR');
            ELSIF lineType = 'MT'
               THEN
               DBMS_OUTPUT.PUT_LINE('MT');
            ELSIF lineType = 'FT'
               THEN
               DBMS_OUTPUT.PUT_LINE('FT');
            ELSE
               DBMS_OUTPUT.PUT_LINE('ERR');
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
               THEN
               DBMS_OUTPUT.PUT_LINE('No Data Found');
               EXIT;
            WHEN OTHERS
               THEN
               dbms_output.put_line(SQLERRM);
         END;
      END LOOP;
      UTL_FILE.FCLOSE(fileHandler);
      DBMS_OUTPUT.PUT_LINE('File Closed');
   END IF;
   DBMS_OUTPUT.PUT_LINE('Exiting');
END;
/


The output of the code above is : 

Begin
File Opened
File is open
----
Line Read
Here
lineType : FH
FH
----
Line Read
Here
lineType : MH
MH
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
ORA-29284: file read error
----
Line Read
Here
lineType :   
                                                  
User-Defined Exception
----
Line Read
Here
lineType : MT
MT
----
Line Read
Here
lineType : FT
FT
----
No Data Found
File Closed
Exiting


My Observations:
1. It reads the first two lines correctly (FH and MH)
2. It throws an error starting on line 3
3. The next line it reads after the error is a a line with all blanks.
4. It reads the last two lines correctly again (MT and FT).

I am not sure why is this happenning. Any help will be greatly appreciated.

Thanks,
Rachit

and Chris said...

Stripping down your example and running it, I see:

declare
  filehandler   utl_file.file_type;
  linedata      varchar2(32767);
  linetype      varchar2( 2 );
  excep exception;
begin

  filehandler   := utl_file.fopen( 'TMP','test.txt','R');
  dbms_output.put_line( 'File Opened' );
  if
    utl_file.is_open( filehandler )
  then
    dbms_output.put_line( 'File is open' );
    loop
      begin
        dbms_output.put_line( '----' );
        linedata   := null;
        utl_file.get_line( filehandler,linedata,32767 );
        
        linetype   := substr( linedata,1,2 );
        dbms_output.put_line( 'lineType : ' || linetype );

      exception
        when no_data_found then
          dbms_output.put_line( 'No Data Found' );
          exit;
        when others then
          dbms_output.put_line( sqlerrm );
      end;
    end loop;

    utl_file.fclose( filehandler );
    dbms_output.put_line( 'File Closed' );
  end if;

end;
/

File Opened
File is open
----
lineType : FH
----
lineType : MH
----
ORA-29284: file read error
----
lineType :   
----
ORA-29284: file read error
----
lineType :   
----
lineType : MT
----
lineType : FT
----
No Data Found
File Closed


So what's the problem?

You've got some loooooong lines in your file. utl_file.fopen has a max_linesize parameter. Which default to 1024. You're going over this limit.

Specify a bigger value (than the max line length) it and all should be fine:

declare
  filehandler   utl_file.file_type;
  linedata      varchar2(32767);
  linetype      varchar2( 2 );
  excep exception;
begin

  filehandler   := utl_file.fopen( 'TMP','test.txt','R', 4000);
  dbms_output.put_line( 'File Opened' );
  if
    utl_file.is_open( filehandler )
  then
    dbms_output.put_line( 'File is open' );
    loop
      begin
        dbms_output.put_line( '----' );
        linedata   := null;
        utl_file.get_line( filehandler,linedata,32767 );
        
        linetype   := substr( linedata,1,2 );
        dbms_output.put_line( 'lineType : ' || linetype );

      exception
        when no_data_found then
          dbms_output.put_line( 'No Data Found' );
          exit;
        when others then
          dbms_output.put_line( sqlerrm );
      end;
    end loop;

    utl_file.fclose( filehandler );
    dbms_output.put_line( 'File Closed' );
  end if;

end;
/

File Opened
File is open
----
lineType : FH
----
lineType : MH
----
lineType : TR
----
lineType : PR
----
lineType : MT
----
lineType : FT
----
No Data Found
File Closed

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect Solution - Works absolutely Fine

Rachit Anand, November 07, 2017 - 3:17 pm UTC

Thanks for providing a solution quickly.

I was not aware of the default size limit on the file and while reading each line. After I increased the size limits while opening the file and while reading each line, it worked perfectly.

Thanks once again for your help.
Chris Saxon
November 08, 2017 - 11:12 am UTC

No problem!

Thanks for the solution

Shirley Liu, June 09, 2022 - 3:39 pm UTC

I have the same issue about the maximum size for get line. After I defined the new one it works!

Thank you so much!

Shirley Liu
Chris Saxon
June 10, 2022 - 8:11 am UTC

You're welcome.

Gracias

A reader, October 04, 2022 - 4:57 pm UTC

Excelente; muy clara la solución.
Me sirvió, llevaba días sin dar con esta solución.

Gracias.
Connor McDonald
October 06, 2022 - 4:36 am UTC

de nada

ORA-29284: file read error on reading xml 38kb file

Imran Khan, March 10, 2025 - 8:16 am UTC

Hi Chris ,
i am also getting the same error on reading xml file (38kb), i have changed the max line size 32767, but still facing the error

DECLARE
v_line VARCHAR2(32767);
v_file UTL_FILE.FILE_TYPE;
linetype varchar2( 2 );
BEGIN
v_file := UTL_FILE.FOPEN('ik_dir', '12.xml', 'r', 32767);
dbms_output.put_line( 'File Opened' );

if
utl_file.is_open( v_file )
then
dbms_output.put_line( 'File is open' );
LOOP
BEGIN
v_line := null;
utl_file.get_line( v_file,v_line,32767 );

linetype := substr( v_line,1,2 );
dbms_output.put_line( 'lineType : ' || linetype );

exception
when no_data_found then
dbms_output.put_line( 'No Data Found' );
exit;
when others then
dbms_output.put_line( sqlerrm );
END;
END LOOP;
utl_file.fclose( v_line );
dbms_output.put_line( 'File Closed' );
end if;
END;


File Opened
File is open
ORA-29284: file read error
lineType : AD
No Data Found
File Closed
Chris Saxon
March 10, 2025 - 3:51 pm UTC

How big are the lines in the file?

Imran Khan, March 11, 2025 - 4:55 am UTC

The file contains 1,524 lines, including blanks, actually holds merchant data to upload to database . When the file contains data for four merchants (approximately 1,200 records, with 300 per merchant), it is successfully processed and uploaded. However, adding data for a fifth merchant and attempting to upload the full file results in an error.

When we split the file into two: one containing the four merchants' data (1,200 records) and another with the fifth merchant's data (324 records). Both files upload successfully when processed separately.
Chris Saxon
March 12, 2025 - 8:17 am UTC

I'm not sure what's going on here, glad you've found a workaround.

Imran Khan, March 11, 2025 - 5:17 am UTC

biggest lines are 5, having 108 byte length, rest having below 80 bytes.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library