| SQL Loader and Replace Function dont work. [message #503329] |
Thu, 14 April 2011 09:49  |
 |
aucrun
Messages: 114
Registered: February 2011
|
Senior Member |
|
|
Hi,
I have the next SQL Loader file, and it is giving me some headches because the Replace instruction is not working. It does not replace the '\ ' by NULL.
Is there anything I can do? What is wrong with it?
load data
CHARACTERSET UTF8
infile TABLE1.unl
BADFILE 'TABLE1.bad'
DISCARDFILE 'TABLE1.dsc'
insert into table GSCIS.table1
fields terminated by "|"
TRAILING NULLCOLS(
codl1 CHAR "REPLACE(:codl1, '\ ', NULL)"
,col2 CHAR "REPLACE(:col2, '\ ', NULL)"
,col3 INTEGER EXTERNAL
,col3 INTEGER EXTERNAL "DECODE(:col3, NULL, 0, :col3)"
)
Thanks!
[Updated on: Thu, 14 April 2011 10:30] Report message to a moderator |
|
|
|
|
|
| Re: SQL Loader and Replace Function dont work. [message #503337 is a reply to message #503334] |
Thu, 14 April 2011 13:00  |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The \ is the escape character. It tells SQL*Loader to treat the character after it as a text character, rather than applying any special meaning that the character may otherwise have. So, when you use '\ ' you are just telling it to replace a space with null. If you want to replace a \ followed by a space with null, then you need to escape the escape character like '\\ '. If you just want to replace the \ then use '\\'. Also, you have col3 in your control file twice. You can only list it once and do not need to list it twice. Please see the simplified demonstration below. I replaced \ followed by a space in cold1 and just the \ in col2.
-- test.ctl:
load data
CHARACTERSET UTF8
infile *
insert into table table1
fields terminated by "|"
TRAILING NULLCOLS(
codl1 CHAR "REPLACE(:codl1, '\\ ', NULL)"
,col2 CHAR "REPLACE(:col2, '\\', NULL)"
,col3 INTEGER EXTERNAL "DECODE(:col3, NULL, 0, :col3)"
)
begindata:
test1\ test1|test2\ test2 |2|
test3\ test3|test4\ test4||
test5\ test5|test5|
SCOTT@orcl_11gR2> create table table1
2 (codl1 varchar2 (12),
3 col2 varchar2 (12),
4 col3 number)
5 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from table1
2 /
CODL1 COL2 COL3
------------ ------------ ----------
test1test1 test2 test2 2
test3test3 test4 test4 0
test5test5 test5 0
3 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
