Tuesday, July 20, 2010

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:

@f645.sql

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

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:

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:

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):

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.

29 comments:

Anonymous said...

This is fantastic, much awaited feature, thanks for implementing it!

Byte64 said...

Cool stuff!

Thank you Joel.
Flavio

vgoncharov said...

This is a useless feature for multilingual applications, because APEX always lose translations when application ID is changed.

Martin D'Souza said...

Thanks Joel!

Joel R. Kallman said...

@vgoncharov

I don't know if I would say useless. You're able to use this to shift the meta data for translated applications as well.

What you're talking about is the loss of the XLIFF files once an application ID is changed, which this API APEX_APPLICATION_INSTALL was never intended to solve.

But sit tight - I have a solution for the problem you describe and I'll make it available (via my blog) in the next day or two.

Joel

Dan McGhan said...

Very nice Joel!

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?

Regards,
Dan

Joel R. Kallman said...

@Dan,

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.

Joel R. Kallman said...

@vgoncharov - Here you go, this blog post is for you: http://joelkallman.blogspot.com/2010/07/moving-your-xliff-files.html

Hopefully you don't deem this useless as well.

Joel

Anonymous said...

Minor typo in the examples - Should be apex_application_install.get_application_id instead of apex_application.get_application_id

Joel R. Kallman said...

@vikasa - thanks for pointing this out. I've corrected it.

Joel

Rene Westenberg said...

"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"

Does this mean that choosing an existing will completely replace the existing application? And thus can be used for updates?

Rene Westenberg said...

"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"

Does this mean that choosing an existing ID will completely replace the existing application? (usefull for updates)

Joel R. Kallman said...

@Rene,

>> Does this mean that choosing an existing ID will completely replace the existing application? (usefull for updates)

Yes - absolutely. I should have stated this so clearly, so thanks for doing so.

Joel

Elie said...

Hi, Joel.

I just now came across this blog about apex_application_install.

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.

Your thoughts?

Thank you.

Elie

Unknown said...

Hi Joel,

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?

Thanks,

Martin

Joel R. Kallman said...

Martin - thank you for pointing this out. I have updated the internal version of this documentation and it will be corrected the next time they publish it.

Thanks again for taking the time to report this.

Joel

Rob van Wijk said...

Hi Joel,

Thank you for this very useful blogpost.

I hope you can clarify some more about offset.

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?

Regards,
Rob.

Joel R. Kallman said...

Hi Rob,

Thanks for the feedback.

>> 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.

I hope this helps.

Joel

Rob van Wijk said...

Thanks for your quick response.

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?

Regards,
Rob.

Joel R. Kallman said...

@Rob,

Sorry for the delayed reply. This fell off the radar.

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:

select to_number(
to_char(wwv_seq.nextval) ||
lpad( substr( abs(wwv_flow_random.rand), 1, 5 ),5, '0' ) || ltrim(to_char(mod(abs(hsecs),1000000),'000000')))
into g_curr_val
from sys.v_$timer;

and wwv_flow_random.rand is an internal random number generator.

I hope this helps.

Joel

Rob van Wijk said...

Thanks Joel. This information is gold. I will test a few of these scenarios and see what works best for us.

Tony Fatouros said...

Hi Joel,

Came accross this thread and noticed that you never came back to Ellie on her comment about having a means of just exporting/importing individual pages.

This for me at the moment in v4.2.2 would've have been very useful and unsure why it wasn't considered.

Any updates on this and whether this feature will one day be available.

Thanks.
Tony.

Joel R. Kallman said...

Hi Tony,

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.

Joel

APEX Developer In the USA said...

Joel,
Quick question.. Do you for see APEX's command line install process in the future having the ability to handle installing supporting objects?

Thank you,

Tony Miller
LuvMuffin Software

Joel R. Kallman said...

Hi Tony,

Yes - that's the plan. And also install and published translated versions too.

Joel

Unknown said...

Hi Joel,

Question: is there a way to set file character set ?
Because the problem is that in french we have to deal with accents (é è à ç) ...

Thanks a lot,
Devi

Joel R. Kallman said...

Hi Devi,

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.

Does this make sense?

Joel

Any said...

I see My application id in www_flows table but not in apex_applications view.
Any idea what is wrong?
How to make my application id appear in apex_applications view.
Please help me to understand.

Joel R. Kallman said...

"Any",

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.

Joel