Home > oracle, SQL Loader > ORA-01722 – Invalid number with SQL*Loader in linux

ORA-01722 – Invalid number with SQL*Loader in linux

I often perform data laods with SQL*Loader. I receive all the scripts and ctl files from windows environment, usually generated with TOAD.

Among all of the problems I usually have, today I found this odd case:

Record 1: Rejected - Error in table XXX.
ORA-00604: error occurred at recursive SQL level 1
ORA-01722: Invalid number

This is because the last column of the table data is decimal data.

The table definition is like this:

create table TEMP_TABLE (
year varchar2(4),
id_est varchar2(11),
id_prod varchar2(13),
factor number);

And the control file is:

load data
infile 'data.dat'
append
into table TEMP_TABLE
fields terminated by ';'
optionally enclosed by '"'
trailing nullcols
(year constant '10',
id_est CHAR,
id_prod CHAR,
factor CHAR "to_number(ltrim(rtrim(replace(:factor,'.',','))))");

The data file has this kind of records:

01126940;3511107596;1.0000
01129192;3511107596;3.9500
01157376;3511107596;4.4343
01159646;3511107596;4.4343

As I work with “;” as column separator, and the last column is number, maybe this problem only happens with the last column being a number, and inserting the data in a number column also.
this happens because in windows the line terminator is CR/LF:

$ file data.dat
data.dat: ASCII text, with CRLF line terminators

… and in linux is LF. So, the easiest way is to convert it to linux/unix:

$ fromdos data.dat

and now the load works perfect.
Another way to solve it, instead of converting the file to unix, is using the WHITESPACE delimiter in the control file:

factor CHAR TERMINATED BY WHITESPACE "to_number(ltrim(rtrim(replace(:factor,'.',','))))"

Just one more thing to mention. I have my local box with ubuntu, and the databases are on RedHat servers. I have a local installation of Oracle 10g on my machine, so, to perform the loads, I have this shell script:

#!/bin/bash

export NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1; echo $NLS_LANG
export ORACLE_HOME=/app/oracle/product/10.2.0/db_1; echo $ORACLE_HOME

$ORACLE_HOME/bin/sqlldr userid=user@db control=control.ctl log=data.log bad=data.bad direct=true

Note the NLS_LANG variable. This is because the files are generated in Windows, and my Ubuntu is in UTF8. I have had problems loading data with catalan/spanish characters (á, é, ç, ñ, etc.), but with this setting all works fine.

Advertisements
  1. November 4, 2013 at 22:27

    Gracias, me ha servido mucho este articulo, no podia lograr la carga de datos con punto decimal. Muchas gracias

    Like

    • November 5, 2013 at 8:40

      Me alegro de que el blog sea de ayuda. Gracias por el comentario Claudio.

      Like

  2. Aleksey Ko
    September 3, 2013 at 13:26

    Thank you very much for advise!
    It was helpfull for me 🙂

    Like

  3. Javier
    February 24, 2012 at 13:41

    Hola Miquel,
    Thanks a lot for the article, it was massively useful for me (after 3 days of trying to solve a very similar issue). In particular, I was affected by this:

    “Among all of the problems I usually have, today I found this odd case:

    1 Record 1: Rejected – Error in table XXX.
    2 ORA-00604: error occurred at recursive SQL level 1
    3 ORA-01722: Invalid number

    This is because the last column of the table data is decimal data.”

    The reason for this, I found out, was that since the last column wasn´t terminated on a “;”, it only had the number, Oracle was reading it as the number plus the carriage return. This made it think that the record was not a number. Adding a ; to the end of each line did the job to solve it.

    Like

    • February 24, 2012 at 14:49

      You’re wellcome, Javier
      Me alegra haberte ayudado

      Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: