Re: [sqlldr] upload number datatype from varchar2 datatype in ctl file

chijar <chijar_at_gmail.com> writes:

> dear friends.
> This is my datfile...in the last field the data is: |3180.8| or |605.|
> like you can see...
>
> in the table destination the field have NUMBER(12,2) and i want to
> upload in this datatype from my control file...
>
> Example of my datfile is:
> 00000000|0|0601|2000082|6|20131023414|3378|1|MATUTE|40|C|MATUTE|CUADRA
> 17 DE ISABEL LA CATOLICA|11|03|150115|3180.8|
> 00000000|0|0601|2000082|1|20131023414|3378|1|MATUTE|40|C|MATUTE|CUADRA
> 17 DE ISABEL LA CATOLICA|11|03|150115|605.|
>

[...]
>
> value used for ROWS parameter changed from 64 to 58
> Record 1: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM,
> column TEST_NUMBER.
> ORA-01722: numero no valido
>
> Record 2: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM,
> column TEST_NUMBER.
> ORA-01722: numero no valido
>
> Record 3: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM,
> column TEST_NUMBER.
> ORA-01722: numero no valido
>
> Record 4: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM,
> column TEST_NUMBER.
> ORA-01722: numero no valido
>
> Record 5: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM,
> column TEST_NUMBER.
> ORA-01722: numero no valido
>
>
> Table PLANELEC.PEMVX_DATOSCOMPDOM:
> 0 Rows successfully loaded.
> 5 Rows not loaded due to data errors.
> 0 Rows not loaded because all WHEN clauses were failed.
> 0 Rows not loaded because all fields were null.
[...]
>
> Please,
> would you help me ? is very important.
> a lot of thanks...
> Whtat is the errors???
>
> cesar

Hello Cesar,
just a wild guess, but since 3180.8 isn't loaded which is a valid numeric value, it seems that your NLS settings use different numeric characters. Maybe 3180,8 should be the right value. Another option would be setting NLS_NUMERIC_CHARS (maybe it's NLS_NUMERIC_CHARACTERS, you'll have to check that :-) prior to calling sqlldr.

Hope that helps,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |