Moving your XLIFF Files

The XML translation files generated from Oracle Application Express are produced in XML Localization Interchange File Format (XLIFF) format. XLIFF is a recognized standard for the localization of computer software. "It is intended to give any software provider a single interchange file format that can be understood by any localization provider."

One of the unique characteristics of Application Express is that it is one of the few development frameworks where the decision to localize and translate an application can be made after the application is actually completed. Because the definition of the application is maintained in meta data in the APEX repository, it's already known in advance which attributes of your application are translatable and which are not.

The process to produce a translated application is pretty straightforward. It's as simple as:

1. Seed the translation repository from your existing application
2. Export the XLIFF file
3. Translate the XLIFF file
4. Upload the XLIFF file
5. Apply the XLIFF file
6. Publish your translated application

The first few translation unit lines of a sample XLIFF file generated from Application Express look like:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

<trans-unit id="S-2-885632848731895778-25721"> <source>Logout <target>Logout</target> </trans-unit> <trans-unit id="S-2-885632950195895779-25721"> <source>Print <target>Print</target> </trans-unit> <trans-unit id="S-2.1-885632848731895778-25721"> <source>Logout <target>Logout</target> </trans-unit> <trans-unit id="S-2.1-885632950195895779-25721"> <source>Print <target>Print</target> </trans-unit> <trans-unit id="S-4-885632364763895772-25721"> <source>Home <target>Home</target> </trans-unit> <trans-unit id="S-4-885632445599895776-25721"> <source>Customers <target>Customers</target> </trans-unit> <trans-unit id="S-4-885632564774895777-25721"> <source>Products <target>Products</target> </trans-unit> <trans-unit id="S-4-885632638841895777-25721"> <source>Orders <target>Orders</target> </trans-unit> <trans-unit id="S-4-885632745057895778-25721"> <source>Charts <target>Charts</target> </trans-unit>

Each translatable string is included as a 'trans-unit' in the XLIFF file. The last two elements of each translation unit ID are the meta data ID and the application ID. For example, in translation unit with id S-4-885632445599895776-25721, the meta data ID is 885632445599895776 and the application ID is 25721. (S-4 is an internal code signifying that this is a meta data string and corresponds to the text of a tab).

As I've discussed in a recent blog post about saved Interactive Reports, I explained how the internal meta data IDs "shift" or are recalculated when importing an application to a new ID. And this has presented problems for those customers who make use of the translation facilities of Application Express. Because the meta data IDs are a part of the XLIFF translation unit IDs, when those IDs change, the existing XLIFF files for the original application cannot be used against a new version of the application imported elsewhere as a new application ID. What a dead end!

I have authored an APEX application which helps customers overcome this problem. You can run the hosted version of the XLIFF Transformation application which is running in my workspace on apex.oracle.com, or you can download a copy of it and run it on your own APEX 4.0 or later instance. You need to provide 3 things when running this application:

1. The original XLIFF file
2. The application ID of the new application
3. The offset value between the two applications

To compute #3, I'll refer you to this same blog post where I give a couple examples how to determine the offset value.

The logic is really quite simple. After importing the application and installing the supporting objects, only 3 objects will be created - a table named XLIFF_FILES, a trigger on this table, and a small PL/SQL package named XLIFF_TRANSFORM. The PL/SQL package parses the XML file and uses some XDB APIs to replace certain elements of the XML file. By exploiting the native functionality of the database, this was really quite easy to write. For anyone who says the Oracle database is only good for "persisting data", I say smoke this!

This isn't my ideal solution. In a future release of Application Express, I'd like to make it as simple as choosing to include your translations in your application export file, and they move around with you. As the metadata gets transformed on a new import, so do the translations. But until then, this solution can be used.

Where Did My Saved Interactive Reports Go?

A problem I've seen reported numerous times from customers is that users' saved (or customized) interactive reports are missing after they import a new version of their application. This is a problem we've known about for a while with no adequate remedy. However, given the introduction of the APEX_APPLICATION_INSTALL API in Application Express 4.0, I can offer a solution. Granted, it's not an ideal answer but it's certainly a feasible and supported solution. Firstly, some explanations are in order.

When an APEX application is imported into a workspace, the very first thing that's done is the existing version of the application is completely deleted. All of the meta data associated with the application is deleted - the definition of the pages, the reports on the pages, the templates, the buttons, the branches, the shared components, everything - it's all deleted. Once this is complete, then the application meta data of the APEX application being imported is then inserted. This whole process is atomic - so if an error occurs, the transaction is rolled back and the net effect is no change.

In the case of saved Interactive Reports, it's a little bit different. Imagine you have a production instance running application 645 - you have numerous users who have saved many Customized Interactive Reports. Upon import of a new version of the application, all meta data associated with application 645 is first deleted except the Customized Interactive Reports. In essence, these are left "dangling" until the new application 645 is installed. Once the application import is complete, then the meta data of the Customized Interactive Reports will reference real interactive report definitions again.

