sqlldr问题:
现在数据库中有一个字段是clob类型的,但数据不是很大,我想对对其进行换行处理,如:
原数据为:1,aaa,ldskfjdsfiefndsa###fdsgdfgfdjgdf;gre###ivmsldf;f'lkjs(这项对应数据库中的clob类型字段)
想将其中的###转为换行符后入库,我用了replace(:col,'###',chr(10))来转换,但我试了,这样做如果对应的字段是varchar型的还好,如果字段对应数据库是clob型的就不能用了,好像说是clob类型入库时不能用replace进行处理,那我应该怎么做?
高手指教呀
现在数据库中有一个字段是clob类型的,但数据不是很大,我想对对其进行换行处理,如:
原数据为:1,aaa,ldskfjdsfiefndsa###fdsgdfgfdjgdf;gre###ivmsldf;f'lkjs(这项对应数据库中的clob类型字段)
想将其中的###转为换行符后入库,我用了replace(:col,'###',chr(10))来转换,但我试了,这样做如果对应的字段是varchar型的还好,如果字段对应数据库是clob型的就不能用了,好像说是clob类型入库时不能用replace进行处理,那我应该怎么做?
高手指教呀
Table created
SQL> insert into test values('1,aaa,ldskfjdsfiefndsa###fdsgdfgfdjgdf;gre###ivmsldf;f''lkjs');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
--------------------------------------------------------------------------------
1,aaa,ldskfjdsfiefndsa###fdsgdfgfdjgdf;gre###ivmsldf;f'lkjs
SQL> update test set a = replace(a,'###',chr(10));
1 row updated
SQL> commit;
Commit complete
SQL> select * from test;
A
--------------------------------------------------------------------------------
1,aaa,ldskfjdsfiefndsa
fdsgdfgfdjgdf;gre
ivmsldf;f'lkjs
SQL>
Table created
SQL> insert into test values('1,aaa,ldskfjdsfiefndsa###fdsgdfgfdjgdf;gre###ivmsldf;f''lkjs');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
--------------------------------------------------------------------------------
1,aaa,ldskfjdsfiefndsa###fdsgdfgfdjgdf;gre###ivmsldf;f'lkjs
SQL> update test set a = replace(a,'###',chr(10));
1 row updated
SQL> commit;
Commit complete
SQL> select * from test;
A
--------------------------------------------------------------------------------
1,aaa,ldskfjdsfiefndsa
fdsgdfgfdjgdf;gre
ivmsldf;f'lkjs
SQL>
col1 int ,
col2 varchar2(20),
col3 clob,
dmp文件数据(a.dmp):
1,aaa,sldfkjsdkljfdglergj###sdlkfg;dfgldf;###kdsfireorgjfgfj
2,bbb,sdlfjsdfjioregjt###fgmfddslf;sad;glfdg###woeirjsdf;dsg
对应的控制文件:
LOAD DATA
INFILE 'a.dmp' BADFILE 'protein.bad'
APPEND
INTO TABLE TB_PROTEIN
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
col1 TERMINATED BY "\t",
col2 TERMINATED BY "\t",
col3 TERMINATED BY "\t" "replace(:col3,'###',chr(10))"
)运行之后会说:"不允许 SQL 字符串作为 col3 字段说明的一部分"
col2 TERMINATED BY "\t""replace(:col2,'###',chr(10))" ,
可以将col2中的"###"转为换行符