sqlldr在插入long字段时,一般需要注意些什么? 我这里出了不少错。还有一个varchar2型的字段--owner,同样也是很多错。其它的字段到都没错。
我就是导入了一个all_tab_columns表结构的表。
其中字段“data_default”是long型字段。
下面截取了一些文件内容:
sqlldr的LOG文件:
Record 14820: Rejected - Error on table KZHOU.ALL_TAB_CONS_SOURCE, column DATA_DEFAULT.
second enclosure string not present
Record 14821: Rejected - Error on table KZHOU.ALL_TAB_CONS_SOURCE, column OWNER.
no terminator found after TERMINATED and ENCLOSED field
Table KZHOU.ALL_TAB_CONS_SOURCE:
33751 Rows successfully loaded.
50 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 255420 bytes(33 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 33801
Total logical records rejected: 50
Total logical records discarded: 0
导入用的数据文件:
"FI_DATA","MTGINDIC","NET_COUPON","NUMBER","","","22","","","Y","7","","","","","","","","","","","","","NO","NO","","0","","NO","YES"
"FI_DATA","MTGINDIC","TERM_ORIGINAL","NUMBER","","","22","","","Y","8","","","","","","","","","","","","","NO","NO","","0","","NO","YES"
导入用的控制文件:
LOAD DATA
INFILE 'ALL_TAB_CONS_SOURCE.CSV'
APPEND
INTO TABLE KZHOU.ALL_TAB_CONS_SOURCE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID,DEFAULT_LENGTH,DATA_DEFAULT,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH,GLOBAL_STATS,USER_STATS,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED,V80_FMT_IMAGE,DATA_UPGRADED)
.bad文件:
"SYS","JIJOIN$","TAB2INST#","NUMBER","","","22","","","Y","9","53","0 /* instance of table 2 (for multiple refs) */
","","","","","","","","","","","NO","NO","","0","","NO","YES"
"SYS","RGCHILD$","INSTSITE","NUMBER","","","22","","0","Y","6","46","0 /* snapshot site id */
","","","","","","","","","","","NO","NO","","0","","NO","YES"
"SYS","SNAP_COLMAP$","SNAPOS","NUMBER","","","22","","0","Y","9","46","0 /* position of col in snapshot table */
","","","","","","","","","","","NO","NO","","0","","NO","YES"
"SYSTEM","REPCAT$_DDL","DDL_NUM","NUMBER","","","22","","0","Y","7","36","1 -- order of ddls to execute
","","","","","","","","","","","NO","NO","","0","","NO","YES"
"EQ_DATA","EQPNL","MATURITYORDER","NUMBER","","","22","8","0","Y","23","37","0 -- display order based on maturity
","","","","","","","","","","","NO","NO","","0","","NO","YES"
"MP_DATA","PRICE_LOAD_XREF","STATUS","CHAR","","","1","","","Y","5","4","'Y'","","","","","","","","","CHAR_CS","1","NO","NO","","1","B","NO","YES"
"COM_DATA","WATCHER_LOG","TIME","DATE","","","7","","","Y","2","8","sysdate","","","","","","","","","","","NO","NO","","0","","NO","YES"
这些注释是sqlldr自己加上的。
表结构:all_tab_cons_source < = = > all_tab_columns
SQL> desc all_tab_cons_source;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
DATA_DEFAULT LONG
请问sqlldr这是什么意思啊?
为什么owner和data_default老出错呀?但绝大部分还是对了。