Importing APEX pages and components

Importing whole applications vs. importing pages and components

This article describes the import of APEX pages and shared components. This operation is relevant when deploying modifications from the development environment to the production environment.

One of the most used and simple ways of transferring modifications to the APEX application into another environment is a complete recreation of the workspace and application. The steps are not subject of this document, but the major drawbacks from this method are:

–          The workspace in the destination environment is deleted, along with all associated applications, and recreated

–          The applications from the workspace need  to be imported again, even if we only want to deploy only one of the applications from the previously deleted workspace

–          All these operations take time and make the applications unavailable during the import

So, a simpler way is to apply modifications to an application stored in a different workspace, component by component and page by page, if the total number of new/modified pages or components is not close to the total number of components and pages in the application (in this case, a full application import will be more suitable).

The main concern and the scope of this document is importing pages or shared components from a development environment to a production environment. Usually, in the production mode we have all applications installed in runtime mode and the APEX installation itself does not have an Admin section, so all operations must be done using PLSQL and SQL scripts.

Import considerations

When importing a page or a shared component from one application to another, it is very important to determine the following:

1.       Is the destination application in the same workspace as the source?

2.       If the workspaces  (APEX instances and so on) are different, do the destination workspace, applications, components and so on, have the same IDs as the source ones?

These cases are very important, as the import will be done differently in each case!

Exporting pages and shared components

The export of pages and shared components from an Apex application can be done very easy from the APEX development GUI.

Exporting a page

A page export can be done via the Export icon on the top right of the Page in Edit Mode and Definition View.

Or by switching the Edit Mode of the page in the “Export” View:

In both cases, the result will be a SQL script that will be saved on the disk, with the name fxxx_page_yyy.sql, where xxx is the application ID and yyy is the page id.

Exporting shared components

The shared components export can be done from the Share Components screen in Application Builder. On the right side on the screen, on the “Tasks” region is the Export Application Components link:

Shared components that can be exported, in relevance with the typical Aegon application structure are:

–          Tabs

–          Lists

–          Trees

–          Navigation bar entries

–          Themes

–          Templates

–          Report queries

–          Report Layouts

–          List of values

Importing pages and shared components

Importing from DEV to PROD when workspace and all objects ID are identical

Usually, when we deploy for the first time an application, along with the workspace, on the production (PROD) environment, it must be done using the same workspace ID as the one from the development environment. This will keep the two applications, from DEV and PROD, in sync regarding the workspace ID, applications ID, shared components ID and so on. This is very useful and very important for the import, because no modifications to the import scripts are necessary!

To import the exported pages and shared components in the PROD environment, we just run the export scripts from SQLPLUS connected as the SYS or APEX_PUBLIC_USER to the destination database.

Example:

We have to exports scripts:

–          F100_page_10.sql, for page 10 of application 100

–          F100_components.sql, for the shared components of application 100

Step 1.Connect to the destinations Database with the sys user:

C:\sqlplus /nolog

SQL>connect sys/syspassword@proddb as sysdba

Step 2.Run the export scripts, disregarding the order:

SQL>@F100_page_10.sql;

SQL>@F100_components.sql;

The export should be successful and the modifications from DEV should be in PROD too.

Importing from DEV to PROD when workspace and objects IDs are different

When we import pages and shared components into an environment that was first deployed with a different Workspace ID , some modifications to the import SQL file must be done. As a result, the IDs of the pages, items, templates and other components may differ and an import proves to be difficult, if not impossible.

But as a standard procedure, before importing a page or a shared component, the export files must be checked and modified. A typical export script for a page looks like this:

set define off

set verify off

set serveroutput on size 1000000

set feedback off

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK

begin wwv_flow.g_import_in_progress := true; end;

/

–application/set_environment

prompt  APPLICATION 599 – WEBM

— Application Export:

—   Application:     599

—   Name:            WEBM

—   Date and Time:   10:11 Tuesday October 6, 2009

—   Exported By:     ADMIN

—   Flashback:       0

—   Export Type:     Page Export

—   Version: 3.1.2.00.02

— Import:

—   Using application builder

—   or

—   Using SQL*Plus as the Oracle user FLOWS_030100 or as the owner (parsing schema) of the application.

—       AAAA       PPPPP   EEEEEE  XX      XX

—      AA  AA      PP  PP  EE       XX    XX

—     AA    AA     PP  PP  EE        XX  XX

—    AAAAAAA AAA    PPPPP   EEEE       XXXX

