Ali Imtiaz aimtiaz11

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.

Commands to install git in Linux:

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

Killing Oracle Database Session

Run the following in SQL* Plus as SYS/SYSDBA user to terminate sessions.

** Update username with your schema name.

DECLARE
	CURSOR c1 is 
	SELECT s.inst_id,

Script to create Oracle Database tablespace & schema

Instructions

Run the script as SYS/SYSDBA. Script will create a new tablespace and schema and provide common grants to create user objects.

In order to kill database sessions, run the following scripts in SQL *Plus and then exit.

Replace SCHEMA_NAME with the name of your schema.

declare
  cursor c1 is
  SELECT s.sid, s.serial# as serial
  FROM v$session s, v$process p
  WHERE s.username = 'SCHEMA_NAME'

Extracting Lines from File

Between 2 line intervals:

 sed -n 2601,2700p 8k_users.csv > logins27.csv

The following function need to placed at the top of the script before it is called.

# $1 = success message
# $2 = failure message
check_err()
{
 if [ $? -gt 0 ]; then

	<link rel="stylesheet" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.tabs.css" type="text/css" />
	<script src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.tabs.min.js" type="text/javascript"></script>

	<script type="text/javascript">

	// Run on page load
	apex.jQuery(function() {

	// Before we invoce the tab() method to initialise the tabs,
	// we will need to create a tab entry as first item which will be "Show All"

	CREATE OR REPLACE FUNCTION "ITEM_IN_LIST" (p_num IN NUMBER, p_list in VARCHAR2, p_separator in varchar2 default ':')
	RETURN NUMBER
	IS
	l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2;
	TYPE number_list is TABLE OF NUMBER;
	num_list number_list := number_list();
	x_ret NUMBER;
	BEGIN
	/* checks if p_num exists in the colon delimited list p_list */
	l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(p_list, p_separator);

	create or replace package body "UTILS_PKG" as

	-- Splits a string into array delitmited by p_delim and returns the array index at p_index
	function split_idx (p_in_string in varchar2,
	p_delim in varchar2,
	p_index in number)
	return varchar2
	is
	i number :=0;
	pos number :=0;