Color Spectrum in APEX report

April 22, 2016 at 9:06 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | Leave a comment
Tags: ,

I recently had the need to display a color spectrum, depending on a column value, in a report. The colors had to go from red (column value zero) to green (column value 100).


To achieve this I added a formula to my query that would generate an RGB value based on the column value. I then used this value to render a circle of that color, with the column value inside of it.

Here  is the sample query for the report. The report has two columns:
VALUE: The value I wish to display.
VALUE_COLOR: The generated RGB color that gets used in the VALUE’s HTML Expression.

select level as value
      ,'rgb('||round((255 * (100 - level)/100),0)
       ||','|| round((255 * level)/100,0) ||',0'||')' 
       as value_color
from dual
connect by level <= 100
order by 1

I used some custom HTML in the Value column’s HTML Expression to render a Font Awesome circle in the color of the generated RGB value, with the column value inside.


That’s it. Have fun with it.





SQL Developer SSH connection

April 21, 2016 at 9:47 am | Posted in Oracle Application Express (Apex), Oracle Developement, Oracle Tools, PL/SQL | 7 Comments
Tags: ,

If you have to access databases that can only be reached through SSH connections, you can configure SQL Developer (4.1) to reach them.

In my example, a .pem key file is required to establish the SSH connection.

  1. In SQL Developer select SSH from the View menu. The opens up the SSH Hosts dock.
  2. Right click the SSH Hosts node in the dock and select New SSH Host from the context menu.
  3. Fill out the New SSH Host form
    1. Name: The name of your connection
    2. Host: Remote host address
    3. Port: Should default to 22
    4. Username: The username of the SSH connection
    5. Use key file: Browse to the .pem key file needed for this connection
    6. Add a Local Port Forward: Check this to forward to a local port.
      1. Name: A name for this port forward
      2. Host: The same as above
      3. Port: Database port of the remote host
      4. Automatically assign local port (selected) or you can choose to use a specific local port if you wish
  4. Click OK.


A node with your new connection should now appear in the SSH Hosts connections.

Right click the node and select Test from the context menu. A message with a success message should pop up.

Create a new database connection:

  1. Fill in the connection name, username and password for the remote database.
  2. For Connection Type choose SSH.
  3. Select the newly created Port Forward from the select list.
  4. Click Service Name and fill in the service name of the remote database.
  5. Test and Save the connection.

That’s it. You should now be able to connect via the SSH Tunnel.

APEX 5: Convert Skillbuilder’s Modal to Universal Theme

June 1, 2015 at 10:52 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 5 Comments
Tags: , , , , , ,

If you have implemented the Skillbuilder’s Modal Page Plugin in an application of a previous version of APEX, and want to move to the APEX 5 Universal Theme, you will need to make some modifications. If you continue in an older theme, the plugin should continue to work as normal.

Since the APEX 5 Universal Theme has a modal page type, the plugin is no longer needed. The steps below allow you to convert the Skillbuilder’s Modal Plugin to the Universal Theme modal page. The steps outline the changes needed to make to the application, the modal page, and the parent page (the one that calls the modal page).


Change theme to Universal Theme.

  1. Go to Shared Components -> Themes.
  2. Click Create Theme.
  3. Select  From the Repository, then click Next.
  4. Choose User Interface Desktop, then click Next.
  5. Select Theme Type Standard Themes, Theme Universal Theme, click Next.
  6. Click Create.
  7. After theme is created select Switch Theme.
  8. Select the current theme in the top select list and the Universal Theme in the bottom select list.
  9. Depending on your application select a Reset Grid option. Click Next.
  10. On the Verify Compatibility page, make the necessary adjustments for your page and click Next.
  11. Click Switch Theme.

The Universal Theme should now be the current theme of your application.

Modal Page:

  1. Page Attributes
    1. Change page mode to Modal Dialog.
    2. Change Dialog Template to Modal Dialog.
      Modal Page Attributes
  2. Cancel Button
    1. Change Action to Defined By Dynamic Action.
    2. Add a dynamic action on click of the cancel button, with a true action of Cancel Dialog.
      Cancel Button DA
  3. Page Processes and Branches
    1. Add a new page process of type Close Dialog, to fire after any database activity/row processing is done. Include a success message if desired.
      Close Dialog Process
    2. Remove any branches from the modal page. They are no longer needed.

