Oracle Database Discussions

Dear buddies,

I am using Oracle 10g R2.

Need to load some records from one text file to Oracle.

This is my control file:

LOAD DATA 
INFILE 'D:\load\dat\FinNote.dat' 
BADFILE 'D:\load\bad\FinNote.bad' 
DISCARDFILE 'D:\load\dat\discard\FinNote.dsc' 
INSERT INTO TABLE FinNote FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING 
NULLCOLS
( IDNo, RegDate date 'YYYY-MM-DD HH24:MI:SS', Col1, Remark, UserID, Status)
{code}

My text file has semicolon as a row delimiter and | as column delimiter. Now, how can I add the semicolon as row delimiter. Please guide me.

I followed the method given in this site: 
http://www.exforsys.com/tutorials/oracle-10g/oracle-10g-sql-loader-input-data-and-datafiles.html


*INFILE datafile_name ["str terminator_string"]*

{code}
LOAD DATA 
INFILE 'D:\load\dat\CRS_FinNote.dat' ";"
BADFILE 'D:\load\bad\CRS_FinNote.bad' 
DISCARDFILE 'D:\load\dat\discard\CRS_FinNote.dsc' 
INSERT INTO TABLE CRS_FinNote FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING 

NULLCOLS
( StudentNo, IDate date 'YYYY-MM-DD HH24:MI:SS', Topic, Remark, UserID, Status)

and received error:

SQL*Loader-500: Unable to open file (D:\load\dat\CRS_FinNote.dat)
SQL*Loader-555: unrecognized processing option
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-513: Unable to close file (D:\load\dat\CRS_FinNote.dat)
SQL*Loader-559: error closing file
SQL*Loader-509: System error: The storage control block address is invalid.

Thanks in advance.
Nith

Edited by: user645399 on Jun 16, 2010 10:35 AM