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  |
Andrey_R
Messages: 441
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  |
 |
Michel Cadot
Messages: 68641
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
[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 |
Andrey_R
Messages: 441
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 |
Solomon Yakobson
Messages: 3273
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Solomon Yakobson wrote on Sun, 16 December 2018 16:19No, 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 |
Solomon Yakobson
Messages: 3273
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 |
Andrey_R
Messages: 441
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
Or concatinated to a 'C' :
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 |
cookiemonster
Messages: 13920
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
cookiemonster wrote on Mon, 17 December 2018 11:19You'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 |
cookiemonster
Messages: 13920
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
cookiemonster wrote on Mon, 17 December 2018 11:46I 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 |
|
|
|
Goto Forum:
Current Time: Thu Apr 18 02:55:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
