Exporting Apex application from Backend – Apex 4.1

Recently I ended up screwing up my Apex application. I was trying to enable SSL (making URL https) for security reason and somehow the configuration did not work on production.

At this stage I was trying to get back my APEX application. Unfortunately nothing seems to be working.

So I thought of doing a complete reinstall of APEX but I didn’t have backup of my application.

Nice thing, I found how to export your APEX application from backend. You don’t have to login to GUI (to your workspace) to export your application. Here are the simple steps to export the applications

Step 1) Make sure you have apex home

Many time we download APEX, install in our database and later delete the files of APEX as they are not required.

If you have delete the files, make sure you download same version again. APEX home is required for exporting application

Step 2) Check APEXExport.class is present in utilities/oracle/apex directory

Check out following 3 files should be present under APEX_HOME

*** I am referring APEX_HOME to the directory where apex is extracted.

1) readme.txt – Should be present in $APEX_HOME/utilities/readme.txt

Check out this file. This will give you idea about how to do the export. Don’t jump in running export command

2) APEXExport.class – Should be present in $APEX_HOME/utilities/oracle/apex/APEXExport.class

This java class file is responsible for doing the export. This will generate f<appl No>.sql file.

3) APEXExportSplitter.class – Should be present in $APEX_HOME/utilities/oracle/apex/APEXExportSplitter.class

This can be used used to split Oracle Application Express export files into separate SQL scripts. This is useful for management of files corresponding to the discrete elements of an Application Express application.

Step 3) Getting correct version and setting correct environment variable

Its very important that we have correct version of JAVA present in our environment and also we should make sure that we set environment variables correctly.

If any of these are not correct (java version or environment variables), export will not work.

You need to download and install JDK1.6 as this will not working on lower version of JDK (to my experience).

You can download the same from http://www.oracle.com/technetwork/java/javase/downloads/index.html

After downloading and installing JDK 1.6, you need to set following environment

  3. PATH

If you set any of environment variable incorrectly, export won’t work.

I tried following CLASSPATH, but got error

export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/ojdbc5.jar
java oracle/apex/APEXExport -db apex-server.db.example.com:1525:apexdb -user <apex_schema> -password <passwd> -applicationid 101
Exception in thread "main" java.lang.NoClassDefFoundError: oracle/jdbc/OracleDriver
 at oracle.apex.APEXExport.main(APEXExport.java:825)
Caused by: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver
 at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
 ... 1 more

I tried few more combination of CLASSPATH variable, but nothing worked. Finally I set following variables and after that export worked.

In my case apex was extracted on $HOME/product/apex location.

Following setting worked:-

export CLASSPATH=.:${ORACLE_HOME}/oui/jlib/classes12.jar
export JAVA_HOME=/opt/app/oracle/product/jdk1.6.0_38/jre
export PATH=/opt/app/oracle/product/jdk1.6.0_38/bin:$PATH
cd $HOME/product/apex/utilities
java oracle/apex/APEXExport -db apex-server.db.example.com:1525:apexdb -user <apex_schema> -password <passwd> -applicationid 101

Exporting application 101
Completed at Mon May 06 15:44:53 UTC 2013

Hope this helps !!


Oracle Application Express – Installtion Guide

This article provides instructions for installing and configuring the Oracle Application Express (formerly HTML DB) software release 2.2. Please note that these instructions cover the standalone version only.

