Tony Andrews

Subscribe to Tony Andrews feed
Some very occasional musings on matters concerning Oracle specifically or databases in general.Tony Andrews
Updated: 4 hours 9 min ago

APEX IDE for Shakespeare Programming Language (SPL)

Fri, 2016-07-22 08:04
Recently I came across an esoteric programming language called The Shakespeare Programming Language (SPL) and become rather fascinated by it.  It's big, and it's clever, but it's not terribly useful or practical.  But this year is the 400th anniversary of Shakespeare's death, which adds some relevance I suppose. Here is an example of an SPL program taken from the SPL docs.  All it does is Tony Andrews

Conditional column linking in APEX

Wed, 2016-07-20 08:19
Sometimes there is a requirement to have a column in an APEX report that acts as a link to another page for some rows but not for others like this: Here, only when a program's status is 'VALID' can we link to another page by clicking on the program name. Until now I only knew a rather bad way of doing this, which would be to write code in the report query like: select case when Tony Andrews


Fri, 2016-05-27 04:51
I have just discovered (thanks to a tweet by @jeffreykemp) that in APEX 5.0 there is now a function called APEX_PAGE.GET_URL: About time!  I've been using this home-made version for years: So if I want to create a URL that redirects back to the same page with a request I can just write:     return my_apex_utils.fp (p_request=>'MYREQUEST'); One difference is that the one I use has a Tony Andrews

APEX plugin: make tabular report responsive

Mon, 2016-05-16 08:45
I often have to build APEX applications that are responsive to the size of the screen they are running on - from desktops down to mobile phones.  While this can be achieved quite easily using a modern responsive theme, reports are often a problem.  For example, this report looks fine on a desktop: ... but gets truncated on a mobile: Here I'm using the APEX 5.0 Universal Theme, which at least Tony Andrews

Trello is my new knowledge base

Fri, 2016-04-08 08:53
How often do you hit an issue in development and think "I know I've had this problem before, but what's the solution?"  Most days if you've been around a long time like me.  It could be "how do you create a transparent icon", or "what causes this Javascript error in an APEX page".  So you can spend a while Googling and sifting through potential solutions that you vaguely remember having seen Tony Andrews

Can't make my mind up about "Feuerstein refactoring"

Fri, 2016-03-04 10:55
When writing large PL/SQL processes I do like to try to make the code as readable as possible.  One way is to follow Steven Feuerstein's advice as exemplified here in a blog post and here in a Youtube video  to refactor the code into small chunks. I have done that, but then find I have my doubts about it.  My problem with it is that it breaks the code into small local procedures and functions Tony Andrews

Another new APEX-based public website goes live

Wed, 2015-07-15 07:48
Another APEX public website I worked on with Northgate Public Services has just gone live: This is a website to handle appeals against parking fines and other traffic/environmental fines issues by London local authorities. It is built on APEX 4.2 using a bespoke theme that uses the Bootstrap framework.  A responsive design has been used so that the site works Tony Andrews

Why won't my APEX submit buttons submit?

Mon, 2015-02-02 07:46
I hit a weird jQuery issue today that took a ridiculous amount of time to solve.  It is easy to demonstrate: Create a simple APEX page with an HTML region Create 2 buttons that submit the page with a request e.g. SUBMIT and CANCEL Run the page So far, it works - if you press either button you can see that the page is being submitted.   Now edit the buttons and assign them static IDs of "Tony Andrews

Ignoring outliers in aggregate function

