Devart Forums

My Environment:
- Oracle 11.2.0.1.0 (NLS_CHARACTERSET = WE8ISO8859P15, NLS_LANGUAGE=ITALIAN)
- Delphi XE7
- ODAC 9.4.14

I have a TOraQuery with this SQL:
UPDATE WKF_TBL_DATA
SET VCHAR1 = :pVCHAR1
WHERE COD_INTERNO = 'xxxx'

I'm trying to write 'Test 1 €' in a VARCHAR2 field with this statement:
OraQuery1.ParamByName( 'pVCHAR1' ).AsString := 'Test 1 €';
OraQuery1.ExecSQL;

The value stored in database, instead, is 'Test 1 ¤'.
How can I solve this problem?

To save the euro symbol in a field of the VARCHAR2 type, passed as a query parameter, you can proceed as follows

(The encoding used in the client environment (ISO 8859-1) and the server (NLS_CHARACTERSET = WE8ISO8859P1) must match):

Without using Unicode:

- specify the required encoding for the OraSession.Options.Charset connection property

Using Unicode:

- set UseUniсode connection property : OraSession.Options.UseUniсode: = True

P.S. In case if, using our recommendations, you will not get the correct result, please provide us the used national encodings on the server and the client

On the client:
NLS_LANG = ITALIAN_ITALY.WE8ISO8859P15

On the server:
SELECT * FROM V$NLS_PARAMETERS

NLS_LANGUAGE = ITALIAN
NLS_TERRITORY= ITALY
NLS_CURRENCY= ¤
NLS_ISO_CURRENCY= ITALY
NLS_NUMERIC_CHARACTERS= ,.
NLS_CALENDAR= GREGORIAN
NLS_DATE_FORMAT= DD-MON-RR
NLS_DATE_LANGUAGE= ITALIAN
NLS_CHARACTERSET= WE8ISO8859P15
NLS_SORT= WEST_EUROPEAN
NLS_TIME_FORMAT= HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT= DD-MON-RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT= HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT= DD-MON-RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY= ¤
NLS_NCHAR_CHARACTERSET= AL16UTF16
NLS_COMP= BINARY
NLS_LENGTH_SEMANTICS= BYTE
NLS_NCHAR_CONV_EXCP= FALSE

A strange behavior is that same program compiled with Delphi 6 work correcly.

Any news?

Please specify whether you tried one of the methods proposed by us. When specifying OraSession.Options.Charset := 'WE8ISO8859P15', the error in your environment is reproduced both in OCI Mode and in Direct Mode?

When I specify OraSession.Options.Charset := 'WE8ISO8859P15', the error occours only in OCI Mode. In Direct Mode is ok.

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

We investigated the problem and came to the following conclusions. The described behavior is determined by mismatch of Oracle and Windows system locale charsets:
Oracle server has the WE8ISO8859P15 charset, and if the Italian (Italy) locale is set on Windows, the charset will be ISO8859P1.
The euro symbol in WE8ISO8859P15 has the code 164, and in the ISO8859P1 charset the euro symbol has the code 128.

When sending the euro symbol value in the UTF-16 charset to the server, Oracle recodes into the WE8ISO8859P15 charset and the euro symbol receives the code 164,
and when the data is refetched, Windows cannot display it correctly, because in the WE8ISO8859P1 charset the symbol with the code 164 is not the euro symbol at all.

You can solve the problem in three ways:

1. Set the WE8ISO8859P1 charset on the server
2. Assign a parameter as OraQuery.ParamByName ('...'). AsAnsiString - that will avoid transcoding UTF16 -> WE8ISO8859P15
3. Correct in ODAC source code (the DBAcccess.pas module) the following functions:
procedure TDAParam.SetAsString(const V ... DIF} end;

I tried solution 3 (by modifiing DBAccess.pas).
It work correctly if I use AsString.
However It doesnt' work if I use Value.
Example:
OraQuery1.ParamByName( 'pVCHAR1' ).Value := 'Test 1 €';

Can you help me?
Thanks.

Up!

Thank you for the information. We will investigate this behavior. We will inform you about the results shortly.

Do you have any news?
Thanks.

We continue investigating the described issue and hope to get fast results. We will let you know at once.

Any news?
Thanks.