Tutorial: Using JavaScript and APEX_JSON to Create an Editable IR Column

As a general rule APEX developers should stick to the built-in, declarative components in APEX to build applications. These are, after all, what make APEX developers so productive! But like most rules, this one has exceptions. Perhaps you hit a limitation or want to do something a little differently to improve the user experience. In times like these you have to write some code - maybe a lot of code. But you know what, even writing custom code is getting easier with each release of APEX!

Believe it or not, there was once a time when jQuery wasn't included - you had to manually add it if you wanted to use it. Of course now it's included, but it's only necessary if Dynamic Actions (your first line of defense) aren't sufficient. In addition to jQuery, JavaScript APIs were added to APEX to help ease client side development. Now, with APEX 5.0 we have APEX_JSON - a PL/SQL API for generating and working with JSON in the database. What did we do before APEX_JSON? Never mind, some memories are just too painful...


The cool thing is that when you combine JavaScript/jQuery with APEX_JSON, you start to realize that there's not much you can't do in APEX.  If you haven't yet used these libraries to do something that's not possible "out of the box" then it's time to get your hands dirty! :D In this tutorial I'll walk you through the implementation of a new feature that puts these libraries to use: an Interactive Report (IR) with an editable column. Here's an overview of what we'll be doing:

1. Install the Sample Application
2. Add and configure the new editable column
3. Add a button and Dynamic Action for the client-side logic
4. Add an Ajax process to update the database with APEX_JSON

If you don't already have an APEX 5 workspace you can use for this tutorial, or if you'd rather use a new one just for testing, head to http://apex.oracle.com to request a free workspace. Let's get started!

Install the Sample Application

If you already have the Sample Application v5.0.3 or later installed you can skip this and move to the next part. If you just requested a new workspace from apex.oracle.com then you should have the Sample Application installed. Otherwise, login to your workspace, navigate to the Application Builder and follow these steps:

1. Click Create (for a new application).
2. Select Packaged Application.
3. Select Sample Database Application.
4. Click Install Application. If the app is already installed you can select Manage > Remove first.
5. Click Next.
6. Click Install Application.

Add and configure the new editable column

Let's take a look at the page we'll be modifying and add our new editable column. Navigate to the Application Builder and follow along:

1. Click the Sample Database Application from the list of applications.
2. Click Run Application. This will open a new window to run the app, continue in that window.
3. Click Products in the menu on the left. You should see the page below. We'll be modifying this report, making the Available column editable.

4. Click the Edit Page 3 link in the Developer Toolbar (anchored to bottom of the browser window). This will return you to the original window where you can work on the application.
5. Click Products in the component panel on the left. This will "select" the Products region and display its properties in the properties panel on the right.
6. In the properties panel, notice that Static ID is set to productsIRR. This is important as that adds an id attribute to the region's HTML code that the JavaScript code uses. If you want to reproduce this exercise in another application, be sure to add a Static ID for the region and update the JavaScript code accordingly.
7. Click the button to open the SQL Query in the code editor.

7. Update the SQL Query with the code below. This will add a new column that we'll be using to change the status of Available. The 4th parameter is adding some attributes to the elements that we'll use later on.

select p.product_id,
       p.product_name, 
       p.product_description, 
       p.category, 
       decode(p.product_avail, 'Y','Yes','N','No') product_avail,
       apex_item.select_list(
           1, 
           p.product_avail, 
           'Yes;Y,No;N', 
           'class="available-edit" data-product-id="' || p.product_id || '"'
       ) product_avail_edit,
       p.list_price,
       (select sum(quantity) from demo_order_items where product_id = p.product_id) units,
       (select sum(quantity * p.list_price) from demo_order_items where product_id = p.product_id) sales,       
       (select count(o.customer_id) from demo_orders o, demo_order_items t where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id) customers,
       (select max(o.order_timestamp) od from demo_orders o, demo_order_items i where o.order_id = i.order_id and i.product_id = p.product_id) last_date_sold,
       p.product_id img,
       apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID,P6_BRANCH:'||p.product_id||','||3,p_dialog=> 'null') icon_link,
       decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
       '') detail_img,
       decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
       apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id))
       detail_img_no_style,
       tags
from demo_product_info p

8. Click OK. This will save your changes in the code editor to the SQL Query in the properties panel.
9. Look back to the left panel and click Columns under Products to show the columns for the region. You should see both the previous PRODUCT_AVAIL column and the new PRODUCT_AVIAL_EDIT column at the bottom of the list.

10. Click the PRODUCT_AVAIL column.
11. Scroll down the properties in the properties panel on the right until you get to the Condition section.
12. Set Type to Request = value. 
13. Set Value to CSV.
14. Return to the component panel on the left and click the PRODUCT_AVIAL_EDIT column.
15. Scroll to the top of the properties panel on the right and set Heading to Available.
16. Set Column Alignment (under Layout) to center.
17. In the Enable Users To section, set everything to No.
18. Under the Condition section, set Type to Request != Value.
19. Set Value to CSV.
20. Under Security, set Escape Special Characters to No.
21. Click the play/run button in the upper right-hand corner of the page to save your changes and run the page. Your page should now see the editable column as shown below.