But there's a catch. If the application ID changes upon import, then this results in totally new meta data IDs being generated. (This is done in an attempt to prevent collisions of meta data, so you can freely export your application and give to anyone in the world to use on their own APEX instance). A meta data offset number is randomly generated and added to all of the existing IDs. This is done uniformly across all of the application meta data (this is important, and you'll see why shortly). Since the IDs of all of the application meta data have changed, all of your users' customized reports in the previous version of the application are forever left orphaned until they're cleaned up by an internal APEX batch process. Yikes!

Let's look at an example. On apex.oracle.com, I created a simple application with an Interactive Report on the EMP table. I defined this application as application 70000. I then exported this application and imported it back as application 70001.

Using SQL Commands and the APEX Data Dictionary views, I ran the following queries:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

select tab_label, tab_id from APEX_APPLICATION_TABS where application_id = 70000   tab_label: Emp tab_id: 1573281607527253166     select tab_label, tab_id from APEX_APPLICATION_TABS where application_id = 70001   tab_label: Emp tab_id: 3146580610985521585

And the difference between the two IDs is 3146580610985521585 - 1573281607527253166 = 1573299003458268419

Let's do this again, but this time, for the APEX data dictionary view for page templates:

1 2 3 4 5 6 7 8 9

select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70000   template_id:  1573270610302252883     select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70001   template_id:  3146569613760521302

If we once again compute the differences between these two IDs, we get: 3146569613760521302 - 1573270610302252883 = 1573299003458268419

This happens to be exactly the difference between the IDs of all of the application meta data, with the exception of the application and page IDs. All of the meta data is consistently "pushed" or offset to a new value.

How is this relevant to missing saved Interactive Reports? Simple. Since we're able to compute the offset which was used between the two applications, if we had a way to ensure that the same offset is used every time upon application import, then there would be no issue with the old saved Interactive Report IDs matching with the newly imported meta data. They would be married again. And how is this done? In Application Express 4.0, there is a new API named APEX_APPLICATION_INSTALL which enables you to control this offset value.

To ensure that I didn't lose the saved Interactive Reports on subsequent imports of application 70000 to application 70001, I included the computed offset before importing this application via SQL*Plus:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

begin        apex_application_install.set_application_id( p_application_id => 70001 );     apex_application_install.set_offset( p_offset => 1573299003458268419 );     --     -- set the alias so it doesn't collide with the alias from app 70000     --     apex_application_install.set_application_alias(         'F' || apex_application.get_application_id );  end; /     @f70000.sql

That's all there is to it. Note that I didn't have to call apex_application_install.set_workspace_id above, because application 70000 and 70001 are in the same workspace where I performed this test.

As I stated earlier, this isn't the most elegant solution on the planet and most people don't want or need to know about meta data IDs or offsets or any of this complexity. But for those experienced users who are stuck with this problem of losing saved interactive reports when migrating from one application ID to another or across workspaces or instances, this is a supported and feasible solution.

APEX_APPLICATION_INSTALL

Overview

Oracle Application Express provides two ways to import an application into an Application Express instance:

1. Upload and installation of an application export file via the Web interface of Application Express.
2. Execution of the application export file as a SQL script, typically in the command-line utility SQL*Plus

Using the file upload capability of the Web interface of Application Express, developers can import an application with a different application ID, different workspace ID and different parsing schema. But when importing an application via a command-line tool like SQL*Plus, none of these attributes (application ID, workspace ID, parsing schema) can be changed without directly modifying the application export file.

As more and more Application Express customers create applications which are meant to be deployed via command-line utilities or via a non-Web-based installer, they are faced with this challenge of how to import their application into an arbitrary workspace on any APEX instance.

Another common scenario is in training classes, to install an application into 50 different workspaces, all using the same application export file. Today, customers work around this by adding their own global variables to an application export file (never recommended and certainly not supported) and then varying the values of these global variables at installation time. However, this manual modification of the application export file (usually done with a post-export sed or awk script) shouldn't be necessary - and again, not supported.

In Oracle Application Express 4.0, there is a new API available named APEX_APPLICATION_INSTALL. This PL/SQL API provides a number of methods to set application attributes during the Application Express application installation process. All export files in Application Express 4.0 contain references to the values set by the APEX_APPLICATION_INSTALL API. However, the methods in this API will only be used to override the default application installation behavior.

APEX_APPLICATION_INSTALL Summary

Workspace

Used to set and get the workspace ID for the application to be imported. This number can be determined by querying the view APEX_WORKSPACES.

procedure set_workspace_id( p_workspace_id in number );

function get_workspace_id return number;

Application ID

Used to set and get the application ID for the application to be imported. The application ID should either not exist in the instance, or if it does exist in the instance, it must be in the workspace where the application will be imported into. This number must be a positive integer and must not be from the reserved range of Application Express application IDs.

procedure set_application_id( p_application_id in number );

function get_application_id return number;

Generates an available application ID on the instance and sets the application ID in APEX_APPLICATION_INSTALL.

procedure generate_application_id;

Offset

