Re: Issue while inserting data through sqlldr
From: ddf <oratune_at_msn.com>
Date: Tue, 14 Sep 2010 09:06:37 -0700 (PDT)
Message-ID: <3308bb8f-68d6-480a-b19d-b3b32c21b84f_at_q16g2000prf.googlegroups.com>
On Sep 14, 10:42 am, gazzag <gar..._at_jamms.org> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 14 Sep 2010 09:06:37 -0700 (PDT)
Message-ID: <3308bb8f-68d6-480a-b19d-b3b32c21b84f_at_q16g2000prf.googlegroups.com>
On Sep 14, 10:42 am, gazzag <gar..._at_jamms.org> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Or the round() function.
David Fitzjarrell Received on Tue Sep 14 2010 - 11:06:37 CDT