What is Oracle Application Express (APEX)?

In my recent travels, I have encountered some individuals who ask what I do.  I have been working on my elevator pitch, and it is harder than I imagined.  When I mention that I have a long history with Oracle APEX, I am often greeted with the question….

Q: What is Oracle Application Express (APEX)?

Short Answer:

Oracle Application Express is a no cost feature of the Oracle database that enables declarative rapid application development using a combination of Oracle database features and web technology.

Long Answer:

APEX is a web based application development framework. It requires an Oracle database to host APEX and can leverage all the features of the Oracle database. The applications are developed using the database, so it has all of the speed, redundancy, backup and reliability of the Oracle database. APEX has been designed to allow applications to be developed declaratively with the option for extensions and custom code development utilizing PL/SQL and any browser based technology. This allows a low barrier for entry as well as potential for advanced development.

Q: “No cost.” Is Oracle Application Express Supported by Oracle?

Short Answer:


Long Answer:

From Oracle

From the Director of Software Development for Oracle APEX

Why might some people believe that APEX is not supported? Maybe because there are notes about unsupported or undesirable ways to run APEX. This does not mean that every implementation is unsupported.

A perfect analogy is Oracle XE.

XE is provided for entry level usage of the Oracle Database, including APEX. But it is not supported by Oracle Support. Instead:
“Support is provided through a free Oracle Discussion Forum monitored by Oracle employees as well as community experts.”
Does this mean that the all Oracle Databases are not supported? Of course not. Code or applications designed in a XE database can be migrated to comparable versions of “supported” instances.

Q: What is APEX used for?

Short Answer:

Web based applications that leverage Oracle Database data.

Long Answer:

APEX excels at applications that are transacting, reporting and analyzing data. Many business have created full custom application environments. Some simply use APEX for RICE components. (Reports Interfaces Customizations Extensions) It can be used for many purposes.
APEX does not fit all software development needs, but if the primary purpose is to interface with Oracle data, it is a serious option.

Q: Why should a business consider using APEX?

Short Answer:

Speed, maximum return on investment in the Oracle Database and utilization of PL/SQL skilled resources.

Long Answer:

APEX is based on the database. If you have skills in the database tools (SQL, PL/SQL, API, etc), reuse in APEX applications is very high. The effort of making APEX declarative caters to individuals strong in SQL and PL/SQL. The amount of time required for APEX to from a table of data to a working application is faster than any other software platform available. As needs and skills in web technologies (HTML, CSS, JS, Jquery, etc) improve, so can the complexity of the APEX applications developed.

Q: Do I need a web server to run APEX?

Short Answer:

Technically no, but you should use one for serious work.

Long Answer:

Oracle APEX has 3 major connection setups:

Embedded Pl/SQL Gateway (EPG)

The Oracle database has the ability to respond to HTTP and FTP requests. This means you can run APEX without using a web server! It is a neat technology, and works in a pinch. Just because you can, doesn’t mean you want to. When you use your database as a web server, every call is a resource usage on the database. This includes image files, JavaScript, CSS and the like. These are the types of requests that web servers are built to handle. Web traffic hosting from the database may make DBA types nervous.

Apache mod_plsql

This is a method of web server to database connection that has a lot of legacy in the eBusiness Suite. It is a modification loaded into the Apache web server. This is still a supported method. Many of the features have been duplicated and superseded by the functionality in the ORDS tool. The configuration and implementation is different, but the functionality is the same.

Oracle Restful Data Services (ORDS) (formerly APEX listener)

ORDS is a Java application that allows connection to the Oracle database through JDBC. It was originally designed as a Java replacement for mod_plsql, but has grown into its own tool that allows easy and effective data delivery through Rest style communication. It is not limited to APEX, but is currently the primary implementation for APEX.

ORDS can be run in standalone mode or as an application on an existing J2EE application server. When run in standalone mode, the Java application will run resident in the current session and respond to HTTP calls. When installed into a web server (WebLogic, GlassFish, Apache Tomcat are documented), the full features of the web server can be used for authentication, security and SSL. Web servers, as with Oracle Database, have features that can be leveraged and should be used to their fullest extent in order to gain the greatest value from that investment.

