我尝试使用sql加载器将数据从数据文件加载到数据库表load_sql。我在数据文件中有如下数据。
empid,ename
1,Raja,**Kanchi
2,Poo,**Kanchi
3,Bhasker,**Kanchi
4,Siva,**Kanchi
我必须加载才能将其加载到load_sql表中,如下所示:
1,Raja,Kanchi
2,Poo,Kanchi
3,Bhasker,Kanchi
4,Siva,Kanchi
我已经在字符操作功能的帮助下编写了一个控制文件,用于在第三列中插入记录,但出现错误:
options(skip = 1,Errors = 100, direct = True)
load data
infile 'D:SQLLDRcontrol.ctl'
truncate into table load_sql
when city = 'Kanchi'
fields terminated by ','
optionally enclosed by '"'
(
empid,
ename,
X filler,
city "ltrim(:city,*)"
)
我收到这样的错误
'SQL*Loader-951: Error calling once/load initialization
ORA-02373: Error parsing insert statement for table ROOT.LOAD_SQL.
ORA-00936: missing expression'
您的控制文件中有一些语法错误。 试试这个:
options(skip = 1,Errors = 100, direct = True)
load data
infile 'D:SQLLDRcontrol.ctl' <-- This doesn't look like a data file name?
truncate into table load_sql
when (city = '**Kanchi')
fields terminated by ','
optionally enclosed by '"'
(
empid,
ename,
city "ltrim(:city, '*')"
)
ltrim(:city,*)
^
|
this is invalid
应该是
ltrim(:city, '*')
或者,可能,
replace(:city, '*', '')