Wed, 2014-09-17 09:22
This is another aide-memoire for myself really.  I want to calculate the average load times per page for an application from timings stored in the database, and see which pages need attention. However, the stats can be skewed by the odd exceptional load that takes much longer than a typical load for reasons that are probably irrelevant to me. Here is a fictitious example: create table timings (Tony Andrews

Why use CASE when NVL will do?

Thu, 2014-09-11 12:42
I've found that many developers are reluctant to use "new" features like CASE expressions and ANSI joins. (By new I mean: this millennium.) But now they have started to and they get carried away.  I have seen this several times recently:     CASE WHEN column1 IS NOT NULL THEN column1 ELSE column2 END Before they learned to use CASE I'm sure they would have written the much simpler:     NVL (Tony Andrews

Handy pre-defined Oracle collections

Sat, 2014-08-30 06:09
Note to self: SYS.DBMS_DEBUG_VC2COLL is a handy pre-defined TABLE OF VARCHAR2(1000) SYS.KU$_VCNT is TABLE OF VARCHAR2(4000) Both are granted to public. Thanks to Eddie Awad's blog for these. Tony Andrews

Hiding APEX report pagination when trivial

Tue, 2014-06-10 12:33
The users are quite happy with pagination like this: However, they don't like it when the report returns less than a pageful of rows and they see this: (Fussy, I know). This is one way to do it.  First, ensure that the pagination area itself is identifiable.  I put a div around it with a class of "pagination": Then add some Javascript to the "Execute when page loads" attribute of theTony Andrews

APEX boilerplate translation

Mon, 2014-05-26 10:25
APEX provides a mechanism for translating applications into other languages: Applications can be translated from a primary language into other languages. Each translation results in the creation of a new translated application. Each translation requires a mapping which identifies the target language as well as the translated application ID. Translated applications cannot be edited directly in Tony Andrews

HGV Levy

Wed, 2014-04-16 05:45
The UK government has introduced a new service for foreign lorry drivers to pay a levy to use UK roads here: It was built by my current employer, Northgate Information Solutions. Guess what technology it runs on? We had a lot of interesting challenges when building this: Compliance with UK Government styling and standards Responsive design to work on Tony Andrews

It's a drag...

Sun, 2014-01-26 06:54
It really used to be a "drag" putting together a set list for my band using my Oracle database of songs we play: the easiest way was to download all the songs to an Excel spreadsheet, manipulate them there, then re-import back into the database.  I tried various techniques within APEX but none was easier than that - until now.  I have just discovered jQuery UI's Sortable interaction, and in a Tony Andrews, UK51.2622513 -0.4672517000000198150.6264563 -1.7581452000000197 51.898046300000004 0.82364179999998011

SQL Developer: add a "child tables" tab to table definition

Wed, 2013-09-18 12:10
I always like to extend SQL Developer's table definition by adding a tab that shows the "child tables" for each table - i.e. the tables that have a foreign key to the table in context. I have lost count of how many times I have started work at a new environment or on a new PC and had to set this up from scratch, so thought I'd document it here for next time! First, create a file containing Tony Andrews

APEX conditions and performance - part 2

Fri, 2013-06-14 01:56
This is a follow-up to my previous post APEX conditions and performance. Roel Hartman has followed up my post by doing the due diligence and testing the performance of different APEX condition types.  His findings back up what I just asserted. (But I wasn't just guessing, my assertions were based on facts I learned long ago from someone in the APEX team via the Oracle APEX forum - so long ago Tony Andrews

APEX conditions and performance

Sat, 2013-04-06 06:45
Having recently seen some examples of non-optimal code in APEX conditions (e.g. item rendering conditions and read-only conditions)  I thought it worth writing a few words about them.  By putting them here on my blog I can refer to them in future rather than writing them again. Also I may receive useful feedback from readers to improve or correct my advice. For a very simple condition Tony Andrews

The curse of the cursor

Tue, 2011-04-19 04:58
For some reason, many Oracle developers avoid SELECT INTO as if it were dangerous, preferring to "have more control" over their code by using cursors for everything. This morning I spent over an hour debugging some code written by one such developer, only to find that the issue I was trying to fix was one that would have been caught by the original developer immediately had he used SELECT Tony Andrews

Apex MP3 Player item plug-in

Mon, 2011-04-04 11:18
I have developed a plug-in item type that renders an MP3 player to play a specified audio file using the free Premiumbeat Single Track Flash MP3 Player. The file may be specified via a URL of as the ID of a file stored in APEX_APPLICATION_FILES. There is a demo of this available here on plug-in has the following settings available:They are mostly fairly self-explanatory (and Tony Andrews