See
https://openacs.org/forums/message-view?message_id=2927592 for background.
The motivation is to avoid wrong locale creation and be able to retrieve 3-chars language if any application needs it.
The idea is:
1) to add 2 independent tables that will be filled with after_install and after_upgrade apm callbacks from the ISO text files (to be stored in acs-lang/resources):
create table language_codes (
iso_639_2 char(3) constraint language_codes_pk primary key,
iso_639_1 char(2),
label varchar(200)
);
comment on table language_codes is 'Contains ISO-639-2 language codes and their corresponding ISO-639-1 when it exists.';
create table country_codes (
label varchar(200),
country char(2) constraint country_codes_pk primary key
);
comment on table country_codes is 'Contains ISO-3166 country codes';
2) to fix buggy languages in ad_locales:
update ad_locales set language = 'ar' where language = 'AR';
update ad_locales set language = 'cs' where locale = 'cz_CZ';
update ad_locales set language = 'zh', country = 'HK' where locale = 'zh_HK';
3) to modify acs-lang/www/admin/locale-new to fill language and country codes from those tables instead of manually (getting the right code in each case, 2 or 3 chars), and build the locale label from there too
4) to fix lang::*::language to retrieve either 2-chars or 3-chars language code
5) to add the following procs in tcl/lang-util-procs.tcl
lang::util::iso6392_from_language -language language
lang::util::iso6392_from_locale -locale locale
lang::util::language_label -language language
I have all this already implemented locally for postgresql. Adding oracle support would be straightforward since the queries are generic, would just need to write the creation and upgrade scripts for it.