LENGTHB and SUBSTRB: when smaller is bigger

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Lately I'm working a lot with UTF-8 files and while doing some test i came across the following situation, which sounds rather odd at a first sight but it's perfectly legitimate in the way Oracle treats multi-byte strings, so i thought it could be an interesting story for those who rarely deal with this class of problems.

In short, given a multibyte string (i.e. AL32UTF8), when you take a byte-wise substring, a bigger chunk of bytes can result in a shorter string character-wise.

Standard functions SUBSTR and LENGTH have their byte-wise counterparts called SUBSTRB and LENGHTB respectively.
Now, let's take a multibyte string made up of two alternating Unicode characters, "A" = ascii 41h and the ellipsis "..." = Unicode 2026h.
As i explained some time ago, in order to convert back and forth Unicode character codes, you need to call dedicated string functions like UNISTR and ASCIISTR. For instance, the internal code for Unicode character 2026h can be obtained with ASCII(UNISTR('\2026')) which returns the magic number 14844070, that you see in the source code below.

The following three blocks of PL/SQL show this strange phenomenon, where the standard LENGTH function initially returns 6 for a substring of 10 bytes, then 5 for a substring of 11 bytes, then again 6 for a substring of 12 bytes. The relevant parameters and values are highligthed in color.

declare
s varchar2(50) := rpad(chr(14844070),10, 'A'||chr(14844070));
t varchar2(16) := substrb(s,1,10);
begin
for i in 1..length(s) loop
dbms_output.put(ascii(substr(s,i)));
dbms_output.put('|');
end loop;
dbms_output.new_line;
for i in 1..length(t) loop
dbms_output.put(ascii(substr(t,i)));
dbms_output.put('|');
end loop;
dbms_output.new_line;
dbms_output.put_line('length:'||length(t));
dbms_output.put_line('bytes:'||lengthb(t));
end;
/

14844070|65|14844070|65|14844070|65|14844070|65|14844070|65|
14844070|65|14844070|65|32|32|
length:6
bytes:10

declare
 s varchar2(50) := rpad(chr(14844070),10, 'A'||chr(14844070));
 t varchar2(16) := substrb(s,1,11);
begin
 for i in 1..length(s) loop
 dbms_output.put(ascii(substr(s,i)));
 dbms_output.put('|');
 end loop;
 dbms_output.new_line;
 for i in 1..length(t) loop
 dbms_output.put(ascii(substr(t,i)));
 dbms_output.put('|');
 end loop;
 dbms_output.new_line;
 dbms_output.put_line('length:'||length(t));
 dbms_output.put_line('bytes:'||lengthb(t));
end;
/

14844070|65|14844070|65|14844070|65|14844070|65|14844070|65|
14844070|65|14844070|65|14844070|
length:5
bytes:11

declare
 s varchar2(50) := rpad(chr(14844070),10, 'A'||chr(14844070));
 t varchar2(16) := substrb(s,1,12);
begin
 for i in 1..length(s) loop
 dbms_output.put(ascii(substr(s,i)));
 dbms_output.put('|');
 end loop;
 dbms_output.new_line;
 for i in 1..length(t) loop
 dbms_output.put(ascii(substr(t,i)));
 dbms_output.put('|');
 end loop;
 dbms_output.new_line;
 dbms_output.put_line('length:'||length(t));
 dbms_output.put_line('bytes:'||lengthb(t));
end;
/

14844070|65|14844070|65|14844070|65|14844070|65|14844070|65|
14844070|65|14844070|65|14844070|65|
length:6
bytes:12

These somewhat bizarre values are easily explained. When Oracle cuts a multi-byte string at some point falling inside a multibyte character, it replaces the meaningless fractional character portion with blanks. You can see this occurring in the first block, where the last two characters are blanks replacing 2/3 of the bytes originally belonging to the ellipsis character taking 3-bytes.

In conclusion when cutting multi-byte strings:

1. you may get spare blanks that were not part of the original string
2. you will get a varying length character-wise, depending on the cutting place.

Not a big deal, but something to be aware of.

ORA-22922: nonexistent LOB value

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

As mentioned in a previous posting about ORA-22275, a temporary LOB can be turned into a persistent LOB by moving it into a table, however one thing is persistence and a different thing is LOB consistency.

Funny things happen when one gets fancy in the use of SAVEPOINT/ROLLBACK in conjunction with LOBs.

