1

I have an Oracle database. I have to execute some insert scripts that fill a nvarchar2 column. The insert statements include some special characters like “(left quote), ”(right quote), ™(trademark sign). 

create table myTable ( column1 number, column2 nvarchar2(50) );
insert into myTable (column1, column2) values(1, 'Lorem Ipsum™ Sed “dolor sit amet”');

I run the query on SQLDeveloper Editor. But special characters replace with question marks as below:

select * from myTable;

Column1     Column2
------      ------------------------------
1           Lorem Ipsum? Sed ?dolor sit amet?

But when I add a new line to table with SQLDeveloper GUI it's just fine like this image

Database NLS parameters is here:

SELECT * FROM NLS_DATABASE_PARAMETERS where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER               VALUE
------------            ------------
NLS_CHARACTERSET        WE8ISO8859P9
NLS_NCHAR_CHARACTERSET  AL16UTF16

What should I do for inserting the characters I mentioned above properly?

yesref
  • 56
  • 1
  • 6
  • What is the environment variable NLS_LANG set to, on the OS where you are running the first insert? – sandman May 29 '17 at 10:26
  • @sandman Both insert query executed on same OS (Windows 10) and same machine. I couldn't see any environment variable named NLS_LANG – yesref May 29 '17 at 11:33
  • try setting NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P9 – sandman May 29 '17 at 12:16
  • Setting NLS_LANG variable didn't solve the problem. Still question marks exists – yesref May 29 '17 at 14:23
  • Haven't you tried `insert into myTable (column1, column2) values(1, N'Lorem Ipsum™ Sed “dolor sit amet”');` ? – krokodilko May 29 '17 at 19:57
  • Yes, I tried. It didn't solve. – yesref May 30 '17 at 05:23
  • Did you try new inserts after setting NLS_LANG? The old characters would not have persisted correctly – sandman May 30 '17 at 07:54
  • @sandman Yes, I tried. On my machine it didn't work. But something interesting happened. Oracle database server OS is CentOS. I set NLS_LANG on server to AMERICAN_AMERICA.WE8ISO8859P9. And when insert new line from SQL Plus on the server it inserted trademark character accurately. But when I select this line from SQL Developer on Windows it display question marks instead of trademark. – yesref May 30 '17 at 08:25
  • yes it's quite annoying... sometimes you have apps that work fine with the character set, as it's programmed to read NLS_LANG, or uses an API that is aware. Other apps you have to go into a menu and set the encoding. SQL*plus in windows and your OS should be fine, but custom windows apps like plsqldeveloper are programmed differently – sandman May 30 '17 at 09:16

1 Answers1

0

Use UNISTR for Unicode characters that may not work in different environments:

create table myTable ( column1 number, column2 nvarchar2(50) );

insert into myTable (column1, column2)
values(1, 'Lorem Ipsum' || unistr('\2122') || ' Sed ' ||
    unistr('\201C') || 'dolor sit amet' || unistr('\201D'));
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Thanks, it works for one query. But I don't run just one insert script. Thousands of insert queries are generated automatically by a third party application. Besides I have a Java application which some insert queries come from. So If I'm not wrong, we can solve the problem with encoding – yesref May 30 '17 at 08:12