Home » RDBMS Server » Server Utilities » SQL Loader and Replace Function dont work. (ORacle 11g R2)
SQL Loader and Replace Function dont work. [message #503329] Thu, 14 April 2011 09:49 Go to next message
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 #503334 is a reply to message #503329] Thu, 14 April 2011 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you could post a data file we can test and see what happens...
And show us what you get with this data file.

Regards
Michel
Re: SQL Loader and Replace Function dont work. [message #503337 is a reply to message #503334] Thu, 14 April 2011 13:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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>





Re: SQL Loader and Replace Function dont work. [message #503344 is a reply to message #503337] Thu, 14 April 2011 17:32 Go to previous message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Barbara Boehmer. You are absolutely right.

Thanks for the explanation ... and thanks for the help.
Previous Topic: Export sequence using expdp
Next Topic: Data Pump
Goto Forum:
  


Current Time: Thu Dec 03 23:04:31 CST 2020