Q: Which APEX setup should I use?

Short Answer:

Depends on your use case, but ORDS installed into a J2EE web server is the recommended approach.

Long Answer:

Analogous to this would be the question: What is the best way to connect to an Oracle Database? SQL*plus, SQL Developer, ODBC, JDBC, OCI, OCCI… It depends on your use case and situation. The web server architecture can be as simple or complex as the business needs. Basic firewall and URL “white listing” is the easiest way to assure that only configured applications and connections are available to external users. These are features of the respective web server choices.

Q: Is Oracle Application Express Secure?

Short Answer:


Long Answer:

Read the book

As with any technology or programming language, the level of security or insecurity depends on the people who designed and built the software. The Oracle database is no different from other technologies in this respect. APEX has been designed with security features built in so that security can be implemented in a rapid and declarative way.

APEX applications benefit from security audits and a solid QA process as does any type of software development. This is why utilizing a team of individuals who understand how to develop a secure APEX application in an effective and efficient manner is key to a successful project.

APEX Authentication with Remember Me Cookie


As part of the ODTUG KSCOPE14 conference, I gave a talk: Cookie Monster – Understanding how and when to use browser cookies with APEX. In that talk I described the technical features of cookies and how the APEX framework uses them.

As the talk progressed, I used the example of how Amazon always seems to know who you are, and how APEX will always throw you out if you close your browser. Even though Amazon knows who you are, any time you try to touch something sensitive such as account details or transactions, Amazon will challenge for authentication credentials. I continued by showing a method in which you could use the Amazon style authentication for APEX applications. It requires a little setup, but it is very possible to do within a single application.

The example application included here operates with APEX in the same way. You can authenticate once and choose to save a “Remember Me” cookie. This cookie will allow you to come back to the application and it will remember who was last authenticated (although not the same sesison). Using an additional check, we can determine if the user has actively authenticated for the current session, and if not, require them to re-confirm their credentials.

** UPDATED ** Download Presentation and APEX Application V1.1  (4.2.2 required)

Demonstration Application on APEX.oracle.com

The High Level Idea

There are three authentication states.

  • Authenticated Automatic by Cookie
  • Authenticated This Session
  • Not Authenticated

An active authentication (user enters password) will set an application attribute that can be used to identify the current APEX session as authenticated. If the user on login chooses the “Remember Me” option, a cookie is set on their browser that allows them to automatically Authenticate next time they come to the application. The application attribute is used to differentiate the difference between authentication by cookie and authentication by password.

The next time the user comes to the application and needs to provide authentication, the application will use the “Remember Me” cookie to authenticate the user. APEX will see this as an authenticated user, but the attribute will show it as an “Automatic” authentication.

Automatic Authentications identify the user to APEX, so as far as APEX is concerned, the user has authenticated.

For sensitive pages, we want to re-challenge anyone using the application with automatic credentials. An authorization (Auth-Z) scheme has been configured to enforce this requirement. When a SECURE page is requested, and the user authenticated with the “Automatic” cookie, they will be re-routed to the login screen. This can either be done with a branch, or with an authorization error notification. (Other designs are also possible)

Steps to Reproduce

Public Home Page

The home page on the application (Mobile or Desktop) is public. It will show either additional pages or tabs when a user is authenticated.

Standard Login

This demonstration has been setup with a dummy Authentication (Auth-N).


  • Click on either LOGIN or on the “Go To Page..” button.
  • Enter TEST for user name
  • Enter TEST for password
  • Check the REMEMBER ME check box (if not defaulted) to generate a Cookie.
  • Click Login
  • Navigate back to the HOME page, you should see additional tabs or pages (Mobile).

Clear the Session

Starting a new session will effectively log you out of APEX. This is the same effect as closing the browser and coming back to the application. (some browsers will remember session state, so your mileage may vary)

AC_new Session

  • Click the “Start a New Session” button.
  • The tabs or pages should be removed.

Auto Authentication

This will show the cookie authentication working


  • On the home page, click the “Go To Page..”

Normally, this page would default to the Login Page. With the “Remember Me” option, it will automatically authenticate the user and navigate to the destination page.
The footer of the page should identify the user as having used the cookie authentication.

