SQL Loader put NULL in NCHAR field when it should be blanks

[petersJazz]

In our sql loader scripts we load text files into unicode char fields. In previous version of the software it loaded the same text files into normal char fields and then it loaded blanks into blanks in table. Is this a bug, can the behavior be overwritten somehow?

 

[LKBrwnDBA]

Can you share a piece of the offending controlfile?
What about the column definitions on which the issue happens?
What about the NLS_LANG environment variable, is some type of character type conversion happening?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[petersJazz]

Thank you for your comment.

Here are some more details:

Command line in unix to do the load is (unix AIX):

sqlldr errors=0 rows=9999 discardmax=1 control=p1.ctl userid=... log=p1.log

p1.dat is the data file, ordinary text file, one line looks like this:

FENIXLR ; ;00;0000001 ; ;00001000; ; ;000000; ;00000;0;A;J;3000001497 ; ;00000000;00100;UQ;00000000;109365;0000000; ; ;00000000;G ;000000;000000;000000; ;100B60003120,26968,1999 ;2; ; ; ; ;00099000;A;AA;00;00;00; ;SEK;000000000.00000;000000000000000;000000; 000000006138000; 000000000000000; ; ; ;FENIXLR 3000001497 ;FENIXLR ;S2102 ;F0010004; ; ;00000000; ; ; ; ; ; ; ;000000;000000; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ;000000; ; ; ; ;0000000;00;00; ;000000; ; ; ;00; ; ;000000;SE ;00000000;000000000000000;FENIXLR ; ;FENIXLR ;000000;000000;D; 000000000000000;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00;00; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ;

And control file p1.ctl looks like this, with my workaround nvl() that I use for the moment to load blank.

LOAD DATA

INFILE '/somepath/p1.dat'

APPEND

INTO TABLE sometable

(VNEDUS POSITION(01:10) char,
VNEDTY POSITION(12:12) char,
...
VNPOST POSITION(154:154) char "nvlVNPOST,' ')",
...
VNITM POSITION(1033:1040) char)

 

[LKBrwnDBA]

To prevent whitespace trimming in all CHAR, DATE, and numeric EXTERNAL fields, you specify PRESERVE BLANKS as part of the LOAD statement in the control file.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[petersJazz]

hi,

thanks. We have tried that but its not a good idee. We get error messages if we do so for every numeric field.

 

[LKBrwnDBA]

And if you do it only for the text fields?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[petersJazz]

Yes, that the work around I described. Thats what we have done. But it takes some time with lots of columns and tables.

 

[Beilstwh]

Using NCHAR fields are not a good idea. Uzse NVARCHAR2 fields and your problem will go away.

Bill
Lead Application Developer
New York State, USA

 

[petersJazz]

Thank you for the advice but this is a standard system (Oracle EnterpriseOne) and we can not change data types in it. Rather, we can but we dont know that effect that will give. We have now already solved it with the work around so its not much more to say I guess. But, thanks for your time.