Create collapsible list (alternative to apex tree)

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

After some testing en struggling with the Apex-tree item I decided to look for an alternative for an expanding and collapsable html-list.

Because of the integration with Jquery in apex, i obviously looked for an apex plug-in and found a really ease one.

The plug-in is written by Milaju and the source and documentation can be found at:

You can find a demo at:

How to integrate:

  1. Download the source-file from
  2. Extract the zip-file
  3. Go to Shared Components – Static files
  4. Upload the files “jquery.collapse.js” and “jquery.easing.js”. You do not need to upload the file “jquery.js”, because it’s already available within Apex
  5. Go to the page where you want to make the list
  6. Create a new HTML-region named “_Javascript”; display point: After Header (I always use a separate region were I put custom javascript. So when I open a page I can see if there is any custom javascript).
  7. Put in the region source:
    <script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.easing.js"></script>
    <script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.collapse.js"></script>
    <script type="text/javascript">
    	      slide: true,
    		  speed: 500,
    		  easing: 'easeOutCubic'
  8. Make a new report region which makes the list-output as shown under. (the most important thing is to give the first ul-element the id “collapser” and keep the structure of the elements UL en LI the same)
    <ul id="collapser">
      <li>Item 1
    	  <li>Item 1.1</li>
    	  <li>Item 1.2
    		  <li>Item 1.2.1</li>
    		  <li>Item 1.2.2</li>
    	  <li>Item 1.3</li>
      <li>Item 2
    	  <li>Item 2.1
    		  <li>Item 2.1.1</li>
    		  <li>Item 2.2.2</li>
      <li>Item 3</li>
  9. And voilà! The list is done! Easy, not?!
<ul id=”collapser”>
<li>Item 1
<li>Item 1.1</li>
<li>Item 1.2
<li>Item 1.2.1</li>
<li>Item 1.2.2</li>
<li>Item 1.3</li>
<li>Item 2
<li>Item 2.1
<li>Item 2.1.1</li>
<li>Item 2.2.2</li>
<li>Item 3</li>

Tabs within APEX (using jQuery)

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

The solution uses APEX 4 built-in jQuery libraries.

The idea is to create a “containing region”, that is a region that will contain the sub-regions.

Those sub-regions then will appear as a tab on your page.

Create a template for the containing region

Go to “Shared Components” – “Templates”, filter on Region Type templates:

Press the create button

choose Region

choose “From Scratch”

Give the template a meaningful name. In my example I use “jQuery Tabs”
Choose “Custom 1” for the template class
Press the “Create button”
Editing the template
You now have the “jQuery Tabs” template in your list.
Open it by clicking on the name:

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

We must place some code in the “Definition” section:

<div style="clear:both;"></div>
<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>

Go to the “Sub Regions” section, this consists of three items:

  1. Header Template
    <ul style="height: auto;">#ENTRIES#</ul>
  2. Header Entry Template
    <li><a href="##REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION_TITLE#</a></li>
  3. Template
    <div id="#REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION#</div>

The template is now ready. Save the template.

Next step is creating a Containing Region on the page
Go to the page:

Create a HTML-region:

The Title doesn’t really differ that much, it’s only for you reference.
Choose the correct Region Template (“jQuery Tabs”)
Leave the rest as defaulted.

leave the presented HTML region source empty. Remember we’re going to use this region only as a container for sub regions
Open the region we just created:
Go to the “Header and Footer” section and place this code in the footer:

