Problem with Character set for special Characters

[DaveE]

Hi,

Firstly, I'm a relative newbie to PL/SQL Developer, but love it so far. We have recently changed over from SQL Developer to PL/SQL Developer.

We're running 11gr2, and I believe my NLS_PARAMETERS are set up correctly, but I'm experiencing the below problem.

In SQL Developer (and Toad actually) the below displays and works correctly:

PKG_DELIMITED.WORD(p_column_list(i), 2, '$','''');

However, in PL/SQL Developer it displays as below, and invalidates the call as the '$' is not recognised and is parsed as a '?':

PKG_DELIMITED.WORD(p_column_list(i), 2, '?','''');

Please note...I've had to replace the special character causing the problem (I believe it to be chr(172)) with a '$' on the 5th line of text above, as it wont let me post the exact character as it breaks the post...the character can be seen by holding shift key and pressing key directly below escape key...

Could you advise how to resolve this, or has anybody else experienced this?

Many Thanks for any help/guidance in advance!

Dave

 

[Hilarion]

You can check if you have "Unicode enabled" option checked in PL/SQL Developer settings (Tools / Preferences / Oracle / Options). The problem may be related to the fact that PL/SQL Developer is unfortunately not a fully Unicode enabled Windows application. You can also check your Windows setting for non-unicode applications (somewhere in regional/lingual settings of Windows).
If nothing helps, post info on your Windows version and it's regional settings and the NLS values as reported by PL/SQL Developer (in Help / Support Info / Info) and by Oracle itself ("SELECT * FROM nls_database_parameters").
PS.: The character you are referring to is probably either tilde (~) or grave accent (`) - they should not pose a problem in most Latin-based alphabets/charsets/encodings.

 

[DaveE]

Hi, thank you for the response.

Unfortunately, I have already tried changing the "Unicode Enabled" setting, and the same problem occurs whether this is enabled or disabled.

It wasn't actually the tilde or the grave accent that was causing the issue, it was the "not" symbol, as used in mathematical equations.

Can be found here under "Math Symbols"..
http://symbolcodes.tlt.psu.edu/accents/codealt.html

Thanks

 

[Hilarion]

You mentioned before, that the problematic character is located on a key just below "Esc" key on your keyboard. Classic EN-US/PL keyboards usually have tilde/accent key there. You did not mention what keyboard layout you have, and I did not notice your location data (UK), so my mistake - sorry. You did state that the character is probably "chr(172)", which indicates something different than tilde/accent, but you weren't sure and 172 character code is code-page/encoding dependant. Again, sorry for assuming incorrectly.

As I said:

post info on your Windows version, it's regional settings and the NLS values as reported by PL/SQL Developer (in Help / Support Info / Info) and by Oracle itself ("SELECT * FROM nls_database_parameters").

 

[DaveE]

Apologies...requested information below.

Windows version: Windows 7 Ultimate 64-bit Service Pack 1

Regional Settings:

Location: United Kingdom
Default input language: English (United Kingdom)
Keyboard: English (United Kingdom)

Short date: dd/MM/yyyy
Long date: dd MMMM yyyy
Short time: HH:mm
Long time: HH:mm:ss
First day of week: Monday
Measurement system: Metric
Standard digits: 0123456789
Use native digits: Never
Currency Symbol: (pound symbol)

From PL/SQL Developer support info:

PL/SQL Developer
Version 10.0.5.1710
01.90723 - 5 user license
Windows 7 (64-bit) 6.1 Build 7601 (Service Pack 1)

Physical memory : 4,194,304 kB (3,430,532 available)
Paging file : 4,194,304 kB (4,194,304 available)
Virtual memory : 2,097,024 kB (1,703,156 available)

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

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

Preference Files
C:\Program Files (x86)\PLSQL Developer\Preferences\Default\Default.ini
C:\Users\david.edwards\AppData\Roaming\PLSQL Developer\Preferences\david.edwards\default.ini

Plug-Ins
*Active Query Builder (C:\Program Files (x86)\PLSQL Developer\PlugIns\ActiveQueryBuilder.dll)
*PL/SQL Documentation (plsqldoc) (C:\Program Files (x86)\PLSQL Developer\PlugIns\plsqldoc.dll)
*Red Gate Deployment Plug-In (C:\Program Files (x86)\PLSQL Developer\PlugIns\RedGate.dll)
*Version Control Interface 1.2 (C:\Program Files (x86)\PLSQL Developer\PlugIns\VCS.dll)
(* is Active)

Aliases
10.0.15.84
Daves_Box
ncpl_neutrino
NEUTRINO
RISHNI_TEST

Homes
OraClient11g_home1 (C:\app\Oracle\product\11.2.0\client_1)

DLLs
C:\app\Oracle\product\11.2.0\client_1\oci.dll

TNS File
C:\app\Oracle\product\11.2.0\client_1\Network\Admin\tnsnames.ora

Using
Home: OraClient11g_home1
DLL: C:\app\Oracle\product\11.2.0\client_1\oci.dll
OCI: version 11.1

Character Sets
Character size: 1 byte(s)
CharSetID: 0
NCharSetID: 1
Unicode Support: False
NLS_LANG: ENGLISH_UNITED KINGDOM.WE8MSWIN1252

From NLS_DATABASE_PARAMETERS:

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 AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0

Many Thanks,
Dave

 

[Hilarion]

Settings you gave look good. (I'd try to check if changing NLS_LANG to something that does not contain space, but still uses same code page, like "ENGLISH_AMERICA.WE8MSWIN1252" but as far, as I know, "ENGLISH_UNITED KINGDOM.WE8MSWIN1252" is correct.)

What you did not give is an important Windows setting: "Language for non-Unicode programs". It should be on "Administrative" tab of "Region and Language" configuration window (where you can also choose various formats and location on other tabs). It should probably be "English (United Kingdom)". If it's not, then that's the most probable cause of the problem. If it is, then I'm afraid I have no idea what else you could try or check. Allround Automations should kick in and help you diagnose the problem.

 

[DaveE]

Thanks for the response again Hilarion...

The "Language for non-unicode programs" is already set to English (United Kingdom).

Thanks for your help and taking them time to have a look...hopefully someone from Allround Automations can help.

Many Thanks,
Dave

 

[IvanZ]

I believe you are talking abount Negation characterhttp://en.wikipedia.org/wiki/Negation

Try to use chr(172) or unistr('\00AC').

If you have ANSI NLS settings (an you do) PLD heavily rely on Windows setting that Hilarion pointed, but doesnt handle it good enough.

 

[Hilarion]

Not sure why, but IvanZ's message reminded me that there might be a workaround for your problem, DaveE. What you could try is: turn back on the Unicode support in the PL/SQL Developer and launch it with altrered NLS_LANG environment variable, with encoding/charset set to some Unicode one, like "ENGLISH_UNITED KINGDOM.AL16UTF16". You can specify it as command line parameter for PL/SQL Developer executable file, or in "params.ini" file.
I use that "trick" to be able to see (view/edit) data (and/or code) in databases that use charsets with Cyrillic alphabet (in your case it might be a bit better to use UTF-8 encoding insead of UTF-16 that I'm using).

As I wrote, your settings should work fine as they are (and - as you stated - they do in other applications) and Allround Automations should try to fix the application error, which prevents it, but the trick described above may let you work relatively well while waiting for proper solution.

 

[IvanZ]

This trick helps when you READ from the database, but you may have *load of problems trying to WRITE to it (in case of UTF -> ANSI conversion handled by PLD).

 

[Hilarion]

You are right, that you may have loads of problems when writing data (and code) to DB because you will loose what is outside DB encoding. That's exactly what is already happening to DaveE, so that will not be a new problem for him. Also if you keep in mind that you have to stick to only those characters that can be handled by DB (for example Cyrillic and basic Latin characters in my case, and Windows-1252 in case of DaveE), the problem does not have such a big impact on you work.
Anyway I should have warned DaveE of those risks and did not, so am grateful that you did. Thank you.

 

[DaveE]

IvanZ / Hilarion,

Thank you both for your responses.

Unfortunately, turning back on Unicode support and launching it with NLS_LANG environment variable set, did not work either.

For the time been, I will be using the workaround IvanZ suggested, using the chr(172) representation.

Hopefully, somebody from Allround Automations will be able to comment on whether this will be addressed for future releases.

Thanks again for your help,
Dave