APEX 5 Login Screen

July 23, 2015 at 11:48 am | Posted in Oracle Developement | Leave a comment
Tags: , , , ,

The login screens of some of the APEX 5 sample applications have a nice look and include a check mark when you enter text into the username/password fields. This article will demonstrate how to create your own login page to look like that of some of the sample applications.

Edit your Login page (101):

  • Change login region template to Login.
  • Changes to Username and Password fields:
    • Change Template to Hidden.
    • Change Value Placehoder to Username / Password respectively.
    • Change Value Required to Yes.
    • Change CSS classes:
      • Username: icon-login-username
      • Password: icon-login-password
    • Change Post Text for username and password to
      <span class=”t-Login-iconValidation a-Icon icon-check”></span>.
  • Change the Login button Button Position to Next.

Save the changes and run the page. The result should look like this:


To can add one of the application icons from the the packaged applications select on of the icons my previous blog post and make the following changes:

Navigate to Edit Application Definition -> User Interface, and edit the interface for your application.

Edit User Interface

Under Cascading Style Sheets add the this CSS file: #IMAGE_PREFIX#pkgapp_ui/css/5.0#MIN#.css

Add CSS file

On the Login page (101) click the Log In region and add the name of the icon class to Icon CSS Classes:


Save and run the log in page. It should look something like this:


Where is my schema?

July 17, 2015 at 5:17 pm | Posted in Oracle Developement | Leave a comment
Tags: , , ,

I just came accross a little nuisance: During attempt to load some spreadsheet data into a new table using the APEX Data Load wizard in the SQL Workshop, I saw that the Schema select list was empty:

The schema select list is empty.

The schema select list is empty.

The issue was that the parsing schema was granted CREATE ANY TABLE. However for this list to be populated, the parsing schema needs the CREATE TABLE privilege.

Use JavaScript to add/remove elements from a delimited list

July 7, 2015 at 6:30 pm | Posted in Oracle Developement | 2 Comments

In my APEX application I have a page item that stores a colon delimited list of items, and I want to use JavaScript to add or remove elements from that list.

P1_LIST =”Black:Red:Gold:Blue:White”

Adding An Element

To add an element to the list with JavaScript you could  simple concatenate a color preceded by a colon:
newColor = ‘Green’
$s(‘P1_LIST’, $v(‘P1_LIST’) + ‘:’ + newColor);

This is problematic, if P1_LIST were empty. Then you could end up with:
P1_LIST = “:Green”

To avoid this you can first split the list into an array, “push” the new color to the array, and then write it back as a delimited list:

newColor = 'Green';
arr = $v('P1_LIST').split(':');
$s('P1_LIST', arr.join(':'));

Removing an Element:

To remove a color from our list is a little trickier. But working with arrays helps. Let’s say we want to remove Blue. We’ll pull the delimited list back into an array (arr). We’ll find the array index of Blue, then loop through arr and push all elements except Blue into a new array (arr2). Lastly we’ll write the new array back to the page item:

arr = $v('P1_LIST').split(':'); //change list into an array
arr.splice(arr.indexOf('Blue'),1); //remove the color blue
$s('P1_LIST', arr.join(':')); //write array back to list

Thanks to Sentinel for the simplified version using splice();

Hierarchical Query: Roll-up leaf nodes to ancestors.

June 26, 2015 at 11:54 am | Posted in Oracle Developement | Leave a comment

This query may be more applicable to a bill of materials type of query. I’ve added a new column to the EMP table (value number). Then I only populated a value for this column for the leaf nodes, i.e. the employees lowest in the hierarchy (ADAMS, SMITH, ALLEN, WARD, MARTIN, TURNER, JAMES, MILLER).

This query the sums up these leaf node values to their manager, and to the manager’s manager.

with totals as (
  select empno
        ,level lev
        ,rownum rn
        ,sum(nvl(value, 0))
             partition by connect_by_root(empno)
             ) tot_val
   from emp t
connect by prior empno=mgr

