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!

On 14 Sep, 14:56, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
Have you tried the TRUNC function?


HTH
-g

On Sep 14, 10:42*am, gazzag <gar... (AT) jamms (DOT) org> wrote:
Or the round() function.



David Fitzjarrell

Thanks for the suggestions!

I removed all the earlier code and just put it as:

COLUMN_2 CHAR "TRUNC(:COLUMN_2,4)"

And this time it processed all the data properly, including the values
with no decimal places. It is working fine now!