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.

RMOUG – 2014 Comming Up!


I have the privilege of presenting at the Rocky Mountain Oracle Users Group (RMOUG) in February.  I will be presenting on Friday – LOBS, BLOBS, CLOBS – Dealing with Attachments Inside Oracle APEX
I look forward to the conference as it will be my first time participating in this user group.

After the presentation, I will be posting the final slides and example code on this blog, so check back after Feb 7th.

Many thanks to Enkitec  for supporting my attendance at the conference.

Relocate APEX Interactive Report Loading Image

If you are in the situation where you have either deviated from the standard APEX theme, or have added an adjustments to your page header, you may have noticed that the AJAX loading image for the interactive report can get covered or simply look out of place. Using some jQuery and CSS adjustments, we can relocate the loading image to a position relative to the report that is being used.
The goal here is to define a dynamic position by re-locating the loading image relative to the interactive report.

Disclaimer: This method is dependent on your theme and css files used. Your mileage may vary.

The default location of the loading image is in the top middle of the page. This is done by referencing a SPAN item by ID (apexir_LOADER; which contains the loading image) and applying an absolute position to the element.
In the generated interactive reports region, the loader is located last in the returned HTML. In order to allow the image to be generated relative to the top of the region, we need to make the position RELATIVE to the container object. Because the image is generated at the bottom, moving the region to a better higher location allows it to flow with the top of the report.

// Change the location of the SPAN object

By setting the loader position to “relative”, the location coordinates are now relative to the report container. This allows adjustment of left and top positions to work with the respective theme.

//Show image for demonstration purposes
$("#apexir_LOADER").css({"position":"relative", "left":"10px", "top":"10px", "height":"0px"});

For the demonstration code above, the following code is used:

