SQL*Loader problem

[PWN]

Hi folks, I am having a problem using SQL loader to load some data
from a text file into a table. I am getting a "ORA-01722: invalid
number" error in the log file when loading data that has decimal
values. Here are some details:

Database: Oracle 9i Personal (9.2.0.1.0)
running on: W2K ws
NLS Language: ENGLISH_CANADA.WE8MSWIN1252
NLS characters: ".,"

The column in the table is defined as NUMBER(8,4) and the data that I
am trying to SQL Load is (e.g.) 1111.99. I can load data that has a
-ve sign to indicate a negative number (e.g. -99) but as soon as the
data has a decimal value, SQL loader dies.

Here is my control file:

LOAD DATA
INFILE 'C:\temp\data.dat'
BADFILE 'C:\temp\data.bad'
DISCARDFILE 'C:\temp\data.dsc'

TRUNCATE
INTO TABLE "T_TEMP"
TRUNCATE

FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'

(CODE,CLOSE,DATEVAL,CHANGE,OPEN,HIGH,LOW,RS)

data file:

"spf",1149.99,"2004-Mar-02",-6,1153,1156,1147,0

error message:
Record 1: Rejected - Error on table "T_TEMP", column CLOSE. ORA-01722:
invalid number

Any tips, suggestions, sites, or other postings that might help would
be greatly appreciated... Many thanks in advance!

[Daniel Morgan]

Post by PWN
Hi folks, I am having a problem using SQL loader to load some data
from a text file into a table. I am getting a "ORA-01722: invalid
number" error in the log file when loading data that has decimal
Database: Oracle 9i Personal (9.2.0.1.0)
running on: W2K ws
NLS Language: ENGLISH_CANADA.WE8MSWIN1252
NLS characters: ".,"
The column in the table is defined as NUMBER(8,4) and the data that I
am trying to SQL Load is (e.g.) 1111.99. I can load data that has a
-ve sign to indicate a negative number (e.g. -99) but as soon as the
data has a decimal value, SQL loader dies.
LOAD DATA
INFILE 'C:\temp\data.dat'
BADFILE 'C:\temp\data.bad'
DISCARDFILE 'C:\temp\data.dsc'
TRUNCATE
INTO TABLE "T_TEMP"
TRUNCATE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(CODE,CLOSE,DATEVAL,CHANGE,OPEN,HIGH,LOW,RS)
"spf",1149.99,"2004-Mar-02",-6,1153,1156,1147,0
invalid number
Any tips, suggestions, sites, or other postings that might help would
be greatly appreciated... Many thanks in advance!

...

Which value is the problem? One thing for sure ... 2004-Mar-02 is not a
date.

[PWN]

Post by PWN
Hi folks, I am having a problem using SQL loader to load some data
from a text file into a table. I am getting a "ORA-01722: invalid
number" error in the log file when loading data that has decimal
values.

Problem Solved.

In case anyone may have this problem, the solution is the following: I
was trying to SQL load data using the "." as a decimal character.
However, the NLS_NUMERIC_CHARACTERS setting (".,") was only set at the
session level, not at the database level. Once I set the
NLS_NUMERIC_CHARACTERS in the system registry and bounced the
database, problem solved. Moral of the story: be sure to set the
NLS_NUMERIC_CHARACTERS in the system registry so they take effect at
the database level.

[Joel Garry]

Post by PWN

Post by PWN
Hi folks, I am having a problem using SQL loader to load some data
from a text file into a table. I am getting a "ORA-01722: invalid
number" error in the log file when loading data that has decimal
values.

Problem Solved.
In case anyone may have this problem, the solution is the following: I
was trying to SQL load data using the "." as a decimal character.
However, the NLS_NUMERIC_CHARACTERS setting (".,") was only set at the
session level, not at the database level. Once I set the
NLS_NUMERIC_CHARACTERS in the system registry and bounced the
database, problem solved. Moral of the story: be sure to set the
NLS_NUMERIC_CHARACTERS in the system registry so they take effect at
the database level.

...

"I sell a lot of reports online. I sold a report to a Japanese client.
The shopping cart system made the mistake with the comma. Instead of
charging my client $1,200, it charged 1.2 cents." - Renato Beninatto

jg
--
@home.com is bogus.
Happy 58th Birthday, Burt Ward!