Re: problem with SQL Loader

You need to be using the TO_NUMBER function for the CLOSE column in your control file. See metalink Note 110227.1 for an example

td

PWN wrote:

> 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 - 19:38:48 CDT