Home » RDBMS Server » Server Utilities » how to use sqlldr direct path to load chr(0)
how to use sqlldr direct path to load chr(0) [message #218735] Fri, 09 February 2007 12:36 Go to next message
Averell
Messages: 17
Registered: November 2006
Junior Member
Hi all,

In db A, I have a table T1 created as follow.
create table T1(f1 number, f2 char(1),...);

In db B, I have:
create table T2(f1 number, f2 char(1));

I want to fill T2 with data from T1. The field f2 is a flag, which can be in one of two values:
SQL> select distinct ascii(F2) from T1;

ASCII(F2)
---------
        1
        0

My un-wanted result is as follow:
SQL> select distinct ascii(F2) from T2;

ASCII(F2)
---------
        1
       48


To unload data, I use: select f1 || ',' || f2 from T1;. My spooled file is somehow as follow:
1234,<a byte = 32>
5678,<a byte = 1>
.

Then I tried to load data into T2 using sqlldr, direct path, with control file is as follow
LOAD DATA
PRESERVE BLANKS
INTO TABLE T2
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(	F1,
	F2	INTEGER EXTERNAL DEFAULTIF F2=BLANKS,
)


Could you please help me solve this problem? (I have millions of records to move, so, I need direct path mode).

Thank you very much!

[Updated on: Sat, 10 February 2007 05:23]

Report message to a moderator

Re: how to use sqlldr direct path to load chr(0) [message #218802 is a reply to message #218735] Sat, 10 February 2007 08:29 Go to previous message
Averell
Messages: 17
Registered: November 2006
Junior Member
Hi all,

I've tried to modify the data file, changed <character = 32> with <char = 0>, then I got the expected result. So, the problem now changes to: how to spool <char = 0> to file.

Thanks for your attention!
Previous Topic: two questions answer please
Next Topic: SQL loader to import
Goto Forum:
  


Current Time: Wed Dec 07 06:43:46 CST 2016

Total time taken to generate the page: 0.07120 seconds