Top of Region

  //Change the location of the SPAN object to be inside the same container as the report table
  //Container for the loading image - place in the middle of the container region at the top with no height
    $("#apexir_LOADER").css({"position":"relative", "left":"50%", "top":"0px", "height":"0px"});
  //Because the container is in the middle, center the loading image, shifting the top as needed
    $("#apexir_LOADER img").css({"position":"absolute", "height":"4px", "width":"200px", "left":"-100px", "top":"-8px"});
  //Change the default loading images to /i/htmldb/icons/loading-gray.gif
    $("#apexir_LOADER img").attr("src",#IMAGE_PREFIX#+"htmldb/icons/loading-gray.gif");

Right of Search Bar

  //Change the location of the SPAN object to be inside the same container as the report table
  // insert AFTER the tool bar DIV 
  //Place the image to the RIGHT of the SEARCH BAR
    $("#apexir_LOADER").css({"position":"relative", "left":"10px", "top":"10px", "height":"0px"});
  //Undo any changes that may have been done to the image itself
    $("#apexir_LOADER img").css({"position":"", "height":"", "width":"", "left":"", "top":""});
  //Change the default loading images to /i/htmldb/icons/loading-gray.gif
    $("#apexir_LOADER img").attr("src",#IMAGE_PREFIX#+"htmldb/icons/loading-gray.gif");

Putting it all together

Create a region either on the page with the interactive report, or on Page 0 with the code outlined below. I used jQuery to set the CSS in order to use conditional logic for when the SEARCH BAR is not included on an interactive report. This could be done by setting the styles directly, but the goal here was to have one script work for interactive reports with and without the query bar.

If you are using a template that has a 100% page width container, the element will wrap. You can then set an offset and float the region to the right (as shown below), allowing it to follow the placement of the region on the page. If using a template that does not have have a search bar with 100% width, letting the image appear normally to the right also useful.

The spacing in this script is designed for Theme 26 in APEX 4.2.3. The query bar is also full width of the region, so having the query bar float to the right allows the image to show without overlap. Adjustment may be needed for other themes.

Demonstration Oracle APEX
Demonstration Enkitec

<!-- For Theme 26 in apex 4.2.3 -->
  // Test to see if the "Search Bar" has been disabled
  if ($("#apexir_TOOLBAR").html()=="")
  { // there is NO SEARCH BAR
    // Add a thin version of the image before the report where the tool bar would be
    // Change the location of the SPAN 
    // set the SPAN to be before the empty toolbar div with a height of 0px
    $("#apexir_LOADER").css({"position":"relative", "left":"50%", "top":"0px", "height":"0px"});
    // alter the image to make it smaller for the space alotted
    // to center, move LEFT half of the image width
    $("#apexir_LOADER img").css({"position":"absolute","height":"4px","width":"200px","left":"-100px","top":"-6px"});
  { //There is a tool bar
    //Place the image to the RIGHT of the SEARCH BAR
    $("#apexir_LOADER").css({"position":"relative", "float":"right", "left":"-10px", "top":"20px", "height":"0px"});
    // Change the location of the SPAN object
  //Show image for demonstration purposes
  // only for testing // $("#apexir_LOADER").css({"display":"inline"});
  //Change the default loading images to /I/htmldb/icons/loading-gray.gif
  $("#apexir_LOADER img").attr("src",#IMAGE_PREFIX#+"htmldb/icons/loading-gray.gif");

Options for Windows Native Authentication with APEX


The Single Sign On (SSO) / Windows Native Authentication (WNA) / Windows Network automatic login concept was something I championed when I was working at my previous employer. The idea was to utilize the internal network logins and provide access to web applications with the current Windows domain user. Because all of the network access was through the Active Directory domain, we wanted to leverage the Windows PC logins. We could always do custom login solutions – prompting for user and password – but the goal was automatic login (no password prompts) similar to what you would get with SharePoint or other Microsoft aware solutions.
Here is an outline of different methods we used to accomplish this goal. This is intended to be a starting point as the technical details to implement will vary based on environment and individual needs.


One of the Oracle subscribed methods to accomplish automatic logins is Oracle Internet Directory (OID) and the native -SSO-Partner Application- option in APEX. By syncing the Active Directory to OID, OID can have a trust relationship back to the main AD server. This is a complex setup process as the sync can be one way or two way. It requires a fair amount of installation and configuration between the two systems. This worked fine for us, until there was a change to OID that needed a patch, or AD attributes would change. Depending on how complex the system architecture group decides to make the OID installation will also impact the ease of use. This worked, but had a very monolithic dependency we had to deal with.
APEX 4.2 Docs


When the APEX listener was in early adopter, we started looking at simpler ways of getting SSO to work. Because we no longer needed to use mod_plsql, there were JAVA options to push the authentication out to the web server. The first simplification was to make the web server easier to maintain. With the help of a Unix administrator, we started using the HTTP server that was part of our Unix operating system. Tomcat is a standard option that can be implemented as a Solaris service with very little configuration required. To achieve this, we implemented the SPNEGO/Kerberos key concept of identity. A key tab file (keytab) is created that enables the trust relationship with active directory. The authentication is implemented using uses a simple FILTER on the apex listener path. This creates the requirement: Any call to the APEX listener is an authenticated user. This concept is not specific to Tomcat. SPNEGO can work on many J2EE web server, and is documented as part of several web server SSO implementations such as Weblogic.

The draw back for the “you must have a windows login” was if we ever used the APEX Listener as a service. Any service calls would have to be authenticated. Because of this, we made a second “non-sso” version of the listener on the same web server. Applications that required authentication would still prompt, but we could then also serve up public pages without authentication using the “http://host:port/apex-nonsso/” path.


Using a similar method of Kerberos keys directly with Tomcat has been written up and published by Niels de Bruijn – Member of MT AGHis paper has been published in Slideshare.  It includes detailed instructions on how to configure Tomcat to directly communicate with an Active Directory server.


As time went on, our colleagues in the Engineering ERP department wanted to do the Windows Native also, but they were all hard core JAVA developers. They had the requirement to put additional logic on the SPNEGO option and allow it to switch between multiple domains. The goal was similar to SPNEGO (with actual underpinnings), but with an ability to put logic into a “fail over” concept. Using the JASIG CAS libraries, the team was able to implement a fall back authentication screen that they could default to if desired that let them pick the domain they wished to log into. (One was Active Directory, one was internal product specific account LDAP). The result was an attempt to log in automatically, but with an option to log out that would prompt to a login window (outside of APEX) enforced by the web server. This had the additional switching options that the team required.



The last way we implemented the automatic login was using WAFFLE. It requires a windows server that gives it access to the Active Directory space without the need for a Kerberos Key specifically made for the sign on. You still get to use Tomcat / Glassfish with the APEX listener, just simpler to configure and deploy in a windows VM environment. This was done for a small project, but proved very effective with less red tape because you do not need the Kerberos key created specifically for authentication. (it is built into the Windows Server)

Header Variable

All these methods (with the exception of OID) utilize the HTTP Header to pass the authenticated user. With the introduction of the authentication “Header Variable” option in APEX 4 it makes it very easy to consume and use this value. We implemented the technique before that was available, so we simply had a custom authentication routine that would read the value and continue on if it was acceptable. There was a quick re-direct past the login page, and the user would be able to continue with their Windows user name as their user.

The concept is simply described by me as: push the authentication back to the web server, then let APEX trust the web server authentication.

APEX Header Auth

These by no means are the only options. These are simply some of the ways that I have experienced and had success with. Comment below with any additions you have had success with.

Oracle Openworld 2013 – APEX and Jasper Reports

I have the privilege of speaking at Oracle Openworld 2013 in San Francisco. The topic of this talk is integrating Oracle Application Express with Jasper Reports to generate highly formatted output.  For ease of distribution, I have published the presentation here.


Many thanks to Enkitec and ODTUG for supporting my attendance at the epic Oracle Conference.

Tim St. Hilaire – Notes and Shares