Installing Oracle Application Express is a two step process:

  1. Configure an Oracle HTTP Server (Release 9.0.3 or higher) with mod_plsql which is used to connect to the Oracle database where the Oracle Application Express objects will be installed. It is also possible to use Oracle 9i Application Server release 1 ( or higher.
  2. Install the database objects that make up Oracle Application Express to a pre-existing Oracle database (Oracle9i Release 2 ( or later).

Important Note: When installing Oracle Application Express, you will be required to install the Oracle HTTP Server which can be found on the Oracle10g Release 2 Companion CD. Although not a requirement, I will be installing the Oracle HTTP Server to the same machine which already includes an installation of the Oracle Database software.

The Oracle HTTP Server cannot be installed into an existing Oracle Home. It must be installed into a new Oracle Home!

Prerequisites for the Installation:

This section describes the requirements for installing Oracle Application Express, Release 2.2.

Browser Requirements

To view or develop Oracle Application Express applications, Web browsers must support Java Script and the HTML 4.0 and CSS 1.0 standards. The following browsers meet this requirement:

  • Microsoft Internet Explorer 6.0 or higher (Windows only)
  • Netscape Communicator 7.2 or higher
  • Mozilla 1.2 or higher
  • Firefox 1.0 or higher

Operating System Requirements

From what I have read and tested, Oracle Application Express can be installed on the following Operating System platforms:

Linux – Red Hat Enterprise version AS/ES 2.1 or higher; or SUSE Enterprise Server version SLES-8 or higher.

  • Solaris 9 or higher.
  • Windows 2000 Professions or higher (with service pack 3 or higher)
  • Windows XP Professional
  • Windows 2003 (32-bit systems)

Disk Space Requirements

Verify that the file system that contains the Oracle home directory contains at least 460MB of free disk space for the installation. Also during the installation process, about 110MB of temporary disk space will be required.

Database Requirements

For installing Application Express we need to have a database created before. Same database will be used for creating the objects required for Oracle Application Express.

The installation of Oracle Application Express requires certain objects to be created in an Oracle database. With Oracle Application Express 2.2, the database is required to be Oracle9i Release 2 ( or later. All of the Oracle Application Express database objects should be created in a separate tablespace which we will create later on in this article.

Shared Pool Size Requirements

Oracle Application Express requires the shared_pool_size of the target database to be at least 100 MB.

Determine the current value of the shared_pool_size parameter:

SQL> show parameter shared_pool_size


The initialization parameter JOB_QUEUE_PROCESSES for the Oracle database determines the maximum number of concurrently running jobs. Starting with Oracle Application Express Release 2.0, transactional support and SQL scripts require jobs. If JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.

You can view the number of JOB_QUEUE_PROCESSES from SQL*Plus by running the following SQL statement:

SQL> SELECT VALUE FROM v$parameter WHERE NAME = ‘job_queue_processes’;

If you need to modify the JOB_QUEUE_PROCESSES initialization parameter, log into the database as SYSDBA using SQL*Plus and run the ALTER SYSTEM … statement. For example, to set the number of JOB_QUEUE_PROCESSES to 20, use:


Installing Oracle HTTP Server

When installing Oracle Application Express, you will be required to install the Oracle HTTP Server which can be found on the Oracle10g Release 2 Companion CD. Although not a requirement, I will be installing the Oracle HTTP Server to the same machine which already includes an installation of the Oracle Database.

Important Note: The Oracle HTTP Server cannot be installed into an existing Oracle Home. If another Oracle product is installed on the same server, you will need to create a new Oracle home name and location for the Oracle HTTP Server

This section assumes you have access to and will be installing the Oracle HTTP Server from the Oracle10g Release 2 Companion CD. Note that with a few minor modifications, you can also install an earlier version of the Oracle HTTP Server from the Oracle10g Release 1 Companion CD.

You can perform the install from the CD or download it from Oracle Technology Network (OTN)

After downloading and unzipping the Oracle10g Release 2 Companion CD software to a temporary directory, follow the instructions below to install the Oracle HTTP Server:

Step 1) Start the Oracle Universal Installer from the Oracle10g Release 2 Companion CD

./runInstaller -invPtrLoc /u01/app/orsbox/OHS/oraInst.Loc

On Select a product to Install Select Oracle Database 10g Companion Products

On Specify Home Details Select Different Oracle Home path and name (other then ORACLE_HOME for database)


On Available Product Components screen select ‘Apache Standalone’ product only


Pre-Requisite check will run. Check if you get any error here. If there is some error then correct the same and repeat the above steps again.


Check the summary screen and press in ‘Install‘ button


Installer will show you the progress for installation


Configuration Assistant will configure Apache HTTP server and OC4J component.


End of installation. Press Exit and come out of the wizard.

Test the Oracle HTTP Server Installation

After the installation of the Oracle HTTP Server is complete, you should test it. Point your browser to the URL that was displayed on the “End of Installation” screen by the Oracle Universal Installer. For me, the URL was http://ocvmrh2124.us.oracle.com:7777/

Starting / Stopping / Restarting Oracle HTTP Server:

Start All: $ORACLE_HOME/opmn/bin/opmnctl startall
Stop All : $ORACLE_HOME/opmn/bin/opmnctl stopall
Start HTTP Process: $ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server
Stop HTTP Process : $ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server
Restart HTTP Process: $ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server

Installing Oracle Application Express:

Download Oracle Application Express 2.2

APEX 2.2 can be downloaded from official OTN site

Oracle Application Express was previously known as HTML DB. With the release of 2.0, Oracle officially changed the name from HTML DB to Oracle Application Express (APEX) on January 30, 2006.

Important Note: Note that the version of HTML DB shipped on the Oracle10g Release 2 Companion CD is version 1.6. With Oracle10g Release 1, the version of HTML DB is 1.5.

After downloading the file from OTN, unzip the same.

You need to create a new tablespace for APEX application.

Create Tablespace for Oracle Application Express Database Objects:


Tablespace created.

Create Oracle Application Express Database Objects:

After the new tablespace has been created, the next step is to create all required database objects for Oracle Application Express. These database objects are all created by running a single SQL script that can be found in the Oracle Application Express software distribution downloaded earlier in this section. The SQL script to run is named apexins.sql and can be found in the apex directory created when unzipping the software distribution. For the purpose of this example, I ran the script as follows (note that this install script can take quite awhile to complete):

Change the directory to APEX directory you have unziped

[orsbox@ocvmrh2124 conf]$ cd apex

Connect to database as sysdba and run following command

@apexins welcome htmldb htmldb temp /i/ orcl

Below is the brief explanation for this command.

Parameters to APEX Database Object Creation SQL Script
welcome The password for the Oracle APEX administrator account, the APEX schema owner (FLOWS_020200), the APEX files schema owner (FLOWS_FILES), and the APEX public user schema (APEX_PUBLIC_USER). The APEX schema owner is the user or schema into which Oracle Application Express database objects will be installed. The APEX files schema owner is the user or schema where uploaded files are maintained in Oracle Application Express.
htmldb Name of the default tablespace for the APEX schema owner – (FLOWS_020200).
htmldb Name of the default tablespace for the APEX files schema owner – (FLOWS_FILES). Note that Oracle Application Express creates a table namedFLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ in this tablespace used to store any uploaded files. Although this could have been a separate tablespace, it is just as easy having the all objects in a single tablespace.
temp Name of the temporary tablespace to be used for all schemas created by the Oracle Application Express install process.
/i/ Virtual directory that is used for images rendered by Oracle Application Express. To support future Oracle HTML DB upgrades, define the virtual image directory as/i/.
orcl Name of the Oracle Net connect string to the database where Oracle Application Express database objects are to be installed. If this is a local install, you can usenone or NONE.

During the Oracle Application Express install process, three schemas will be created. A description of each of these schemas is provided in the following table:

Oracle Application Express Database Schema Accounts
FLOWS_020200 This is basically the schema owner of all objects (tables, views, packages, functions, etc.) used by Oracle Application Express. The only exception is the table installed in the FLOWS_FILES schema (FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$) for storing uploaded files. Note that this account is locked at the end of the installation and cannot be used unless it is first unlocked.
FLOWS_FILES This schema is used to store uploaded files to Oracle Application Express. These can include scripts, documents, cascading style sheets, etc. Note that this account is locked at the end of the installation and cannot be used unless it is first unlocked.
APEX_PUBLIC_USER This schema is used by Oracle Application Express to login to the database and has access to database objects in the above two schemas for all application functionality.

Recompiling Invalid PL/SQL Packages:

After installing the Oracle Application Express database objects, it is recommended (however not required) to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

Run the utlrp.sql script from the Oracle Database home:

SQL> @?/rdbms/admin/utlrp.sql

Install Oracle Application Express Database Images:

After successfully creating all of the required Oracle Application Express database objects, the next step is to copy the necessary images, templates, cascading style sheets, themes, java scripts, (and several other file types) into the directory tree of the Oracle HTTP Server. The Oracle HTTP Server was installed earlier in this article and is located at:

$ORACLE_HOME = /u01/app/orsbox/OHS

The images and other file types to copy can be found in the Oracle Application Express software distribution downloaded earlier under the …/apex/images directory.

The files will need to be copied to the $ORACLE_HOME/Apache/Apache/images directory as illustrated in the following examples:

[orsbox@ocvmrh2124 orsbox]$ cd apex
[orsbox@ocvmrh2124 orsbox]$ cp -R images $ORACLE_HOME/Apache/Apache

Configure Database Access Descriptor:

Now that the Oracle Application Express images are in place, the next step is to configure the Oracle Database Access Descriptor (DAD) file. The configuration file is named dads.conf and should be located in the $ORACLE_HOME/Apache/modplsql/conf directory. The DAD is used by the Oracle HTTP Server and mod_plsql extension module to connect to the Oracle database.

Change the below text according to your setup and copy the below text to dads.conf

Parameters to be changed in below text are

Alias -> This is the alias for images directory under Apache Home
PlsqlDatabasePassword -> This is the password for APEX user, you can set any password here, which will be used further
PlsqlDatabaseConnectString -> This is the connect string for your database. This is of the form HOST.DOMAIN:PORT:DB_NAME

=============================================================== Alias /i/ “/u01/app/orsbox/OHS/Apache/Apache/images/”
AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDatabasePassword welcome
PlsqlDatabaseConnectString ocvmrh2124.us.oracle.com:1522:ORCL
PlsqlAuthenticationMode Basic
PlsqlDefaultPage apex
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download

For a complete description of each of the parameters used in the dads.conf configuration file, read through the file dads.README located in the same directory. The following table provides a brief description of the key parameters:

Oracle DAD Configuration Parameters and Description
Alias This is set to the virtual directory you specified when running theapexins.sql file to create the Oracle Application Express database objects. This value must match the value you specified which is recommended to be /i/. The virtual directory should refer to the location where you copied the Oracle Application Express images to in the Oracle HTTP Server directory tree.
<Location /pls/apex> This is the name of the virtual path that will be used to access application in Oracle Application Express. For example,http://linux3:7777/pls/apex/f?p=100.
PlsqlDatabaseUsername This is the database username that will be used by the mod_plsqlextension module to connect to Oracle database. The database username should be APEX_PUBLIC_USER – the schema created by theapexins.sql script. All connections coming from an Oracle Application Express application, regardless of their login id and password, will be connected to the database with this username. This even includes applications that use a different parsing schema! Consider an application that uses a parsing schema of DEV_WS. The session user will be APEX_PUBLIC_USER but the Current User will be set to DEV_WS

    sys_context('USERENV','SESSION_USER') "Session User"
  , sys_context('USERENV','CURRENT_USER') "Current User"
FROM dual;

Session User      Current User
----------------- ------------
PlsqlDatabasePassword This must match the password you provided during the creation of the Oracle Application Express database objects (@apexins.sql). This is the password used by the PlsqlDatabaseUsername (above) to connect to the Oracle database.
PlsqlDatabaseConnectString The connection URL string used to connect to the Oracle database in the format server.domain:port:sid. If the database is located on the same server, you can use localhost.

Obfuscate the PlsqlDatabasePassword Parameter:

Notice that when we configured the dads.conf file, we put in the plain text password for the Oracle database user. Exposing plain text passwords is never good security practice, especially for this sensitive database account. Fortunately, Oracle provides an obfuscation utility that targets and encrypts the PlsqlDatabasePassword entry in the dads.conf file. This utility is named dadTool.pl and can be found in the $ORACLE_HOME/Apache/modplsql/conf directory.

Run the dadTool.pl utility as follows:

[orsbox@ocvmrh2124 orsbox]$ export ORACLE_HOME=/u01/app/orsbox/OHS
[orsbox@ocvmrh2124 orsbox]$ echo $LD_LIBRARY_PATH

[orsbox@ocvmrh2124 orsbox]$ PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH;export PATH
[orsbox@ocvmrh2124 orsbox]$ PATH=$ORACLE_HOME/perl/bin:$PATH:.;export PATH
[orsbox@ocvmrh2124 orsbox]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
[orsbox@ocvmrh2124 orsbox]$ PERL5LIB=$ORACLE_HOME/perl/lib:.;export PERL5LIB
[orsbox@ocvmrh2124 orsbox]$ cd $ORACLE_HOME/Apache/modplsql/conf
[orsbox@ocvmrh2124 conf]$ perl dadTool.pl -o

Backed up older dads.conf as /u01/app/orsbox/OHS/Apache/modplsql/conf/dads.conf.orig.2007-07-13_09-04

All passwords successfully obfuscated. New obfuscations : 1
[orsbox@ocvmrh2124 conf]$

Restart the Oracle HTTP Server:

[orsbox@ocvmrh2124 conf]$ $ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server
opmnctl: restarting opmn managed processes…

Now you can access your APEX URL: http://ocvmrh2124.us.oracle.com:7777/pls/apex


Cleaner APEX URLs

Some times for production systems, we need to have cleaner URLs, specially for APEX application in order to hide the details about the host or the parameters that are passed when we go from one page to another in an application.

This post is about giving a simpler way to do the same. I faced similar issue for one of out main production system where objective was to standardise our URLs. We have more then 20 applications running on same APEX installation having different application ID.

Here is what I did.

1) Check the document root of Apache

/slot/ems2154/oracle/product/Apache/Apache/Apache/htdocs in my case.

2) Suppose my APEX URL is some thing like this


