APEX 5 Theme/Static files issue (503 error)

September 7, 2016 at 10:32 am | Posted in Oracle Application Express (Apex), Oracle Developement | Leave a comment
Tags: , , , , ,

I’ve recently come across the issue of static images not working and my custom theme roller theme not being applied to my APEX 5 app.

When looking at the browser’s console, I noticed a bunch of errors:
Failed to load resource: the server responded with a status of 503 (Service Unavailable)

After digging around some and scratching my head for a while, the issue turned out to be expired APEX_REST_PUBLIC_USER and APEX_LISTENER database accounts.

When the passwords were reset for these accounts, the images came back and the custom theme was applied.

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 ( this.data.successMessage ) {
        lSuccessMsg = this.data.successMessage.text;
      } else {
        /* Fallback for 5.0.0 */
        lSuccessMsg = unescape(this.data.APEX_SUCCESS_MESSAGE);
        //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!.

Excel Style Row Numbering in SQL

February 3, 2014 at 12:59 pm | Posted in Oracle Application Express (Apex), Oracle Developement, Oracle Tools | Leave a comment
Tags: , , , , , , , , ,

Here is a trick to change your rownumbering (or any other sequential numbering) to the way Excel names their columns. The first 26 rows will be named A – Z, the next 26 AA – AZ, then BA – BZ, etc..

SELECT decode( ceil( ( ROWNUM / 26 ) - 1)
             , 0, ''
             , chr( ceil( (ROWNUM/26)-1 ) + 64)
     || chr(64 + decode( MOD(ROWNUM, 26)
                       , 0, 26
                       , MOD(ROWNUM, 26)
            ) col2
 FROM dual
CONNECT BY LEVEL <= 702; --limits the result to 702 rows 

Row	Label
1	A
2	B
3	C
4	D
5	E
28	AA
29	AB
30	AC
31	AD
32	AE
698	ZV
699	ZW
700	ZX
701	ZY
702	ZZ

Note that this will be good for 702 rows (nbr_of_letters * (nbr_of_letters+1)). After that you’d have to concatenate a third character, or write a function.

Configuring Apex Listener Version 2

February 21, 2013 at 10:52 am | Posted in Linux, Oracle Application Express (Apex), Oracle Developement, Oracle Tools | 21 Comments

“Where is the listenerConfigure page?”

That seems to be a common question when people attempt to deploy the Apex Listener Version 2.

Version 2 of the Apex listener is very different than version one. The configuration and maintenance is no longer done through web pages, but either through the command line or through SQL Developer. In this post I’ll try to explain how to configuration via the command line is made. I’ll dedicate another post to configuring the listener through SQL Developer.

In my example I have downloaded and unzipped the apex listener into the directory /u01/cmr. You will probably choose a different path, so make sure you substitute your path whenever necessary.

Before you configure the database connection of the listener, it is good to specify the directory where the configuration files will be stored. If you don’t do that, then the config files will be stored in /tmp/apex, which is not a good place.

To specify the configuration directory for the apex listener, navigate to the directory where you unzipped the listener files (/u01/cmr) and run:

java -jar apex.war configdir /u01/cmr

Note that whenever you run the java -jar apex.war command, it changes the files inside the apex.war archive. In this case I just picked the same directory where I  unzipped the files. The listener will create an apex directory here.

Now that we know where the configuration files will be, lets configure the database connection:

java -jar apex.war setup
 $ java -jar apex.war setup
 Feb 19, 2013 1:39:11 PM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
 INFO: Using configuration folder: /u01/cmr/apex
 Enter the name of the database server [localhost]:
 Enter the database listen port [1521]:
 Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
 Enter the database SID [xe]:vrep
 Enter the database user name [APEX_PUBLIC_USER]:
 Enter the database password for APEX_PUBLIC_USER:
 Confirm password:
 Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for APEX_PUBLIC_USER or, 3 to skip this step [1]:3
 Feb 19, 2013 1:39:50 PM oracle.dbtools.common.config.file.ConfigurationFiles update
 INFO: Updated configurations: apex

After this process is complete, you will find a newly created directory inside your config dir. Note that the name of the configuration folder is mentioned on top of the program output. The listener is now ready to be either run in standalone mode or to be deployed to your web server. You can run the listener in standalone mode to test the database connection:

$ java -jar apex.war standalone --apex-images /u01/apex/images

Note that I included the argument –apex-images. This will let the listener know from which directory to pull the images. In my case I unzipped the Apex installation files into /u01. When you run the command, the last line of the screen output will tell you the URL for connecting to Apex. If everything works, deploy your file to your web server.  That process hasn’t changed from version 1.
FYI: Inside the apex/ configuration directory is a file named apex.properties which contains the port number and the images directory. This file is read when the listener starts up in standalone mode.

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.

Getting and setting Apex page item values using $v(), $s(), $v2()

January 11, 2013 at 2:06 pm | Posted in Oracle Application Express (Apex), Oracle Developement | 24 Comments
Tags: , , ,

The Apex JavaScript API has some very convenient functions to access the values of page items.

For example, if you wante to access the contents of a text field with JavaScript, would would need to reference it something like this:


If the item you want to reference is a display only item, then the syntax changes:

x = $("#P2_DISPLAY_ONLY").text();

If you need to set the values of these items, the you need to use the varying syntax as well:

$x("P2_TEXT_FIELD").value = "Hello World!";


$("#P2_DISPLAY_ONLY").text("Hello World!");

Dealing with these various syntax constructs can be confusing. Fortunately the Apex JavaScript API makes this process much easier. To get page item values simply use $v(“<item_name>”):

x = $v("P2_TEXT_FIELD");
y = $v("P2_DISPLAY_ONLY");

To set the item values use:

$s("P2_TEXT_FIELD","Hello World!");
$s("P2_DISPLAY_ONLY","Hello World!");

See an example on my demo page.

The $v2() is handy if you need to access multiple items in an array. For example multiple selections from check boxes or from a shuttle control can be fetched as an array and handled that way in JavaScript:

myArr = $v2("P2_SHUTTLE_CONTROL");
for (idx=0; idx<myArr.length; idx++) {
  //do something with myArr[idx];

An example of this functionality can be seen on my demo page, where I also compare $v() and $v2() when used in an array.

Getting started with PL/PDF in Oracle Application Express

December 14, 2012 at 10:36 am | Posted in Oracle Application Express (Apex), Oracle Developement | Leave a comment
Tags: , , , ,

PL/PDF (www.plpdf.com) is a PL/SQL library for Oracle, which allows you to generate PDF documents.

You can use these libraries from within Apex to generate PDF reports, too. Here are a few simple steps on how to get it going.

  1. Download PL/PDF library
  2. Create a new schema on your database (maybe something like “tools”).
  3. Install PL/PDF into the new schema (see PL/PDF installation guide).
  4. Grant execute privileges on the PL/PDF packages to the Apex parsing schema. (In my case the parsing schema is SCOTT. For this demo, SCOTT only needs execute privileges on the PLPDF package.)
  5. Create synonyms for the PL/PDF packages (again in this case only for the PLPDF package).
  6. Grant execute on OWA_UTIL to the parsing schema (SCOTT).
  7. In the parsing schema, create a procedure to generate the PDF document by means of the PL/PDF library (see procedure PRINT_PDF) below.
  8. Create two pages in Apex:
    a. Page 200 with a text field (P200_TEXT), a button (P200_RUN_REPORT) that submits the page, and a branch that goes to page 201 when the submit button is clicked.Screen Shot 2012-12-13 at 4.43.40 PM

    b. Page 201 with a call to PRINT_PDF in a before header process (page 201).
    Screen Shot 2012-12-13 at 4.38.51 PM

    Screen Shot 2012-12-13 at 4.38.40 PM

  9. Run page 200
  10. Enter some text into the text field and click the button to submit the page.

Now the PDF document with the text entered on page 200 should show up.

create or replace
PROCEDURE PRINT_PDF (p_text in varchar2)
  l_blob BLOB;
  l_mime_type VARCHAR2(255) := 'application/pdf';


  owa_util.mime_header(l_mime_type, FALSE) ;
  htp.p('Content-Disposition: filename="sample.pdf"') ;
  htp.p('Content-Length: ' || dbms_lob.getlength(l_blob)) ;

Next Page »

Entries and comments feeds.