The following PL/SQL block represents a simple scenario of an ill-designed procedure that is rolling back under a certain condition thus invalidating the LOB pointer.

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
  rollback;
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at "SYS.DBMS_LOB", line 560
ORA-06512: at line 19

The procedure can be modified as follows:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
 savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
  rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

This time no ORA-22922 will be raised and apparently we managed to transform a temporary LOB locator into a persistent one without inserting any rows, because the insert statement was rolled back.

Interestingly enough on my Oracle XE instance, if i further modify the program as follows, i'm eventually getting ORA-00600:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
 insert into files(document) values(l_clob);
 commit;
end;
/

ORA-00600: internal error code, arguments: [7999], [300], [], [], [], [], [], []
ORA-06512: at line 21

If i insert a COMMIT statement before the final INSERT, we're back to the initial situation:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
 rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
 commit;
insert into files(document) values(l_clob);
commit;
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at line 22

In conclusion ORA-22922 is a symptom of a program that is incorrectly handling LOB locators across transactions.

For a complete review of weird effects achievable by tampering with LOB locators and partial LOB manipulation procedures, see the linked paragraph in the Oracle Application Developer's Guide for Large Objects - Advanced design considerations chapter.

See message translations for ORA-22922 and search additional resources.

ORA-22275: invalid LOB locator specified

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get ORA-22275 in the following situation:

declare
l_clob clob;
begin
l_clob := dbms_xmlgen.getxml('select * from user_tables');
 dbms_lob.freetemporary(l_clob);
insert into files(document) values(l_clob);
end;
/

ORA-22275: invalid LOB locator specified
ORA-06512: at line 6

The problem here is in the premature call of procedure DBMS_LOB.FREETEMPORARY, although the error is raised in the subsequent line.
This particular instance of DBMS_XMLGEN.GETXML is returning a temporary LOB that must be freed after use, therefore DBMS_LOB.FREETEMPORARY must be called after moving the LOB object into its "final" position inside the table.
When you insert a temporary LOB into a table, it becomes persistent.
See also see entry for another occurrence of ORA-22275.

See message translations for ORA-22275 and search additional resources.

ORA-00904 when clicking on edit in SQLDeveloper 1.5.4

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If you are wondering why you are getting the following pop-up screen when attempting to edit a procedure (or a trigger, a type or anything else that can be edited in the editor panel, but not views for instance) after upgrading to the latest version of SQL Developer (1.5.4)...


ORA-00904: "ATTRIBUTE": invalid identifier
vendor code 904

... you may ask yourself if you are trying to perform this operation on a Oracle 9i database.

Please note that the annoying pop-up message doesn't prevent you from editing the source and apparently you can still compile it successfully, although every time you hit on the compile icon you'll get the exception again.
It looks like a problem with some underlying view or table that is queried by SQL Developer and is lacking a column in oracle 9i, but this is just a speculation of mine.

Initially i had a feeling that someone had told me that as of version 1.5.x Oracle 9i was no longer supported, but when i read the release notes i read that Oracle 8.1.7 was no longer supported, so i failed to find a specific statement about Oracle 9i.
I must conclude that it is still supported or i did not search hard enough in the accompanying documentation...

If i click on EDIT using SQL Developer 1.5.1 (the previous version installed on my PC) i don't get any errors of this kind.

See message translations for ORA-00904 or read more articles about SQL Developer.

What's new in Apex 3.2 dictionary?

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

In case you are wondering what's new inside the apex 3.2 data dictionary, the following query run from SQL Workshop will quickly return the desired answer.

select table_name, column_name from all_tab_columns
where owner = 'APEX_030200'
and table_name like 'APEX%'
minus
select table_name, column_name from all_tab_columns
where owner = 'FLOWS_030100'
and table_name like 'APEX%';

The following query will also return the comments alongside.

select b.table_name, b.column_name, b.comments
from
(select table_name, column_name
  from all_tab_columns
where owner = 'APEX_030200'
and table_name like 'APEX%'
minus
select table_name, column_name
 from all_tab_columns
where owner = 'FLOWS_030100'
and table_name like 'APEX%') a, all_col_comments b
where b.owner = 'APEX_030200'
and b.table_name = a.table_name
and b.column_name = a.column_name;

