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
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 #497875 is a reply to message #497874] Mon, 07 March 2011 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: sql loader issue [message #497877 is a reply to message #497874] Mon, 07 March 2011 12:34 Go to previous messageGo to next message
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 messageGo to next message
joy_division
Messages: 4949
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 messageGo to next message
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 #497886 is a reply to message #497885] Mon, 07 March 2011 13:15 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use the dump function to find out what's really there:
select dump(weight) from trlc
Re: sql loader issue [message #497888 is a reply to message #497885] Mon, 07 March 2011 13:18 Go to previous messageGo to next message
joy_division
Messages: 4949
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 #497896 is a reply to message #497888] Mon, 07 March 2011 13:47 Go to previous messageGo to next message
newsurfgal
Messages: 12
Registered: August 2009
Junior Member
This is the output I get:

select dump(weight) from trlc;

Typ=1 Len=1: 0

Any suggestions please? Thanks
Re: sql loader issue [message #497915 is a reply to message #497896] Mon, 07 March 2011 15:15 Go to previous messageGo to next message
joy_division
Messages: 4949
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 13895
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 messageGo to next message
Barbara Boehmer
Messages: 8922
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
Barbara Boehmer
Messages: 8922
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)"
Previous Topic: expdp only procedures
Next Topic: Unable to Create Control file
Goto Forum:
  


Current Time: Sun Nov 29 04:55:26 CST 2020