Oracle APEX: Command line Export/Import of application

about.md

Oracle APEX: Command line export/import of application

Below are 2 scripts to export and import applications via command-line. This is very useful in cases where you want to automate the backups as part of a nightly job or you need a scripted method of importing application.

app_export.sh

This bash script takes a export of of your Oracle APEX application using the command-line utility and (additionally) back-up by pushing it to a git repository.

app_import.sql

This PL/SQL script imports an APEX application. It should be run via SQL Plus. To get started, configure the below 3 variables in the script.

l_workspace_name varchar2(100) := 'GEOCODER';
l_application_id number := 502;
l_parsing_schema varchar2(100) := 'GEOCODER';

Installing git

yum install curl-devel expat-devel gettext-devel \ openssl-devel zlib-devel
 
wget https://git-core.googlecode.com/files/git-1.9.0.tar.gz
tar -xvzf git-1.9.0.tar.gz
cd git-1.9.0
 
make prefix=/usr/local all
make prefix=/usr/local install

app_export.sh

#!/bin/bash

# About:
# This script will take backup of your Oracle APEX application and upload to git SCM
# This should be ideally be run via crontab on regular interval.
# Pre-Requisites:
#  1. Ensure git is installed and repository initialised in BACKUP_DEST
#  2. Configure the variables below where "Required Info" is specified
#
# If you dont want to back to SCM, remove the bottom part where it uploads to GIT
# 
# License: MIT
#

# Required Info - Ensure DB_PASS is set in your bashrc
cd ~/ && . .bashrc

# Required Info - Update this for your env
DB_HOSTNAME=localhost							
export DB_HOSTNAME

# Required Info - Update this for your env
DB_PORT=1521
export DB_PORT

# Required Info - Update this for your env
DB_SERVICE_ID=XE
export DB_SERVICE_ID

# Required Info - Update this for your env
APP_ID=100
export APP_ID

# Required Info - Update this for your env
APP_PARSING_SCHEMA=MY_SCHEMA
export APP_PARSING_SCHEMA

# Required Info - Update this for your env
BACKUP_DEST=/home/oracle/apex_application_backups
export BACKUP_DEST


# Required Info - Update this for your env
# Apex Install directory
APEX_HOME=/home/oracle/staging/apex
export APEX_HOME

# Required Info - Update this for your env
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME

CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar:./
export CLASSPATH

#######################
## Export run from here
#######################

cd $APEX_HOME/utilities

echo Running Export

# Run the actual export
# NOTE: Double check the Java path below and adjust as per your environment
/home/oracle/jdk1.7.0_51/bin/java oracle.apex.APEXExport -db $DB_HOSTNAME:$DB_PORT:$DB_SERVICE_ID -user $APP_PARSING_SCHEMA -password $DB_PASS -expPubReports -expSavedReports -expIRNotif -applicationid $APP_ID

echo Export complete. Will run backup now.

# Copy to backup destination
cp f100.sql $BACKUP_DEST

echo Removing the export from $APEX_HOME/apex/utilities after copy
rm f100.sql

echo ....removed

cd $BACKUP_DEST

_now=$(date +"%Y%m%d")

/usr/local/bin/git add *
/usr/local/bin/git commit -m "Scheduled backup via crontab "$_now
/usr/local/bin/git push origin master

echo Oracle APEX application export and backup complete.

app_import.sql

-- Connect via SQL Plus and import Oracle APEX application via Command line.
-- License: MIT

set serveroutput on
WHENEVER SQLERROR EXIT SQL.SQLCODE
declare
	l_workspace_id number;
	-- command line configuration 
	-- ** MODIFY WITH CAUTION - CAN BREAK/OVERWRITE OTHER APEX APPLICATIONS IF CONFIGURED WRONG **
	l_workspace_name varchar2(100) := 'GEOCODER';
	l_application_id number := 502;
	l_parsing_schema varchar2(100) := 'GEOCODER';
begin
	select workspace_id into l_workspace_id
	from apex_workspaces
	where upper(workspace) = upper(l_workspace_name);
	 
	apex_application_install.set_workspace_id( l_workspace_id );
	apex_application_install.set_application_id(l_application_id);
	apex_application_install.generate_offset;
	apex_application_install.set_schema( l_parsing_schema );
	apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;

/
@f502.sql