SQL Development in Multiple Languages

It’s All Greek To me

Author: Christina Moore

Date: 18NOV2022

Abstract

Every once in a while, a North American software developer has to remember that not all clients and not all users live in Kansas (or “From California to the New York Islands” as the old song goes). In 2017 through early 2019, our team supported our flagship application in Puerto Rico running an Oracle APEX application with 2000 users in both English and Spanish. Since the summer of 2021, my team and I have been developing software for clients based in Europe.

We find that we still run into issues with non-English characters, regional settings, time zones, date formatting, and more. For 18 months, we get Extended ASCII characters to mess up in our SQL Developer when we compile packages. Examples include:

l_warning := l_warning || ' Région absente';

becomes this after compiling in our database with SQL Developer:

l_warning := l_warning || ' R�gion absente';

The é with the accent-acute (accent ague) gets replaced with a set of odd crap. The solution involves several steps:

1. Make sure that in all possible places where code is edited that the Encoding is set a global norm such as UTF-8.
2. Add a library of required characters as global constants to a package
3. Check these settings after system upgrades, software upgrades, or replacing a work computer. My needs do NOT match default settings for a North American developer.

Step 1 – UTF-8 character set

SQL Developer

Within SQL Developer, I must check and recheck the environmental preferences for “encoding.” The image below illustrates the setting our team uses:

Navigate to this menu by going to Tools then Preferences. If downloaded as I did recently, it defaults to something entirely else. Yes, I got a new desktop computer and had to download all of my developer tools again. The trick is everyone on the team must agree and have the same settings. It only takes one person with the wrong encoding for the lovely € to get messed up.

Notepad ++

Within Notepad++, you can find preferences under the “Settings” menu. Confirm this editor, or any you use, is also set to match the team’s encoding value:

Step 2 – Build a Library

I’ve been doing geeky stuff with ASCII since one of us was very young (either ASCII or me). I am that person that can tell you that Cap-A is ASCII 65. A fact that does prove to drop into conversation easily, but I have tried. It does bring a room to silence. WTF is ASCII? And frankly nobody even cares. When ASCII and I were both young, ASII and Binary were close friends. ASCII was, then, limited to 2 ⁸ – 2 values (for normals, that is 126 values) with 00 as Null and 126 representing the tilde “~”. Then someone said: “Gee, do French people with their accent ague actually need to use computers?” So little ASCII became Extended ASCII.

With Oracle, I thought – given I’ve known ASCII since were both small and young – that I could just plop my favorite non-English characters into the CHR() function and call the job done. So many of our package simply have this at the top:

	g_charset 		constant varchar2(8 char)		:= 'AL32UTF8';
	crlf   			constant varchar2(2 char)		:= chr(13) || chr(10);
	ht			constant varchar2(2 char)		:= chr(9);
	cr			constant varchar2(2 char)		:= chr(13);	
	lf			constant varchar2(1 char)		:= chr(10); 
	amp			constant varchar2(1 char)		:= chr(38);	
	g_ISO8601_format	constant varchar2(30 char) 	:= 'YYYY-MM-DD"T"HH24:MI:SS.FF"Z"';

We’ll just create a library of needed character using the ASCII conversion and call it done. That failed. While I know that é is accomplished with an alt-0233 on my Window’s keyboard, this character is ASCII 130. I failed:

select chr(130) from dual;

I get a null value in return. I had to do the work in reverse to discover what Oracle calls these guys:

select ascii('é') from dual;

The result was: 50089

I run the CHR() value on that and get my “é”.

select chr(50089) from dual;

This is a range of ASCII values unknown to me. It is described in the Oracle on-line documentation, but like so much of their helpful articles, I didn’t understand it very well. Using a navigation technique the globe learned from Cristobal Colon, I just guessed and guessed my way into building a useful library of global constants.

euro		varchar2(2 char) := chr(14844588);
a_grave		varchar2(2 char) := chr(50080);
a_acute		varchar2(2 char) := chr(50081);
a_circum	varchar2(2 char) := chr(50082);
a_tilde		varchar2(2 char) := chr(50083);
a_umlaut	varchar2(2 char) := chr(50084);
c_cedill	varchar2(2 char) := chr(50087);
e_grave		varchar2(2 char) := chr(50088);
e_acute		varchar2(2 char) := chr(50089);
e_circum	varchar2(2 char) := chr(50090);
e_umlaut	varchar2(2 char) := chr(50091);
i_grave		varchar2(2 char) := chr(50092);
i_acute		varchar2(2 char) := chr(50093);
i_circum	varchar2(2 char) := chr(50094);
i_umlaut	varchar2(2 char) := chr(50095);	
n_tilde		varchar2(2 char) := chr(50097);
o_grave		varchar2(2 char) := chr(50098);
o_acute		varchar2(2 char) := chr(50099);
o_circum	varchar2(2 char) := chr(50100);
o_tilde		varchar2(2 char) := chr(50101);
o_umlaut	varchar2(2 char) := chr(50102);		
u_grave		varchar2(2 char) := chr(50105);
u_acute		varchar2(2 char) := chr(50106);
u_circum	varchar2(2 char) := chr(50107);
u_umlaut	varchar2(2 char) := chr(50108);	

With this completed, we can reference this library of constants from anywhere in our database:

util_pkg.a_acute

So this block of code:

Becomes this

l_warning := l_warning || ' R' || util_pkg.e_acute || 'gion absente';

While not pretty, it better tolerates the vagaries of mismatched SQL Developer and Editor settings.

Step 3 – Check and Recheck Settings

I used to think I owned my own computers and phones. But the software vendors prove me wrong. I set something one way, then it gets reset to a default I didn’t want, don’t want, and did not select. I need to check and recheck my regional and language settings.

• Category: Uncategorized
• Tag: non-english chararacters, oracle, plssql, sql