DBA Hub

📋Steps in this guide1/8

Liquibase : Deploying Oracle Application Express (APEX) Applications

This article demonstrates how to deploy Oracle Application Express (APEX) applications using Liquibase.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Liquibase Pro (runWith)

The Liquibase default JDBC connector is fine for running simple SQL files, but it struggles with more complex files, especially those including SQL*Plus-specific settings and commands. This makes trying to load an Oracle APEX workspace or application export file impossible without a lot of editing. In the past your options were as follows. - Export to a single file and manually edit it, removing SQL*Plus-specific commands and providing a valid split string between calls, which you would reference in the attribute of the tag. - Export to multiple files, and build a potentially complex change log to call all the resulting files in the correct sequence. Both options were quite frankly annoying. Liquibase 3.10.0 has a new feature that allows you to offload some of the work to an "executor", like SQL*Plus. This means you can process regular SQL files as normal, but if you hit a tricky situation, such as APEX export files, you can use SQL*Plus to do the the heavy lifting. Liquibase allows you to define custom executors, but the SQL*Plus executor is there by default in the Pro edition. All you need to do is add the attribute to the relevant tag. This article provides a simple example of how you might approach that.
2

Assumptions

This article makes some assumptions about what you have available and your preexisting knowledge. - You already have a basic understanding of Liquibase. If not, you would be better to start by reading this article . - You have a basic understanding of APEX, and how to manually export/import applications. - You have a functioning APEX installation to work with. - You have a download of Liquibase 3.10.0 or above. You need the Pro edition for some parts of this article to work. You can use a free trial to try this out. - You have a Java Runtime Environment (JRE) suitable for running Liquibase. - The machine running Liquibase has a valid SQL*Plus installation available in the environment variable.
3

Liquibase Environment

Unzip liquibase into a directory of your choice. Make sure the environment variable includes the Liquibase directory and the environment variable includes the location of the JRE or JDK. Adjust the paths as required. We need two properties files to hold our database connection information, as well as the Liquibase Pro license key. The "apex_priv_user_liquibase.properties" file contains the following properties. These will be used to make connections to a user called , which will be used to run privileged actions, such as creating new users and APEX workspaces. The "dev_ws_liquibase.properties" file contains the following properties. These will be used to make connections to a user called , which is the APEX workspace user. Check SQL*Plus is available in the .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Rem Windows
set PATH=c:\software\liquibase\liquibase-3.10.0;%PATH%
set JAVA_HOME=c:\Program Files\Java\jre1.8.0_221

# Linux
export PATH=/u01/software/liquibase/liquibase-3.10.0:$PATH
export JAVA_HOME=/u01/java/latest

driver: oracle.jdbc.OracleDriver
classpath: /software/liquibase/liquibase-3.10.0/lib
url: jdbc:oracle:thin:@localhost:1521/pdb1
username: apex_priv_user
password: apex_priv_user
liquibaseProLicenseKey: {put your license key here}

driver: oracle.jdbc.OracleDriver
classpath: /software/liquibase/liquibase-3.10.0/lib
url: jdbc:oracle:thin:@localhost:1521/pdb1
username: dev_ws
password: dev_ws
liquibaseProLicenseKey: {put your license key here}

sqlplus -H
4

The Demo Files

The demos will use the following files. They are available from a GitHub repository ( here ). The privileged and normal operations are kept in a single repository for this demonstration, but you may wish to split the privileged operations off into a separate repository, depending the the trust level in your organisation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
+--- normal
|   +--- changelogs
|   |   +--- changelog_master_dev_ws.xml
|   |   +--- changelog_setup_demo_app_lb_pro.xml
|   |   +--- changelog_setup_tab1.xml
|   |   +--- changelog_setup_tab2.xml
|   +--- scripts
|   |   +--- demo_app_f101.sql
|   |   +--- get_tab1_count.sql
|   |   +--- tab1.sql
|   |   +--- tab1_seq.sql
|   |   +--- tab2.sql
|   |   +--- tab2_seq.sql
+--- privileged
|   +--- changelogs
|   |   +--- changelog_create_dev_workspace_lb_pro.xml
|   |   +--- changelog_create_dev_workspace_user.xml
|   |   +--- changelog_master.xml
|   +--- scripts
|   |   +--- create_apex_priv_user.sql
|   |   +--- create_dev_workspace.sql
|   |   +--- create_dev_workspace_user.sql
|   |   +--- remove_apex_priv_user.sql
|   |   +--- remove_dev_workspace.sql
|   |   +--- remove_dev_workspace_user.sql
5

Create Privileged User

In any deployment process you will occasionally need to make changes that require elevated privileges. You can choose to leave these as manual operations, or you can automate them with Liquibase. In this example we want a user called to perform privileged actions and administer the APEX instance. You may prefer to split these roles into separate users. The "create_apex_priv_user.sql" file contains the commands to create this privileged user. The addition of allows the user to administer the APEX instance. In this case we've included the role to allow other privileged actions, which is rather excessive. You need to grant the least privileges necessary for the user to perform the operations you need, which may vary on a project-by-project basis. Needless to say, this should really have a strong password and have a restricted audience. With this user in place, all the following actions can be performed using Liquibase.