The SQL queries above work on the assumption that you have not dropped the schema FLOWS_030100 after upgrading to Apex 3.2.
Note also that as of Apex 3.2 the apex repository schema name prefix has changed from the "historic" FLOWS to APEX.

See more articles about Oracle Application Express or download tools and utilities.

How to change the language of SQL Developer's user interface?

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Yesterday i upgraded SQL Developer from version 1.5.1 to 1.5.4 and when i opened it, i noticed that the user interface was automagically showing up in Italian.
I say automagically because i am italian indeed, but the operating system on this particular machine is localized in mexican-spanish, so i wonder if SQL Developer was spying my conversations... ;-)

While i appreciate the globalization efforts, i feel much better having certain programs with an English user interface for the simple reason that when i need to communicate with someone else who doesn't speak Italian, i can talk about menu entries, settings, button names using a commonly understood language and without having to guess what the original entry in English language could be.

How to get rid of this automatic translation then?
I started wandering through the preference menu and either because i am getting old or there are too many settings or this setting can't be changed in the preferences, i failed to find a list available translations.

After a quick OTN search i found a thread where users were reporting some problems when creating new connections and i noticed a workaround where one must add the following two lines to the Sqldeveloper.conf file in bin subdirectory.

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

I promptly tried it out to see if that could solve my problem and bingo!

I don't know if this is the only "supported" way of changing the user interface language, but it's certainly working.

Please let me know if there are any other options.


See more articles about SQL Developer.

PS: I encourage everyone interested in this feature to cast their vote in the SQLDeveloper Feature request application: https://apex.oracle.com/pls/apex/f?p=43135:7:0::NO:RP,7:P7_ID:10821

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

There are oracle errors that can be easily explained.
Sometimes one of these trivial error messages pops up in an unexpected situation and it's really tough to get to the bottom of it.
And that is exactly what happened yesterday, but i am still doing some tests today.

Let's see first the simplest form of ORA-01841:

select to_date('0000-03-10','YYYY-MM-DD') as d
from dual;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Gregorian calendar starts with year 1 and there is very little to say about it when you get this error in a query: just fix the input value.

When the input value comes from an external source and it contains all zeroes, something that I've seen happening quite often when you receive a file from a third party meaning a NULL date value, then you'll have to deal with it using one of those special init_spec clauses like NULLIF or DEFAULTIF (both in SQL*Loader and external tables using the SQL*Loader driver).

However there are less common situations like that i came across today, where i found this message in the log of the SQL*Loader driver invoked by an external table definition in a program that was running since months.

 LOG file opened at 03/10/09 11:13:10

Field Definitions for table IMP_BAD_BOXES
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Reject rows with all null fields

 Fields in Data Source:

   CREATED                         CHAR (14)
     Date datatype DATE, date mask YYYYMMDDHH24MISS
     Record position (1, 14)
     Trim whitespace same as SQL Loader
   BOX_ID                          CHAR (8)
     Record position (15, 22)
     Trim whitespace same as SQL Loader
   DEPT                            CHAR (2)
     Record position (23, 24)
     Trim whitespace same as SQL Loader
error processing column CREATED in row 5 for datafile BOXES.dat
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Date Cache Statistics for table IMP_BAD_BOXES
 Max Size:      1000
 Entries :         4
 Hits    :         0
 Misses  :         0

A batch script invoked by a Windows service running as System was failing to load a file whereas the same script run manually as Administrator was running just fine and the only difference between the two log files was in the aforementioned error on the last imported line of the file.

The only known difference with the past is in that the input file is now generated by an export procedure whereas it was previously exported manually from an excel spreadsheet.
Practically speaking the old files did not contain the trailing newline at the bottom.

Given the following external table definition:

CREATE TABLE "IMP_BOXES"
( "BOX_ID" NUMBER(8,0),
 "DEPT" VARCHAR2(2 BYTE),
 "CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
(
CREATED POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
BOX_ID  POSITION(15:22) CHAR,
DEPT    POSITION(23:24) CHAR
)
   )
LOCATION
( "IMPORT_DIR":'BOXES.dat'
)
);

