Archive

Archive for the ‘SQL Loader’ Category

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

August 23, 2011 5 comments

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.

Wrong characters using SQL Loader against an UTF8 database

February 8, 2011 2 comments

I have to load periodically data with SQL Loader. The source data is generated in a Windows environment, and I load the data in our servers (linux-based).

I have problems uploading special characters (Ñ, Ç, Á, …) because of the different NLS_LANG of the source data (ISO-8859, WE8ISO8859P1) and the NLS_LANG of the database (SPANISH_SPAIN.UTF8).

I’ve tried to convert the source data to UTF8 with iconv, but I still get wrong data.

Trying to specify the NLS_LANG in the control file does not work:


LOAD DATA
CHARACTERSET UTF8
INFILE ...

So finally, the solution that worked for me is to export NLS_LANG before uploading the data with SQL Loader:


export NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1

sqlldr userid=user@database control=control_file.ctl log=log_file.log bad=bad_records.bad direct=true

And I get the correct data.

A few and interesting references about SQL Loader:

http://www.orafaq.com/wiki/SQL*Loader_FAQ

http://www.akadia.com/services/ora_sql_loader_utf8.html