Home » RDBMS Server » Server Utilities » sql loader issue (Oracle 10.2.0.4, Windows 2003)
sql loader issue [message #497874] |
Mon, 07 March 2011 12:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
newsurfgal
Messages: 12 Registered: August 2009
|
Junior Member |
|
|
Hi all,
I'm importing data from SQL Server to Oracle. I used BCP to export the data from SQL Server. Below is the 1st record of table trlc from the csv file.
trlc.CSV
11032|100|Wman| | |2008-02-08| |
Using SQL Loader to import into Oracle:
TRLC table in Oracle database:
est_no varchar2(10) default ' '
right_no number(4) default 0
maj_auth varchar2(15) default ' '
weight varchar2(10) default ' '
idm_ht varchar2(8) default ' '
c_date date
P_tkt varchar2(5) default ' '
sqlldr user/pwd@db02 control=trlc.ctl log=trlc.log
trlc.ctL:
load data
infile 'trlc.csv'
replace
into table trlc
fields TERMINATED BY '|'
TRAILING NULLCOLS
(est_no,right_no,maj_auth,weight,idm_ht,c_date,P_tkt)
The rows get inserted successfully. But the result sets are different, for example:
When I do a select in SQL Server,'select len(weight) from trlc;' , I get the length as 0. But when I do a select in oracle database, I get the length as 1. Also, the result set varies for the query below:
select * from trlc where weight=' ';
(SQL Server returns 1 row but Oracle returns no rows)
Do I need to mention any conversion code for the weight field to accept ' ' value? Any suggestions please? Thanks a lot.
[EDITED by LF: disabled smilies in this message]
[Updated on: Mon, 07 March 2011 15:18] by Moderator Report message to a moderator
|
|
|
|
Re: sql loader issue [message #497877 is a reply to message #497874] |
Mon, 07 March 2011 12:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
newsurfgal
Messages: 12 Registered: August 2009
|
Junior Member |
|
|
Sorry.. Here it is:
Hi all,
I'm importing data from SQL Server to Oracle. I used BCP to export the data from SQL Server. Below is the 1st record of table trlc from the csv file.
trlc.CSV
11032|100|Wman| | |2008-02-08| |
Using SQL Loader to import into Oracle:
TRLC table in Oracle database:
est_no varchar2(10) default ' '
right_no number(4) default 0
maj_auth varchar2(15) default ' '
weight varchar2(10) default ' '
idm_ht varchar2( default ' '
c_date date
P_tkt varchar2(5) default ' '
sqlldr user/pwd@db02 control=trlc.ctl log=trlc.log
trlc.ctL:
load data
infile 'trlc.csv'
replace
into table trlc
fields TERMINATED BY '|'
TRAILING NULLCOLS
(est_no,right_no,maj_auth,weight,idm_ht,c_date,P_tkt)
The rows get inserted successfully. But the result sets are different, for example:
When I do a select in SQL Server,'select len(weight) from trlc;' , I get the length as 0. But when I do a select in oracle database, I get the length as 1. Also, the result set varies for the query below:
select * from trlc where weight=' ';
(SQL Server returns 1 row but Oracle returns no rows)
Do I need to mention any conversion code for the weight field to accept ' ' value? Any suggestions please? Thanks a lot.
[Updated on: Mon, 07 March 2011 13:06] Report message to a moderator
|
|
|
Re: sql loader issue [message #497881 is a reply to message #497877] |
Mon, 07 March 2011 12:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
All I can tell you is that you are asking for trouble (and it looks like you got it) by setting default values of columns to spaces or try to load spaces into columns.
If a column is supposed to be void of a value then it should be NULL.
|
|
|
Re: sql loader issue [message #497885 is a reply to message #497881] |
Mon, 07 March 2011 13:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
newsurfgal
Messages: 12 Registered: August 2009
|
Junior Member |
|
|
I understand, but believe it or not- that is our application requirement to have ' ' as default for these fields..
After importing the table, when I run select length(weight) from trlc or select datalength(weight) from trlc - queries return 1.
So does it mean there is indeed a blank space ' ' for the weight field? But when I do select count(*) from trlc where weight= ' ' - query returns 0.
Any help please?
Thanks
|
|
|
|
Re: sql loader issue [message #497888 is a reply to message #497885] |
Mon, 07 March 2011 13:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
That's why I mentioned trouble.
You may have a control character, your character set setting may affect your query.
You'll have to select dump(weight) from trlc to be able to debug better.
No surprise that something coming from SQL Server requires a space as value in fields. It makes you wonder if technology people or clueless managers were behind this application.
I genuinely feel sorry for you to code around someone's short-sightedness.
|
|
|
|
Re: sql loader issue [message #497915 is a reply to message #497896] |
Mon, 07 March 2011 15:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Well, 32 would be the decimal representation of a space. 0 is the representation of a NULL, but you would not get a Typ=1 or Len for a NULL.
Incidentally, if I use a default of for a varchar2 column and insert a NULL or '', it inserts a NULL.
SQL> create table yy (a1 number,a2 varchar2(10) default ' ');
Table created.
SQL> insert into yy values (1,null);
1 row created.
SQL> insert into yy values (2,'');
1 row created.
SQL> insert into yy values (3,' ');
1 row created.
SQL> select a1,dump(a2) from yy;
A1 DUMP(A2)
---------- ---------------------------------------
1 NULL
2 NULL
3 Typ=1 Len=1: 32
SQL> select a1,length(a2) from yy;
A1 LENGTH(A2)
---------- ----------
1
2
3 1
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Your table description is "mocked up" and not a real one so who knows how you really created it.
[Updated on: Mon, 07 March 2011 15:19] Report message to a moderator
|
|
|
Re: sql loader issue [message #498081 is a reply to message #497874] |
Tue, 08 March 2011 10:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
newsurfgal
Messages: 12 Registered: August 2009
|
Junior Member |
|
|
Thanks. This is the table creation:
create table trlc (
est_no varchar2(10) default ' ',
right_no number(4) default 0,
maj_auth varchar2(15) default ' ',
weight varchar2(10) default ' ',
idm_ht varchar2(20) default ' ',
c_date date,
P_tkt varchar2(5) default ' '
);
trlc.txt from BCP OUT (SQL SERVER)
11032|100|Wman| | |2008-02-08| |
11791|320|Wman|CELLS | |2008-02-08| |
I tried changing a few things for the weight in the ctl file:
trlc.ctL:
load data
infile 'trlc.txt'
replace
into table trlc
fields TERMINATED BY '|'
TRAILING NULLCOLS
(est_no,right_no,maj_auth,
weight CHAR "DECODE(:weight,'',' ',:weight)",
idm_ht,
c_date,P_tkt)
sqlldr user/pwd@db02 control=trlc.ctl log=trlc.log
After the Import when I do select dump(weight) from trlc - I get Typ=1 Len=1: 0 for the 1st row & Typ=1 Len=5: 67,101,108,108,115 for the 2nd row from the data file..
But the dump should be Typ=1 Len=1: 32 for the 1st row for ' ' value.
I also tried with weight CHAR "DECODE(:weight,' ',' ',:weight)" this doesn't work either. There is space for trlc.weight field in the data file, but not sure what the
sql loader is interpreting as...
Please give me your thoughts..Thanks a lot
|
|
|
Re: sql loader issue [message #498083 is a reply to message #498081] |
Tue, 08 March 2011 11:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well either:
1) The space in the text file isn't really a space, but is actually some unprintable character. A hex editor would show that up.
2) Or oracle is getting confused somehow - probably character set issues.
Try creating your own dat file in notepad or vi. Just a single line of data with a space for the weight. Load it and see what you get.
If it works then it's option 1 and there is a problem with the sql server extract.
|
|
|
Re: sql loader issue [message #498087 is a reply to message #498083] |
Tue, 08 March 2011 11:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
There are a couple of different issues here. One is that SQL Server recognizes chr(0) as a null value, but Oracle does not. The other is that null is a value. Defaults are only used when no value is supplied. So, if you insert null, it does not use the default space, which is chr(32). If you want a space instead of an Oracle null or a SQL Server chr(0), then you can use a before insert row trigger to do this. This will work whether you are inserting using inserts statements or inserting via SQL*Loader. Please see the demonstration below in which I have first demonstrated what is currently happening, showing how Oracle interprets SQL Server chr(0) and an inserted null value and no value inserted, then demonstrated what happens if you use a trigger.
-- what is happening now:
SCOTT@orcl_11gR2> create table test_tab
2 (id number,
3 weight varchar2 (10) default ' ',
4 comments varchar2 (39))
5 /
Table created.
SCOTT@orcl_11gR2> insert into test_tab values (1, chr(0),
2 'value like your data, default not used')
3 /
1 row created.
SCOTT@orcl_11gR2> insert into test_tab values (2, null,
2 'null value inserted, default not used')
3 /
1 row created.
SCOTT@orcl_11gR2> insert into test_tab (id, comments) values (3,
2 'no value inserted, default will be used')
3 /
1 row created.
SCOTT@orcl_11gR2> column dump format a15
SCOTT@orcl_11gR2> select id,
2 weight,
3 dump (weight) dump,
4 comments
5 from test_tab
6 order by id
7 /
ID WEIGHT DUMP COMMENTS
---------- ---------- --------------- ---------------------------------------
1 Typ=1 Len=1: 0 value like your data, default not used
2 NULL null value inserted, default not used
3 Typ=1 Len=1: 32 no value inserted, default will be used
3 rows selected.
SCOTT@orcl_11gR2> select * from test_tab where weight = ' '
2 /
ID WEIGHT COMMENTS
---------- ---------- ---------------------------------------
3 no value inserted, default will be used
1 row selected.
SCOTT@orcl_11gR2>
-- how to get what you want with a trigger:
SCOTT@orcl_11gR2> create table test_tab
2 (id number,
3 weight varchar2 (8) default ' ',
4 comments varchar2 (39))
5 /
Table created.
SCOTT@orcl_11gR2> create or replace trigger test_tab_bir
2 before insert on test_tab
3 for each row
4 begin
5 if :new.weight is null or :new.weight = chr(0)
6 then :new.weight := chr(32);
7 end if;
8 end test_tab_bir;
9 /
Trigger created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> insert into test_tab values (1, chr(0),
2 'value like your data, default not used')
3 /
1 row created.
SCOTT@orcl_11gR2> insert into test_tab values (2, null,
2 'null value inserted, default not used')
3 /
1 row created.
SCOTT@orcl_11gR2> insert into test_tab (id, comments) values (3,
2 'no value inserted, default will be used')
3 /
1 row created.
SCOTT@orcl_11gR2> column dump format a15
SCOTT@orcl_11gR2> select id,
2 weight,
3 dump (weight) dump,
4 comments
5 from test_tab
6 order by id
7 /
ID WEIGHT DUMP COMMENTS
---------- -------- --------------- ---------------------------------------
1 Typ=1 Len=1: 32 value like your data, default not used
2 Typ=1 Len=1: 32 null value inserted, default not used
3 Typ=1 Len=1: 32 no value inserted, default will be used
3 rows selected.
SCOTT@orcl_11gR2> select * from test_tab where weight = ' '
2 /
ID WEIGHT COMMENTS
---------- -------- ---------------------------------------
1 value like your data, default not used
2 null value inserted, default not used
3 no value inserted, default will be used
3 rows selected.
SCOTT@orcl_11gR2>
|
|
|
Re: sql loader issue [message #498088 is a reply to message #498087] |
Tue, 08 March 2011 11:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you are only concerned with how data is loaded via SQL*Loader, then you could use the following in your SQL*Loader control file, but you should be aware that future null values inserted via SQL without a trigger will be nulls, not spaces.
weight "DECODE(:weight,chr(0),chr(32),null,chr(32),:weight)"
|
|
|
Goto Forum:
Current Time: Sat Sep 07 23:46:54 CDT 2024
|