Reading file in pl/sql 1

[llmclaughlin]

I've tried to use the external table, it works, but not for what I am doing. The trouble with the below code is if there is only one record in the file it will exit on the last string in the file, meaning it will not get the kit type.

Any help.

text file

T61235,65499157588932185233756,SINGLE KIT ' will get all this line
T61239,6549915758893218523311,SINGLE KIT ' will get barcode but not the kit and exit loop.

l_bfile := BFILENAME ('RECEIVED_FILES', inFILENAME);
DBMS_LOB.fileopen (l_bfile);
l_size := DBMS_LOB.getlength (l_bfile);

LOOP
DBMS_APPLICATION_INFO.set_client_info (l_last || ' of ' || l_size);

l_current :=
DBMS_LOB.INSTR (l_bfile,
l_comma,
l_last,
1);
EXIT WHEN (NVL (l_current, 0) = 0);
l_rec.PLATE_NAME :=
UTL_RAW.cast_to_varchar2 (
DBMS_LOB.SUBSTR (l_bfile, l_current - l_last, l_last));
l_last := l_current + 1;

l_current :=
DBMS_LOB.INSTR (l_bfile,
l_comma,
l_last,
1);
EXIT WHEN (NVL (l_current, 0) = 0);
l_rec.FEDEX_BARCODE :=
UTL_RAW.cast_to_varchar2 (
DBMS_LOB.SUBSTR (l_bfile, l_current - l_last, l_last));
l_last := l_current + 1;

l_current :=
DBMS_LOB.INSTR (l_bfile,
l_newline,
l_last,
1);
EXIT WHEN (NVL (l_current, 0) = 0);
l_rec.KIT_TYPE :=
UTL_RAW.cast_to_varchar2 (
DBMS_LOB.SUBSTR (l_bfile, l_current - l_last, l_last));
l_last := l_current + 1;

INSERT INTO TEMP_PARSE_RECEIVING VALUES(l_rec.PLATE_NAME, l_rec.FEDEX_BARCODE, l_rec.KIT_TYPE);
END LOOP;

Louie

 

[SantaMufasa]

Louie,

My very first impression of the possible cause is that your external data file needs to end with a carriage-return character for your operating system (i.e., either a [CR] or a [CR/LF]). Can you please confirm that your file ends with a proper end-of-line character, then re-run your code, then advise us of your findings?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[llmclaughlin]

The last record in the file does not have a CR. If I go in the file and press enter on the last record, then yes, it does read all. But the file is written out by a third party program, which I have no control over in having them put in a CR after the last record.


Thanks

Louie

 

[SantaMufasa]

I have no control over in having them put in a CR after the last record.

Technically, then, the third party have a bug in their program...By definition, a record/line does not end until there is an "End-of-Record"/"End-of-Line" character.

As a work around, when you receive the file from the third party, you can either manually add in an EoL character, or, as part of your file-intake process, you concatenate their (erroneous) file with a file that contains a single EoL character.

Regardless, PL/SQL is not going to process row in an external file if the row does not terminate with an End-of-Line character.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[llmclaughlin]

Ok, thanks, I'll add it myself.



Thanks again

Louie

 

[dkyrtata]

Long ago, I had a similar problem which I handled at the Unix level. I would create a copy of the file and add a linefeed to it. Then I would read back the last line with a "tail -1" command. If it was read back as a null line I would know the original file was properly sent to me. If the last line was not null, I would know that the original file came across with a missing linefeed, in which case I would use the copy as the file to process (i.e I would overwrite the original file with the copy)

In a similar scenario, I received a data file that would sometimes arrive double spaced (i.e an extra linefeed between each record). I never identified, the cause of this problem, but I simply enhanced my Unix script to create another data file without any blank lines.