<script type="text/javascript">
 apex.jQuery(function() {

Now open each region that should be transferred into a tab and change the parent region (within the section “User Interface”) to the newly created container-region (“jQueryTabs”)

Repeat this step for each tab you need.
Ready.. Good luck!

Oracle APEX report refresh the easy way

I’ve seen numerous solutions for partial page refreshes. All using javascript, timers etc.

I just want a report to refresh itself after 5 seconds.

I add this to the region-footer:

<script type="text/javascript">
setInterval( "$a_report('##REGION_ID#'.substring(1), 1, 7, 7);", 5000);

On request of Patrick Wolf (in the remarks below) this also works:

<script type="text/javascript">
  setInterval("jQuery('##REGION_ID#').trigger('apexrefresh');", 5000);

No extra javascript functions, no dificult parameters, works for multiple regions..

Essentially it will do a region refresh using the $a_report apex-javascript function only once (after 5 seconds)..

But when the region gets refreshed…. this region-footer gets executed again…. hence: a refresh every 5 seconds.

$a_report takes these parameters:

  1. the region_id that needs to be refreshed. (apex puts an ‘R’ in front of it. the substring(1) removes the ‘R’ again)
  2. the 1st record to show
  3. the last record to show
  4. the number of records to show
  5. sorting

I hope this helps.

APEX built-in variables

For all you guys ‘n dolls here an overview of the built-in apex variables and how/when to use ’em:

  • APP_ID
  • Application Express SCHEMA OWNER

taken from this source

Variables can be referred to in three ways:

  • Bind variable ( :APP_ID )
    Used inside APEX in SQL and PL/SQL
  • V and NV functions ( NV(‘APP_ID’) )
    Used outside APEX ( in PL/SQL packages for example )
  • Substitution strings ( &APP_ID. )
    Used inside APEX in items for example


Apex listener v 1.1

I simply love the Apex Listener that Oracle sent out a couple of weeks ago.

I now allways use it to do my (one developer = me) development, for a number of reasons.

  1. It’s easy to set up.
  2. I can edit my CSS and Images on the filesystem (no WebDav needed).
  3. It saves me resources within the database

I also use it for one of the production systems, because APEX would break down when doing heavy database work. (It’s a 10g and EPG is officially not supported). With the apex-listener the system gets a bit sluggish, but at least continues to work.

Warp11 Apex plugins available

Today I published the plugins we’ve written to the plugin-library at

  1. Warp11 CSS Menu
    The menu is based upon Jessica Wilson’s drop-down menu, but has unlimited levels and more control on the styles used. We also used a different JQuery plugin
  2. Warp11 GMaps item
    The item shows a Google map with some buttons within the application-builder to play around with

You can have a look at .


APEX 4.0 on Oracle database with EPG

Ok. I just finished creating two extra Apex-environments (TEST and PROD)

Where I mention “SQL>” you are logged in in your sqlplus as SYS as SYSDBA privileges.

I now have a plan-of-attack for getting Apex 4 on a 10g database with EPG:

  1. Start the APEX installer (I unzipped my into D:APEX, so there is a D:APEXapex folder!)
    @apexins apex_ts apex_files_ts /i/
  2. load the apex images
    @apxldimg D:APEX
  3. start the EPG configuration:
    @apex_epg_config D:APEX
  4. unlock some accounts:
    alter user ANONYMOUS account unlock;
    alter user APEX_PUBLIC_USER account unlock;
    alter user APEX_040000 account unlock;
    alter user FLOWS_FILES account unlock;
    alter user XDB account unlock;
  5. check the DAD authorization:
    select * from dba_epg_dad_authorization;

    if no rows are returned use this:

    exec dbms_epg.authorize_dad('APEX', 'ANONYMOUS');
  6. set the SHARED_SERVERS environment:
  7. execute this
         l_configxml XMLTYPE;
         l_value VARCHAR2(5) := 'true'; -- (true/false)
           l_configxml := DBMS_XDB.cfg_get();
           IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0
             -- Add missing element.
             SELECT insertChildXML(l_configxml, '/xdbconfig/sysconfig/protocolconfig/httpconfig', 'allow-repository-anonymous-access', XMLType('' || l_value || ''), 'xmlns=""')
             INTO l_configxml
             FROM dual;
             DBMS_OUTPUT.put_line('Element inserted.');
             -- Update existing element.
             SELECT updateXML(DBMS_XDB.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()', l_value, 'xmlns=""')
             INTO l_configxml
             FROM dual;
             DBMS_OUTPUT.put_line('Element updated.');
           END IF;
  8. Find the HTTP port on which the EPG is running:

    you can change it using:

    exec DBMS_XDB.SETHTTPPORT(portnumber of your choice);
  11. start your browser and open the apex page.

I hope this helps, but be aware EPG on 10g is not supported by Oracle.


APEX Popup LOV with more columns

Today I found a really neat plug-in to Oracle’s APEX.

Have a look here:

All’s well, ut there is a downside to using this plug-in. Cascading Parent LOV’s are not supported yet. I hope they will soon. So I came up with an alternative that does use the native popup LOV.

  • I altered the template that displays the LOV to use a fixed with font for the body
  • I use LPAD and RPAD in combination with ‘ | ‘ to neatly outline the columns

the result looks like this:

Maybe is not the most beautiful solution, but it suffices for now.