A very interesting problem popped up when tried to load LOB data into table using "sql*loader". Here is the story....
Table:
DB_FILES_FERMAT
SQL> desc db_files_fermat
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
FILENAME VARCHAR2(512 CHAR)
DESCRIPTION VARCHAR2(4000 CHAR)
DATA BLOB
SYNCHRONIZE_FILE CHAR(1 CHAR)
FILE_TIMESTAMP DATE
DB_TIMESTAMP DATE
FILE_TYPE VARCHAR2(10 CHAR)
SQL>
CTL File:
DB_FILES_FERMAT.CTL
OPTIONS (SILENT=(HEADER, FEEDBACK), DIRECT=FALSE)
LOAD DATA
CHARACTERSET UTF8
LENGTH SEMANTICS CHAR
BYTEORDERMARK CHECK
INFILE 'DB_FILES_FERMAT.DAT' "STR '#$EOR$#\r\n'"
APPEND
INTO TABLE DB_FILES_FERMAT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
(
ID "SEQ_DB_FILES.NEXTVAL"
,FILENAME CHAR(512) "TO_CHAR(:FILENAME)"
,DESCRIPTION CHAR(4000) "TO_CHAR(SUBSTR(:DESCRIPTION,1,2000))||TO_CHAR(SUBSTR(:DESCRIPTION,2001))"
,DATA LOBFILE(FILENAME) TERMINATED BY EOF
,SYNCHRONIZE_FILE CHAR(1) "TO_CHAR(:SYNCHRONIZE_FILE)"
,FILE_TIMESTAMP DATE "YYYY-MM-DD HH24:MI:SS"
,DB_TIMESTAMP DATE "YYYY-MM-DD HH24:MI:SS"
,FILE_TYPE CHAR(10) "TO_CHAR(:FILE_TYPE)"
)
Please note that the column "DATA" will be loaded from the out-of-the-line lob file -- "LOBFILE(FILENAME)"
Data File:
DB_FILES_FERMAT.DAT (partial content)
,"EXPFBI_CATALOG@RFO_CUBES.MIG","RFO_CUBES","",,,"MIG"#$EOR$#
,"EXPFBI_CATALOG_REFERENCE@RFO_CUBES.MIG","RFO_CUBES","",,,"MIG"#$EOR$#
,"EXPMIGRATION_DEF@RFO_CUBES.MIG","RFO_CUBES","",,,"MIG"#$EOR$#
,"EXPMIGRATION_EXPORTED_TABLES_TMP@RFO_CUBES.MIG","RFO_CUBES","",,,"MIG"#$EOR$#
Please note that the file names -- "EXPFBI_CATALOG@RFO_CUBES.MIG" and the like, those binary files will be read and loaded into the column "DATA" in the table DB_FILES_FERMAT. Those MIG files are put under the same folder as DB_FILES_FERMAT.DAT...
Weird Problems Occur
When tried to run "sqlldr" to load the data, the following error messages pop up...
SQL*Loader-502: unable to open data file 'EXPFBI_CATALOGdbciRFO_CUBES.MIG' for field DATA table DB_FILES_FERMAT
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-502: unable to open data file 'EXPFBI_CATALOG_REFERENCEdbciRFO_CUBES.MIG' for field DATA table DB_FILES_FERMAT
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-502: unable to open data file 'EXPMIGRATION_DEFdbciRFO_CUBES.MIG' for field DATA table DB_FILES_FERMAT
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-502: unable to open data file 'EXPMIGRATION_EXPORTED_TABLES_TMPdbciRFO_CUBES.MIG' for field DATA table DB_FILES_FERMAT
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-502: unable to open data file 'EXPMIGRATION_SETdbciRFO_CUBES.MIG' for field DATA table DB_FILES_FERMAT
SQL*Loader-553: file not found
…
…
It’s quite weird that “sql*loader” replace the character “@” with “dbci” when processing those MIG files! “dbci” is actually one “active” oracle instance in the machine I ran the sqlldr. Then I tried to run the sql*loader in another machine, this time the error messages changed to like “sql*loader is unable to open ‘'EXPMIGRATION_EXPORTED_TABLES_TMPprimaryRFO_CUBES.MIG”. Note the primary is the “active” oracle instance in that machine. However, after shutting down the oracle instances on that machine, it ran successfully!
After such experiments, I suspected it to be one “sql*loader” bug. It might interpret “@” specially. So I tried to change the file name to replace “@” with “#”, this time it worked as well!
Quite a stange problem. A sqlldr bug?