Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can sqlldr be forced to ignore special characters?

LostInPermuationJun 4 2013 — edited Jun 12 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Is there a way to force the sqlldr to ignore ALL special characters found in an entry, and treat them as just plain text?

I've tried multiple different delimiters, and have fallen back to using PIPE, because it throws the least amount of errors.

Here is the ctl file start and a few lines of insert.

LOAD DATA
INFILE *
BADFILE './NRN_SITES.BAD'
DISCARDFILE './NRN_SITES.DSC'
APPEND INTO TABLE FS_NRIS_NVUM.NRN_SITES
Fields terminated by ";" Optionally enclosed by '|'
(
  SITE_CN,
  AFOREST_CODE,
  ROUND NULLIF (ROUND="NULL"),
  SITENUMBER NULLIF (SITENUMBER="NULL"),
  RD_CODE,
  SITENAME,
  SITETYPE,
  INFRA_ID,
  INFRA_ORACLE_CN,
  COMPLIANCE_RATE NULLIF (COMPLIANCE_RATE="NULL"),
  PROXYCODE,
  LATITUDE NULLIF (LATITUDE="NULL"),
  LONGITUDE NULLIF (LONGITUDE="NULL"),
  SITE_STATUS,
  LOCATION,
  TRAVELWAYCAT,
  MPOST_BEGIN,
  SEGLENGTH,
  SBYWAY,
  UNSAFESITE,
  ROSCAT,
  NOTES,
  CDA1,
  CDA2,
  CDA3,
  USERNAME,
  FINAL,
  LAST_UPDATE TIMESTAMP WITH TIME ZONE "MM/DD/YYYY HH24:MI:SS.FF9 TZR" NULLIF (LAST_UPDATE="NULL"),
  IS_NEW,
  VPDUNIT_ID,
  SAMPLEYEAR
)
BEGINDATA
|9decda2b-cba9-4619-911e-8b29c4df73fd|;|A01003|;3;1138;|Stevensville Ranger District|;|Charles Waters CG Non-proxy|;|OUDS|;||;||;NULL;|NONE|;46.575556;114.139772;|Active|;||;||;||;||;||;|N|;||;||;||;||;||;|TFRASER|;|Y|;06/17/2011 11:25:20.327719000 -05:00;|Y|;|0103|;|F2012|
|39770896-009b-4331-b705-0704d09a5d30|;|A01003|;3;1139;|Darby Ranger District|;|Rock Creek Horse CG  Non-proxy|;|OUDS|;||;||;NULL;|NONE|;46.06353;-114.23062;|ACTIVE|;||;||;||;||;||;|N|;||;||;||;||;||;|TFRASER|;|Y|;06/17/2011 11:25:20.365451000 -05:00;|Y|;|0103|;|F2012|
|854b6d35-0590-4bdb-8a47-7cd77d5a0631|;|A01003|;3;1141;|West Fork Ranger District|;|Alta CG Non-proxy|;|OUDS|;||;||;NULL;|NONE|;45.624487;-114.302009;|ACTIVE|;||;||;||;||;||;|N|;||;||;||;||;||;|TFRASER|;|Y|;06/17/2011 11:25:20.381963000 -05:00;|Y|;|0103|;|F2012|

But the users have thrown STUPID text in the columns! Things like

typed explicitely as seen in the rows.

: )   (no space, yes, that's a smiley)
--
X)
'Y'
'T'
"Y"
"T"
1)  (numbered steps or events)

when I sift through the error logs, it's hanging up on these causing errors like invalid length of the column and such.

So how can I make it not care what is between the field delimiters?
The sqlldr ctl file was generated using the export function in TOAD.

Thanks.