Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!p37g2000pra.googlegroups.com!not-for-mail
From: ddf <oratune@msn.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: handling special characters in sqlldr
Date: Thu, 9 Sep 2010 14:13:34 -0700 (PDT)
Organization: http://groups.google.com
Lines: 44
Message-ID: <3e817221-8f4f-430f-b543-e6f2be4da50e@p37g2000pra.googlegroups.com>
References: <a6b7e40b-b7dd-4b0e-a00f-ea7486fe68b6@j19g2000vbh.googlegroups.com>
NNTP-Posting-Host: 72.192.72.65
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1284066814 12586 127.0.0.1 (9 Sep 2010 21:13:34 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 9 Sep 2010 21:13:34 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: p37g2000pra.googlegroups.com; posting-host=72.192.72.65; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1;
 InfoPath.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152;
 .NET CLR 3.5.30729),gzip(gfe)
Xref:  news.cambrium.nl

On Sep 9, 12:11=A0pm, dmardkar <dmard...@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: =A0If 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 =A0 =A0 =A0 =A0 ,
> HLP_FLD_NM =A0 =A0 =A0 =A0 ,
> HLP_LN_NO =A0 =A0 =A0 =A0 =A0,
> 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