0

I have a problem regarding a special character. I have a table test as below:

SQL> desc test
Name           Type              Nullable Default Comments 
-------------- ----------------- -------- ------- -------- 
DOT            DATE              Y                         
LOWVAL_CHAR    VARCHAR2(3)       Y                         

I am inserting data in this table using sqlldr. The data file is as below:

1984/01/10ÿ:-1    0     -99999+99999Sourav         Bhattacharya        

ctl file is as below:
LOAD DATA
CHARACTERSET AL32UTF8
APPEND
PRESERVE BLANKS
INTO TABLE TEST
(dot            POSITION(1) CHAR(10) "TO_DATE(:DOT,'YYYY/MM/DD')",
 lowval_char    POSITION(11) CHAR(1),
 highval_char   POSITION(12) CHAR(1),
 lowval_un_num  POSITION(13) INTEGER EXTERNAL(6),
 highval_un_num POSITION(19) INTEGER EXTERNAL(6),
 lowval_si_num  POSITION(25) INTEGER EXTERNAL(6),
 highval_si_num POSITION(31) INTEGER EXTERNAL(6),
 fn             POSITION(37) CHAR(10),
 mn             POSITION(47) CHAR(5),
 ln             POSITION(52) CHAR(20)
 )

Data got inserted correctly.

Now if I execute the query

select dot,lowval_char from test;

It is giving me correct result but if I concatenate i.e

select dot||lowval_char from test;

ÿ is not displaying, only the dot value is visible.

If I do not use SQL loader and do any insert into it is giving correct result for both the cases.

Settings:

character set is AL32UTF8
nls_language='AMERICAN'
NLS_LENGTH_SEMANTICS='BYTE'
halfer
  • 19,824
  • 17
  • 99
  • 186

0 Answers0