Re: Issue while inserting data through sqlldr

From: gazzag <gareth_at_jamms.org>
Date: Tue, 14 Sep 2010 07:42:02 -0700 (PDT)
Message-ID: <7096f97c-2d85-4efd-878d-9e9cd73dcac4_at_a19g2000vbi.googlegroups.com>

On 14 Sep, 14:56, Sandy80 <svarshneym..._at_gmail.com> wrote:
> 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!

Have you tried the TRUNC function?

HTH
-g Received on Tue Sep 14 2010 - 09:42:02 CDT