Add a button and Dynamic Action for the client-side logic

With the editable column in place, we need to add a new button that users can click to save any changes they've made. We'll then add a Dynamic Action to handle the click event. Return to the Page Designer for page 3 and complete these steps:

1. Click the Buttons button in the component menu at the bottom/center of the screen.
2. Drag a Text button into the RIGHT OF INTERACTIVE REPORT SEARCH BAR display position of the Products region.

3. In the properties panel on the right, set Button Name to SAVE.
4. Return to the component panel on the left, right-click the new SAVE button, and select Create Dynamic Action from the context menu.
5. In the properties panel on the right, set Name to Save Clicked.
6. Return to the left panel and click the true action Show which is currently red.
7. In the properties panel on the left, set Action to Execute JavaScript Code.
8. Set Code to the code below. The first part of the code declares some variables, including a function, that are used later. Lines 20-36 are where the real work is done. This code loops over the rows looking for changes and adds them to a JavaScript array. Lines 38-50 invoke an Ajax process (defined in the next part) which pass the serialized/stringified version of the array to the p_clob_01 parameter.

//an array to store products that have changed
var productsToUpdate = [];

//a variable to hold references to the table rows in the report
var $trs = $('#productsIRR table:last tr:not(:first)');

//a function to detect changes in select lists
var selectValueChanged = function($select) {
  var opts = $select[0].options;

  for (var x = 0; x < opts.length; x += 1) {
    if (opts[x].selected !== opts[x].defaultSelected) {
      return true;
    }
  }

  return false;
};

//Loop over the rows in the report
$trs.each(function() {
  var $tr = $(this);
  var product = {};

  //Get the select list in the row via the class added in the SQL query
  var $available = $tr.find('.available-edit');

  //check to see if something changed
  if (selectValueChanged($available)) {
    product.id = $available.data('product-id');
    product.available = $available.val();

    //add the changed product to our array
    productsToUpdate.push(product);
  } 
});

//Send the changes to the server via Ajax
apex.server.process(
  'UPDATE_PRODUCTS',
  {
      p_clob_01: JSON.stringify(productsToUpdate)
  },
  {
    success: function(data) {
      //refresh the report to show the latest data
      $('#productsIRR').trigger('apexrefresh');
    }
  }
);

9. Under Affected Elements, set Selection Type to - Select - (essentially disabling it).
10. Under Execute Options, set Fire On Page Load to No.
11. In the upper right-hand corner of the page, click Save to save your changes. You could run the page at this point to see how the button looks, but it will not work until we finish the next part.

Add an Ajax process to update the database with APEX_JSON

The last thing we need to do is create the PL/SQL process that will save the changes from the UI to the database. If you're not already there, return to the Page Designer for page 3 and follow along:

1. Right-click AJAX Callback under the processing tab of the left panel and click Create Process.

2. In the properties panel on the right, set Name to UPDATE_PRODUCTS.
3. Set PL/SQL Code to the following code.

declare

   l_json_clob   clob;
   l_json_values apex_json.t_values;
   l_count       pls_integer;
   l_product_rec demo_product_info%rowtype;

begin

   --Get the clob passed in from the Ajax process
   l_json_clob := apex_application.g_clob_01;

   --Parse the clob so we can work with it
   apex_json.parse(
      p_values => l_json_values,
      p_source => l_json_clob
   );
   
   --Get a count of the products that were passed in
   l_count := apex_json.get_count(
      p_path   => '.',
      p_values => l_json_values
   );
   
   for idx in 1 .. l_count
   loop
      --Get the values from each element in the array
      l_product_rec.product_id := apex_json.get_number(
         p_path   => '[%d].id',
         p0       => idx,
         p_values => l_json_values
      );
      
      l_product_rec.product_avail := apex_json.get_varchar2(
         p_path   => '[%d].available',
         p0       => idx,
         p_values => l_json_values
      );

      --Issue the SQL statement to update the data
      update demo_product_info
      set product_avail = l_product_rec.product_avail
      where product_id = l_product_rec.product_id;
   end loop;

   --Write some JSON out for the response
   apex_json.open_object();
   apex_json.write('status', 'success');
   apex_json.close_object();

end;

3. Click Save.

That's it - you should now be able to run the page, make changes to the Available column, and save those changes to the database!

Wrapping up

In this tutorial, you've seen how JavaScript and APEX_JSON can be used to create an updatable column in an IR. The concepts learned here can be applied to a variety of business requirements that require more than built-in components alone can provide.

If you'd like to learn more about creating JSON with APEX_JSON, have a look at this post from my other blog on JavaScript and Oracle: https://jsao.io/2015/07/relational-to-json-with-apex_json

Also, for a really cool demonstration involving a JavaScript based template engine in APEX, checkout this post from Marko Gorički: http://apexbyg.blogspot.com/2015/10/speed-up-your-apex-apps-with-json-and.html