A bug of "sql*loader"?

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?