Setting And Retrieving CLOB values in APEX with JavaScript

September 1, 2015 at 12:36 pm | Posted in Oracle Developement | Leave a comment
Tags: , , , ,

Here is a very helpful blog post by Andy Tulley on how to set/get CLOB values from an APEX collection.

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.

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.




Set Session State with JavaScript and dynamic action

January 16, 2015 at 9:38 am | Posted in Oracle Developement | 6 Comments
Tags: , , , , ,

I have two page items, P1_FIRST_NAME and P1_LAST_NAME whose values I wish to remain in session state. Here are two ways you can achieve this.

To have item values persist in session state with JavaScript in APEX, you can use the following code:

apex.server.process(‘DUMMY’,{pageItems: ‘#P1_FIRST_NAME,#P2_LAST_NAME’},{dataType: “text”});

An alternative method would be using a dynamic action that fires a dummy PL/SQL process. The action allows you to specify page items to submit in session state.;

PL/SQL Process to Set Session State

PL/SQL Process to Set Session State

Random Data Generator

April 9, 2014 at 2:54 pm | Posted in Oracle Developement | 1 Comment
Tags: , , , ,

Today I came across this awesome website which will automatically generate data for you, and spit it out in different formats. These include XML, CSV, Excel, and more. But the coolest format of course is Oracle SQL. The website will generate the table creation script and the insert statements for your data:

 id number primary key,
 name varchar2(255) default NULL,
 city varchar2(255),
 age varchar2(50) default NULL,
 zip varchar2(10) default NULL,

INSERT INTO myTable (name,city,age,zip) VALUES ('Ian Parks','New Orleans',15,'8587');
INSERT INTO myTable (name,city,age,zip) VALUES ('Silas Blackburn','Dunbar',40,'97129');
INSERT INTO myTable (name,city,age,zip) VALUES ('Kenyon Hammond','Varsenare',60,'6744');

Note: You may have to work with the quotes.

How cool is that?

I’ve previously blogged about another way of generating random data, so take a look at

Generating Data: Increase a number by a random percentage

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.

Enkitec APEX Resource Center

December 18, 2013 at 3:17 pm | Posted in Oracle Developement | 2 Comments
Tags: , , ,

I’m proud to announce the launch of the Enkitec APEX Resource Center (aka ARC) this week. It consists of a number of APEX and PL/SQL specific articles, tips & tricks, how-to’s and demos.

Read some more about it at Scott Spendolini’s blog.

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

Geocoding with PL/JSON

September 17, 2013 at 3:23 pm | Posted in Oracle Developement | 7 Comments
Tags: , , , , , , ,

The Google Maps API geocoding web service makes it easy to geocode addresses. You can pass an address to the web service and receive either an XML or a JSON response back.

In this post I’ll show how to use the PL/JSON package to parse the JSON response from Google, in order to get to the various address elements and the coordinates associated with a given address.

PL/JSON is a library consisting of a PL/SQL package and some PL/SQL objects and types. It is designed to create and parse JSON. The documentation is a bit lacking, especially when it comes to handling a more complicated JSON object like the Google geocode response. Hopefully, this article will help to clarify its usage.

To get started, first download and install PL/JSON into a schema in your Oracle database. I used version 1.0.3. The installation is simply done by extracting the downloaded zip file, and running the install.sql script. Once installed, make sure all objects are valid. You can run a quick test of the package by running the json_test.sql included in the testsuite directory of the PL/JSON installation.

The Google Geocoding Web Service

Version 3 of the Google Maps API, no longer requires a key. So you can make a simple call to the service like this:

The result of the URL is a JSON object containing the address and geocode data.

To make sense of the JSON object you need to understand that curly brackets {} contain objects, and square brackets [] contain arrays. Strings are enclosed with double quotes, numbers are not. Data is represented in name/value pairs separated by colons.

The Google response consists of two objects: results and status. A successful response will have a status of “OK”.

The results object is an array of four other objects:

  1. address_components – an array of elements such as street number, city, state, etc.
  2. formatted_address – a single string containing the full address
  3. geometry – an array of coordinate data elements
  4. types – an array of strings, which in this case is always “street_address”.

Calling the Web Service

Calling the Google web service from within PL/SQL, I use the ult_http package.  Since the JSON response can be quite large, I use the utl_http.request_pieces function to fetch the result. This works around the 2000 byte limit of the utl_http.request function .  I simply loop through all the pieces returned, and concatenate them to a CLOB variable inside the procedure.

Note that I make the http request inside a WHILE loop (line 50). This is helpful when you attempt to geocode a number of addresses in rapid succession (e.g. by looping through a table with addresses that need to be geocoded). Google will return an error (OVER_QUERY_LIMIT) when it receives too many requests per second. So the loop waits two seconds before the next attempt. I try a total of three attempts. If I still don’t get an OK status back, I give up. Note that Google limits the free geocoding to 2,500 requests per IP per day. If that limit is reached, you also receive an OVER_QUERY_LIMIT error.

In order to get to the status of the response, I use the json() function to convert the JSON string to a JSON object (lines 64 and 67).

Parsing the JSON response

Now that we have our geocoded address stored in a JSON object, I can now use PL/JSON to extract the various elements we need.

Since the results object consist of an array, I pass this array to a JSON_LIST object (l_results) (line 96). This array, however, consists only of a single element, so I pass this to a variable of type JSON, named l_tempobj (line 99).

The variable l_tempobj now contains the JSON of the four elements listed above (address_components, formatted_address, geometry, and status).

Since the address_components is yet another array, I pass it to another JSON_LIST object named l_addr_comps (line 102). Now I can loop through the address components and test the types array element to find out what type of address component I’m dealing with. I use the CASE statement (line 117) to test the types and to assign the address elements to their individual PL/SQL variables.

Once the loop is done, I have all my address components I looked for, but I still want to get to the coordinates. Those are stored in the third array element (geometry) of l_tempobj.  I pass the location element to the l_loc variable (line 140) of type JSON., then extract the lat/lng strings from it, and pass them to PL/SQL varchar2 variables (lines 146 and 147).

At this point the procedure is done, and I print the results.


The JSON and JSON_LIST data types are created on installation of PL/JSON. When parsing through a JSON object, you need to be keenly aware of what you are processing: Whether it is a nested JSON object or a string or number. Notice that I had to use the get_number function to extract lat/lng, while using get_string to get the status.

Use json_list() to pass the value of an element that consists of an array:

"address_components" :  [{ "name 1" : value} ,{"name 2" : "value"},{"name 3" : "value"}]

Use json_ext.get_string (or get_number) to get the value of a non-array element:

"status" : "OK"

Other errors from the Google API:

ZERO_RESULTS – no address data was fund

REQUEST_DENIED – most likely the sensor parameter was not specified correctly. Check the error_message text included in the response.

I’ve also included a few commented out <obj>.print statements for you to see the contents of various objects.

Let me know how you fare with this. Comments are always appreciated.

  l_obj           json;
  l_results       json_list;
  l_tempobj       json;
  l_addr_comps    json_list;
  l_addr          json;
  l_typesarr      json_list;
  l_geom_obj      json;
  l_loc           json;

  l_status        VARCHAR2(255);
  l_types         VARCHAR2(30);
  l_short_name    VARCHAR2(200);
  l_street_number VARCHAR2(200);
  l_street        VARCHAR2(200);
  l_city          VARCHAR2(200);
  l_state         VARCHAR2(30);
  l_zip           VARCHAR2(10);
  l_county        VARCHAR2(30);
  l_country       VARCHAR2(10);
  l_tries         PLS_INTEGER := 0;
  l_address       VARCHAR2(4000);
  l_request       VARCHAR2(4000);
  l_pieces        utl_http.html_pieces;
  l_json          CLOB;
  l_lat           VARCHAR2(40);
  l_lng           VARCHAR2(40);

  e_query_limit  EXCEPTION;
  e_zero_results EXCEPTION;


  dbms_application_info.set_module(module_name => l_prc, action_name => 'START');

  -- replace empty spaces with +
  l_address := REPLACE(TRIM(p_address), ' ', '+');


    l_request := '' ||
                 l_address || chr(38) || 'sensor=false';

    l_tries  := 0;
    l_status := NULL;
    l_json   := NULL;

    -- Try three gets in case of OVER_QUERY_LIMIT errors
    WHILE nvl(l_status, '*') != 'OK' AND l_tries < 3
      dbms_application_info.set_client_info(client_info => 'HTTP REQUEST try ' ||

      -- JSON object may be long, so split it into pieces
      l_pieces := utl_http.request_pieces(l_request);

      FOR i IN 1 .. l_pieces.count
        l_json := l_json || l_pieces(i);
      END LOOP;

      -- Create json object from google response
      l_obj    := json(l_json);

      -- Check the response status. If it is OK then proceede with parsing.
      l_status := json_ext.get_string(l_obj, 'status');

      IF l_status != 'OK'
        dbms_output.put_line(l_prc || '  status [' || l_status || ']');
        IF l_status = 'OVER_QUERY_LIMIT' AND l_tries >= 3
          dbms_output.put_line(l_prc || ': query limit reached. Exiting');
          RAISE e_query_limit;
        ELSIF l_status = 'ZERO_RESULTS'
          RAISE e_zero_results;
          l_tries := l_tries + 1;
        END IF;

      END IF;

      l_json := NULL;



      dbms_application_info.set_client_info(client_info => 'PARSE JSON');

      -- The overall JSON is an array named results
      l_results := json_list(l_obj.get('results'));

      -- There is only a single element in the results array, so get the first (and last) one
      l_tempobj := json(l_results.get(1));

      -- The next level contains an array named address_components
      l_addr_comps := json_list(l_tempobj.get(1));

      -- loop through the address components and test the types array for address elements
      FOR i IN 1 .. l_addr_comps.count
        l_addr := json(l_addr_comps.get(i));

        l_typesarr := json_list(l_addr.get('types'));

        -- Types is not a json array, but a string array so we have to get
        -- the first element using the types[1] syntax
        l_types      := json_ext.get_string(l_addr, 'types[1]');
        l_short_name := json_ext.get_string(l_addr, 'short_name');

        CASE l_types
          WHEN 'street_number' THEN
            l_street_number := l_short_name;
          WHEN 'route' THEN
            l_street := l_short_name;
          WHEN 'locality' THEN
            l_city := l_short_name;
          WHEN 'administrative_area_level_1' THEN
            l_state := l_short_name;
          WHEN 'administrative_area_level_2' THEN
            l_county := l_short_name;
          WHEN 'postal_code' THEN
            l_zip := l_short_name;
          WHEN 'country' THEN
            l_country := l_short_name;
        END CASE;

      END LOOP;

      -- now get lat/lng
      l_geom_obj := json(l_tempobj.get(3));

      l_loc := json_ext.get_json(l_geom_obj, 'location');

      l_lat := to_char(json_ext.get_number(l_loc, 'lat'));
      l_lng := to_char(json_ext.get_number(l_loc, 'lng'));

      dbms_output.put_line('Address: ' || l_street_number || ' ' ||
                           l_street || ' ' || l_city || ' ' || l_state || ' ' ||
                           l_zip || ' ' || l_county || ' ' || l_country);
      dbms_output.put_line('lat/lng: ' || l_lat || ' ' || l_lng);

      WHEN e_query_limit THEN
        raise_application_error(-20001, 'GOOGLE QUERY LIMIT REACHED');
        dbms_output.put_line(l_prc || ': ' || SQLERRM);
    WHEN e_zero_results THEN
      dbms_output.put_line(l_prc || ': Zero Results. Getting next address');


  dbms_application_info.set_client_info(client_info => 'DONE');

END geocode_address;

Using Notepad++ with PL/SQL

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

Next Page »

Entries and comments feeds.