Query result for a varchar2 field doesn't show the entire string value

[gad]

The field's length is set to varchar2(30 char), but the query result is only 17 to 18 characters in length.
I thought it might've had something to do with the character set:
the database
NLS_CHARACTERSET is IW8ISO8859P8
NLS_LANGUAGE is AMERICAN
NLS_TERITORY is AMERICA
(The field itself contains hebrew characters)

On the client side:
NLS_LANG in the registry is set to AMERICAN_AMERICA.IW8ISO8859P8

TIA

 

[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?

 

[gad]

PL/SQL Developer
Version 14.0.2.1969 (32 bit)
01.137135 - 1 user license
Service Contract:
Windows 7 (64-bit) Build 7601 (Service Pack 1)

Physical memory : 25,049,648 kB (16,594,552 available)
Paging file : 50,097,396 kB (40,966,984 available)
Virtual memory : 2,097,024 kB (1,744,420 available)

Parameters
C:\Program Files (x86)\PLSQL Developer 14\plsqldev.exe

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

Preference Files
C:\Users\amos_b\AppData\Roaming\PLSQL Developer 14\Preferences\default\Default.ini
C:\Users\amos_b\AppData\Roaming\PLSQL Developer 14\Preferences\amos_b\default.ini
C:\Users\amos_b\AppData\Roaming\PLSQL Developer 14\Preferences\amos_b\Default Francesco.ini

License File
C:\ProgramData\PLSQL Developer\aalf.dat

Debug file
C:\Users\amos_b\AppData\Roaming\PLSQL Developer 14\PlSqlDev.elf

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

Aliases
GADIRDEV
GADIRPRD
ZGADIRPRD

Homes
OraClient11g_home1 (D:\oracle\product\11.2.0\client_1)
OraClient11g_home2 (D:\app\amos_b\product\11.2.0\client_1)
OraClient11g_home3 (D:\app\amos_b\product\11.2.0\client_2)

DLLs
D:\oracle\product\11.2.0\client_1\bin\oci.dll

TNS File
D:\oracle\product\11.2.0\client_1\Network\Admin\tnsnames.ora

Using
Home: OraClient11g_home1
DLL: D:\oracle\product\11.2.0\client_1\bin\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: 873
NCharSetID: 2000
Unicode Support: True
NLS_LANG: AMERICAN_AMERICA.AL32UTF8
NLS_NCHAR_CHARACTERSET: AL16UTF16
NLS_CHARACTERSET: IW8ISO8859P8

Process
Working Set = 131,727,360
Memory = 26,945,992
GDI Objects = 1382
User Objects = 372
Handles = 348

Monitors
PixelsPerInch = 96

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

Id = 1
PPI = 96
Primary = False
Handle = 65539
Left = -1920
Top = 0
Width = 1920
Height = 1080

MainFormOnTaskbar = True

 

[Marco Kalter]

I see that this is an AL32UTF8 database, and that your client character set is IW8ISO8859P8. Is that correct?

Can you check if it helps if you change the client character set to AL32UTF8 as well?

 

[gad]

I've tried changing the registry NLS_LANG value
to "AMERICAN_AMERICA.AL32UTF8" or to "HEBREW_ISRAEL.AL32UTF8"
but the query result still shows up to 18 characters from the field.
furthermore, the Support Info output (the Character Set section) remains the same no matter what I put in the registry, even after a reboot:

Character Sets
Character size: 1 byte(s)
CharSetID: 873
NCharSetID: 2000
Unicode Support: True
NLS_LANG: AMERICAN_AMERICA.AL32UTF8
NLS_NCHAR_CHARACTERSET: AL16UTF16
NLS_CHARACTERSET: IW8ISO8859P8

 

[gad]

Actually, it turns out that I've been checking the wrong registry keys
because it's a 32bit client on a 64bit machine
in which case the keys are found under "Wow6432Node",
So changing NLS_LANG from "AMERICAN_AMERICA.AL32UTF8" to "AMERICAN_AMERICA.IW8ISO8859P8" was the solution.
Thank you

 

[gad]

However, a similar problem is occurring on an XP machine:
When NLS_LANG is set to "AMERICAN_AMERICA.AL32UTF8" - the query result shows up to 18 characters just like befure,
but when applying the solution (changing to "AMERICAN_AMERICA.IW8ISO8859P8") - the result seems to show the entire string, but in gibberish