Subscribe: Tony Andrews on Oracle and Databases
Added By: Feedage Forager Feedage Grade A rated
Language: English
apex conditions  apex page  apex  application  case  code  conditions  create  developer  javascript  new  oracle  page  report  sql   i 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Tony Andrews on Oracle and Databases

Tony Andrews on Oracle and Databases

Some very occasional musings on matters concerning Oracle specifically or databases in general.

Updated: 2017-12-11T03:12:28.623+00:00


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 (image)

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 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 (image)

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 (image)

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 (image)



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 (image)

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 (image)

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 (image)

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 (image)

Another new APEX-based public website goes live


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 (image)

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 "(image)

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 ((image)

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 ((image)

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. (image)

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 the(image)

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 (image)

HGV Levy


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 (image)

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 (image)

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 (image)

APEX conditions and performance - part 2


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 (image)

APEX conditions and performance


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 (image)

The curse of the cursor


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 (image)

Apex MP3 Player item plug-in


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 (image)

Dutch Oracle User Group's Apex Day 2011


This week I attended the Dutch Oracle User Group (OGh)'s Apex Day as a presenter, along with an ex-colleague from Northgate, Nigel Blair. We were talking about how we converted 1500+ Forms modules to Apex. There was quite a lot of interest from the 250 people present. We hope the interest wasn't due to a mix-up in the agenda that had us down as Nigel Andrews and Tony Blair!Luckily we were on (image)

Fame at last for my biggest Apex project to date


I'm very pleased to see that the Apex project I started and worked on for several years is now the subject of an entry under Customer Quotes on the OTN Apex page."At Northgate Revenues & Benefits, we have used APEX to replace our legacy Oracle Forms system comprising around 1500 Forms. Our user interface has 10,000 end users daily, across 172 clients, who this year sent out over 12 million (image)

SQL Overlap Test


Many times I come across SQL where the developer is trying to check for overlap between two ranges (usually date ranges, but sometimes numbers). For example, to meet the requirement "select all employees whose hired from and to dates overlap with the project start and end dates".The developer sketches out all the possible overlap scenarios and finds four:1) End of range A overlaps start of range(image)