Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!j19g2000vbh.googlegroups.com!not-for-mail From: dmardkar Newsgroups: comp.databases.oracle.server Subject: handling special characters in sqlldr Date: Thu, 9 Sep 2010 09:11:52 -0700 (PDT) Organization: http://groups.google.com Lines: 41 Message-ID: NNTP-Posting-Host: 198.168.207.252 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Trace: posting.google.com 1284048712 30785 127.0.0.1 (9 Sep 2010 16:11:52 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 9 Sep 2010 16:11:52 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: j19g2000vbh.googlegroups.com; posting-host=198.168.207.252; posting-account=V8R9PgoAAADc93TwSrrH1OzlUf_gknD9 User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; GTB6.5; SLCC1; .NET CLR 2.0.50727; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; .NET CLR 3.5.30729),gzip(gfe) Xref: news.cambrium.nl 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.