APEX

My objective was to translate user role names displayed in a Classic Report on our Administration page. The role names are dynamic and come from the apex_appl_acl_roles view.

What I Did

1. Created a Custom Translation Table: I created a table to hold the translations for our dynamic data.

   CREATE TABLE DYNAMIC_TRANSLATIONS (
       LANG_CODE     VARCHAR2(10)   NOT NULL,
       MESSAGE_KEY   VARCHAR2(255)  NOT NULL,
       MESSAGE_TEXT  VARCHAR2(4000) NOT NULL
   );
   

   I populated this table with the role names (as the MESSAGE_KEY) and their translations for English, Italian, French, etc.

   

2. Wrote a Translated SQL Query: I updated the report's SQL source to join this table and fetch the correct translation based on the user's session language (:APP_LANG).

   

3. SELECT
       COALESCE(t.message_text, r.role_name) AS translated_role_name,
       COUNT(ur.role_id) AS user_count,
       r.role_id
   FROM
       apex_appl_acl_roles r
   LEFT JOIN
       apex_appl_acl_user_roles ur ON r.role_id = ur.role_id
   LEFT JOIN
       DYNAMIC_TRANSLATIONS t ON r.role_name = t.message_key AND t.lang_code = :APP_LANG
   WHERE
       r.application_id = :APP_ID
   GROUP BY
       COALESCE(t.message_text, r.role_name),
       r.role_id
   ORDER BY
       translated_role_name;
   

   

   The Problem

   SQL Workshop Test: When I ran this query in SQL Workshop, it worked perfectly. If I set :APP_LANG to 'it', I correctly received the translated Italian role names. This confirmed my data and SQL logic were correct.

   Application Behavior: In the APEX application, the report would not update.

   1. The page loads in our primary language (English), and the report shows English role names.
   2. The user changes the language to Italian using a process that calls APEX_UTIL.SET_SESSION_LANG.
   3. The page reloads. All static text (labels, region titles) correctly translates to Italian.
   4. However, the report data remained in English. The query was not re-executing with the new :APP_LANG value.