and I want to make is some thing like


just for example I am using “test” as context root. In your case you can have any name. So when we hit this second URL (http://rws60106rems.us.oracle.com:7790/test), Apache will search for a file called test under document root (/slot/ems2154/oracle/product/Apache/Apache/Apache/htdocs).

So we can create a simple file in DocumentRoot location and name that file as test. Below should be the content of that file (in my case).

  <title>Some Title</title>
<frameset rows="100%,*" border="0">
  <frame src="http://your_host/pls/otn/f?p=xxxxx" frameborder="0" />
  <frame frameborder="0" noresize />

In your case you can change the src=<URL> accordingly. Bounce the Apache.

After that you can hit the URL http://rws60106rems.us.oracle.com:7790/test and it will work. Also it will not show any details about your application.

Hope this helps !!


Patrick Wolf Blog

Uploading excel sheet using Oracle Application Express (APEX)


Some times we need the flexibility to upload the data to a table from excel sheet. The can be implemented very well in JSP and other application. I was having this requirement to implement the same in Oracle Application Express (APEX).

I was going though one of the blog by Ittichai, for the same and he has given a very clean steps for getting this work. I followed the steps and it worked for me. I have documented the similar steps below as per my understanding. Below are the details.

Implementing data upload using excel sheet

Following is the table detail which I want to populate from data in excel sheet.

SQL> desc data_upld
Name Null? Type
—————————————– ——– —————————-

1) Created a “File Browse” item on a page and having internal name it P35_UPLOAD.

