SQL Loader sample

exported the following as we have some problems with special characters

export NLS_LANGUAGE=AMERICA_AMERICAN.AL32UTF8

or

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1


2nd one worked for me

from here control file looks like below

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'EVStations11.pvn1.psv'
BADFILE 'EVStations11.pvn1.bad'
DISCARDFILE 'EVStations11.pvn1.dsc'
REPLACE
INTO TABLE WGPCCWUSR.BT_CHARGE_STATION
fields terminated by "|"
TRAILING NULLCOLS
(CSREGION, CSUID, VER, BRAND, CSLOGO, NAME, LDESC, ADDRESS, CITY, STATE, ZIP, PHONE, LAT, LON, CHTYPE, CHNUM, AMEN1, AMEN2, CSSEQ "WGPCCWUSR.BT_CHARGE_STATION_SEQ.NEXTVAL")

***************************************************

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'EVCDesc18.pvn1.psv'
BADFILE 'EVCDesc18.pvn1.bad'
DISCARDFILE 'EVCDesc18.pvn1.dsc'
REPLACE
INTO TABLE WGPCCWUSR.BT_CHARGE_STATION_DESC
fields terminated by "|"
TRAILING NULLCOLS
(CHARGERTYPEID, CVER, STDESC, LTDESC)


sqlldr \'/@xxx as sysdba\' control=loader.ctl

sktms lo export/oracle/home/sv/sqlldr


**********************************************************

Some times i got an error for few records saying

Record 3739: Rejected - Error on table WGPCCWUSR.BT_CHARGE_STATION, column LDESC.
Field in data file exceeds maximum length

I checked the bad file and located the record and found it has 256 characters. The column also has varchar(256). But we got the error in sqlldr because sqlldr only supports 255 characters. To fix this issue update the controlfile like below . Add the char(256) next to the column and rerun. The issue will be fixed

OPTIONS (SKIP=1)

LOAD DATA

INFILE 'EVStations20.pvn1.psv'

BADFILE 'EVStations20.pvn1.bad'

DISCARDFILE 'EVStations20.pvn1.dsc'

REPLACE

INTO TABLE WGPCCWUSR.BT_CHARGE_STATION

fields terminated by "|"

TRAILING NULLCOLS

(CSREGION, CSUID, VER, BRAND, CSLOGO, NAME, LDESC char(256), ADDRESS, CITY, STATE, ZIP, PHONE, LAT, LON, CHTYPE, CHNUM, AMEN1, AMEN2, CSSEQ "WGPCCWUSR.BT_CHARGE_STATION_SEQ.NEXTVAL")