a source file of 4 records containing a newline (0D0A) in the last record position would cause ORA-01841 to be raised on the 5th record when launched automatically from the Windows service. The same source file did not cause any error when executed as a user with administrator rights.
This problem was reproduced on two different 10GR2 instances running on top of Windows 2003 SE, so, at least, the good news is this is a consistent pattern.
Oracle NLS session parameters were looking the same (i spooled NLS_SESSION_PARAMETERS before invoking the script), so i'd rule them out as a possible cause, i wonder if the system user is reading a different parameter set from the registry.
Note that adding any combination of MISSING FIELD VALUES ARE NULL and/or REJECT ROWS WITH ALL NULL FIELDS did not affect the result.

So, in the end, i don't really know yet what's going on.

I failed to find the exact reason (or oracle bug?) but at least i could find a workaround, consisting in changing the external table definition as follows (inserted clause in green color):

CREATE TABLE "IMP_BOXES"
( "BOX_ID" NUMBER(8,0),
 "DEPT" VARCHAR2(2 BYTE),
 "CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
LOAD WHEN((1:1) != NULLS)
FIELDS
(
CREATED POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
BOX_ID  POSITION(15:22) CHAR,
DEPT    POSITION(23:24) CHAR
)
   )
LOCATION
( "IMPORT_DIR":'BOXES.dat'
)
);

The advantage of checking a character range using the syntax (n:m) is in that i can discard the record before the parser fails to read the last record.
While i was looking for a solution i also incidentally found out that all Oracle Utilities manual through version 11G, where they talk about the so-called condition specifier, are wrong when they say that you can use the keyword NULL in the comparison.
The correct keyword is NULLS.

Another interesting finding is in that the .bad file generated by Oracle contained a weird 1A character preceding the rejected newline character, a character that was nowhere to be found in the source file. This file is only generated when the script is launched from the service.

Curious, isn't it?

See message translations for ORA-01841 and search additional resources.

SAQ: Seldom Asked Questions about Apex Globalization

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I like jokes as you can understand from the title of this posting, however i was in the mood for it because sometimes i happen to read FAQ lists where there are questions that i'd never ask in centuries, hence the idea of a SAQ list.

Apex globalization mechanism is extremely powerful and flexible, yet you need to know what you are doing if you don't like last minute surprises. In other words you need to master the globalization process if you don't want to accidentally delete translated text or put in jeopardy the whole application with a wrong action.

Before delving into the list of questions, let's take a tour of the main translation page of Oracle Application Express.

The Apex globalization process is managed from the page below, that you can easily access from the shared components page of the application builder:

The central region of this page outlines the steps from the beginning to the end of the globalization process and provides the links to the key phases of the process.

Step 1 in this list is a one-off task that needs to be performed only when:

1. you need to create a new translated application
2. you need to change an existing mapping or delete it altogether.

Step 2 is the real starting point each time you need to update an existing application when you've changed something that needs to be propagated to the various translations.

Step 3 is a curious fake link. Indeed it's just a reminder of what you need to do next, that is either translate the text yourself or ask someone else to do it for you.

Step 4 takes you to the page where you can see the list of XLIFF files loaded in the repository and you can apply one of them or you upload a freshly translated one.

Step 5 points to the page where you manage the static message translations and is optional. If you don't use translatable text messages, those retrieved via the API call to APEX_LANG.MESSAGE you don't need to go there. Translatable messages are extremely useful for translating static text that needs to be stored inside page items or application items or when the value needs to be returned through a PL/SQL function in the current language. See the linked page for a practical example.

Step 6 leads you to an even more sophisticated feature, the dynamic translation of text returned by dynamic LOVs, that is list of values based on queries. While text descriptions coming from static LOVs are automatically retrieved by Apex and inserted into the repository, this is not possible with dynamic LOVs that are based on user defined queries on arbitrary tables, however Apex gives you the possibility of performing dynamic translations, so, if you have a query like:

SELECT fruit_name d, fruit_id r
FROM fruits

you can get "apple" when viewing the page in English or "Apfel" when viewing it in German.


The menu called Navigate on the right hand side is a fine grained list of tasks and gives you instant access to the critical features.

The menu below it, called Translation Utilities, is also important because it's the only place where you find a link to the page for manually translating the text.

For some reason the link to the page that you'll visit most frequently has been named Export Translations, followed by the link Export XLIFF, which is the real page where you actually download a dump of the translation repository.

