I would very much appreciate some help with the following
To handle an interface with a legacy system I need to create strings containing both printable and non-printabel ascii characters. And with non printable characters I mean in particular those in the range of ASCII 128 to 159.
It seems it is not possible to insert a string containting both printable and not printable characters from the afore mentioned range into a VARCHAR2 table column as the following demonstrates:
insert into test values(chr(156)); -- this inserts the 'œ' symbol.
SQL> select test, ascii(test), length(test), substr(test,1,1), ascii(substr(test,1,1))from test;
TEST ASCII(TEST) LENGTH(TEST) SUBSTR(TEST,1,1) ASCII(SUBSTR(TEST,1,1))
┐ 156 1
That the the character mapped is shown as '┐' and not 'œ' is not really issue for my application, what is important is that the ASCII value is shown as 156, which is the ASCII code of the character I inserted.
What is however strange (actually probably not strange but has to do with the lack of understanding of the issue at hand) is that substr returns an empty string...
Now I try to insert a concatenated string, first the "non printable" character then a printable character
insert into test values(chr(156)||chr(65));
SQL> select test, ascii(test), length(test), substr(test,1,1), ascii(substr(test,1,1))from test;
TEST ASCII(TEST) LENGTH(TEST) SUBSTR(TEST,1,1) ASCII(SUBSTR(TEST,1,1))
A 65 1 A 65
For some reason the not printable character (chr(156)) is now not inserted or at least does not appear when I selected the data from the table, this effect seems to apply to all characters in the range of ASCII 128 to 159 (tried some but not all) However for instance CHR(13) can be inserted as part of a string as shown above .
For our application I really don't care much what character is shown or not show, what is important is that I can retrieve the ASCII value and that this value matches the one I inserted which for some reason does not seem to work.
This seems to be, at least to some extent a character set issue. I have also tested this on a database with character sets set as follows
NLS_CHARACTERSET
WE8MSWIN1252
NLS_NCHAR_CHARACTERSET
AL16UTF16
With WE8MSWIN1252 the described issue does NOT occur, however unfortunately I must use NLS_CHARACTERSET AL32UTF8 which produces the results as described above!
As said any insights would be much appreciated as I am slowly but surely starting to despair.
For completions sake, character sets are set as follows (changing it is NOT an option):
NLS_CHARACTERSET
AL32UTF8
NLS_NCHAR_CHARACTERSET
AL16UTF16
The test table is created as follows
CREATE TABLE TEST
(
TEST VARCHAR2(1000 BYTE)
)
Database Version 11.2.0.3.0
Edited by: helios.taraba on Dec 2, 2012 10:18 AM --Added database version
Edited by: helios.taraba on Dec 2, 2012 10:24 AM Added description of test results using NLS_CHARACTERSET WE8MSWIN1252