Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL*Loader problem

SQL*Loader problem

From: PWN <pwneilson_at_telus.net>
Date: 2 Jul 2004 15:09:32 -0700
Message-ID: <f8044af9.0407021409.333df0ed@posting.google.com>


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! Received on Fri Jul 02 2004 - 17:09:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US