Tony Andrews

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

Oracle APEX Global Notification

Tue, 2020-03-17 07:37
In the APEX application properties there is a property called Global Notification where you can enter some text that will be displayed on every page - the help text says: You can use a global notification to communicate system status. If your page template contains a #GLOBAL_NOTIFICATION# substitution string then the text entered here displays on each page. For example, suppose you entered theTony Andrews

APEX Interactive Grids - what I've learned so far

Fri, 2017-08-18 06:09
My latest project has been building new pages on APEX 5.1, and quite a few of them have involved the new Interactive Grids (IGs).  I've learned I few things about them in the process, which I thought I'd record here both for my own benefit and in case it helps others. They are addictive I've seen a video of a David Peake presentation about IGs where he warns that developers will be tempted to Tony Andrews

APEX applications that run without Javascript just got harder

Thu, 2017-06-29 06:46
Long ago in 2009 I wrote a blog post called Accessible APEX and in it there is a link to an application on that would work even when Javascript was disabled in the browser.  However, since APEX 5.1 changes the way page items are mapped, that old application no longer works when Javascript is disabled. Oh dear , never mind, who cares?  Why would anyone in 2017 want to disabled Tony Andrews

Editing PL/SQL with Sublime Text 3 and Multiple Cursors

Wed, 2016-11-09 09:16
I'm a recent convert to the Sublime Text 3 editor, having managed for many years with TextPad.  One of the most useful features for me is multiple cursors, which allow you to make the same change to multiple lines at once. For example, it comes in very handy for quickly adding an API over a table (assuming you don't have a nice utility to generate one for you already): 1. Describe the table andTony Andrews

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