Used to set the offset value during application import. This value is used to ensure that the metadata for the Application Express application definition does not collide with other metadata on the instance. For a new application installation, it's almost always sufficient to call generate_offset to have Application Express generate this offset value for you. This number must be a positive integer.

procedure set_offset( p_offset in number );

function get_offset return number;

procedure generate_offset;

Schema

Used to set the parsing schema ("owner") of the Application Express application. The database user of this schema must already exist, and this schema name must already be mapped to the workspace which will be used to import the application.

procedure set_schema( p_schema in varchar2 );

function get_schema return varchar2;

Name

Sets the application name of the application to be imported.

procedure set_application_name( p_application_name in varchar2 );

function get_application_name return varchar2;

Alias

Sets the application alias of the application to be imported. This will only be used if the application to be imported has an alias specified. An application alias must be unique within a workspace, and it's recommended to be unique within an instance.

procedure set_application_alias( p_application_alias in varchar2 );

function get_application_alias return varchar2;

Image Prefix

Sets the image prefix of the application to be imported. The default can usually be used, as most Application Express instances use the default image prefix of /i/.

procedure set_image_prefix( p_image_prefix in varchar2 );

function get_image_prefix return varchar2;

Proxy

Sets the proxy server attributes of the application to be imported.

procedure set_proxy( p_proxy in varchar2 );

function get_proxy return varchar2;

Clear

Clears all values currently maintained in the APEX_APPLICATION_INSTALL package.

procedure clear_all;

Examples

Using the workspace FRED_DEV on the development instance, you generate an application export of application 645 and save it as file f645.sql. All examples below assume you are connected to SQL*Plus.

To import this application back into the FRED_DEV workspace on the same development instance using the same application ID:

1 2 3

@f645.sql

To import this application back into the FRED_DEV workspace on the same development instance, but using application ID 702:

1 2 3 4 5 6 7 8 9 10

begin   apex_application_install.set_application_id( 702);   apex_application_install.generate_offset;   apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id ); end; /   @645.sql

To import this application back into the FRED_DEV workspace on the same development instance, but using an available application ID generated by Application Express:

1 2 3 4 5 6 7 8 9 10

begin   apex_application_install.generate_application_id;   apex_application_install.generate_offset;   apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id ); end; /   @f645.sql

To import this application into the FRED_PROD workspace on the production instance, using schema FREDDY, and the workspace ID of FRED_DEV and FRED_PROD are different:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

declare     l_workspace_id number; begin     select workspace_id into l_workspace_id       from apex_workspaces      where workspace = 'FRED_PROD';     --     apex_application_install.set_workspace_id( l_workspace_id );     apex_application_install.generate_offset;     apex_application_install.set_schema( 'FREDDY' );     apex_application_install.set_application_alias( 'FREDPROD_APP' ); end; /   @f645.sql

To import this application into the Training instance for 3 different workspaces (each workspace with their own schema):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

declare     l_workspace_id number; begin     select workspace_id into l_workspace_id       from apex_workspaces      where workspace = 'TRAINING1';     --     apex_application_install.set_workspace_id( l_workspace_id );     apex_application_install.generate_application_id;     apex_application_install.generate_offset;     apex_application_install.set_schema( 'STUDENT1' );     apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id ); end; /   @f645.sql   declare     l_workspace_id number; begin     select workspace_id into l_workspace_id       from apex_workspaces      where workspace = 'TRAINING2';     --     apex_application_install.set_workspace_id( l_workspace_id );     apex_application_install.generate_application_id;     apex_application_install.generate_offset;     apex_application_install.set_schema( 'STUDENT2' );     apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id ); end; /   @f645.sql   declare     l_workspace_id number; begin     select workspace_id into l_workspace_id       from apex_workspaces      where workspace = 'TRAINING3';     --     apex_application_install.set_workspace_id( l_workspace_id );     apex_application_install.generate_application_id;     apex_application_install.generate_offset;     apex_application_install.set_schema( 'STUDENT3' );     apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id ); end; /   @f645.sql

Now a final word of caution - with great power comes great responsibility. You should almost never set the offset value yourself unless you absolutely know what you're doing. One of the primary benefits of letting Application Express generate the meta data offset value for you is you avoid the possibility of any "collisions" with the meta data of any other application on any other APEX instance on the planet. If you have no known reason to manually set the offset value, then simply let Application Express set it for you.

In summary, the APEX_APPLICATION_INSTALL API in Application Express 4.0 now enables you to overcome a limitation in all previous versions of Application Express - namely, to take an arbitrary application export file and import it into any workspace on any arbitrary Application Express instance using SQL*Plus or any other command-line tool.

Welcome Mike Hichwa to the Blogosphere!

My manager since 1999, Mike Hichwa, has finally decided to join the blogosphere. Mike is the visionary and driving force behind Oracle Application Express and has been since he authored the very first line of APEX (then called "Flows") in 1999. Mike is the Vice President of Database Tools and is responsible for Application Express, SQL Developer, .NET tools, Java in the database, and others.