How to reduce Merge Conflicts of exported APEX Apps

When exporting an APEX app by a tool like SQLcl splitted in separated files, each SQL file will include arguments inside the Metadata export which can differ when exporting from different APEX instances. Depending on the amount of generated files, this may cause a huge number of merge conflicts when versioning the exported app in a version control system like Git or Apache SVN, which have to be resolved manually. In this blog post, I would like to show an example how to reduce the amount of conflicts.

If you are using a tool for exporting the Apps, you may include a script which removes information that is not needed (or could be replaced) during an import. Most of the merge conflicts are caused by arguments like “p_default_id_offset” as well as “Manifest” information. While the Offset depends on the APEX instance from which you have exported, the Manifest gives various information about a single application component, and therefore usually remains identical. However, if different Application IDs are used on multiple APEX instances for the same application, some of this information (which relates to these IDs) may also differ between exports.

Based on the components your APEX app consists of, the folder structure can look different. While the application folder will always exist and which stores all application resources, including pages, build options, files and other related logics, your export could also include other folders like workspace. This is the case if you are using workspace related resources inside your application logics (e.g. Web credentials).

The Bash script below will do the following actions for all SQL files inside the given subfolders:

1. Remove all Manifest information
2. Replace any value for Default Offset Id with 0

#!/bin/bash

# Go into the apex folder of application
# this is where the install.sql file is located
# Then run below commands for all sql files in specified subfolders

# Adjustments:
#   Manifest:  Delete every Manifest information
#   Offset Id: Replace any value of p_default_id_offset with 0

APEX_APP_BASE_FOLDER="apex"
APEX_APP_SEARCH_SUBFOLDERS=("application" "workspace")

for subfolder in "${APEX_APP_SEARCH_SUBFOLDERS[@]}"
do
    if [ -d ./"$APEX_APP_BASE_FOLDER"/"$subfolder" ]; then
       find ./"$APEX_APP_BASE_FOLDER"/"$subfolder" -type f -name "*.sql" -exec sed -i -e '/--   Manifest/,/--   Manifest End/d' -i -e 's/p_default_id_offset.*/p_default_id_offset=>0/g' {} +
    fi
done

The Manifest contains information about to which resource the component belongs to (e.g. App Id). This information is not needed for the import. The OffsetId is always linked to an APEX instance and ensures that the metadata for the Oracle APEX application definition does not collide with other metadata on the instance. This means, that the Offset value should always differ, especially if you are importing the same application (or components) to several workspaces on the same APEX instance. If you set the OffsetId to 0, you need to make sure that you are setting this Offset value – or even better, that APEX generates an appropriate one for you – before the APEX app gets imported. The Offset needs to be a positive integer. You have two procedures and one function in the package wwv_flow_application_install for generating, setting and getting the OffsetId value. They are accessable over the synonym APEX_APPLICATION_INSTALL.

-- Generate a new Offset value before application import
begin
  APEX_APPLICATION_INSTALL.GENERATE_OFFSET;
end;

-- Set the Offset value manually
declare
  v_offset_id NUMBER;
begin
  v_offset_id := DBMS_RANDOM.VALUE(100000000000, 999999999999); -- may cause collisions
  APEX_APPLICATION_INSTALL.SET_OFFSET(p_offset => v_offset_id);
end;

-- Get the current Offset value
declare
  v_offset_id NUMBER;
begin
  v_offset_id := APEX_APPLICATION_INSTALL.GET_OFFSET;
end;