French Canadian special character not showing on query

[Ghislain]

Hi,

I use Pl/SQL Developer v13.0.6.1911

My NLS_LANG in the KEY_OraClient11g_home1 from the ORACLE regedit is : CANADIAN FRENCH_CANADA.WE8ISO8859P1

V$NLS_PARAMETERS:
PARAMETER VALUE
NLS_LANGUAGE CANADIAN FRENCH
NLS_TERRITORY CANADA
NLS_CURRENCY $
NLS_ISO_CURRENCY CANADA
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR-MM-DD
NLS_DATE_LANGUAGE CANADIAN FRENCH
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT CANADIAN FRENCH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

NLS_DATABASE_PARAMETERS:
PARAMETER VALUE
NLS_LANGUAGE CANADIAN FRENCH
NLS_TERRITORY CANADA
NLS_CURRENCY $
NLS_ISO_CURRENCY CANADA
NLS_NUMERIC_CHARACTERS ,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR-MM-DD
NLS_DATE_LANGUAGE CANADIAN FRENCH
NLS_SORT CANADIAN FRENCH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.3.0

In the DB we have varchar2 containing words like 'préposé' and 'Stéphane'.
My issue is that when we query the data it dosen't show the accent like 'prepose' and 'Stephane'.
And also if we try to where the column and use the accent it do not return any data, also if we don't use the accent we get no data.

if we do some select:
select 'éçà' from dual; we get : '??????'
select 'éHçHà' from dual; we get : '??H??H??'

If i look at the data in sql plus i have the accent and the query with accent are working, if i make a report in Apex and show the column i have the accent.

Anyone know why i have this problem with PL/SQL Developer ?
Also my coleague don't have this issue and the accent are showing on there PL/SQL Developer.

 

[Marco Kalter]

Maybe this is an environment problem. Can you start PL/SQL Developer, connect to this database, go to Help > Support Info and send me the text from the "Info" tab page?

 

[Ghislain]

PL/SQL Developer
Version 13.0.6.1911 (64 bit)
Windows 10 Build 17134

Physical memory : 8 257 760 kB (4 966 312 available)
Paging file : 9 568 480 kB (4 697 076 available)
Virtual memory : 137 438 953 344 kB (137 434 029 488 available)

Parameters
C:\Program Files\PLSQL Developer 13\plsqldev.exe

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False
Allow Multiple Connections: True

Plug-Ins
*Active Query Builder (C:\Program Files\PLSQL Developer 13\PlugIns\ActiveQueryBuilder.dll)
*PL/SQL Documentation (plsqldoc) (C:\Program Files\PLSQL Developer 13\PlugIns\plsqldoc.dll)
(* is Active)

Aliases
12CDFSCT
12CLUDF
12CLUDP
12CLUDT
12CPERT
...

Homes
OraClient11g_home1 (C:\Oracle\11.2.0.2.1\client_x64)

DLLs
C:\Oracle\11.2.0.2.1\client_x64\oci.dll

Using
Home: OraClient11g_home1
DLL: C:\Oracle\11.2.0.2.1\client_x64\oci.dll
OCI: version 11.1 (11.2.0.1.0)
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0

Character Sets
Character size: 1 byte(s)
CharSetID: 178
NCharSetID: 2000
Unicode Support: True
NLS_LANG: CANADIAN FRENCH_CANADA.WE8ISO8859P1
NLS_NCHAR_CHARACTERSET: AL16UTF16
NLS_CHARACTERSET: WE8MSWIN1252

Process
Working Set = 101 916 672
Memory = 32 401 316
GDI Objects = 2269
User Objects = 870
Handles = 879

Monitors
PixelsPerInch = 96

Id = 0
PPI = 96
Primary = False
Handle = 35068019
Left = 1920
Top = 0
Width = 1920
Height = 1080

Id = 1
PPI = 96
Primary = True
Handle = 113317133
Left = 0
Top = 0
Width = 1920
Height = 1080

MainFormOnTaskbar = True

 

[Marco Kalter]

I see that your client character set is WE8ISO8859P1, and your database character set is WE8MSWIN1252. To prevent any conversion issues, can you edit the Params.ini file in the PL/SQL Developer installation directory and add the following line?

NLS_LANG = CANADIAN FRENCH_CANADA.WE8MSWIN1252

Restart PL/SQL Developer and check if this affects the issue.

 

[Ghislain]

After a PC restart this is still not working.

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 13\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 13 directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer 13).

 

[Ghislain]

I don't see the option to send an attach file am I missing it or do you want me to paste the content of the debug.txt file in a reply ?

 

[Marco Kalter]

Can you send it by e-mail to support@allroundautomations.com?

 

[Ghislain]

The Email is sent. Thanks a lot