Why do i need to map an application?
Apex globalization mechanism works by creating a sort of ghost application with its own application ID.
Mapping means assigning a number to a translated application. In an hosted environment like apex.oracle.com, where there are thousands of application IDs already taken by other users, it can be tricky to find out a free application ID because Apex does not automatically suggest the next available number.
Note that Apex doesn't actually reserve the application ID until you publish the translation, so it might happen that you choose an ID but then it's also taken by another user before you had the time to publish the translation.
If that happens, you'll get a run-time error when you attempt to publish (but not at time of seeding):

ORA-20001: Error during execution of wwv_flow_copy: WWV_FLOWS
ORA-00001: unique constraint (FLOWS_030100.WWV_FLOW_FLOW_PK) violated

Each application ID maps to a language code, like it, fr, de, zh-cn, ja and so on.
Apex maintains a consistent session state whenever you switch from one language to another by any means, within the same apex session, which is a good thing.


1. What does it mean to seed an application?
Seeding is the process of preparing a translated version of your primary application.
When you seed the text, Apex inserts/updates/deletes the translation repository with the most recent version of the translatable strings. Seeding alone does not change anything in the currently running translated applications until you publish the translated version.
Note also that when you seed an application you are doing a twofold action: you are not only updating the source text but also the target text. An important thing to bear in mind is that the target text won't be touched only if the old source text and the new source text are equal, (which means that a single space makes a difference!), otherwise it will be "reset". See later on for a typical scenario.


2. What happens when i publish an application?
Publishing is the final step in the globalization process.
The application is created by assembling the metadata from the primary application and merging it with the translated text. Note that you cannot access directly a translated application by invoking Apex with the mapped application ID, if you attempt to do so, you'll get a page like this:



3. What if i need to update only a few new translated strings?
whenever you add a new element which is a candidate for a translation, like a page item or a new shared component like a list entry, translated applications won't pick them up until you seed the application to the desired language. After seeding the application, the fastest way to get a string translated is to manually edit it. After editing the translation, you can go straight to the publishing phase, without importing anything.

1. seed the application
2. click on translation home
   
3. click on manually edit translations
4. update the translations
   
5. export the XLIFF file for future reference ( this step is not required but highly recommended!)
6. publish the selected translation

4. What if i need to update a few existing strings because i changed the corresponding source element?
you must be careful when changing the text of a source element because Apex will automatically wipe out the translation upon seeding! Imagine you have a large text in the source language and you realize there is a grammatical error for instance. This grammatical error is probably absent in the target language(s) so you could easily have a situation where you want to change just the source element, not the existing translations.
So, you fix the source text, seed the application and bang, the old translation is lost!
The target element is erased and replaced with the new untranslated text.
This example demonstrates why you ought to export a full XLIFF file whenever you translate something, the file will be the backup in case something goes wrong.
Back to our problem, if you change a translatable string of an existing element, if you seed the application again, then you need to recover the translation from the XLIFF file, if it still applies, or provide a new one if it does not apply or if you didn't export the XLIFF file. See entry #3 on how to manually edit a translation.

5. What is an XLIFF?
XLIFF is an international file format standard based on XML tailored for translation tools. You can find out more on XLIFF on wikipedia.

6. Do i necessarily need to translate the content of the XLIFF file to translate an application?
No, you don't. You can manually edit the translations after seeding the translation. However, after doing so, you should immediately take an export in XLIFF format as a backup of your work!
The XLIFF format comes in handy if you are going to use some tool to translate the text, otherwise you can easily break the XML file format by deleting or inserting unwanted characters, especially if the translated text contains some HTML tags. There is a free on-line tool kindly provided by iAdvise, that allows you to manipulate XLIFF files generated by Apex.

7. Is there any apex dictionary view containing the translations?
At time of writing (version 3.1.2) there isn't any built-in apex dictionary view available and unless i overlooked it, there isn't any view in the just released version 3.2.

8. Why when i query certain apex dictionary views like apex_application_pages columns like help_text or label are always returned in the primary language?
The apex dictionary view doesn't currently support a language "context", so it always returns the text stored in the primary application, even when the query is originated from a translated application.
Hopefully one day apex dictionary views will fully support translated applications.

