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.




SSH Login Without Password

May 28, 2015 at 2:11 pm | Posted in Oracle Developement | Leave a comment

Quick instructions on how to setup SSH login without password:

Upgrading to Application Express 5.0 in Your Oracle Database Release 12.1 Multitenant Container Database (CDB)

April 30, 2015 at 8:48 am | Posted in Oracle Developement | Leave a comment

This is a reblog from Jason Straub’s blog Application Express Nuggets, discussing how to upgrade APEX in CDB$ROOT of your Oracle 12.1 Container DB.


Oracle 12c Developer Days VM

April 24, 2015 at 10:36 am | Posted in Oracle Developement | Leave a comment

Get OS authentication to work with the Dev. Days VM

Reblogged from Stojan’s Oracle Blog.

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.