09 August 2019

3 ways to synchronize your Oracle Text indexes

One of my favourite features of the Oracle database is Oracle Text.  In this blog post I'll discuss different approaches to the synchronisation of Oracle Text indexes.

Oracle Text indexes are synchronized via a queue of pending updates.  Whenever the rows upon which the index is built are inserted or changed, those rows are added to the queue.  This queue is then processed according to the synchronization method that you defined at index creation time.  This means that the synchronization is independent of your transaction,  i.e. your transaction completes without waiting for the index synchronization.

It is important to note that the index is not fully recreated when synchronized but is incrementally updated.  Therefore, updates or insertions of a few rows will be synchronised very quickly but an update or insertion of, let's say, 800'000 rows will take significantly longer to synchronize.

You can view the queue of pending rows using the ctx_user_pending view.

To see how many rows are pending for each index:
select pnd_index_name, count(*)
  from ctx_user_pending
 group by pnd_index_name;
When creating your Oracle Text indexes, you have three ways to specify how you want them to be synchronized
  • manually (this is the default if no syncing method is specified)
create index txt_index on my_table (text_col)
indextype is ctxsys.context;
  • on commit - this will start synchronizing immediately after the transaction is committed
create index txt_index on my_table (text_col)
indextype is ctxsys.context
parameters ('sync (on commit) ');
  • at regular intervals (needs CREATE JOB) privilege - this example syncs hourly
create index txt_index on my_table (text_col)
indextype is ctxsys.context
parameters ('sync (every "sysdate+(1/24)")');
Each of these methods has its advantages and disadvantages.  For example, if you can live with an index that is synchronized daily and want to minimise the database load during working hours, maybe you might consider syncing at a regular daily interval.

Similarly, if you have a lightish load and just have a few changes at a time, then maybe an "on commit" synchronization is the right one for you.  In my experience, the majority of Oracle Text applications have (sometimes wrongly) opted for this.

I hope that this has explained these three options in a quick and simple way.  The well-written Oracle Text Developer's Guide contains a lot of detail on how to manage your Oracle Text indexes.

06 June 2019

Setting HTTP Response Status Code 301 (Moved Permanently) in APEX

This will be a short one.  As our public-facing applications age and new applications are developed to replace them, we often have the problem that search engines still send people to the old application pages.

One way of solving this issue, and of giving some feedback to the search engines so that they will update their indexes, is by using the HTTP Response Status Code 301 "Moved Permanently".  Any self-respecting search engine that receives this response code will remove the outdated link from their index.  Also, any users that click on the old link will be automatically redirected to the new one.

Let's take an example: suppose that I have an old APEX application (e.g. app ID 88203) and I want to redirect people to the Universal Theme application (App ID 42) instead of my old page.

The only thing that I need to do is to add a "Before Header" PL/SQL process.  Let's call this process "Permanent Redirect".

This process does three things:
  1. it sets the http status to 301
  2. it redirects to the new url
  3. then it immediately halts all APEX execution by stopping the apex engine

Here's the PL/SQL code for the process:
owa_util.REDIRECT_URL('https://apex.oracle.com/pls/apex/f?p=42:100', true);

Before Header process to be created - Permanent Redirect

The Permanent Redirect process definition

One online tool that I found useful while testing this approach was https://httpstatus.io/, it's an online tool to easily check status code, response headers and redirect chains.

I hope that this comes in useful to you one of these days.  It's a quick and relatively simple approach to solving a common problem.

01 February 2019

Solving an issue regarding escaping substitution strings in Oracle APEX

In a recent dashboard-type application, I was using Google Charts for one of my dashboard regions.  This meant that I needed to place some javascript inside the html page header.

The JavaScript was broken into two types

  1. static sections that would never change, and
  2. a dynamic section that was generated by assigning the result of PL/SQL package function to a hidden item named P1_DRAWMAP_JS.
This is the code that was run "before header" to initialise the item.

:P1_DRAWMAP_JS := centutils.genDrawMap (p_language => v(:FSP_LANGUAGE_PREFERENCE));

Once initialised, item P1_DRAWMAP_JS contained a part of a javascript function with about 200 lines of code.  This code also contained some html tags.  A snippet of the generated code is shown here,  although it's not important to understand it for this article, just to note its general structure:

function drawMap() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Country'); // Implicit domain label col.
data.addColumn('number', 'Value'); // Implicit series 1 data col.
data.addColumn({type:'string', role:'tooltip', p: {html: 'true'}}); //
data.addRows([ [{v:"BA",f:"Bosnia and Herzegovina"},1,"<strong>P029</strong> - entry into force - 09 Aug 2019"],
[{v:"BE",f:"Belgium"},1,"<strong>C187</strong> - entry into force - 31 May 2019"],
[{v:"BG",f:"Bulgaria"},1,"<strong>C131</strong> - entry into force - 20 Mar 2019"],
[{v:"BR",f:"Brazil"},1,"<strong>C189</strong> - entry into force - 31 Jan 2019"],

The page header looked something like this (below) with the dynamically generated P1_DRAWMAP_JS substitution variable placed between a <script> tag and a </script> tag along with a few other bits of static javascript code.

So everything should work then, right?   Unfortunately not - my region was blank.  When viewing the html source, what I actually saw was that the javascript code contained in my substitution variable had been escaped by APEX.

This is when I decided to search the APEX documentation to see how to get around this problem.  After a few minutes, I came across this documentation on escaping substitution strings.

From the documentation we can see that there are 5 different ways of escaping APEX substitution variables.
They all start with an exclamation mark and immediately follow the name and can also be used in report and interactive grid columns.

!HTML escapes reserved HTML characters
!ATTR escapes reserved characters in a HTML attribute context
!JS   escapes reserved characters in a JavaScript context
!RAW  preserves the original item value and does not escape characters
!STRIPHTML removes HTML tags from the output and escapes reserved HTML characters

The solution to my issue was very simple.  I disabled the escaping of my substitution variable by adding !RAW to the name of the substitution string, so it now became &P1_DRAWMAP_JS!RAW. 

Everything worked!  The result was exactly what I expected and my region looked now looked as it should, like this.

Sometimes the solutions to our problems are very simple. 
And in this case, the APEX development team at Oracle had already anticipated what was needed and provided a mechanism for implementing it.  Another reason that Oracle APEX is one of the best low-code development tools out there!