Skip to Main Content
  • Questions
  • ORA-29284: file read error for a few lines

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rachit.

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

Last updated: October 06, 2022 - 4:36 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

  (3 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

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