Re: handling special characters in sqlldr
From: ddf <oratune_at_msn.com>
Date: Thu, 9 Sep 2010 14:13:34 -0700 (PDT)
Message-ID: <3e817221-8f4f-430f-b543-e6f2be4da50e_at_p37g2000pra.googlegroups.com>
On Sep 9, 12:11 pm, dmardkar <dmard..._at_gmail.com> wrote:
> Hi,
>
> I am trying to load a record(s) in a table which contains a field of
> text datatype. The records in question contain occasional single
> qoutes. This is an example of such records:
>
> en|PRD-SHPE|11.0|NOTE: If a slab is a dummy', an asterisk is
> displayed at the end of the|20011003.0|
>
> I am then creating a control file and use sqlldr to load the data but
> the load fails with the following messege:
>
> Record 1: Rejected - Error on table SCRHLP_REC, column HLP_TXT.
> ORA-12899: value too large for column
> "RPSNX31"."SCRHLP_REC"."HLP_TXT" (actual: 75, maximum: 74)
>
> Here is the control file I am using:
>
> load data
> infile "./scrhlp.dat" "str X'400A'"
> badfile "./scrhlp.bad"
> into table scrhlp_rec
> fields terminated by "|"
> (
> HLP_LNG_CD ,
> HLP_FLD_NM ,
> HLP_LN_NO ,
> HLP_TXT ,
> HLP_REF_DT
> )
>
> If I remove the single qoute in the data, all works fine. How do I
> specify that a single qoute is acceptable in this case? Please help.
>
> Thanks.
Date: Thu, 9 Sep 2010 14:13:34 -0700 (PDT)
Message-ID: <3e817221-8f4f-430f-b543-e6f2be4da50e_at_p37g2000pra.googlegroups.com>
On Sep 9, 12:11 pm, dmardkar <dmard..._at_gmail.com> wrote:
> Hi,
>
> I am trying to load a record(s) in a table which contains a field of
> text datatype. The records in question contain occasional single
> qoutes. This is an example of such records:
>
> en|PRD-SHPE|11.0|NOTE: If a slab is a dummy', an asterisk is
> displayed at the end of the|20011003.0|
>
> I am then creating a control file and use sqlldr to load the data but
> the load fails with the following messege:
>
> Record 1: Rejected - Error on table SCRHLP_REC, column HLP_TXT.
> ORA-12899: value too large for column
> "RPSNX31"."SCRHLP_REC"."HLP_TXT" (actual: 75, maximum: 74)
>
> Here is the control file I am using:
>
> load data
> infile "./scrhlp.dat" "str X'400A'"
> badfile "./scrhlp.bad"
> into table scrhlp_rec
> fields terminated by "|"
> (
> HLP_LNG_CD ,
> HLP_FLD_NM ,
> HLP_LN_NO ,
> HLP_TXT ,
> HLP_REF_DT
> )
>
> If I remove the single qoute in the data, all works fine. How do I
> specify that a single qoute is acceptable in this case? Please help.
>
> Thanks.
I have no problems loadng data with a single-quote inline; are your text strings enclosed with ' or "? Using " my loads succeed. Possibly you should change the enclosing quotation character for your string data.
David Fitzjarrell Received on Thu Sep 09 2010 - 16:13:34 CDT