TD 5.2 SP1 and ORACLE UTF8

[GB]

Hi,
I' ve created a new oracle instance with utf8.
Then:

Code: Select all

create table test (ssource nvarchar2(10), svalue nvarchar2(30));

on oracle Client:
NLS_LANG = AMERICAN_AMERICA.UTF8

The system enviroment is:
- Teamdeveloper/Sqltalk versions 5.2 till 5.2 SP1 Build 17499
- Windows 7 (german version and settings)
- Oracle client 11.1.0.6.0
- Oracle server 10.2.0.3.0

Please note: The result will be the same when working with an td application. so it not any special feature of sqltalk.

Insert data with critical characters:

Code: Select all

insert into test values ('unify', 'öäüÖÄÜ߀');
insert into test values ('unify', 'ČčĎďĚěŇňŘřŠšŤťŮůŽž');
insert into test values ('unify', '不忍  有缺陷的 废旧');

Select the inserted data:

Code: Select all

SSOURCE    SVALUE
========== ==============================
unify      öäüÖÄÜ߀
unify      CcDdEeNnRrŠšTtUuŽž
unify      ¿¿  ¿¿¿¿ ¿¿

SqlTalk.jpg

If I insert the same data using oracle sql developer then the select in SqlTalk shows the expected result:

SqlTalkInsertedByOracle.jpg

Summary: It seems to me that the database and the client is properly configured because unicode data can be stored and selected to/from database.
Inserting data from SqlTalk (and td application) kills most of the special characters.

Question: What is needed to activate the unicode feature in sqltalk or teamdeveloper? What information is needed to analyze the problem?


regards

GB

[mkonir]

https://www.oracle.com/technology/tech/g ... %20faq.htm

You can use UTF8 as Oracle client character set (=NLS_LANG) on Windows NT, 2000 and XP but you will be limited to use only client programs that explicitly support this configuration. This is because the user interface of Win32 is not UTF8, therefore the client program have to perform explicit conversions between UTF8 (used on Oracle side) and UTF16 (used on Win32 side).

Looks like TD / SqlTalk does not support that. Check the FAQ to find out what you should set NLS_LANG on your client to.

[GB]

Thank you for reply.
since td supports unicode and has rewritten all native database routers it should work with UTF8 but it does not.
I added the repro case as a td application including the sql script.

OracleUtf8.zip

regards
GB

[GB]

...additional hint: you have to set the font of the outline to a font which is able to show all needed characters. This is e.g. in my case Arial.

[GB]

Any comments?
Do I something wrong?
Should it work as I expected?
Is it a bug?

[GB]

Is there anybody using ORACLE and unicode?

[Didiman]

Hi,
we're using ORACLE with Unicode. We also had some problems, esp. with the european Symbol für the EURO (€). At least we put the codepage of the Database to win1252 and now everything is working for us.

--Andreas

[Jean-Marc Gemperle]

Hi GB,

Sorry for the delay on this....

I checked you issue with your testcase both with SQLTalk and the TD APP without problem with TD 5.2 last EMP5265...

When you say

I' ve created a new oracle instance with utf8.

I believe you mean your instance is of NLS_CHARACTERSET : AL32UTF8 is that correct or it is WIN1252 or OTHER non UNICODE charset ? I ask because you us NVARCHAR, and in the case of an AL32UTF8 ORACLE instance you could use also simply varchar2 as everything is unicode.

Please let me knwo about that, because indeed if I have a WIN1252 charset ORACLE DB with NVARCHAR beeing UTF then I do have a problem... I also do have the problem with SqlDeveloper to insert/fetch your data if the instance is WIN1252 and using NVARCHAR; I don't understand why as my NLS_LANG charset is UTF8 and SqlDeveloper is UNICODE.... so IMO it should work..but does not...need to check further...


The first snapshot it is when it fails using WIN1252 and NVARCHAR
The second snapshot it is when I use a full unicode instance also bellow are the information of my instance.

JM

--------------------------------FULL UNICODE

SQL> SELECT * from NLS_DATABASE_PARAMETERS;

PARAMETER
---------------------------------------------------
VALUE
---------------------------------------------------
NLS_LANGUAGE
ENGLISH

NLS_TERRITORY
UNITED KINGDOM

NLS_CURRENCY
#


PARAMETER
---------------------------------------------------
VALUE
---------------------------------------------------
NLS_ISO_CURRENCY
UNITED KINGDOM

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
AL32UTF8


PARAMETER
---------------------------------------------------
VALUE
---------------------------------------------------
NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
ENGLISH


PARAMETER
---------------------------------------------------
VALUE
---------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH24.MI.SSXFF

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH24.MI.SSXFF


PARAMETER
---------------------------------------------------
VALUE
---------------------------------------------------
NLS_TIME_TZ_FORMAT
HH24.MI.SSXFF TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH24.MI.SSXFF TZR

NLS_DUAL_CURRENCY
?


PARAMETER
---------------------------------------------------
VALUE
---------------------------------------------------
NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE


PARAMETER
---------------------------------------------------
VALUE
---------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_RDBMS_VERSION
11.2.0.1.0


20 rows selected.

SQL>

----------------------WIN1258 AND NVARCHAR = AL16UTF16

SQL> SELECT * from NLS_DATABASE_PARAMETERS;

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$


PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
WE8MSWIN1252


PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
AMERICAN


PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM


PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
$


PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE


PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_RDBMS_VERSION
11.2.0.1.0


20 rows selected.

SQL>

[tMarx]

Hello,
here is the result from NLS_DATABASE_PARAMETERS from our Database:
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 10.2.0.3.0

Is is right, that i have to change NLS_NCHAR_CHARACTERSET to AL16UTF16 ?
In this case i have to make a new oracle instance with this caracter set.

regards
Thomas Marx

[tMarx]

Hello,
it works with a FULL UNICODE INSTANCE : NLS_CHARACTERSET=AL32UTF8 and NLS_NCHAR_CHARACTERSET=AL16UTF16

regards
Thomas Marx