Database Software

Hi,

I am getting the special characters as part of my input file to SQL loader. The problem is that because the length of the input string (having special characters) is more than 1000 in input data, the converted bytes length is becoming more than 4000 and the defined substrb() function in control file is not working for it and the target 'ADDR_LINE' column in table 'TEST_TAB' is defined Varchar2(1024 char).

Following is a sample ctl file and data i am using for it.

LOAD DATA
CHARACTERSET UTF8
INFILE 'updated.txt'
APPEND INTO TABLE TEST_TAB
FIELDS TERMINATED BY "|~"
TRAILING NULLCOLS
(
INDX_WORD ,
ADDR_LINE "SUBSTRB(:ADDR_LINE , 1, 1000)",
CITY
)

following is the actual data which i am receiving as part of input file to sql loader for the ADDR_LINE column:


'RUA PEDROSO ALVARENGA, 1284 AƒAƒA‚AƒAƒA‚A‚AƒAƒAƒA‚A‚AƒA‚A‚AƒAƒAƒA‚AƒAƒA‚A‚A‚AƒAƒA‚A‚AƒA‚A‚AƒAƒAƒA‚AƒAƒA‚A‚AƒAƒAƒA‚A‚AƒA‚A‚A‚AƒAƒA‚AƒAƒA‚A‚A‚AƒAƒA‚A‚AƒA‚A‚A‚AƒAƒA‚AƒAƒA‚A‚AƒAƒAƒA‚A‚AƒA‚A‚AƒAƒAƒA‚AƒAƒA‚A‚A‚AƒAƒA‚A‚AƒA‚A‚A‚AƒAƒA‚AƒAƒA‚A‚AƒAƒAƒA‚A‚AƒA‚A‚A‚AƒAƒA‚AƒAƒA‚A‚A‚AƒAƒA‚A‚AƒA‚A‚A– 10 ANDAR'

My database is having following settings.
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR


Any help in this regard will be much appreciated.

Thanks in advance.