I am using oracle 11g and trying to insert a string containing special UTF8 characters eg '(ε- c'. The NLS character sets for the databse are...
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8ISO8859P1
when I copy and paste the above string into a NVARCHAR field it works fine.
if I execute the below I get an upside down question mark in the field
insert into title_debug values ('(ε- c');
where title debug table consists of a single NVARCHAR2(100) field called title.
I have attempted to assign this string to a NVARCHAR2(100) variable then iserting this. And also attempted all the different CAST / CONVERT ect functions I can find and nothing is working.
Any assistance would be greatly appreciated.
UPDATE
I have executed
select dump(title, 1016), dump(title1, 1016)
into v_title, v_title1
from dual
where title is the string passed in as a varchar and title1 is the string passed in as a NVarchar.
Unsuprisingly the encodings come through as WE8ISO8859P1 and AL16UTF16. but on both the ε comes through as hex 'BF'. This is the upside down Question mark.
My only thought left is to try and pass this through as a raw and then do something with it. However I have not yet been able to figure out how to convert the string into a acceptable format with XQuery (OSB).
Continued thanks for assistance.
Our DBA found the solution to this issue. The answer lay in a setting on the dbc connection on the bus to tell it to convert utf8 to NChar.
On The connection pool page add the following lines to the Properties box.
oracle.jdbc.convertNcharLiterals=true oracle.jdbc.defaultNchar=true
this will allow you to be able to insert into NVarchar2 fields while maintaining the utf8 characters.
Cheers