Exporting APEX with Original IDs

October 6, 2023 cruepprich

When exporting an APEX application, there is an option to Export with Original IDs. This option can be selected on the export page in the UI, or you can add it as a parameter to the APEX export command in SQLcl.

Let’s take a look what happens behind the scenes during an export when this option is selected.

About IDs

APEX components are stored in the APEX instance’s metadata tables, with their component IDs serving as the primary key.

For example application 100 has a page item P1_NAME. The ID for P1_NAME can be seen in the metadata view apex_application_page_items:

select item_id
  from apex_230100.apex_application_page_items 
 where application_id = 100
   and item_name = 'P1_NAME';

             ITEM_ID
____________________
   17314956676049404

When exporting the application, the resulting export file, f100.sql, will show the same ID passed to the wwv_flow_imp.id() function:

wwv_flow_imp_page.create_page_item(
p_id=>wwv_flow_imp.id(17314956676049404)
,p_name=>'P1_NAME'
...

About Importing an APEX Application

When importing an APEX application, the import process needs to assure that no duplicate component IDs are used, as this would cause primary key constraint violations. This is not an issue, when replacing an application, i.e. importing f100.sql with application ID 100. However, when the f100.sql file is imported again into the same APEX instance but under a different application ID, a new ID for P1_NAME needs to be calculated in order to not violate that primary key constraint .

When importing via the UI, this calculation is done automatically. However, when importing via SQLcl, that calculation is not done automatically, and can therefore cause a primary constraint violation error if another application ID is used.

How the IDs are Calculated

The IDs are calculated using an offset value. When importing via the UI, an offset value is generated randomly, and then added to all the component IDs, thus incrementing the component IDs by the offset values.

When importing via SQLcl, the offset value needs to be set first via a PL/SQL procedure call. The generate_offset procedure, will generate the value randomly. You can also use the set_offset procedure which allows you to specify the offset number manually. Be careful with this option. If the number you specify is too small, you can still run into primary key violations.

The offset value is stored in the application’s metadata.

Example:

I want to import f100.sql into the same APEX instance (workspace doesn’t matter) under a different application ID (200).

When the import proceeds, the offset value is generated and then added to all the component IDs of the application. So if I import this application as application ID 200 and query the apex_application_page_items view, I’ll see a different ID for P1_NAME:

select item_id
  from apex_230100.apex_application_page_items 
 where application_id = 200
   and item_name = 'P1_NAME';

             ITEM_ID
____________________
   27148398050234465

When I export application 200 without original IDs, the new ID is written to the export file:

wwv_flow_imp_page.create_page_item(
p_id=>wwv_flow_imp.id(27148398050234465)
,p_name=>'P1_NAME'
...

When I now export application 200, and choose the Export with Original IDs option, the f200.sql file will show the original ID from f100.sql

wwv_flow_imp_page.create_page_item(
p_id=>wwv_flow_imp.id(17314956676049404)
,p_name=>'P1_NAME'
...

Importing vi SQLcl

In this example, I’ll show what happens when I import f100 with a new application ID, but without setting an offset value. This will result in primary key constraint violations because the same component IDs are trying to be inserted for a different application ID.

SQL> begin
2    apex_util.set_workspace('FOO');
3    apex_application_install.set_application_id(200);
4 end;
/
SQL> @f100.sql
--application/set_environment
[...]
ID offset during import: 0
New ID offset for application: 0
 
APPLICATION 100 - demo
--application/delete_application
[...]
Error report -
ORA-20001: call=wwv_flow_imp_shared.create_list, id=9534072013038871, component=Navigation Menu, sqlerrm=ORA-00001: unique constraint (APEX_230100.WWV_FLOW_LISTS_PK) violated
ORA-06512: at "APEX_230100.WWV_IMP_UTIL", line 137
[...]

Note how the output shows ID offset during import: 0, causing no change in component IDs an therefore an ORA-00001. To fix this, i simply add a call to generate a new offset.

SQL> begin
2    apex_util.set_workspace('ACME');
3    apex_application_install.generate_offset;
5    apex_application_install.set_application_id(200);
6 end;
/
SQL> @f100.sql
--application/set_environment
API Last Extended:20230428
Your Current Version:20230428
This import is compatible with version: 20230428
COMPATIBLE (You should be able to run this import without issues.)
ID offset during import: 10932306388837821
New ID offset for application: 9930191121648313

APPLICATION 100 - demo
--application/delete_application
--application/create_application
--application/user_interfaces
[...]
... elapsed: 47.7 sec

...done
SQL>

Notice how the install output shows that the offset is now 10932306388837821, which is added to all the component IDs, and therefore sufficiently large enough to not cause any more primary key constraint violations.

Let’s validate the new ID for P1_NAME in application 200:

select item_id
  from apex_230100.apex_application_page_items 
 where application_id = 200
   and item_name = 'P1_NAME';

             ITEM_ID
____________________
   38080704439072286

In app 100, P1_NAME is 27148398050234465. The offset during import was 10932306388837821. Add the two together results in 38080704439072286.

Export with Original IDs

When exporting an application that was built with the APEX builder, that is, it was not previously imported, the resulting export file will record the same component IDs as recorded in the database, regardless whether Export with Original IDs used.

User Interface Export
SQLcl	`SQL> apex export -applicationid 100 -exporiginalids`

However, when exporting an application that was previously imported from an export file we have two options:

Export without original IDs (default): The component ID recorded in the database are used.
Export with original IDs: The component IDs are calculated back to the values of the export file from which the application was imported. This is done by taking the offset value that was used during the import and subtracting it from the current component IDs. Note that the offset value is also recorded in the database when the application is imported.

Example of Exporting with/without Original IDs

Create a new application 100 with page item P1_FOO. The item number for P1_FOO is recorded in the database as 123.
Export application 100 (doesn’t matter with or without original IDs). P1_FOO is written with ID 123 in the export file f100.sql.
Import f100.sql into the same APEX instance with application ID 200. This requires a different ID for P1_FOO. When using the UI to import, an offset is automatically generated. Let’s assume the offset value 333 was generated. That value is added to P1_FOO component ID 123 resulting in 456. So P1_FOO in application 200 now has component ID 456 (123+333).
Export application 200:
- Without original IDs: P1_FOO is written to export file f200.sql with ID 456.
- With original IDs: P1_FOO is written to export file f200.sql with ID 123, the original ID from application 100. It is calculated by subtracting the offset from the current ID: 456-333=123.

When Is Exporting with Original IDs Useful?

When using a source code control system like Git or SVN, exporting with original IDs can be useful to avoid complicated diff comparisons. If multiple developers work on copies of an application, they can export with original IDs and check that code in or compare it to the original export. That way, component IDs are ignored from the diffs.

Big thank you to Christian Neumüller for his help and guidance on this.