, lev1 as (
  select  empno
    from totals
   where lev = 1

select lpad(' ', 2*level, ' ')
       ||ename name
  from lev1
connect by prior empno=mgr
start with mgr is null 

Hierarchy Rollup

Hierarchical Query: Get the ancestors in top down order

June 25, 2015 at 4:39 pm | Posted in Oracle Developement | Leave a comment

This query starts with the leaf node and climbs up the hierarchy to the top ancestor. It then displays the result in the usual top-down order:

CMR@vrep > list
 select lvl
   from (select level    lvl
	    ,lpad(' ', 2 * (  
                     over () - level)
                 , ' ')
	     ||', '
	     ||ename employee
	    ,rownum  rn
       from emp
     connect by empno=prior mgr
     start with ename=upper('&ename'))
  order by rn desc

CMR@vrep > /
Enter value for ename: adams

---------- --------------------------
     4      7839, KING
     3        7566, JONES
     2          7788, SCOTT
     1           7876, ADAMS

CMR@vrep >


APEX 5: JavaScript Debugging

June 19, 2015 at 8:45 am | Posted in Oracle Developement | Leave a comment

Here is a re-blog of Matt Mulvaney’s post about debugging JavaScript in Oracle APEX 5:


ORDS 3 – Enabling RESTful service for database tables

June 18, 2015 at 2:26 pm | Posted in Oracle Developement | Leave a comment

We’ll take a look at how to enable a RESTful service in ORDS 3.0 via PL/SQL, and how to retrieve some data and metadata from this service.

ORDS 3.0 is installed and configured with RESTful services.
A schema named ORDSTEST with the EMP and DEPT tables is installed.
DBA privileges are needed to execute the PL/SQL.

RESTful Service

To expose tables through RESTful calls, the schema needs to be enabled and given a base URI path. In our example we will enable the ORDSTEST schema with the URI path ot.

   p_schema => 'ORDSTEST',
   p_url_mapping_type => 'BASE_PATH',
   p_url_mapping_pattern => 'ot',
   p_auto_rest_auth => FALSE);

 -- 2. Enable individual tables in the schema
   p_schema => 'ORDSTEST',
   p_object => 'EMP',
   p_object_type => 'TABLE',
   p_object_alias => 'emp',
   p_auto_rest_auth => FALSE);
   p_schema => 'ORDSTEST',
   p_object => 'DEPT',
   p_object_type => 'TABLE',
   p_object_alias => 'dept',
   p_auto_rest_auth => FALSE);

To disable a schema use:

-- 3. Disable the schema:
  p_enabled => FALSE,
  p_schema => 'ORDSTEST');

To re-enable the schema enable the schema and it’s objects again.


To check whether the RESTful service works, check the metadata catalog (don’t forget the trailing forward slash):



   metadata: [
           name: "dept",
           links: [
                   rel: "describedby",
                   href: "http://vm2:8080/ords/ot/metadata-catalog/objects/dept"
                   rel: "canonical",
                   href: "http://vm2:8080/ords/ot/objects/dept/"
           name: "emp",
           links: [
                   rel: "describedby",
                   href: "http://vm2:8080/ords/ot/metadata-catalog/objects/emp"
                  rel: "canonical",
                   href: "http://vm2:8080/ords/ot/objects/emp/"

Follow the links to see metadata about the individual objects.


— To query table by primary key, simply add the key value to the end of the URI:

— To query a table with a predicate:

eq: equal to
lte: less than or equal to
gte: greater than or equal to
ne: not equal to

Reblog: Oracle Responsive Email Templates

June 11, 2015 at 8:37 am | Posted in Oracle Developement | 2 Comments

Vincent Morneau has done a fantastic job of creating responsive email templates for Oracle:


APEX 5: Packaged Application Icons

June 3, 2015 at 8:30 am | Posted in Oracle Developement | 3 Comments

To use some of the packaged application icons for your own applications, include the packaged apps css file in your application, and reference the appropriate css class for the icon you want.

Navigate to Edit Application Definition -> User Interface, and edit the interface for your application.

Edit User Interface

Under Cascading Style Sheets add the this CSS file: #IMAGE_PREFIX#pkgapp_ui/css/5.0#MIN#.css

Add CSS file

In your application create a div with a height and width, and include one of the icon classes to display the icon.

<div style="height: 100px; width: 100px;" class="app-application-standards-tracker"></div>

Here are the available packaged application icons:



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 | 2 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.




Next Page »

Create a free website or blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.


Get every new post delivered to your Inbox.