Using PL/SQL to Seed and Publish Oracle APEX application translations

For multilingual applications, it can become a little tiresome to constantly have to reseed and republish your application while testing multiple languages or debugging language-switch problems.

The other day, while tracking down a tricky language-switch bug, I thought to myself that there must be an faster way to seed and publish.  So I pulled up the documentation for the APEX_LANG API and found these two procedures: apex_lang.seed_translations and apex_lang.publish_application.

Using these two procedures meant that I could write a little script to seed and publish my application (ID 12345) in French and Spanish:

begin
  apex_lang.seed_translations   
     (p_application_id => 12345, p_language => 'fr');
  apex_lang.publish_application 
     (p_application_id => 12345, p_language => 'fr');

  apex_lang.seed_translations
     (p_application_id => 12345, p_language => 'es');
  apex_lang.publish_application 
     (p_application_id => 12345, p_language => 'es');
end;

Note, however, that when running the above outside an APEX environment, this script fails to execute. This because the security group for this workspace needs to be manually set. The error message looks like this:

ORA-20001: Package variable g_security_group_id must be set.
ORA-06512: at "APEX_240200.WWV_FLOW_IMP", line 109
ORA-06512: at "APEX_240200.HTMLDB_LANG", line 328

To set the security group for the workspace, we first have to retrieve the workspace ID and then call the apex_util.set_security_group_id procedure. An additional complication is that a schema may be associated with more than one APEX workspace, but we'll deal with that shortly.

declare
  l_workspace_id  apex_workspaces.workspace_id%type;
begin
  -- get the workspace ID for this schema 
  -- (for now, let's assume there's only 1) 
  select workspace_id 
    into l_workspace_id
    from apex_workspaces;  
  --
  -- set security group for the Oracle APEX workspace 
  apex_util.set_security_group_id (l_workspace_id);
  
  -- seed and publish french
  apex_lang.seed_translations   
     (p_application_id => 12345, p_language => 'fr');
  apex_lang.publish_application 
     (p_application_id => 12345, p_language => 'fr');

  -- seed and publish spanish
  apex_lang.seed_translations
     (p_application_id => 12345, p_language => 'es');
  apex_lang.publish_application 
     (p_application_id => 12345, p_language => 'es');
end;

Well, that takes care of my immediate issue for application 12345 and the French and Spanish translations.  

But.... Wouldn't it be so much better if we could have a solution that would check whether any of my applications need seeding and publishing, and that would then seed and publish them as needed?

We can start building one by querying the APEX data dictionary view apex_application_trans_map.  The requires_synchronization column tells us which language translations need to be seeded and published. This query returns results for all multiple APEX workspaces associated with the current schema.

select t.*
  from apex_application_trans_map t
 where substr(t.requires_synchronization,1,1) = 'Y'
 order by t.primary_application_id,
          t.translated_app_language;

We've got pretty much everything that we need to put it all of this together as a procedure:

1. Let's loop through an SQL query that retrieves every language translation (translated_app_language) that needs to be synchronized for this schema along with its workspace, application name and ID.

2. Within the loop, whenever there's a change of workspace, we'll reset the security group.

3. We'll then seed and publish the application translation.

4. The procedure will accept a single parameter, p_app_id.  If this parameter is passed with a value of null, it will loop through, seed and publish all APEX applications that need to be synchronized.  This will be done across all the APEX workspaces associated with the current schema.

procedure seedAndPublishTranslationsApp (
  p_app_id in apex_application_trans_map.primary_application_id%type
) is
  l_workspace_id apex_workspaces.workspace_id%type := 0;
begin
  --
  -- Loop through all of this schema's workspaces that have applications
  -- with translation languages that require synchronisation.
  --
  for rec_trans in (
    select t.workspace,
           t.primary_application_name,
           t.primary_application_id,
           t.translated_app_language,
           w.workspace_id
      from apex_application_trans_map t
           join apex_workspaces w
             on w.workspace = t.workspace  -- to get workspace ID
     where t.primary_application_id = nvl(p_app_id, t.primary_application_id) -- null means all apps
       and substr(t.requires_synchronization, 1, 1) = 'Y'
     order by w.workspace_id,
              t.primary_application_id,
              t.translated_app_language
  ) loop
  
    -- If there's a change of workspace, (re)set the security group.
    if l_workspace_id <> rec_trans.workspace_id then
      dbms_output.put_line('Workspace: "' || rec_trans.workspace || '":');
      apex_util.set_security_group_id(rec_trans.workspace_id);
      l_workspace_id := rec_trans.workspace_id;
    end if;

    apex_lang.seed_translations(
      p_application_id => rec_trans.primary_application_id,
      p_language       => rec_trans.translated_app_language
    );

    apex_lang.publish_application(
      p_application_id => rec_trans.primary_application_id,
      p_language       => rec_trans.translated_app_language
    );

    dbms_output.put_line(
      'Seeded and published language: "' || rec_trans.translated_app_language ||
      '" for application: ' || rec_trans.primary_application_id || ' - ' ||
      rec_trans.primary_application_name
    );
  end loop;

end seedAndPublishTranslationsApp;

As I don't particularly like having free-floating procedures and functions cluttering up my database, I've made a package, translateTools, that encapsulates this procedure.  You can download the package here.

There are various ways that you can use this procedure.
For example, during development, I like to have a worksheet open that let's me quickly run
  exec translateTools.seedAndPublishTranslationsApp();
whenever I want to. It certainly saves me a lot of clicking within the APEX application development environment.

Another use-case could be to implement it as a regular job that will run nightly to ensure that there are no unpublished translations left lying around.

It's a small solution to a small problem. I hope that some of you will find it useful.

Happy APEXing and Happy Translating!!