9. How can i compare two XLIFF files?
There are some tools around to do this specifically on XML files, besides some powerful plain text editors or Unix and DOS commands.
Some years ago i bought a license of XMLSpy, a powerful Windows based XML editor that comes with a document comparison function. I used it just yesterday to understand why i got two different links in two different application translations. As far as i know you can install a trial of XMLSpy valid for 30 days, so if it is a one-off requirement, it won't cost you a penny. Altova released also a stand-alone utility called diffDog for comparing files, so you might want to check it out as well.
As i am working more and more time on the Mac, today i searched for a file compare utility for Mac OS X and i found what it seems to me like a perfect match, a multiplatform utility called DeltaWalker.
I quickly installed the trial and it worked like a charm although i didn't perform any "stress test". Even in this case you have days ahead to evaluate the product before buying a license.
This was not meant to be a comprehensive software review, so you may want to conduct a deeper search on the web.

10. The Application Language Derived From attribute (in the Globalization Attributes page) contains several options, two of them are called Application Preference and Item Preference: what's the difference between the two?
The former option automatically retrieves the language from a user preference, supposing you have some process that calls the API procedure APEX_UTIL.SET_PREFERENCE. The preference name is called FSP_LANGUAGE_PREFERENCE, a name that may easily lead to some confusion with the other option.
This approach makes sense when the application requires authentication because it stores the value in a sort of user profile. The advantage is in that a returning user doesn't need to switch the language after the login, it will be automatically set by Apex (but he/she can still change it at any time afterwards).
The latter option works by storing the language option inside an application item called FSP_LANGUAGE_PREFERENCE (that you need to create yourself, don't forget it), so the user might have to change it every time if the primary language is not the preferred one. The advantage of this approach is in that it works also with public pages, where you can pick a specific language by clicking on an icon or selecting from a list. The logic for updating the application item must be written by the developer.

11. Is there any way to set a given language from the URL?
This problem has haunted me for quite some time.
The solution i found works well with real users but doesn't work well with web spiders like googlebot who is very picky with web redirects.
First of all you need to set the globalization attribute "Application Language Derived From" to "Item Preference (use item containing preference)".
Secondly you must set up an application process that runs before header performing a conditional redirect using the following PL/SQL call:

begin
:FSP_LANGUAGE_PREFERENCE := :REQUEST;
htp.init;
owa_util.redirect_url('f?p='||:APP_ID||':'||:APP_PAGE_ID||':'||:APP_SESSION);
end;

and the condition is "Request is contained within Expression1", where expression1 contains the list of the expected language codes like "en,de,fr,..." as shown in this sample page:

http://apex.oracle.com/pls/otn/f?p=multilangdemo:1:0:en
http://apex.oracle.com/pls/otn/f?p=multilangdemo:1:0:es

As i stated initially, redirecting the page may adversely affect the page indexing process, for instance Google spiders don't like at all redirects and this may prevent the link from being harvested. This is not a problem if the application is not aimed to the public and you don't expect users to reach you through a web search.

Note that setting the item FSP_LANGUAGE_PREFERENCE directly from the browser achieves curious results owing to the sequence of operations that Apex does when building a page. If you set this application item from an URL, apex will start building the page using the current language, then, at a certain point it will set the new value for the variable in the session state. This may result in pages with mixed languages, where static text is still in the old language and dynamic content is in the new language. If you reload the page though, the new language will be used. This is clearly a suboptimal solution.
There must be a reason if the Apex team decided to retrieve the current page language before setting values in session state, if not, may be one day we will be able to change the current language before the page loads without the need for an additional redirect.
Updated on march 13:
i simplified the argument in the call to OWA_UTIL.REDIRECT_URL, instead of using an absolute path, now i'm using a relative path, which makes the transition from a development or test environment to a production environment much easier because there is no need to worry about the current service path.

12. How to show the current page in a different language by pressing a button?
With a logic similar to that i explained in question #11, but replacing the redirect process with an application computation that updates the value of FSP_LANGUAGE_PREFERENCE after submitting the page, you can switch language in every page of your application by clicking on a button defined as illustrated below:
The second component is a simple conditional after submit application computation (a computation that will automatically run on each and every page if a condition is met) defined as follows:


With just a few components like a region of buttons (or icons or whatever you prefer) defined on page zero and a single application computation, you can easily enable language switching on each page. If you prefer you can move this function to the navigation bar, where you can define each entry as an URL containing a javascript call like "javascript:doSubmit('es');".
See how it works in a live demo application.

More infrequently asked questions to be added in the future.


See more articles about Oracle Application Express or download tools and utilities.