Tuesday, July 20, 2010

APEX_APPLICATION_INSTALL

Overview

Upload and installation of an application export file via the Web interface of Application Express.

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:

@f645.sql

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

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.

What about supporting objects? They can't be installed via the command line can they? If not, could any procedures be added for installs/upgrades? Or is it just assumed that if you're using the command line you're capable of write the scripts manually?

No - supporting objects aren't installed via the command line. Granted, it would be ideal to have one single point of truth for installation, regardless if an application is imported via the Web or via SQL*Plus or SQL Developer, but that's not the case. The "alternative" is execution of a SQL script to create your database objects, as everyone is accustomed to.

Unfortunately, what I do NOT see is any reference to importing an exported page or set of pages.

In my situation, we need to import over 40 pages from a development Apex app (which contains over 200 pages) into a production Apex app.

I see no way how to do this except for the tedious method of manually modifying the export sql files for each page and then (hope for the best) import these modified sql files into my target (production) app.

I had hoped that this new PLSQL package would work with individual pages rather than just the entire application.

Just to let you know the example you gave uses different names for each TRAINING workspace (1,2, and 3). In the documentation (http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_app_inst.htm#CHDEBIAG) it just repeats it as TRAINING1 instead of 1,2, and 3. Can you please pass along to the appropriate person?

I have one APEX environment, with a workspace per developer. I import the same application (under a different application id) into each of those developer workspaces. What will happen if we don't generate an offset during import? Will future object ID's collide when we merge each developers work into version control?

Using generate_offset during import works great, but has a disadvantage that each developer's export, after an export split, contains new ID's, making each version totally different from the version under version control.

In general, can you tell something about how those 20 digit long id's are generated under the covers?

>> What will happen if we don't generate an offset during import? Will future object ID's collide when we merge each developers work into version control?

Even if you choose a different application ID, if you don't choose some different offset for each of the developers (assuming you're importing the same application from the same application export file into each workspace), then you won't even get past the second import. The ID's of the metadata on the import into the second workspace will collide with the ID's of the import into the first workspace.

Why don't you do this? Why don't you choose some arbitrarily high offset per developer - 111111111 for the first one, 22222222 for the second one, and so on. Then, on subsequent imports, instead of calling apex_application_install.generate_offset, you would simply call apex_application_install.set_offset - setting the offset to 111111111, 222222222, etc. This way, they all remain consistent.

Using specific offsets for each developer is indeed better than using random offsets using generate_offset, but still has the disadvantage that an export from a developer workspace and a subsequent export split, will lead to every file being different due to the offset, even if you only changed one object. Which leads to a lot of overhead in your version control system.

My goal is to develop completely in parallel, with each developer having its own APEX workspace and its own database schemas.

Suppose each developer -as you recommended- gets its own specific offset during import. And after the developer completes his work, he exports his application and wants to put it back in version control. We parse the export file and subtract the same offset as used during import from all ID's in the export file. Then we split the export file and now most of the files will be the same as the original, and only the real changes will appear in version control. This will work for sure with one developer, but will it work with several colleagues at the same time at the same application? It depends on how object ID's are generated. Will ID's of new objects collide if -for example- I add a new page 1 and my colleague developer adds a new page 2?

Can you tell a bit more about the 20 digit long ID's and how you make them unique? Are they generated using a sequence or using (yuck) nvl(max(id),0)+1, or completely random. I studied the export file and its ID's and none of the above seems to apply. But what does?

I think it's going to be a bit of a challenge to have developers works independently and then yet collaborate on effectively the same application. Your biggest conflict may be in the use of Shared Components - unless you propagate all shared components to all environments, and keep them up to date, then you're going to have conflicts. If 2 developers create an LOV named "EMP_LOV", which one is used? How about page templates? Authentication schemes?

Must you segregate this by workspace? Can you have the developers work on distinct applications, all in the same workspace? Can you have them work on the same application and then segregate their work by page ID range?

The synthesized almost globally unique ID is based on a sequence, some random number, and time:

Thanks for your comment. My apologies, especially to Elie, for not addressing her comment. It was an unintentional oversight.

As you may know, you can already export individual pages. And you can directly import these into another APEX instance, assuming the workspace ID and application ID are identical. However, in the case that the application ID and workspace ID are different across two instances, you should be able to use the APEX_APPLICATION_INSTALL API's to import a page into the target application. To make sure everything is in sync, you'd have to compute and properly set the workspace ID, application ID and offset, but that's all that should be required.

I've just scanned a page export file, and I *believe* this should work. Let me test it out on Wednesday and I'll followup with my findings.

You set the character set portion of the local NLS_LANG environment variable equal to the file character set *prior* to initiating SQL*Plus.

For example, if you're in France and your file is encoded in character set windows-1252, you would set your client NLS_LANG environment variable to FRENCH_FRANCE.WE8MSWIN1252. You could even set it to AMERICAN_AMERICA.WE8MSWIN1252 if you wanted to. It's the character set portion of this variable that is most relevant for the file encoding. As another example, if your file was encoded in utf-8, you could set the NLS_LANG environment variable to FRENCH_FRANCE.AL32UTF8.

When I say "local client", I mean the local environment where I'm starting up SQL*Plus and running the application import.

You don't say anything about the specific APEX version you're using. But my guess, on such little information, is that you're encountering a bug fixed in a later patch set of APEX 4.2, where a translation mapping in another workspace is concealing the existence of the application ID in your workspace.