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 DATACHARACTERSET UTF8LENGTH SEMANTICS CHARBYTEORDERMARK CHECKINFILE 'DB_FILES_FERMAT.DAT' "STR '#$EOR$#\r\n'"APPENDINTO TABLE DB_FILES_FERMATFIELDS 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_FERMATSQL*Loader-553: file not foundSQL*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_FERMATSQL*Loader-553: file not foundSQL*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_FERMATSQL*Loader-553: file not foundSQL*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_FERMATSQL*Loader-553: file not foundSQL*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_FERMATSQL*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?