Unicode characters not displayed correctly

[Simon Martinelli]

If I open one of our package with Unicode characters in PL/SQL Developer it looks like this.

actions(actions.last) := new ob_action('🙂'||chr(4036988338),'','','CANCEL');

In DBeaver it's displayed correctly

actions(actions.last) := new ob_action(''||chr(4036988338),'','','CANCEL');

How can I configure PL/SQL Developer to display Unicode?

 

[Marco Kalter]

If you want to use Unicode characters in your SQL and PL/SQL code, you need to set the client character set to UTF8. For example:

NLS_LANG=german_switzerland.utf8

If the character set of your NLS_LANG is we8iso8859p1, then all SQL and PL/SQL code will be converted to we8iso8859p1 by the Oracle Client when it is passed to the server.

 

[Simon Martinelli]

That's what I thought as well but this setting produces the same result.

 

[Marco Kalter]

Can you connect to this database, go to Help > Support Info, and send me the text from the "Info" tab page?

 

[Lei]

Hi,

I am using PL/SQL developer 14.0.6.1988 (64bit) 01.264452 to query data from Oracle database 19c.
The Oracle database uses character set AL32UTF8 and National character set AL16UTF16.
I have a table defined as
create table T001 (
tid int,
var1 varchar2(100),
nvar1 nvarchar2(100)
);
Then i insert the same Unicode characters into both var1 and nvar1 columns.
Then i query the table from both Oracle SQL developer and Allround Automations PL/SQL developer.
Query result screenshot is attached.
We can see Oracle SQL developer can display all characters in all columns correctly.
PL/SQL developer can display all characters (seems so) in NVARCHAR2 columns correctly.
PL/SQL developer can display characters with Unicode U+FFFF in VARCHAR2 columns correctly.
I think this issue is NOT caused by setting of NLS_LANG as characters with Unicode

 

[Marco Kalter]

Can you send me the SQL to create the table, populate it, and select the data?

At the top left of the forum thread list there is a "New thread" option.
From the context of a thread there is a "New thread" option from the "Thread Options" list.

 

[Lei]

Hi Marco Kalter,

Here is the SQL.

create table T001 (
tid int,
var1 varchar2(100),
nvar1 nvarchar2(100)
);

insert into t001 values (1, utl_raw.cast_to_varchar2(hextoraw('6163C3A3C3A7E69C83E8ADB03132')), utl_raw.cast_to_nvarchar2(hextoraw('0061006300E300E767038B7000310032')));
insert into t001 values (2, utl_raw.cast_to_varchar2(hextoraw('F0AA9CB1')), utl_raw.cast_to_nvarchar2(hextoraw('D869DF31')));
insert into t001 values (3, utl_raw.cast_to_varchar2(hextoraw('F0AABD87')), utl_raw.cast_to_nvarchar2(hextoraw('D86BDF47')));
insert into t001 values (4, utl_raw.cast_to_varchar2(hextoraw('E39B87F3A08481E8A186F3A08488')), utl_raw.cast_to_nvarchar2(hextoraw('36C7DB40DD018846DB40DD08')));
insert into t001 values (5, utl_raw.cast_to_varchar2(hextoraw('E39B87F3A08482E8A186F3A08489')), utl_raw.cast_to_nvarchar2(hextoraw('36C7DB40DD028846DB40DD09')));
commit;

select tid, var1 ,nvar1, utl_raw.cast_to_raw(var1) utf8,utl_raw.cast_to_raw(nvar1) utf16 from t001;