Code/Command (click line numbers to comment):

1
2
create user apex_priv_user identified by apex_priv_user quota unlimited on users;
grant dba, apex_administrator_role to apex_priv_user;
6

Create a New APEX Workspace

We are going to split the creation of a new workspace into two parts. We use the "create_dev_workspace_user.sql" script to create a new user for the workspace. The "changelog_create_dev_workspace_user.xml" change log for this action is shown below. It uses a standard tag to reference the script. The "create_dev_workspace.sql" file contains an APEX workspace definition. This was manually created in an APEX environment, then exported so it could be replayed in other environments. The "changelog_create_dev_workspace_lb_pro.xml" change log for this action is shown below. It uses a standard tag to reference the script, but notice the attribute in the tag. This tells Liquibase to execute this this using SQL*Plus, not the default JDBC connector. We add these two changesets to the "changelog_master.xml" file for the privileged user. Using this and the "apex_priv_user_liquibase.properties" file we created earlier, we can now create the workspace user and the associated APEX workspace by running the following Liquibase command. If you check your APEX instance, you will see the workspace is now present. You can log into it with the "Dev1Workspace2!" password, then you'll be prompted to reset it.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
create user dev_ws identified by dev_ws quota unlimited on users;
grant create session, create cluster, create dimension, create indextype,
      create job, create materialized view, create operator, create procedure,
      create sequence, create synonym, create table,
      create trigger, create type, create view to dev_ws;

<?xml version="1.0" encoding="UTF-8"?> 
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">

    <changeSet author="tim" id="create_dev_workspace_user">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/create_dev_workspace_user.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?> 
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">

    <changeSet author="tim" id="create_dev_workspace_v1"
runWith="sqlplus"
>
      <sqlFile dbms="oracle"
               path="../scripts/create_dev_workspace.sql"
               relativeToChangelogFile="true"/>
    </changeSet>
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
  <include file="./changelog_create_dev_workspace_user.xml" relativeToChangelogFile="true"/>
  <include file="./changelog_create_dev_workspace_lb_pro.xml" relativeToChangelogFile="true"/> 
</databaseChangeLog>

Rem Windows
liquibase --defaultsFile="c:\software\liquibase\apex_priv_user_liquibase.properties" ^
          --changelog-file="c:\git\oraclebase\liquibase_apex_demo\privileged\changelogs\changelog_master.xml" ^
          update

# Linux
liquibase --defaultsFile="/u01/software/liquibase/apex_priv_user_liquibase.properties" \
          --changelog-file="/u01/git/oraclebase/liquibase_apex_demo/privileged/changelogs/changelog_master.xml" \
          update
7

Create a New APEX Application

With the APEX workspace in place, we can create some objects and an APEX application to reference those objects. Most of the SQL files for the supporting objects are straight from this article , so I won't waste time explaining them again. They create some tables that will be referenced by the APEX application. Instead we will focus on the APEX-specific piece. The "demo_app_f101.sql" file contains a really basic APEX application called "Demo App", which we'll use to demonstrate the deployment process. The "changelog_setup_demo_app_lb_pro.xml" change log shows how to load this file. Similar to the workspace example, we use the attribute in the to offload the work to SQL*Plus. We include this change log into the "changelog_master_dev_ws.xml" file, which is the master change log for this environment. Using this and the "dev_ws_liquibase.properties" file we created earlier, we can now create the APEX application, along with the supporting objects using the following command.. If you check your APEX instance, you will see the workspace now includes an application called "Demo App".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?xml version="1.0" encoding="UTF-8"?> 
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">

    <changeSet author="tim" id="demo_app_v1" runOnChange="true"
runWith="sqlplus"
>
      <sqlFile dbms="oracle"
               path="../scripts/demo_app_f101.sql"
               relativeToChangelogFile="true"/>
    </changeSet>
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
  <include file="./changelog_setup_tab1.xml" relativeToChangelogFile="true"/> 
  <include file="./changelog_setup_tab2.xml" relativeToChangelogFile="true"/>
<include file="./changelog_setup_demo_app_lb_pro.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>

Rem Windows
liquibase --defaultsFile="c:\software\liquibase\dev_ws_liquibase.properties" ^
          --changelog-file="c:\git\oraclebase\liquibase_apex_demo\normal\changelogs\changelog_master_dev_ws.xml" ^
          update

# Linux
liquibase --defaultsFile="/u01/software/liquibase/dev_ws_liquibase.properties" \
          --changelog-file="/u01/git/oraclebase/liquibase_apex_demo/normal/changelogs/changelog_master_dev_ws.xml" \
          update
8

Clean Up

The "remove_*.sql" files have been provided to allow you to clean up the demo. Collectively they perform the following commands. For more information see: - Liquibase - Liquibase : All Articles - Liquibase : Deploying Oracle Application Express (APEX) Applications - Liquibase : Automating Your SQL and PL/SQL Deployments Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
-- Run as the APEX_PRIV_USER.
begin
  apex_instance_admin.remove_workspace('DEV_WS', 'N', 'N');
end;
/

-- Run as another privileged user.
drop user dev_ws cascade;
drop user apex_priv_user cascade;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!