—   AA        AA   PP      EE        XX  XX

—  AA          AA  PP      EE       XX    XX

—  AA          AA  PP      EEEEEE  XX      XX

prompt  Set Credentials…

 

begin

 

— Assumes you are running the script connected to SQL*Plus as the Oracle user FLOWS_030100 or as the owner (parsing schema) of the application.

wwv_flow_api.set_security_group_id(p_security_group_id=>1274323608806696);

prompt  Set Application ID…

begin

 

— SET APPLICATION ID

wwv_flow.g_flow_id := 598;

wwv_flow_api.g_id_offset := 0;

null;

 

end;

/

 

In this example, we may to manually modify the p_security_group_id, g_flow_id and g_id_offset parameters.

The p_security_group_parameter is actually the workspace ID; if it differs in the destination environment, than it must be changed accordingly, to export the page in the correct workspace. Also, if the g_flow_id (application ID) differs, then it must be changed also.

Unfortunately, a complete check of the shared components and object IDs in the export file must be done in this care, to be sure that the pages we wish to import and it’s components and items, have the same IDs on the destination environment. Otherwise, the checksum of the page will be corrupted and the page will be unusable.

Importing pages in the same workspace and/or application

The operations for importing (copying) pages inside the same workspace and/or application is the same as described above. But in this case also, we must manually correct the export file.

After we opened the export file we must  look for the following lines:

— SET APPLICATION ID

wwv_flow.g_flow_id := 520;

wwv_flow_api.g_id_offset := 0;

 

If we want to import the page in another application, we must manually update thewwv_flow.g_flow_id parameter>

wwv_flow.g_flow_id := 120;

If we import the page into the same application, chances are that – even if the script deletes the page before importing it – the import script will fail because it will try to re-create page plugs that already exist. To fix this, we must also update the offset with a random value:

— SET APPLICATION ID

wwv_flow.g_flow_id := 520;

wwv_flow_api.g_id_offset := 11;

 

This will ensure that the import script will run without error and the page will be created. But, again, one more issue will appear: the page will be created without the original templates. The templates will also not be associated to any items contained in the page.

This happens because of the wwv_flow_api.g_id_offset value being applied to the creation of an item, like this:

wwv_flow_api.create_page_button(

p_id             => 9601238436401455 + wwv_flow_api.g_id_offset,

p_flow_id        => wwv_flow.g_flow_id,

p_flow_step_id   => 100,

p_button_sequence=> 20,

p_button_plug_id => 9582432045762662+wwv_flow_api.g_id_offset,

p_button_name    => ‘CLONARE_OFERTA’,

p_button_image   => ‘template:’||to_char(9381740039361030+wwv_flow_api.g_id_offset),

p_button_image_alt=> ‘Genereaza Clona’,

p_button_position=> ‘BOTTOM’,

p_button_alignment=> ‘LEFT’,

p_button_redirect_url=> ”,

p_button_condition=> ‘(select count(*) from V_OFFR_LOV )>1 and :P100_COD_OFERTA is not null;’,

p_button_condition_type=> ‘SQL_EXPRESSION’,

p_required_patch => null + wwv_flow_api.g_id_offset);

 

The procedure call for creating a button (above), uses wwv_flow_api.g_id_offset in 3 parameter calls:

–          P_id, to generate the new object ID, but here it’s irrelevant if the offset is 0

–          P_button_plug_id, where the offset must not be 0 if the import the page in the same application where it was exported

–          P_button_image, where the offset determines the template to be lost. In this line we must delete +wwv_flow_api.g_id_offset , to ensure the template ID stays the same. This operation must be done for all objects for the import script! Attention, do not remove the offset from other procedure/function calls, that have nothing to do will application or page templates!

 

Marking the application as unusable

Usually, when we import multiple pages and shared components, we may want to make the application unusable by the end/users and developers during the import.

For this we call the following scripts, before calling the import files (also connected to the destination DB with SYS or APEX_PUBLIC_USER via sqlplus):

declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

wwv_flow_api.set_security_group_id(p_security_group_id=>994313389747514);

wwv_flow_api.set_flow_status (p_flow_id=>120,p_flow_status=>’UNAVAILABLE’);

commit;

end;

 

After the import scripts were executed successfully we mark the application as available again:

declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

wwv_flow_api.set_security_group_id(p_security_group_id=>994313389747514);

wwv_flow_api.set_flow_status (p_flow_id=>120,p_flow_status=>’AVAILABLE’);

commit;

end;

Leave a Reply