sqlldr and numbers formatted according 9999D99MI

I have to import numbers that are formattet according 9999D99MI (unfortunately I cannot change the input format). For example 1234,56- or 1234,56B. The B indicates a blank.

I tried it as follows:

>

LOAD DATA
INFILE * REPLACE INTO TABLE test
(
 col1 position(1:17) CHAR "TO_NUMBER(:col1, '9999999999999D99MI')" )
BEGINDATA
0000000107617,64
0000000107617,64-
<

The fist value has a trailing blank.
The second value can be processed by sqlldr80 but not the positive value. I get the error message:
ORA-01722: Invalid number.

When I execute with sqlplus:

select TO_NUMBER('12345,90 ', '9999999999999D99MI') from dual;

TO_NUMBER('12345,90','9999999999999D99MI')

                                   12345,9

it works!

Why?

I have already tried to go around this problem by substituting the blank with a '+'. But the transformation fails.

LOAD DATA
INFILE * REPLACE INTO TABLE test
(
 col1 position(1:17) CHAR "TO_NUMBER(TRANSLATE(:col1, ' ', '+'), '9999999999999D99MI')"
)
BEGINDATA
0000000107617,64
0000000107617,64-

But it did not work either (same error message). Again this works in sqlplus.

Does anyone can give me a hint?

Thanks a lot in advance!
Sascha Received on Tue Mar 13 2001 - 18:25:25 CET