Building a chrome extension for Oracle APEX Builder

As a developer I like to create my own tools to make my work easier. In our current Oracle APEX project we have build a custom authorization mechanism, based on page numbers. A drawback the development team faced was that we had to register every single page in our custom authorization tables. So I came to think that if I could extend the APEX builder with some custom code on the page builder page, it would make our life a lot easier. When working on a new page we could register the page with a simple click.

The technique I describe in this blog can be used to extract information from the APEX Builder and call some stored procedure in the database to do some magic with it.

I wanted to build an extension for Chrome. With some googling I stumbled upon Chrome extensions. As always, the documentation of these Google API’s is excellent.

I find that building a very, very simple extension for Chrome is relatively easy. Of course you can always be overcomplicating things, but following the KISS principle I was able to build an extension that served my purpose.

The idea behind my extension is, that when the extension is clicked, some JavaScript is fired that “gets” the application id and page id from the current tab and sends it to an API on the database to register the page. But the last API can of course do all kinds of magic with the application and page id.

This is what I did:

Create a directory to host the files for the extension. E.g.

D:\ChromeExtensions\AddToAuth

The extension needs a manifest file with the name manifest.json.
My manifest file became

{
  "name": "Add a page to Auth",
  "version": "1.0",
  "manifest_version": 2,

  "description": "With this extension one can add a page from APEX builder to Auth",
  "icons":{"128":"db.png"},

   "browser_action": {
    "name": "Add a pagina to Auth",
    "default_icon": "db.png"
  },

  "background": { "scripts": ["background.js"] },
  "permissions": [
    "http://*/*"
  ]
}

See Formats: Manifest Files for a full description of the fields of a manifest file.

If you look at the manifest two things pop up

  • db.png This is a 128×128 png file that is shown in the browser
  • background.js This is the event page of the extension.

When the extension – a browserAction – is clicked I have to fire some JavaScript. So, the background.js would have to add a listener to the event.
background.js

chrome.browserAction.onClicked.addListener(function(tab) {
  chrome.tabs.executeScript(null, {file:"worker.js"});
});

When the browserAction is clicked, the worker.js is executed
worker.js

var APPL_XPATH = '//*[@id="apex-breadcrumbs"]/a[3]';
var PAGE_XPATH = '//*[@id="apex-breadcrumbs"]/span[4]';

var
  application,
  local,
  page,
  tabQuery,
  xhr;

function getNumber(pXpath, pItem) {

  var
    nextResult,
    lReturn,
    xpathResult;

  xpathResult = document.evaluate(pXpath, document.body, null, XPathResult.ANY_TYPE, null);
  nextResult = xpathResult.iterateNext();

  while (nextResult) {
    if (nextResult.textContent.indexOf(pItem) !== -1) {
      lReturn = parseInt(nextResult.textContent.substr(pItem.length), 10);
    }
    nextResult = xpathResult.iterateNext();
  }
  return lReturn;
}

tabQuery = location.search.split('=')[1].split(':');
local = 'http://' + location.host + '/' + location.pathname.split('/')[1] + '/';

if (tabQuery[0] === '4000') {    /* Builder   */
  if (tabQuery[1] === '4150') {  /* Edit page */
    /* 
      Get the pagenumber from the body
    */
    page = getNumber(PAGE_XPATH, 'Page');

    if (page) {
      /* 
        The page is found. Now get the applicationnumber
      */
      application = getNumber(APPL_XPATH, 'Application');
    }
    if (application && page) {
      /* 
        Both items have been found. Call the stored procedure to do some magic.
      */
      xhr = new XMLHttpRequest();
      xhr.open("GET", local + 'AUTH_API.ADD_PAGE?p_application_id=' + application + '&p_page_id=' + page, true);
      xhr.send();
      alert('application: ' + application + ' page: ' + page + ' is added');
    }
  }
}

When the query part of the URL is like 4000:4150 we know that we are on the page edit page.Then we use some XPATH on the body of the page to get the application id and page id. The XPATH expressions are stored in two constant vars. The XPATH expressions you see are working for the current APEX 4.2 version; to get these expressions is very easy using Chrome:

  • In APEX Builder, go to the page edit page.
  • In the breadcrumbs go to the Page xx part.
  • Inspect the element using the context menu in chrome.
  • In the elements tab use the context menu Copy Xpath

The last step to take is to send the two id’s to the database.
For this, I just use a HTTP-request to a stored packaged procedure AUTH_API.ADD_PAGE with two parameters p_application_id and p_page_id.

In our architecture (OHS with mod_plsl and a standard dads.conf) for the development database we are allowed to fire stored procedures this way. In our development
database the wwv_flow_epg_include_mod_local is non-restrictive:

create or replace function wwv_flow_epg_include_mod_local
  ( procedure_name in varchar2
  ) return boolean
is
begin
  return true; 
end wwv_flow_epg_include_mod_local;

The connecting user, defined in the dads.conf, -e.g. APEX_PUBLIC_USER-, has to have proper grants on the stored procedure that is being called by the HTTP-GET.

Importing the extension.
– Go to the Extra > extentions part of the menu
– Enable developmentmodus
– Use the load button to load the directory where you put all your files.

The new browserAction should show up in the browser.

That’s it. With this you can build an extension in chrome to parse a page in APEX Builder and send the information to some API in the database.

Advertisements

Using Knockout.js with Oracle Apex

If you’re one of the cool kids in the JavaScript block, you might have heard of Knockout.js. With Knockout you can e.g. use declarative binding to DOM elements and use it to dynamically refresh your UI. Just go through the Tutorials on the site. They are excellent!

Sounds like Dynamic Actions in Oracle Apex, right? But if you are starting to hate the dense forest and – in my humble opinion – bugginess of Dynamic Actions and are looking around for alternatives, I would suggest a look into Knockout.

I will give a very, very simple example to dynamically show a message after inserting a value in a text item. Some of the basic Apex concepts I will not discuss.

First of all, you will have to download the latest version of knockout.js and link it to your page c.q. application. You could also link to a Content Delivery Network (CDN) like cdnjs.com.

The Item where you have to enter the text in has to be bound to the viewmodel. You can use the attributes part of the HTML element.
element

In my example I have a P60_ELI text field which I bind to the observable ELI.

The text that automagically will appear will be shown in another Text Field P60_ELI_VERBRUIK, bound to the knockout id ELI_VERBRUIK.
element2

To put it all together you have to apply the bindings with some small JavaScript in the page:

function getVerbruik(p_x, p_y) {
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=SomeOnDemandProcedure',0); 
  //get.add('X',p_x); you could pass the input to the process
  //get.add('Y',p_y);
  gReturn = get.get();    
  return gReturn; 
}  

function viewModel() {
  var self = this;  
  self.ELI = ko.observable();
  self.ELI_VERBRUIK = ko.computed( function() { 
                                     return getVerbruik('ELI', self.ELI() ); /* passing through the value that is entered in the observable ELI field, i.e. the P60_ELI item */
                                   }
                                 );
}
/*
  Bind the right viewModel (you could have created several) to the elements on the page. 
*/ 
$(function(){
  ko.applyBindings(new viewModel());
});
 

The function getVerbruik will give some text back from the database using an ON-DEMAND process. You can of course just give back text, but I would suggest to return some JSON. Just because you can.