2) Created a button having internal name as “Upload“. This button is used to upload and process the data in the excel sheet.

3) Created a conditional branch when upload button is pressed, it should branch to the same page (35 in my case)

4) When we select an excel sheet using browse button and click on submit button, it will branch to same page (indirectly we are saying that its going to refresh the page). In such case even if there is no code written in the backend, APEX is going to load the excel sheet in a BLOB format into a table wwv_flow_files.

This is the internal table used by APEX. Our task is to read this table and get the required row which got inserted into this table. With each upload 1 row will get inserted into the table. One of the column of the table (BLOB_CONTENT) is BLOB where the actual excel sheet is uploaded. All other columns are metadata about excel.

When we upload an excel sheet, a random name will get generated for the file in the form FXXXXX/<file_name_you_uploaded>. You can use this to get the file details. Also when you refresh the page, it will upload the file to this table wwv_flow_files, but as soon as refresh completed and it displays the page back again, that row will get deleted again. So you have to get that row processed immediately using the code that you will write when submit button is pressed.

In my case, I want to upload the rows into data_upld table. My excel sheet content looks as shown below.

I want to upload the above 6 rows.

5) Following is the code for the same

Before using the below code, make sure you have a function hex_to_decimal in your database. The code for the same is as given below.

create or replace function hex_to_decimal
--this function is based on one by Connor McDonald
( p_hex_str in varchar2 ) return number
v_dec   number;
v_hex   varchar2(16) := '0123456789ABCDEF';
v_dec := 0;
for indx in 1 .. length(p_hex_str)
v_dec := v_dec * 16 + instr(v_hex,upper(substr(p_hex_str,indx,1)))-1;
end loop;
return v_dec;
end hex_to_decimal;

