Home » SQL & PL/SQL » SQL & PL/SQL » Cannot insert specific char (Oracle 11.2.0.1.0 - 64bit, Linux OEL 6)
Cannot insert specific char [message #673898] Sat, 15 December 2018 15:39 Go to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I am trying to perform an insert statement with an expression that contains the characters 'C' and 'º' ( char 186 ).

I am trying to do it without being dependent on stuff like code page of my client, character sets or any other settings..

I've prepared a user and a table for it:

SQL> drop user a cascade;

User dropped.

SQL> create user a identified by a;

User created.

SQL> grant dba to a;

Grant succeeded.

Then I connected with this user and tested creation of this expression:

SQL> conn a/a@MYCONNSTRING
Connected.
SQL>
SQL>
SQL>
SQL> show user
USER is "A"
SQL>
SQL>
SQL>
SQL> create table mytest as select 'C'||chr(186) as res from dual;

Table created.

SQL>

Then I tried to access this table with dump function, to see the insides:

SQL> select dump(res) from mytest;

DUMP(RES)
--------------------------------------------------------------------------------
Typ=1 Len=1: 67

SQL>


Only the symobol 67, which means 'C' is present there.

If I insert only this symbol it seems alright:

SQL> insert into mytest values (chr(186));

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> select dump(res) from mytest;

DUMP(RES)
---------------------------------------------
Typ=1 Len=1: 67
Typ=1 Len=1: 186

SQL>

Why is that happening, and how can I explicitly insert the letter 'C' concatinated with char No 186 via a textual script to the database without literals ?


Thanks in advance,
Andrey
Re: Cannot insert specific char [message #673905 is a reply to message #673898] Sun, 16 December 2018 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I can't reproduce what you said (2 sessions with different client character set):
SQL> select distinct client_charset||' - '||client_version
  2  from v$session_connect_info
  3  where sid = sys_context('USERENV','SID')
  4    and client_charset is not null
  5  /
CLIENT_CHARSET||'-'||CLIENT_VERSION
------------------------------------------------------------------
WE8PC850 - 11.2.0.4.0

1 row selected.

SQL> drop table mytest;

Table dropped.

SQL> create table mytest as select 'C'||chr(186) as res from dual;

Table created.

SQL> insert into mytest values (chr(186));

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(res) from mytest;
DUMP(RES)
------------------------------------------------------------------
Typ=1 Len=2: 67,186
Typ=1 Len=1: 186

2 rows selected.
SQL> select distinct client_charset||' - '||client_version
  2  from v$session_connect_info
  3  where sid = sys_context('USERENV','SID')
  4    and client_charset is not null
  5  /
CLIENT_CHARSET||'-'||CLIENT_VERSION
-------------------------------------------------------------------
WE8MSWIN1252 - 11.2.0.4.0

1 row selected.

SQL> drop table mytest;

Table dropped.

SQL> create table mytest as select 'C'||chr(186) as res from dual;

Table created.

SQL> insert into mytest values (chr(186));

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(res) from mytest;
DUMP(RES)
--------------------------------------
Typ=1 Len=2: 67,186
Typ=1 Len=1: 186

2 rows selected.

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------
WE8MSWIN1252

1 row selected.

SQL> select banner from v$version where rownum=1;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
I can't image a reason why, if you insert 2 characters (on CTAS), Oracle stores only 1.

[Updated on: Sun, 16 December 2018 04:59]

Report message to a moderator

Re: Cannot insert specific char [message #673906 is a reply to message #673905] Sun, 16 December 2018 06:05 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

The version I replicated it with the behavior in question I showed is 11.2.0.1 and character set of the database is AL32UTF8


When I tried it on a 11.2.0.3 database ( also AL32UTF8 ) it showed correct results as expected, just like you showed.


Could it be a bug ?

[Updated on: Sun, 16 December 2018 06:05]

Report message to a moderator

Re: Cannot insert specific char [message #673907 is a reply to message #673906] Sun, 16 December 2018 08:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2948
Registered: January 2010
Location: Connecticut, USA
Senior Member
No, it isn't a bug. CHR(167), same as any CHR > 127 is extended ASCII character and simply doesn't exists in unicode. Read MOS doducment AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1):

"Only US7ASCII (A-Z,a-z,0-9) characters have the same codepoints in AL32UTF8 as in US7ASCII, WE8ISO8859P1, AR8MSWIN1256 etc. meaning that using chr() for any value above 128 should be best avoided".

You can get strange and unexpected results when using extended ascii ranging from all sorts of errors (including 600) to results where same extended ascii character acts differently:

select  level - 1 n,
        chr(level-1) c,
        dump(chr(level-1)) d,
        dump('C' || chr(level-1)) dd
  from  dual
  connect by level <= 256
/

         N C    D                    DD
---------- ---- -------------------- --------------------
         0      Typ=1 Len=1: 0       Typ=1 Len=2: 67,0
         1     Typ=1 Len=1: 1       Typ=1 Len=2: 67,1
         2     Typ=1 Len=1: 2       Typ=1 Len=2: 67,2
         3     Typ=1 Len=1: 3       Typ=1 Len=2: 67,3
         4     Typ=1 Len=1: 4       Typ=1 Len=2: 67,4
         5     Typ=1 Len=1: 5       Typ=1 Len=2: 67,5
         6     Typ=1 Len=1: 6       Typ=1 Len=2: 67,6
         7     Typ=1 Len=1: 7       Typ=1 Len=2: 67,7
         8     Typ=1 Len=1: 8       Typ=1 Len=2: 67,8
         9          Typ=1 Len=1: 9       Typ=1 Len=2: 67,9
        10      Typ=1 Len=1: 10      Typ=1 Len=2: 67,10
        11     Typ=1 Len=1: 11      Typ=1 Len=2: 67,11
        12     Typ=1 Len=1: 12      Typ=1 Len=2: 67,12
        13      Typ=1 Len=1: 13      Typ=1 Len=2: 67,13
        14     Typ=1 Len=1: 14      Typ=1 Len=2: 67,14
        15     Typ=1 Len=1: 15      Typ=1 Len=2: 67,15
        16     Typ=1 Len=1: 16      Typ=1 Len=2: 67,16
        17     Typ=1 Len=1: 17      Typ=1 Len=2: 67,17
        18     Typ=1 Len=1: 18      Typ=1 Len=2: 67,18
        19     Typ=1 Len=1: 19      Typ=1 Len=2: 67,19
        20     Typ=1 Len=1: 20      Typ=1 Len=2: 67,20
        21     Typ=1 Len=1: 21      Typ=1 Len=2: 67,21
        22     Typ=1 Len=1: 22      Typ=1 Len=2: 67,22
        23     Typ=1 Len=1: 23      Typ=1 Len=2: 67,23
        24     Typ=1 Len=1: 24      Typ=1 Len=2: 67,24
        25     Typ=1 Len=1: 25      Typ=1 Len=2: 67,25
        26     Typ=1 Len=1: 26      Typ=1 Len=2: 67,26
        27     Typ=1 Len=1: 27      Typ=1 Len=2: 67,27
        28     Typ=1 Len=1: 28      Typ=1 Len=2: 67,28
        29     Typ=1 Len=1: 29      Typ=1 Len=2: 67,29
        30     Typ=1 Len=1: 30      Typ=1 Len=2: 67,30
        31     Typ=1 Len=1: 31      Typ=1 Len=2: 67,31
        32      Typ=1 Len=1: 32      Typ=1 Len=2: 67,32
        33 !    Typ=1 Len=1: 33      Typ=1 Len=2: 67,33
        34 "    Typ=1 Len=1: 34      Typ=1 Len=2: 67,34
        35 #    Typ=1 Len=1: 35      Typ=1 Len=2: 67,35
        36 $    Typ=1 Len=1: 36      Typ=1 Len=2: 67,36
        37 %    Typ=1 Len=1: 37      Typ=1 Len=2: 67,37
        38 &    Typ=1 Len=1: 38      Typ=1 Len=2: 67,38
        39 '    Typ=1 Len=1: 39      Typ=1 Len=2: 67,39
        40 (    Typ=1 Len=1: 40      Typ=1 Len=2: 67,40
        41 )    Typ=1 Len=1: 41      Typ=1 Len=2: 67,41
        42 *    Typ=1 Len=1: 42      Typ=1 Len=2: 67,42
        43 +    Typ=1 Len=1: 43      Typ=1 Len=2: 67,43
        44 ,    Typ=1 Len=1: 44      Typ=1 Len=2: 67,44
        45 -    Typ=1 Len=1: 45      Typ=1 Len=2: 67,45
        46 .    Typ=1 Len=1: 46      Typ=1 Len=2: 67,46
        47 /    Typ=1 Len=1: 47      Typ=1 Len=2: 67,47
        48 0    Typ=1 Len=1: 48      Typ=1 Len=2: 67,48
        49 1    Typ=1 Len=1: 49      Typ=1 Len=2: 67,49
        50 2    Typ=1 Len=1: 50      Typ=1 Len=2: 67,50
        51 3    Typ=1 Len=1: 51      Typ=1 Len=2: 67,51
        52 4    Typ=1 Len=1: 52      Typ=1 Len=2: 67,52
        53 5    Typ=1 Len=1: 53      Typ=1 Len=2: 67,53
        54 6    Typ=1 Len=1: 54      Typ=1 Len=2: 67,54
        55 7    Typ=1 Len=1: 55      Typ=1 Len=2: 67,55
        56 8    Typ=1 Len=1: 56      Typ=1 Len=2: 67,56
        57 9    Typ=1 Len=1: 57      Typ=1 Len=2: 67,57
        58 :    Typ=1 Len=1: 58      Typ=1 Len=2: 67,58
        59 ;    Typ=1 Len=1: 59      Typ=1 Len=2: 67,59
        60 <    Typ=1 Len=1: 60      Typ=1 Len=2: 67,60
        61 =    Typ=1 Len=1: 61      Typ=1 Len=2: 67,61
        62 >    Typ=1 Len=1: 62      Typ=1 Len=2: 67,62
        63 ?    Typ=1 Len=1: 63      Typ=1 Len=2: 67,63
        64 @    Typ=1 Len=1: 64      Typ=1 Len=2: 67,64
        65 A    Typ=1 Len=1: 65      Typ=1 Len=2: 67,65
        66 B    Typ=1 Len=1: 66      Typ=1 Len=2: 67,66
        67 C    Typ=1 Len=1: 67      Typ=1 Len=2: 67,67
        68 D    Typ=1 Len=1: 68      Typ=1 Len=2: 67,68
        69 E    Typ=1 Len=1: 69      Typ=1 Len=2: 67,69
        70 F    Typ=1 Len=1: 70      Typ=1 Len=2: 67,70
        71 G    Typ=1 Len=1: 71      Typ=1 Len=2: 67,71
        72 H    Typ=1 Len=1: 72      Typ=1 Len=2: 67,72
        73 I    Typ=1 Len=1: 73      Typ=1 Len=2: 67,73
        74 J    Typ=1 Len=1: 74      Typ=1 Len=2: 67,74
        75 K    Typ=1 Len=1: 75      Typ=1 Len=2: 67,75
        76 L    Typ=1 Len=1: 76      Typ=1 Len=2: 67,76
        77 M    Typ=1 Len=1: 77      Typ=1 Len=2: 67,77
        78 N    Typ=1 Len=1: 78      Typ=1 Len=2: 67,78
        79 O    Typ=1 Len=1: 79      Typ=1 Len=2: 67,79
        80 P    Typ=1 Len=1: 80      Typ=1 Len=2: 67,80
        81 Q    Typ=1 Len=1: 81      Typ=1 Len=2: 67,81
        82 R    Typ=1 Len=1: 82      Typ=1 Len=2: 67,82
        83 S    Typ=1 Len=1: 83      Typ=1 Len=2: 67,83
        84 T    Typ=1 Len=1: 84      Typ=1 Len=2: 67,84
        85 U    Typ=1 Len=1: 85      Typ=1 Len=2: 67,85
        86 V    Typ=1 Len=1: 86      Typ=1 Len=2: 67,86
        87 W    Typ=1 Len=1: 87      Typ=1 Len=2: 67,87
        88 X    Typ=1 Len=1: 88      Typ=1 Len=2: 67,88
        89 Y    Typ=1 Len=1: 89      Typ=1 Len=2: 67,89
        90 Z    Typ=1 Len=1: 90      Typ=1 Len=2: 67,90
        91 [    Typ=1 Len=1: 91      Typ=1 Len=2: 67,91
        92 \    Typ=1 Len=1: 92      Typ=1 Len=2: 67,92
        93 ]    Typ=1 Len=1: 93      Typ=1 Len=2: 67,93
        94 ^    Typ=1 Len=1: 94      Typ=1 Len=2: 67,94
        95 _    Typ=1 Len=1: 95      Typ=1 Len=2: 67,95
        96 `    Typ=1 Len=1: 96      Typ=1 Len=2: 67,96

         N C    D                    DD
---------- ---- -------------------- --------------------
        97 a    Typ=1 Len=1: 97      Typ=1 Len=2: 67,97
        98 b    Typ=1 Len=1: 98      Typ=1 Len=2: 67,98
        99 c    Typ=1 Len=1: 99      Typ=1 Len=2: 67,99
       100 d    Typ=1 Len=1: 100     Typ=1 Len=2: 67,100
       101 e    Typ=1 Len=1: 101     Typ=1 Len=2: 67,101
       102 f    Typ=1 Len=1: 102     Typ=1 Len=2: 67,102
       103 g    Typ=1 Len=1: 103     Typ=1 Len=2: 67,103
       104 h    Typ=1 Len=1: 104     Typ=1 Len=2: 67,104
       105 i    Typ=1 Len=1: 105     Typ=1 Len=2: 67,105
       106 j    Typ=1 Len=1: 106     Typ=1 Len=2: 67,106
       107 k    Typ=1 Len=1: 107     Typ=1 Len=2: 67,107
       108 l    Typ=1 Len=1: 108     Typ=1 Len=2: 67,108
       109 m    Typ=1 Len=1: 109     Typ=1 Len=2: 67,109
       110 n    Typ=1 Len=1: 110     Typ=1 Len=2: 67,110
       111 o    Typ=1 Len=1: 111     Typ=1 Len=2: 67,111
       112 p    Typ=1 Len=1: 112     Typ=1 Len=2: 67,112
       113 q    Typ=1 Len=1: 113     Typ=1 Len=2: 67,113
       114 r    Typ=1 Len=1: 114     Typ=1 Len=2: 67,114
       115 s    Typ=1 Len=1: 115     Typ=1 Len=2: 67,115
       116 t    Typ=1 Len=1: 116     Typ=1 Len=2: 67,116
       117 u    Typ=1 Len=1: 117     Typ=1 Len=2: 67,117
       118 v    Typ=1 Len=1: 118     Typ=1 Len=2: 67,118
       119 w    Typ=1 Len=1: 119     Typ=1 Len=2: 67,119
       120 x    Typ=1 Len=1: 120     Typ=1 Len=2: 67,120
       121 y    Typ=1 Len=1: 121     Typ=1 Len=2: 67,121
       122 z    Typ=1 Len=1: 122     Typ=1 Len=2: 67,122
       123 {    Typ=1 Len=1: 123     Typ=1 Len=2: 67,123
       124 |    Typ=1 Len=1: 124     Typ=1 Len=2: 67,124
       125 }    Typ=1 Len=1: 125     Typ=1 Len=2: 67,125
       126 ~    Typ=1 Len=1: 126     Typ=1 Len=2: 67,126
       127     Typ=1 Len=1: 127     Typ=1 Len=2: 67,127
       128 ?    Typ=1 Len=1: 128     Typ=1 Len=1: 67
       129 ?    Typ=1 Len=1: 129     Typ=1 Len=1: 67
       130 ?    Typ=1 Len=1: 130     Typ=1 Len=1: 67
       131 ?    Typ=1 Len=1: 131     Typ=1 Len=1: 67
       132 ?    Typ=1 Len=1: 132     Typ=1 Len=1: 67
       133 ?    Typ=1 Len=1: 133     Typ=1 Len=1: 67
       134 ?    Typ=1 Len=1: 134     Typ=1 Len=1: 67
       135 ?    Typ=1 Len=1: 135     Typ=1 Len=1: 67
       136 ?    Typ=1 Len=1: 136     Typ=1 Len=1: 67
       137 ?    Typ=1 Len=1: 137     Typ=1 Len=1: 67
       138 ?    Typ=1 Len=1: 138     Typ=1 Len=1: 67
       139 ?    Typ=1 Len=1: 139     Typ=1 Len=1: 67
       140 ?    Typ=1 Len=1: 140     Typ=1 Len=1: 67
       141 ?    Typ=1 Len=1: 141     Typ=1 Len=1: 67
       142 ?    Typ=1 Len=1: 142     Typ=1 Len=1: 67
       143 ?    Typ=1 Len=1: 143     Typ=1 Len=1: 67
       144 ?    Typ=1 Len=1: 144     Typ=1 Len=1: 67
       145 ?    Typ=1 Len=1: 145     Typ=1 Len=1: 67
       146 ?    Typ=1 Len=1: 146     Typ=1 Len=1: 67
       147 ?    Typ=1 Len=1: 147     Typ=1 Len=1: 67
       148 ?    Typ=1 Len=1: 148     Typ=1 Len=1: 67
       149 ?    Typ=1 Len=1: 149     Typ=1 Len=1: 67
       150 ?    Typ=1 Len=1: 150     Typ=1 Len=1: 67
       151 ?    Typ=1 Len=1: 151     Typ=1 Len=1: 67
       152 ?    Typ=1 Len=1: 152     Typ=1 Len=1: 67
       153 ?    Typ=1 Len=1: 153     Typ=1 Len=1: 67
       154 ?    Typ=1 Len=1: 154     Typ=1 Len=1: 67
       155 ?    Typ=1 Len=1: 155     Typ=1 Len=1: 67
       156 ?    Typ=1 Len=1: 156     Typ=1 Len=1: 67
       157 ?    Typ=1 Len=1: 157     Typ=1 Len=1: 67
       158 ?    Typ=1 Len=1: 158     Typ=1 Len=1: 67
       159 ?    Typ=1 Len=1: 159     Typ=1 Len=1: 67
       160 ?    Typ=1 Len=1: 160     Typ=1 Len=1: 67
       161 ?    Typ=1 Len=1: 161     Typ=1 Len=1: 67
       162 ?    Typ=1 Len=1: 162     Typ=1 Len=1: 67
       163 ?    Typ=1 Len=1: 163     Typ=1 Len=1: 67
       164 ?    Typ=1 Len=1: 164     Typ=1 Len=1: 67
       165 ?    Typ=1 Len=1: 165     Typ=1 Len=1: 67
       166 ?    Typ=1 Len=1: 166     Typ=1 Len=1: 67
       167 ?    Typ=1 Len=1: 167     Typ=1 Len=1: 67
       168 ?    Typ=1 Len=1: 168     Typ=1 Len=1: 67
       169 ?    Typ=1 Len=1: 169     Typ=1 Len=1: 67
       170 ?    Typ=1 Len=1: 170     Typ=1 Len=1: 67
       171 ?    Typ=1 Len=1: 171     Typ=1 Len=1: 67
       172 ?    Typ=1 Len=1: 172     Typ=1 Len=1: 67
       173 ?    Typ=1 Len=1: 173     Typ=1 Len=1: 67
       174 ?    Typ=1 Len=1: 174     Typ=1 Len=1: 67
       175 ?    Typ=1 Len=1: 175     Typ=1 Len=1: 67
       176 ?    Typ=1 Len=1: 176     Typ=1 Len=1: 67
       177 ?    Typ=1 Len=1: 177     Typ=1 Len=1: 67
       178 ?    Typ=1 Len=1: 178     Typ=1 Len=1: 67
       179 ?    Typ=1 Len=1: 179     Typ=1 Len=1: 67
ERROR:
ORA-29275: partial multibyte character



180 rows selected.

SQL> 

Notice, chr disappears in concatenation as soon as we go over 127.
Anyway, use unistr, not chr if your character set is unicode.


SY.
Re: Cannot insert specific char [message #673908 is a reply to message #673907] Sun, 16 December 2018 08:36 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

Solomon Yakobson wrote on Sun, 16 December 2018 16:19
No, it isn't a bug. CHR(167), same as any CHR > 127 is extended ASCII character and simply doesn't exists in unicode. Read MOS doducment AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1):

"Only US7ASCII (A-Z,a-z,0-9) characters have the same codepoints in AL32UTF8 as in US7ASCII, WE8ISO8859P1, AR8MSWIN1256 etc. meaning that using chr() for any value above 128 should be best avoided".

You can get strange and unexpected results when using extended ascii ranging from all sorts of errors (including 600) to results where same extended ascii character acts differently:

select  level - 1 n,
        chr(level-1) c,
        dump(chr(level-1)) d,
        dump('C' || chr(level-1)) dd
  from  dual
  connect by level <= 256
/

         N C    D                    DD
---------- ---- -------------------- --------------------
         0      Typ=1 Len=1: 0       Typ=1 Len=2: 67,0
         1     Typ=1 Len=1: 1       Typ=1 Len=2: 67,1
         2     Typ=1 Len=1: 2       Typ=1 Len=2: 67,2
         3     Typ=1 Len=1: 3       Typ=1 Len=2: 67,3
         4     Typ=1 Len=1: 4       Typ=1 Len=2: 67,4
         5     Typ=1 Len=1: 5       Typ=1 Len=2: 67,5
         6     Typ=1 Len=1: 6       Typ=1 Len=2: 67,6
         7     Typ=1 Len=1: 7       Typ=1 Len=2: 67,7
         8     Typ=1 Len=1: 8       Typ=1 Len=2: 67,8
         9          Typ=1 Len=1: 9       Typ=1 Len=2: 67,9
  .....
  ......
  ........
  .........


       113 q    Typ=1 Len=1: 113     Typ=1 Len=2: 67,113
       114 r    Typ=1 Len=1: 114     Typ=1 Len=2: 67,114
       115 s    Typ=1 Len=1: 115     Typ=1 Len=2: 67,115
       116 t    Typ=1 Len=1: 116     Typ=1 Len=2: 67,116
       117 u    Typ=1 Len=1: 117     Typ=1 Len=2: 67,117
       118 v    Typ=1 Len=1: 118     Typ=1 Len=2: 67,118
       119 w    Typ=1 Len=1: 119     Typ=1 Len=2: 67,119
       120 x    Typ=1 Len=1: 120     Typ=1 Len=2: 67,120
       121 y    Typ=1 Len=1: 121     Typ=1 Len=2: 67,121
       122 z    Typ=1 Len=1: 122     Typ=1 Len=2: 67,122
       123 {    Typ=1 Len=1: 123     Typ=1 Len=2: 67,123
       124 |    Typ=1 Len=1: 124     Typ=1 Len=2: 67,124
       125 }    Typ=1 Len=1: 125     Typ=1 Len=2: 67,125
       126 ~    Typ=1 Len=1: 126     Typ=1 Len=2: 67,126
       127     Typ=1 Len=1: 127     Typ=1 Len=2: 67,127
       128 ?    Typ=1 Len=1: 128     Typ=1 Len=1: 67
       129 ?    Typ=1 Len=1: 129     Typ=1 Len=1: 67
       130 ?    Typ=1 Len=1: 130     Typ=1 Len=1: 67
       131 ?    Typ=1 Len=1: 131     Typ=1 Len=1: 67
       132 ?    Typ=1 Len=1: 132     Typ=1 Len=1: 67
       133 ?    Typ=1 Len=1: 133     Typ=1 Len=1: 67
       134 ?    Typ=1 Len=1: 134     Typ=1 Len=1: 67
       135 ?    Typ=1 Len=1: 135     Typ=1 Len=1: 67
       136 ?    Typ=1 Len=1: 136     Typ=1 Len=1: 67
       137 ?    Typ=1 Len=1: 137     Typ=1 Len=1: 67
       138 ?    Typ=1 Len=1: 138     Typ=1 Len=1: 67
       139 ?    Typ=1 Len=1: 139     Typ=1 Len=1: 67
       140 ?    Typ=1 Len=1: 140     Typ=1 Len=1: 67
       141 ?    Typ=1 Len=1: 141     Typ=1 Len=1: 67
       142 ?    Typ=1 Len=1: 142     Typ=1 Len=1: 67
       143 ?    Typ=1 Len=1: 143     Typ=1 Len=1: 67
       144 ?    Typ=1 Len=1: 144     Typ=1 Len=1: 67
       145 ?    Typ=1 Len=1: 145     Typ=1 Len=1: 67
       146 ?    Typ=1 Len=1: 146     Typ=1 Len=1: 67
       147 ?    Typ=1 Len=1: 147     Typ=1 Len=1: 67
       148 ?    Typ=1 Len=1: 148     Typ=1 Len=1: 67
       149 ?    Typ=1 Len=1: 149     Typ=1 Len=1: 67
       150 ?    Typ=1 Len=1: 150     Typ=1 Len=1: 67
       151 ?    Typ=1 Len=1: 151     Typ=1 Len=1: 67
       152 ?    Typ=1 Len=1: 152     Typ=1 Len=1: 67
       153 ?    Typ=1 Len=1: 153     Typ=1 Len=1: 67
       154 ?    Typ=1 Len=1: 154     Typ=1 Len=1: 67
       155 ?    Typ=1 Len=1: 155     Typ=1 Len=1: 67
       156 ?    Typ=1 Len=1: 156     Typ=1 Len=1: 67
       157 ?    Typ=1 Len=1: 157     Typ=1 Len=1: 67
       158 ?    Typ=1 Len=1: 158     Typ=1 Len=1: 67
       159 ?    Typ=1 Len=1: 159     Typ=1 Len=1: 67
       160 ?    Typ=1 Len=1: 160     Typ=1 Len=1: 67
       161 ?    Typ=1 Len=1: 161     Typ=1 Len=1: 67
       162 ?    Typ=1 Len=1: 162     Typ=1 Len=1: 67
       163 ?    Typ=1 Len=1: 163     Typ=1 Len=1: 67
       164 ?    Typ=1 Len=1: 164     Typ=1 Len=1: 67
       165 ?    Typ=1 Len=1: 165     Typ=1 Len=1: 67
       166 ?    Typ=1 Len=1: 166     Typ=1 Len=1: 67
       167 ?    Typ=1 Len=1: 167     Typ=1 Len=1: 67
       168 ?    Typ=1 Len=1: 168     Typ=1 Len=1: 67
       169 ?    Typ=1 Len=1: 169     Typ=1 Len=1: 67
       170 ?    Typ=1 Len=1: 170     Typ=1 Len=1: 67
       171 ?    Typ=1 Len=1: 171     Typ=1 Len=1: 67
       172 ?    Typ=1 Len=1: 172     Typ=1 Len=1: 67
       173 ?    Typ=1 Len=1: 173     Typ=1 Len=1: 67
       174 ?    Typ=1 Len=1: 174     Typ=1 Len=1: 67
       175 ?    Typ=1 Len=1: 175     Typ=1 Len=1: 67
       176 ?    Typ=1 Len=1: 176     Typ=1 Len=1: 67
       177 ?    Typ=1 Len=1: 177     Typ=1 Len=1: 67
       178 ?    Typ=1 Len=1: 178     Typ=1 Len=1: 67
       179 ?    Typ=1 Len=1: 179     Typ=1 Len=1: 67
ERROR:
ORA-29275: partial multibyte character



180 rows selected.

SQL> 

Notice, chr disappears in concatenation as soon as we go over 127.
Anyway, use unistr, not chr if your character set is unicode.


SY.
It's character 186 , not 167..
But anywats.. in sources like this https://www.utf8-chartable.de/unicode-utf8-table.pl?utf8=dec&unicodeinhtml=dec it is stated that it's a unicode character..
I'm not sure - how can I distinguish ?

Which database version is this from ?

[Updated on: Sun, 16 December 2018 08:55]

Report message to a moderator

Re: Cannot insert specific char [message #673909 is a reply to message #673908] Sun, 16 December 2018 12:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2948
Registered: January 2010
Location: Connecticut, USA
Senior Member
Andrey_R wrote on Sun, 16 December 2018 09:36

It's character 186 , not 167..
But anywats.. in sources like this https://www.utf8-chartable.de/unicode-utf8-table.pl?utf8=dec&unicodeinhtml=dec it is stated that it's a unicode character..
I'm not sure - how can I distinguish ?

Which database version is this from ?

My db version is:

SQL> select version from v$instance;

VERSION
-----------------
12.2.0.1.0

SQL> select  value
  2    from  nls_database_parameters
  3    where parameter = 'NLS_CHARACTERSET'
  4  /

VALUE
--------------------------------------------
AL32UTF8

SQL> 

But you'll get same results on other versions. And where in that link you see CHR(186)? All I see is :

--------------------------------------------------------------
| Unicode |           |  UTF-8  | numerical HTML |           |
|   code  | character |  (dec.) |    encoding    |   name    |
|   point |           |         | of the Unicode |           |
|         |           |         |    character   |           |
|---------|-----------|---------|----------------|-----------|
|  U+00BA |     º     | 194 186	| º       |   º  | MASCULINE |
|         |           |         |         |      | ORDINAL   |
|         |           |         |         |      | INDICATOR |
--------------------------------------------------------------


SY.
Re: Cannot insert specific char [message #673910 is a reply to message #673909] Mon, 17 December 2018 02:56 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

It just doesn't make sense that I can store it using a program interface external to Oracle that uses a non-unicode font , and it is stored as 186 ASCII,
as dump function showing

Typ=1 Len=1: 186

Or concatinated to a 'C' :

Typ=1 Len=2: 67,186

When I try one of these:
SQL> select dump(chr_fn),dump(chr_using),dump(nchr_fn) from (
  2  SELECT 'C'||CHR(186) AS CHR_FN,
  3  'C'||CHR(186 USING NCHAR_CS) AS CHR_USING,
  4  'C'||NCHR(186) AS NCHR_FN
  5  FROM dual);

DUMP(CHR_FN)
------------------------------------------------------------
DUMP(CHR_USING)
--------------------------------------------------------------------------------
DUMP(NCHR_FN)
--------------------------------------------------------------------------------
Typ=1 Len=1: 67
Typ=1 Len=4: 0,67,0,186
Typ=1 Len=4: 0,67,0,186



I am always getting this leading zero which means it is unicode ( ? ) and my application doesn't display it properly...
When I insert it from the application - I can see using dump function ( as you can see above in 1st example of this reply ) that it doesn't have this leading zero...

Please help... what am I misunderstanding here? how can I input this ASCII symbol explicitly by inputting it's ASCII identifier ?
The purpose is to embed it in a script so it needs to work in different client environments..

TIA
Re: Cannot insert specific char [message #673912 is a reply to message #673910] Mon, 17 December 2018 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're trying to make oracle do something it isn't designed to do (work with characters in the wrong charset).
Given that, as Solomon said before:

Solomon Yakobson wrote on Sun, 16 December 2018 14:19


You can get strange and unexpected results when using extended ascii ranging from all sorts of errors (including 600) to results where same extended ascii character acts differently:

That says you shouldn't be trying to look for logic here.
Either change your charset or stop trying to use those characters.
Re: Cannot insert specific char [message #673914 is a reply to message #673912] Mon, 17 December 2018 03:30 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Mon, 17 December 2018 11:19
You're trying to make oracle do something it isn't designed to do (work with characters in the wrong charset).
Given that, as Solomon said before:

Solomon Yakobson wrote on Sun, 16 December 2018 14:19


You can get strange and unexpected results when using extended ascii ranging from all sorts of errors (including 600) to results where same extended ascii character acts differently:

That says you shouldn't be trying to look for logic here.
Either change your charset or stop trying to use those characters.

I understand what you are saying. But developers tell me that "my application can input these characters. Why can't you ? "
And I do feel like they have a point. If I know what result I want to get, there must be a way to have it inputted, moreover when the application managed to input the desired value correctly to the same place I fail to....
Re: Cannot insert specific char [message #673915 is a reply to message #673914] Mon, 17 December 2018 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I imagine it works for the same reason that you can use OCI to insert invalid dates in a date datatype column - it bypasses a layer of checks.

But you're using a rather limited definition of "works" here - if various functions don't work properly on the data once it's in the DB then it's not really working is it?

The answer remains what we've said above - oracle have said you'll get weird results if you try this.
Re: Cannot insert specific char [message #673916 is a reply to message #673915] Mon, 17 December 2018 04:06 Go to previous message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Mon, 17 December 2018 11:46
I imagine it works for the same reason that you can use OCI to insert invalid dates in a date datatype column - it bypasses a layer of checks.

But you're using a rather limited definition of "works" here - if various functions don't work properly on the data once it's in the DB then it's not really working is it?

The answer remains what we've said above - oracle have said you'll get weird results if you try this.
I see.

Many thanks for your advise and attention cookiemonster, Michel Cadot , Solomon Yakobson.

[Updated on: Mon, 17 December 2018 04:06]

Report message to a moderator

Previous Topic: Trigger Error ORA-00604
Next Topic: APEX (merged 3 duplicate posts)
Goto Forum:
  


Current Time: Fri May 29 09:58:13 CDT 2020