Issue while inserting data through sqlldr

From: Sandy80 <svarshneymail_at_gmail.com>
Date: Tue, 14 Sep 2010 06:56:40 -0700 (PDT)
Message-ID: <3f097e41-beff-4ad4-95b0-a854c6b8df52_at_v23g2000vbi.googlegroups.com>

Hi,

I am having an issue while inserting data into an oracle table through sqlldr. My requirement is to be able to enter data into a field with 4 decimal places. Since I am getting data which has values with more than 4 decimal places those records are rejected.

I use the following command in the control file for the table:

LOAD DATA
   APPEND
   INTO TABLE TB_01

        WHEN record_type_CD = 'RECORD'
	FIELDS TERMINATED BY "|"
	trailing NULLCOLS
(
       COLUMN_1      	INTEGER		EXTERNAL,
       COLUMN_2         CHAR
		"TO_NUMBER(:COLUMN_2,'999999999.9999')",
       COLUMN_3        INTEGER		EXTERNAL

)
The records get rejected when the data for column 2 is like "| 80300.3519999999989522621035575866699219|"

If I change the control file and make the code for column 2 as COLUMN_2 CHAR "TO_CHAR(:COLUMN_2,'999999999D9999')" then the load rejects records with data like "|4257000000|" in column 2.

I want to be able to insert both the records into the table with the value being limited to 4 decimal places. Let me know if you can provide any help on this. Any help would be appreciated. Thanks! Received on Tue Sep 14 2010 - 08:56:40 CDT