Below is the code for uploading the excel sheet.

 v_blob_data       BLOB;
 v_blob_len        NUMBER;
 v_position        NUMBER;
 v_raw_chunk       RAW(10000);
 v_char      CHAR(1);
 c_chunk_len   number       := 1;
 v_line        VARCHAR2 (32767)        := NULL;
 v_data_array      wwv_flow_global.vc_arr2;
 v_rows number;
 v_sr_no number := 1;


delete from data_upld;

-- Read data from wwv_flow_files</span>
 select blob_content into v_blob_data
 from wwv_flow_files
 where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)
 and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);

v_blob_len := dbms_lob.getlength(v_blob_data);
 v_position := 1;

-- Read and convert binary to char</span>
 WHILE ( v_position <= v_blob_len ) LOOP
 v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
 v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk)));
 v_line := v_line || v_char;
 v_position := v_position + c_chunk_len;
 -- When a whole line is retrieved </span>
 IF v_char = CHR(10) THEN
 -- Convert comma to : to use wwv_flow_utilities </span>
 v_line := REPLACE (v_line, ',', ':');
-- Convert each column separated by : into array of data </span>
 v_data_array := wwv_flow_utilities.string_to_table (v_line);
 -- Insert data into target table </span>
 values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)'
 -- Clear out
 v_line := NULL;
 v_sr_no := v_sr_no + 1;


Create a new PLSQL process under Processes section in APEX and put this code under the same. Make the process run when upload button is pressed.

Data in excel sheet will get loaded into table.

Hope this helps !!