Tony Andrews
Some very occasional musings on matters concerning Oracle specifically or databases in general.Tony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.comBlogger45125
Updated: 14 hours 46 min ago
Oracle APEX Global Notification
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2020/03/oracle-apex-global-notification.html
APEX Interactive Grids - what I've learned so far
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com1http://tonyandrews.blogspot.com/2017/08/apex-interactive-grids-what-ive-learned.html
APEX applications that run without Javascript just got harder
Long ago in 2009 I wrote a blog post called Accessible APEX and in it there is a link to an application on apex.oracle.com 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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0
Editing PL/SQL with Sublime Text 3 and Multiple Cursors
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2016/11/editing-plsql-with-sublime-text-3-and.html
APEX IDE for Shakespeare Programming Language (SPL)
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2016/07/apex-ide-for-shakespeare-programming.html
Conditional column linking in APEX
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2016/07/conditional-column-linking-in-apex.html
At last APEX_PAGE.GET_URL!
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2016/05/at-last-apexpagegeturl.html
APEX plugin: make tabular report responsive
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com7http://tonyandrews.blogspot.com/2016/05/apex-plugin-make-tabular-report.html
Trello is my new knowledge base
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com4http://tonyandrews.blogspot.com/2016/04/trello-is-my-new-knowledge-base.html
Can't make my mind up about "Feuerstein refactoring"
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com3http://tonyandrews.blogspot.com/2016/03/cant-make-my-mind-up-about-feuerstein.html
Another new APEX-based public website goes live
Another APEX public website I worked on with Northgate Public Services has just gone live:
https://londontribunals.org.uk/
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com1http://tonyandrews.blogspot.com/2015/07/another-new-apex-based-public-website.html
Why won't my APEX submit buttons submit?
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2015/02/why-wont-my-apex-submit-buttons-submit.html
Ignoring outliers in aggregate function
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2014/09/ignoring-outliers-in-aggregate-function.html
Why use CASE when NVL will do?
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com3http://tonyandrews.blogspot.com/2014/09/why-use-case-when-nvl-will-do.html
Handy pre-defined Oracle collections
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2014/08/handy-pre-defined-oracle-collections.html
Hiding APEX report pagination when trivial
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2014/06/hiding-apex-report-pagination-when.html
APEX boilerplate translation
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2014/05/apex-boilerplate-translation.html
HGV Levy
The UK government has introduced a new service for foreign lorry drivers to pay a levy to use UK roads here:
https://www.hgvlevy.service.gov.uk/
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2014/04/hgv-levy.html
It's a drag...
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com8Surrey, UK51.2622513 -0.4672517000000198150.6264563 -1.7581452000000197 51.898046300000004 0.82364179999998011http://tonyandrews.blogspot.com/2014/01/its-drag.html
SQL Developer: add a "child tables" tab to table definition
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 Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com6http://tonyandrews.blogspot.com/2013/09/sql-developer-add-child-tables-tab-to.html