Parent Page

  1. Remove Dynamic Actions
    If you followed my previous post, the parent page will have three dynamic actions to handle the Skillbuilder’s Modal page: One that fires on the Create button, one that fires on the report’s edit link, and one that handles the Auto Close of the modal in order to refresh the report on the parent page.
    All dynamic actions that have to do with the plugin will need to be removed.
  2. Create Button
    1. The create button that opens the modal to a new, empty record. Change  the Behavior -> Action to Redirect to Page in this Application. Reference the modal page number and also clear the cache for that page. In this example the modal page number is 2.
      Create Button
  3. Edit Links
    In this example there is a report on the EMP table. Each row of the report contains an edit link which opens the modal page (2) to allow editing of the data. In my previous blog post I explained the changes you have to make in order to make the Skillbuilder’s Modal to pop-up. We now need to undo these changes and use the new APEX 5 methodology. In this example, the edit link exists on the EMPNO column.

    1. Remove the Link Attributes from the column link.
    2. Change the Link Target to navigate to the modal page (2) and set the modal page’s primary key (EMPNO).
      Column Link
  4. Dynamic Actions to handle report refresh on modal close.
    Create a dynamic action that fires on the new Dialog Closed event. This action needs to be attached to the report region.

    1. Right-click on the report region and select Create Dynamic Action from the context menu.
      Close Dialog DA
    2. Add a true action to refresh the report.
      Refresh Report DA
  5. Dynamic Action to handle the success message on modal close.
    To do this we need some JavaScript to capture the success message, and create a new div to display the message on the parent page.

    1. Right-click on the report region and select Create Dynamic Action from the context menu.
      DA for Success Message
    2. Create a true action of type JavaScript to display the success message. Add the following code to the true action:
      //Capture success message
      var lSuccessMsg;
      if ( ) {
        lSuccessMsg =;
      } else {
        /* Fallback for 5.0.0 */
        lSuccessMsg = unescape(;
        //Remove checksum
        lSuccessMsg = lSuccessMsg.substr(0,lSuccessMsg.indexOf('/'));
      //Construct HTML to emulate standard success message alert
      var successHTML;
      successHTML = '<div class="t-Body-alert" id="customSuccessMessage">';
      successHTML += ' <div class="t-Alert t-Alert--defaultIcons t-Alert--success t-Alert--horizontal t-Alert--page t-Alert--colorBG is-visible" id="t_Alert_Success" role="alert">';
      successHTML += ' <div class="t-Alert-wrap">';
      successHTML += ' <div class="t-Alert-icon">';
      successHTML += ' <span class="t-Icon"></span>';
      successHTML += ' </div>';
      successHTML += ' <div class="t-Alert-content">';
      successHTML += ' <div class="t-Alert-header">';
      successHTML += ' <h2 class="t-Alert-title">'+lSuccessMsg+'</h2>';
      successHTML += ' </div>';
      successHTML += ' </div>';
      successHTML += ' <div class="t-Alert-buttons">';
      successHTML += ' <a href="javascript:void(0);" onclick="$(\'#customSuccessMessage\').remove();" class="t-Button t-Button--noUI t-Button--icon t-Button--closeAlert" type="button" title="Close">';
      successHTML += ' <span class="t-Icon icon-close"></span>';
      successHTML += ' </a>';
      successHTML += ' </div>';
      successHTML += ' </div>';
      successHTML += ' </div>';
      successHTML += '</div>';
      // Display Success Message

All the pieces should now be in place to have the Universal Theme modal windows behave like the Skillbuilder’s Modal Plugin.




SQLd360 v3 now available, new features and (hopefully) less bugs!

February 27, 2015 at 8:51 am | Posted in Oracle Application Express (Apex), Oracle Developement, Oracle Performance, Oracle Tools, PL/SQL | Leave a comment

SQLd360 v3 now available, new features and (hopefully) less bugs!.

Meet “edb360”: a free tool that provides a 360-degree view of an Oracle database

February 20, 2014 at 9:50 am | Posted in Oracle Developement, Oracle Performance, Oracle Tools, PL/SQL | Leave a comment
Tags: , , , ,

Meet "edb360": a free tool that provides a 360-degree view of an Oracle database.

Find ancestor from hierarchy

October 2, 2013 at 11:06 am | Posted in Oracle Developement, PL/SQL | 4 Comments
Tags: , , , , , , , , , , , ,

I needed to find a quick way to select a particular ancestor for a child in a hierarchical table. For example: “Find the manager of SMITH” (parent), or “Find SMITH’s manager’s manager” (grand parent).

Using the emp table as an example, I came up with the following solution:

SELECT sys_connect_by_path(ename, ':') path
      ,ltrim(regexp_substr(sys_connect_by_path(ename, ':'), ':[A-Z]{1,}', 1, &amp;x), ':') ancestor
  FROM emp
 WHERE connect_by_isleaf = 1
CONNECT BY PRIOR mgr = empno

This query traverses the hierarchical tree up from the child, and limits it to only that row where the employee I’m looking for is the leaf (connect_by_isleaf=1), i.e. the lowest in the hierarchy.  Then I use the sys_connect_by_path to fetch the entire hierarchical path, and utilize the regexp_substr function to pick out the ancestor. Which ancestor is selected depends on &x. If x=1 then the child itself is selected. If x=2, the parent is selected; x=3 is the grand parent and so on.

SQL > /
Enter value for x: 3
PATH                           ANCESTOR
------------------------------ ----------

Lastly, here are a couple of examples that show traversing the path forwards and backwards from/to a given descendant:

col ename for a20
col root for a20
col path for a30
PROMPT Forwards path:
PROMPT ==============
SELECT connect_by_root ename root
     , sys_connect_by_path(ename, ':') path
     , ename
  FROM emp
 WHERE ename          = 'SMITH'
START WITH ename       = 'KING'
CONNECT BY prior empno = mgr

PROMPT Backwards path:
PROMPT ===============
SELECT connect_by_root ename ename
     , sys_connect_by_path(ename, ':') path
     , ename root
  FROM emp
 WHERE connect_by_isleaf = 1
 START WITH ename       = 'SMITH'
CONNECT BY empno = prior mgr

Forwards path:

ROOT                 PATH                           ENAME
-------------------- ------------------------------ --------------------
KING                 :KING:JONES:FORD:SMITH         SMITH

Backwards path:

ENAME                PATH                           ROOT
-------------------- ------------------------------ --------------------
SMITH                :SMITH:FORD:JONES:KING         KING

Apex JSTree Nodes Remain Expanded After Page Refresh

February 13, 2013 at 3:30 pm | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | Leave a comment
Tags: , , ,

To prevent a navigation JSTree to collapse after the page has been submitted, you need to have a page or application item to hold the menu ID value, so that the JSTree can remember which node was clicked after the page was refreshed.

In the Tree Attributes page you can then set the Selected Node Page Item to that new item and then the tree will remember which node to expand when it re-loads.

In my example I’ve created an unrestricted application item AI_TREE_NODE. You can alternatively use a page zero item or a page item.

In the link column of the tree SQL statement, I set AI_TREE_NODE to the menu id.:

select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status,
       "ENAME" as title,
       null as icon,
       "EMPNO" as value,
       ename as tooltip,
       'f?p=&APP_ID.:3:&APP_SESSION.::::AI_TREE_NODE:' || EMPNO as link
from "#OWNER#"."EMP"
start with "MGR" is null
connect by prior "EMPNO" = "MGR"
order siblings by "ENAME"

In the Display Attributes section of the Tree Attributes I set the Selected Node Page Item to the application item AI_TREE_NODE.

Screen Shot 2013-02-13 at 2.23.29 PM

Now, whenever you click a node, the tree will remember which node was clicked, and expand the tree accordingly.

Screen Shot 2013-02-13 at 2.28.31 PM

Thanks to Doug Gault for help on this.

Using Notepad++ with PL/SQL

January 14, 2013 at 10:54 am | Posted in Oracle Developement, PL/SQL | Leave a comment
Tags: , ,

Neat little comment trick.

November 7, 2012 at 5:55 pm | Posted in Oracle Developement, PL/SQL | Leave a comment
Tags: , ,

I often have some test code that spans multiple lines in my PL/SQL programs that I need to comment/un-comment frequently.

When placing the comment marks (/* */) at the beginning and the end of the test code, you always have to know where the first and last lines of the code are in order to comment/un-comment them.

With this little trick, you only need to know where the test code section starts, and the end of the code section comments/un-comments it self!.

All you have to do is place a line comment (–) in front of the end comment (*/).
If you now place a line comment in front of the begin comment (/*), the code section becomes active (un-commented). If you then remove the line comment (–) from the begin comment (/*), the code section will be commented out.

Commented out:

 l_count NUMBER := 0;
   INTO l_count
   FROM user_objects;
  dbms_output.put_line('I counted ' || l_count);

Code not commented out:

 l_count NUMBER := 0;
   INTO l_count
   FROM user_objects;
dbms_output.put_line('I counted ' || l_count);

So by commenting/un-commenting the first comment (/*), the entire code section gets affected.

LDAP Authentication with APEX

November 2, 2012 at 10:37 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 16 Comments
Tags: , , , ,

Here is a quick tutorial on how to set up LDAP authentication with Oracle Application Express 4.1.1.
Let’s assume that my LDAP server is on port 389. The ou group is “People”.
The first thing you need to do if working with Oracle database 11G, is to set up an access control list (ACL) for the Apex schema, so it can resolve and connect to the LDAP server. This step is not neccessary in database 10G.

-- Run as sysdba
  l_acl       VARCHAR2(100) := 'ldapacl.xml';
  l_desc      VARCHAR2(100) := 'LDAP Authentication for';
  l_principal VARCHAR2(30)  := 'APEX_040100'; -- upper case
  l_host      VARCHAR2(100) := '';
  -- Create the new ACL.
  -- Also, provide one starter privilege, granting the schema the privilege to connect.
  dbms_network_acl_admin.create_acl(l_acl, l_desc, l_principal, TRUE, 'connect');

  -- Now grant privilege to resolve DNS names.
  dbms_network_acl_admin.add_privilege(l_acl, l_principal, TRUE, 'resolve');

  -- Specify which hosts this ACL applies to.
  dbms_network_acl_admin.assign_acl(l_acl, l_host);


Now configue a new authentication scheme in Application Express to authenticate via LDAP.

In your Apex application go to Shared Components -> Authentication Schemes, and click the Create button.

Choose the radio button Based on a pre-configured scheme from the gallery, and click Next.

Provide a name, for example LDAP Authentication.

Select the scheme type LDAP Directory.

Now the new Settings region appears. Fill in the values as in the example below, and make substitutions to host, domain, ou, etc. as necessary.

Note that the substitution string %LDAP_USER% will contain the username that you will enter in the login screen.

For Microsoft Active Directory the entry is slightly different (make substitutions as necessary). Note that in this example, the Active Directory server name is

Once all fields are filled in, click the Create button. You should now see the newly created authentication screen in the list, and marked as Current.

Now try to log into your application.

If you have trouble logging in, you can use the PL/SQL procedure below to trap any error messages:

-- Code by Scott Spadofore
-- OTN:
  l_retval PLS_INTEGER;
  l_retval2 PLS_INTEGER;
  l_session dbms_ldap.session;
  l_ldap_host VARCHAR2(256);
  l_ldap_port VARCHAR2(256);
  l_ldap_user VARCHAR2(256);
  l_ldap_passwd VARCHAR2(256);
  l_ldap_base VARCHAR2(256);

  l_retval := -1;
  dbms_ldap.use_exception := TRUE;
  l_ldap_host := '';
  l_ldap_port := '389';
  l_ldap_user := 'uid=cruepprich,ou=People,dc=mydomain,dc=com';
  l_ldap_passwd := 'secret';

  l_session := dbms_ldap.init(l_ldap_host, l_ldap_port);
  l_retval := dbms_ldap.simple_bind_s(l_session,
  dbms_output.put_line('Return value: ' || l_retval);
  l_retval2 := dbms_ldap.unbind_s(l_session);

    dbms_output.put_line(rpad('ldap session ', 25, ' ') || ': ' ||
    rawtohex(substr(l_session, 1, 8)) || '(returned from init)');
    dbms_output.put_line('error: ' || SQLERRM || ' ' || SQLCODE);
    dbms_output.put_line('user: ' || l_ldap_user);
    dbms_output.put_line('host: ' || l_ldap_host);
    dbms_output.put_line('port: ' || l_ldap_port);

    l_retval := dbms_ldap.unbind_s(l_session);
Next Page »

Entries and comments feeds.