Reading file in pl/sql 1

[llmclaughlin]

The file I'm reading has 3 columns comma seperated, the below code never gets the last record 3rd column always exit. The one that has KIT_TYPE. If my file has 25 rows it gets all information for 24 rows fine and get the plate_name and fedex_barcode for the 25 record.

Any help.

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,
'0A',
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;

Thanks


Louie

 

[Thargy]

Louie,

are you attempting to read a text file into oracle?
If you are, then an external table would be much easier.

Can you post a sample of the file in question? Just a few lines if it's huge, if it's just 25 as indicated by your question, then please post all of it.

Regards

T

 

[llmclaughlin]

The file can be just 1 row to whatever, hardly ever over 100.
Yes, I am reading one row at a time and inserting into an oracle table, the file looks like this.

T60031,865700232856,SINGLE KIT
T52853,989996267510,SINGLE KIT
T49088,797425592891,SINGLE KIT
T59107,408002946251,SINGLE KIT
T57450,865700232926,SINGLE KIT
T37433,968217344661,SINGLE KIT
E2398,408002952901,SINGLE KIT
T56220,796404434906,SINGLE KIT
T59434,796441264091,SINGLE KIT
T59435,796441264091,SINGLE KIT
T59408,797429450659,SINGLE KIT
T59438,796441264091,SINGLE KIT
T59439,796441264091,SINGLE KIT
T56365,408002952923,SINGLE KIT
T59409,797429450659,SINGLE KIT
T59437,797429450659,SINGLE KIT
T60806,865700230842,SINGLE KIT
T53757,408002946056,SINGLE KIT
T47185,862411307950,SINGLE KIT
T50197,862411307950,SINGLE KIT
T47794,797425592891,SINGLE KIT
T60780,408002952555,SINGLE KIT
TX8114,968217325850,SINGLE KIT
T55952,968217303694,SINGLE KIT
T61222,862411391870,SINGLE KIT

Thanks for your help

Louie

 

[Thargy]

In that case, I think that an external table would be much easier. You have to put the file in a folder accessible to the database, create an oracle directory and then you can load it.

So here goes (I'm using windows here, if you're on linux or solaris or whatever, the folder name will be different because it's operating system specific.

If you don't have sufficient privileges to do so, you must get a DBA to create the directory, and grant read and write on it to your account, otherwise this will fail due to insufficient privileges.

I have copied your posted data and put it into a file C:\TEMP\LOUIE_TEST.TXT.

CREATE OR REPLACE DIRECTORY L_TEST AS 'C:\TEMP';

CREATE TABLE EXT_LOUIE
(
PLATE_NAME  VARCHAR2(6),
FEDEX_BARCODE VARCHAR2(20),
KIT_TYPE  VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
TYPE              ORACLE_LOADER
DEFAULT DIRECTORY L_TEST
  ACCESS PARAMETERS (
    RECORDS DELIMITED  BY NEWLINE
    FIELDS  TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('LOUIE_TEST.TXT')
)
REJECT LIMIT UNLIMITED;

SELECT * FROM EXT_LOUIE;

the select statement produced the expected results.

Note that in this way you don't have to load the data into oracle, you can just treat the text file as if it was an oracle table. If you want it permanently in the database, then you'll have to copy from the external table into an ordinary internal one.

The illustrious Laurent Schneider as an excellent example at

http://www.adp-gmbh.ch/ora/misc/ext_table.html

Note that I have used oracle 10, but external tables are available in 9.

Regards

T

 

[llmclaughlin]

Thats easy enough, thanks.
I need to run it from within stored procedure, I tried

CREATE OR REPLACE PROCEDURE TESTME(inFILENAME VARCHAR2) IS

BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE ext_louie('||
' PLATE_NAME VARCHAR(20),'||
' FEDEX_BARCODE VARCHAR2(20),'||
' KIT_TYPE VARCHAR2(20))' ||
' ORGANIZATION EXTERNAL ('||
'TYPE oracle_loader'||
'DEFAULT DIRECTORY RECEIVED_FILES' ||
'ACCESS PARAMETERS (' ||
'RECORDS DELIMITED BY NEWLINE' ||
'FIELDS TERMINATED BY '',''' ||
'MISSING FIELD VALUES ARE NULL' ||
' ) '||
'LOCATION' || '(' ||inFILENAME||')'||
')'||
'REJECT LIMIT UNLIMITED';
END TESTME;

It compiles and runs but no data.

Louie

 

[Thargy]

To get the data, you have to select from the table, just like an ordinary one.

Regards

T

 

[llmclaughlin]

Yeah, the procedure wasn't creating the table is what I was talking about. It does now.


Thanks again.

Louie