Navigate to a secure page

Navigating to a secure page will identify the session as automatic – and either generate an authorization failure (Auth Z) or generate an automatic branch back to the login screen.


  • Click the Secure – Auth-Z Tab
  • Click Back
  • Click the Secure – Branch Tab
  • Login as before
  • Secure page is shown

Deep Link Feature

A good authentication feature should automatically navigate the user to the page requested. The login page here has a feature defined that will set the post login page when needed.

Logout Feature

The feature built into the logout request (same page as login) will clear the “Remember Me” cookie as well as the current session.

  • Click the Logout Button
  • Click the “Go To Page..” button
  • Notice Auto Authentication did not occur


This example can also be run on a mobile device (same application – using auto detection)


Pseudo code

Standard Login

(Page 101 / 1001 (Mobile) Called directly with no request)

  • Authenticate User (Package Code)
  • If Valid Authentication and Remember Me Requested – Write Cookie

Automatic Login

(Page 101 / 1001 (Mobile) called with request “AUTO”)

  • Cookie is read and validated against table store (Package Code)
  • If cookie is valid…
    • User is set
    • Application Item set to AUTO
    • If Post Login Page is given – redirect
    • If no Post Login Page is given – default apex behavior
  • If cookie is not valid…
    • Default to login page with no request
    • Application Item is set to NULL

Authorization Scheme (Auth-Z)

  • Validate Application Item = PASSED indicating a valid session login
  • Provide link in the message to default login page – No request in the link.

Secure Page Auth-Z

  • Authorization is tested at the page level

Secure Page Branch

  • A “before header” branch is configured to call the LOGIN URL (no request) and set the Post Login page to the current page.
  • This branch uses the Auth-Z as the security scheme.


  • Page 101 / 1001 (Mobile) called with request “LOGOUT”
  • Package code that clears the cookie and logs out the session using standard apex features


  • The cookie is written as a HTTP only cookie
  • The cookie key is in a custom table that was created on install
  • The “user agent” is used with the key as an added level of security
    (this is just an example of how the cookie structure can be modified to your needs)
  • There is an expiration date (30 days) for the cookie both in the browser and the table

Is it Secure?

That depends on who you are and what you are doing.
Here are some considerations:

  • Are you using HTTPS? – This will increase the security of cookies and cookie transmission.
  • Are you displaying sensitive information if they cookie authentication is used?
  • Are you working on an internal corporate network only?
  • Do you review your code for security compliance?

Your Design

There is more than one way to address the protection of sensitive data. This example there is simply one of many suggestions.
Applications which contain sensitive information should always be evaluated extensively to assure security and effective design.


My history on this topic goes back some time, but never in a way that was presentable to others. There were always too many specifics that made the application only applicable in my situation. This here is an attempt to take what I have done in the past and make it understandable by others, and maybe even useful.

Credit to those who have done similar work is implicit into all of my posts, if not explicit. In this case, Christian Rokitta did an excelent post on APEX and Remember Me options. His approache similar, and an excelent learning platform.

APEX BLOB CLOB Sample Application and Mobile Issue

I have done a presentation on how attachments work in Oracle APEX.  Most recently at the RMOUG 2014 training days conference in Denver.  The presentation walks through several of the options for uploading, storing and presenting files to the user.

The packaged up the sample application and the presentation are now ready to share.  The Desktop application is installable on APEX version 4.1.1 and later.

BLOB CLOB Sample Application 1.1

On the same vein, an attachment related issue was presented to me recently with regards to the APEX Mobile themes.

When using Mobile reports, if you want to show the contents of the attachment, it may be a challenge.  There is an issue with attachments links generated by APEX that prevent them from being displayed when using the jQuery Mobile theme.  The issue lies with the AJAX loading of the page before it is displayed.  This will generate an page with the error message “undefined”.

BLOB CLOB - Mobile - Not working
Shows the error seen when using the download link for attachments.

This link was generated using a standard report BLOB column format.

Standard Report - Link definition.
Standard Report – Link definition.

There is a fix for this issue.  The link needs to be generated with an attribute of either:




It is possible to generate the links another way, or to write your own download routine, but I think this is a good opportunity for jQuery to save us some time.

On my report page, I have included a small snippet of jQuery code to locate report links and add the rel=”external” attribute to the anchor tags inside reports.

<script type="text/javascript">
  $(document).bind('pageinit',function() {
    $("[id^=report] .list-view-cell .ui-link").attr( "rel", "external" );
    //alert(" I am done");

Note: Template changes my require adjustment to this example. Review the classes assigned to rows in the report on the theme that you are using.

Reviewing the resulting link for the download should snow the attribute as part of the anchor tag.  This should result in a successful attachment retrieval process on using the mobile theme.

Shows a working example of the Mobile Theme attachment.
Shows a working example of the Mobile Theme attachment.


Note: These examples were developed with APEX 4.2.5.  Future versions may vary.

Scripts for Setting Oracle ACL

Understanding and configuring the ACL (Access Control List) in an Oracle database can be a challenge. It has functionality to control network access from the database which is important to understand to prevent abuse and security leaks, but can be a little un-clear.  Because of this, people often set their ACL to be too “loose” so that they don’t have to mess with it again.

If you have ever run across the ORA-24247 error, you can understand why.

ORA-24247: network access denied by access control list (ACL)

While security is good, too much security prevents functionality. Oracle APEX requires access in order to communicate with e-Mail services, LDAP authentication, BI Publisher servers, and any other service type architecture.  It is better to understand your needs and set the security accordingly.

I have constructed a series of scripts to make seeing and changing ACL settings easier.  Each script has a purpose and is designed to accept user inputs.  Built in checks try to reduce input errors.

To run the scripts and make changes to the ACL setup of your database,  you will need to be logged into your database with a user that has the appropriate credentials (SYSDBA and the like).

 SQL> @acl_menu
============= Display Only =====================================================
== @acl_menu     = This file
== @show_hosts   = Server and port range assignemtns in ACL files
== @show_users   = User assignemtns to ACL files
== @show_valid   = Test for invalid ACL files
== @show_descrip = Show developer descriptions of ACL files
== @show_all     = Show above information about all the ACL files
== @show_test    = Quick port 80 test to google
============== Make Changes ====================================================
== @create_new_acl = Create an ACL file
== @drop_acl       = Remove an ACL file
== @add_user       = Prompt for User to assign to an ACL
== @remove_user    = Prompt for User to remove from an ACL
== @add_host       = Add a host definition to an ACL file
== @remove_host    = Remove a host definition from an ACL file

Download ACL Management Scripts

Let me know if you find these scripts useful, or if you have suggestions for improving them.

Additional References

APEX 4.2 ACL Post Installation Steps
Joel Kallman Application Express Network ACL
11.2 Managing Fine-Grained Access in PL/SQL Packages and Types

Sublime Text for Oracle Apex Developers

The following is information related to using Sublime Text for developing Oracle APEX Applications.  Well, actually it is more for any PL/SQL development, but it just sounds cooler.

I use Sublime Text every day and have found it to be a great productivity tool.  As it is cross platform, I use it on MAC, Windows, and Linux operating system (often simultaneously). The pictures may have steps or details specific to one OS or the other. Regardless, the concepts apply to Linux, Mac or Windows. As with many things, there is more than one way to accomplish a goal. This is especially true with Sublime Text. My intention not to re-do Sublime documentation or duplicate what others have already done, but to show you what has worked for me.

Initial Setup

Sublime Text is a commercial software that has a fully functional trial. To the best of my knowledge the trial does not expire, but I like the idea of being able to exercise the software fully before purchasing. (I am a licensed user to support their good work)

Sublime Text

Install Package Control

One of the exciting parts of Sublime Text is not just the multi cursors, but the ability for people to Extend the platform. The folks at Wbond have a package manager that makes extensions very simple.

Package Control Installation


After the Package Control is installed, open the use Package Control Command List and install the following packages:

  • Abacus
  • Oracle PL SQL

These two extension packages are what I like to use most to complement the built in functionality of Sublime Text to develop Oracle packages.

Tutorials and References

Some of my favorite tutorial and document sites:
Video Tuts
Theme Editor (Chrome Only)
Tip Sheet
Docs and Keybard Shortcuts


Abacus is useful for aligning multiple values that follow a similar syntax such as a := ‘value’;
The issue for oracle developers is that the := and the => are not supported by default. This is easily fixed.

Navigate to Preferences -> Package Settings -> Abacus -> Settings – Default
As a good practice, don’t change the default settings file.  Instead, do a “save as” into the “User Directory” with the same file name.



Add additional “token” values to the USER settings for Abacus.  Here are the ones that I use.

            "token":                ":=",
            "gravity":              "right",
            "preserve_indentation": true
            "token":                "=>",
            "gravity":              "right",
            "preserve_indentation": true
            "token":                " OUT",
            "gravity":              "right",
            "preserve_indentation": true
            "token":                " IN",
            "gravity":              "right",
            "preserve_indentation": true
            "token":                "||",
            "gravity":              "right",
            "preserve_indentation": true
        // end my additions

Now when using Abacus, it will align the :=, =>, OUT, IN and || tokens in a line.  To use Abacus, you select the lines you would like to align using your mouse or keyboard, then select abacus from the Selection menu or use the shortcut key assigned.


Before Abacus Alignment
After Alignment
After Alignment
Before Alignment
Before Alignment
After Alignment
After Alignment

Abacus Package Manager
Abacus Git Hub

Oracle PL SQL Package

The Oracle PL SQL extension provides the addition of PL/SQL syntax highlighting in Sublime Text beyond the standard included SQL highlighter.
This allows for easier to read code and syntax identification.

For our projects, we use the extensions of .pks and .pkb to identify PL/SQL code. It is easy enough to default the Syntax for the file type using built in Sublime Text features.
Use the menu View -> Syntax -> Open All with Current Extension As… to select the default when you have one of the files open.

Default Extension
Default Extension

Oracle PL SQL Package at Wbond
Oracle PL SQL Package at Git Hub

Building Code

I subscribe to the adage that Application Express code should be in packages and complex queries exposed via Views. When using Sublime to write that code, I wanted an easy way to compile that code into the database. I researched available packages and did find one, but it was more complex than I desired. (Probably because I don’t know Python that well) I decided to write my own build process that was easier for me to understand and debug. The basis of my Oracle Compilation approach is the same as an installation script. The code I write these days need to be installed via command prompt SQL*PLUS scripting, so this lines up with my goals nicely.

The build process consists of two parts
1 – Sublime Text build option that calls a script command to do the build
2 – A wrapper script that does any work to connect to the database with SQL*Plus and run the desired file

It is necessary to customize as OS environments will handle terminal prompts differently. This is a known behavior. (Windows is different than Mac… so it is said)

SQL*Plus Script

My direction is to have a “script” directory that is in my path for my little tools. This allows the “build shell script” to be called from any directory on that machine. This would apply to any operating system.
I place the build script into the accessible path location. Now Sublime Text can open a “shell” and utilize that script.

Build Option

Sublime allows you to write any type of build command you desire. There are substitutions available for passing values to a command. In my case, the command is a “build script”.   From there, I can do any additional work that I may require inside the script with the single command.

To make a new build system available, you simply need to put a *.sublime-build file into your User directory. (see Browse Packages)
Once a valid build file has been placed in the directory, it should be immediately available using the name of the file as the Build option.


Selecting a build option will now allow the quick key of Ctrl+B to automatically send the active file to the build script. There is an option to “Save All on Build” that can be turned off if desired.

Note: The build file does have the user name and password embedded in it for convenience and speed. This is a known and intended for development only.

I have included here the scripts that I use for both MAC and Windows. They have different syntax as the environments are different.
Example: %1 vs $1 for variables. Place the respective Mac or Windows .sublime_build file in your Sublime Text Packages/Users directory. Place the respective Shell script into a directory in your path statement.

Duplicate the .sublime_build file for each connection making updates to the title and the contents appropriately to reflect it’s connection details.

Sublime Text Oracle Build Scripts

The build process produces a results window that has the output of the script with a simple keystroke. (Ctrl+B)


Any errors that are encountered are identified by the standard SQL*Plus error reporting process.


Sublime Text can work very well for building DDL scripts.  I do not recommend it for query building or testing as the formatting is SQL*Plus text based.  SQL Developer is a much better choice for developing queries and testing results.  Improved productivity can come about by knowing what the right tool for the job is, and how to use it.

Side Note – linking SQL Developer to Sublime Text

If you are interested in using Sublime Text with SQL Developer – Take a look at the post by Jeff Smith that outlines now to setup external editors inside of the SQL Developer tool.

LOB, CLOB, BLOB – Dealing with Attachments in APEX

Included here is the presentation slides that I gave during the RMOUG 2014 conference.


Demonstration Application Update:

I am working to make the Demonstration application installable to allow users to be able to install and see behind the slides.  This is still in progress.  If you are interested in testing the application, please leave me a comment below.

How to break SQL Developer -or- Dr. It hurts when I do this…

I have been working with SQL Developer a lot over the last few years. Being a Toad user beforehand, it was an adjustment to say the least.  With the last few releases, I have come to understand the working much better and can see where the seams are in the product.  That being said, something I still don’t understand fully (or trust) is performance tuning with SQL Developer. When dealing with troublesome tuning, I have come to trust SQL scripts (see SQLPLUS TIMING) as a much more accurate representation of performance rather than running through a GUI system.


Because there are too many variables that are included with a GUI that are intended to HELP me.
Variables that I don’t’ always understand when or why they have been put in place and hence run a risk of giving me bad results that I then make decisions on.  Decisions that I thought were based on empirical data, but turn out making me question my own understanding.

Problem “Java Heap Space error in Query Results Window”

In this story, I was working on a particularly complex and layered performance issue that was behaving differently in SQL Developer vs. the resulting system usage. In trying to duplicate the performance issue in SQL Developer. I was able to get the high level issue to duplicate, but when breaking the parts down, I was unable to get same issue with the parts of the statement.

Array Fetch Size Setting

After investigating: There is a setting called SQL Array Fetch Size (between 50 and 200)
This will greatly change the behavior of a running query when attempting to get all rows returned.

What I found was if I turn this value up, I would be able to see the performance issue more clearly.

Array Fetch Size used in Query Results (adjusted to 53)

If you have done performance tuning before, you can see where this little story is heading.
When the number of rows greatly affect the performance, it is a good indicator of where the problem lies.

How to fix “Java Heap Space error in Query Results Window”

Java Heap Size – error message in query results tab

So.. back to the main theme of this post.
If you set the SQL Array Fetch Size to something too large – it may help you for the moment find your problem, but it can also have a side affect.  What I found was that I would get a “Java heap space” error message and a gray window in the Query Results.  Adjusting the SQL Array Fetch Size to a value in the suggested range will correct this error (after a re-start of SQLDEV).

How to debug SQL Developer settings issue

Now if you try to do a web search for SQL DEVELOPER JAVA HEAP SPACE ERROR, you will get a whole listing of issues and text to read, most of which have to do with the startup of SQL Developer. Often the most successful results people have is by re-installing SQL Developer. This can be misleading. When just replacing the binaries the settings will persist.
Keep in mind – it isn’t the main code that had this issue, as much as the SETTINGS the (l)user set.

To find out where your settings are located for SQL Developer (system agnostic) – go to HELP -> About -> Properties then search for ide.system.dir

IDE Settings
IDE Settings

Purging the settings will also purge all of your CONNECTIONS. If you are like me, that is what I didn’t want to lose.
Your settings directory can be backed up (copied, zipped, whatever) until you determine the actual cause of the IDE error.
This is the best way to determine if the issue is with something you have done, or something wrong with the IDE.

This is sound methodology with more than just SQL Developer.

Disclaimer -or- Knowing where the seams are

Just because this was the issue that I found that generated the very generic “Java heap space” error, does not mean it will be the same for you.  The modular construction of SQL Developer makes me think there is more than one way to get that error.  The take away here is more determining how to debug a packaged product before going off the deep and blaming the product.

Special thanks to Jeff Smith (@thatjeffsmith) for letting me know that it was just me.

Tim St. Hilaire – Notes and Shares