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