Re: SQLLOADER: rejects caused by wrong decimalpoint

Hi,

> I want to read textfiles with the sqlloader. Everything works fine exept
> numbers with decimal separator. SQLLOADER wants a ',' and in the textfile a
> point is used. I think that is because of my german version of Personal
> Oracle 7. How can I tell him to accept the point?
> An example: PI is written in the file 3.1415, but sqlloader wants 3,1415.

There is certainly a right way to do it, which would be to set NLS Parameters right when SQL Loader connects to Oracle ( alter session set nls_numeric_characters = ',.' ; ) but as I don't know how to achiev this there is another way :

The datatype for a field in your datafile doesn't have to be the same as the datatype of the column you are loading. Oracle does implicit conversion when it sends loaded data to the database doing an insert.
So you can say in your controlfile

my_numeric_field char terminated by ',' "TRANSLATE( :my_numeric_field , '.',',')"

This produces on the database something like  insert ( ..... , target_numeric_colum , .... )

values ( ... ,
translate('NUMERIC_AS_CHAR_VALUE_JUST_LOADED.SMALLERTHANONE','.',',' ) , ..... )

To verify for you that it works I constructed some testdata , which I attached to this mail.
I used the following command to start sqlloader c:\orant\bin\sqlldr73 userid= the_user/the_password_at_the_tns_alias data=test.dat log=test.log control=test.ctl bad=test.bad

Finally the result
select * from loadertest ;

TEXT NR
-------- -----
first 23,4

second   2,1
third    3,5
fourth   7,6

4 rows retrieved

This "workaround" is not the best way to do it, but it works.

HTH , Regards
 Robo

LOAD DATA

        INFILE    *
        INTO TABLE  loadertest
        FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"'
(  text 	,
   nr 		CHAR TERMINATED bY ',' "TRANSLATE( :nr , '.',',')"

"first", 23.4 , 
"second", 2.1 ,
"third",  3.5 ,
"fourth", 7.6 ,






application/x-unknown-content-type-txtfile attachment: test.log