Using inline dialogs in APEX 5

APEX 5 has built-in support for dialog boxes. These come in two varieties; dialog pages (which show another standard APEX page in a dialog box using an iframe) and inline dialogs (which show a region on the current page as a dialog box). John Snyders goes into great technical detail about APEX dialogs and the differences between the two types.

This post is a quick summary of how to use inline dialogs. As John points (more...)

APEX Survey Results: Workspace Activity Log

This question in my 2015 survey relates to built-in instrumentation.

Q7. Do you utilise apex_workspace_activity_log (for monitoring/reporting application usage)?

Yes (90)  47%
No (102) 53%

This log table reports details of all page rendering and AJAX process calls. I really like this information, though I would like to be able to add to the output things such as
sys_context('userenv', 'server_host')

In addition to the Monitoring pages you'll find in the APEX administration section, I've built (more...)

What is the difference between NVL and COALESCE?

Dear Patrick,

Could you tell me what the difference is between NVL and COALESCE?

Kindest regards,
Paul McCurdey

Dear Paul,

NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of (more...)

Oracle Developer Cloud to build and deploy Nodejs REST project on Application Container Cloud

This blog will give an understanding of deploying a simple Nodejs based REST service project on Oracle Application Container Cloud using Oracle Developer Cloud. This post will show the essentials for the Nodejs project for maintaining, building Nodejs code on Developer Cloud Service and deploying from Developer Cloud to Application Container Cloud.


Tech Stack Usage

Eclipse: IDE for Nodejs development.

Expressjs: Nodejs module for REST web service development.

Grunt: Tool for building the Nodejs (more...)

Virtual Development Server: Provide Docker images in docker registry for Oracle XE Database and others

For later creation of containers as needed in the deployment process we have first to build docker images whenever we do not find suitable one in docker hub.

For most of my development I need a oracle database. On the internet you can find a lot of Dockerfile’s for this, but no ready image. This is because for running a database you have to accept a license and this happens when you download the installation (more...)

Must See: AUSOUG Connect 2016 Perth


If you can get yourself to Perth in November, you must try to get to AUSOUG Connect. Here is my “Must See” list based on the current program (subject to change):

Monday 7 Nov

Users still double click buttons

Despite web pages having been around for quite some time, and users have been given years of affordance, people still double click on links & buttons in web pages.

This can cause a few issues in an APEX environment as the underlying process may get executed twice, and today I came up with what I thought was a simple, declarative solution.

In my case I had a button that submitted a modal page. I (more...)

Dictionary Metadaten in der Multitenant Infrastruktur

Franck Pachot hat seit 2014 eine Reihe interessanter Artikel zu den internen Implementierungsdetails der Dictionary-Zugriffe im Multitenant-Kontext veröffentlicht. Ich spare mir eine detaillierte Zusammenfassung der umfangreichen Inhalte und beschränke mich auf die Verweise:

Moving to selfhosted and failing hard

I had decided to move one of my blogs to a self-hosted model to get more control over plugins and other things. After reading up a bit on different vendors I found Digital Ocean to be my choice. What’s not to like with a place that can spin up a complete Linux with WordPress installed and ready for me to use? Yes, it only takes them seconds and it is powered by SSD. Adding that (more...)

APEX Survey Results: Instrumentation / Debugging

This question from my 2015 survey may pique curiosity among some APEX developers.

Q6: How do you instrument your code?

Hopefully those in the 22% who do not add instrumentation just didn't understand the question.

Instrumentation is another term for adding debugging information to your code. There is plenty of commentary on this topic and done right it can make tracking down problems a breeze.

It can be as simple as knowing when a procedure (more...)

How do I get my query results paginated?

Dear Patrick,

I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?

Kindest regards,

Mitchell Ian

Dear Mitchell,

Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I (more...)

RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with (more...)

Maintaining disabled FK’s, wisdom or farce?

A while back, I wrote a post about having FKs (foreign keys) in your data warehouse. Well, a similar question came up recently on an Oracle forum with the above title. It is a fair question and it does surface fairly regularly in a variety of contexts (not just data warehousing). Of course, as The […]

Statistikerfassung für Tabellen mit mehr als 255 Spalten mit dbms_stats

Nur eine kurze Notiz auf einen Artikel von Randolf Geist, der erklärt, dass Oracle 12c eine deutliche Verbesserung bei der Erfassung von Statistiken für Tabellen mit sehr vielen Spalten eingeführt hat: in älteren Releases mussten die Statistiken für solche Tabellen mit mehr als 255 Spalten, deren Datensätze intern auf mehrere row pieces verteilt werden müssen, in mehreren Leseoperationen ermittelt werden (multi pass). Diese Einschränkung ist mit 12c aufgehoben: auch für Tabellen mit mehreren row (more...)

Umwandlung von LONG in CLOB mit SYS_DBURIGEN

Meine Standardantwort auf die Frage, wie man die Inhalte von LONG-Spalten auslesen kann, war seit vielen Jahren: ich hab's vergessen, aber Adrian Billington hat alles notiert, was man über diesen unerfreulichen Datentyp wissen muss. Diese Antwort kann ich jetzt modifizieren: der handlichste Weg, um LONGs in etwas weniger Häßliches zu verwandeln, ist die Verwendung der builtin-Funktion SYS_DBURIGEN, der von Marc Bleron (aka odi_63) in seinem Blog beschrieben wird. Intern erzeugt Oracle in diesem Zusammenhang ein (more...)

Zerlegung von Strings

Stew Ashton hat in jüngerer Vergangenheit eine ganze Reihe interessanter Lösungen zum Problem der Zerlegung von Strings in einzelne Token in SQL vorgestellt. Die klassischen Lösungen dafür sind die (in der Regel auf Tom Kyte zurückgeführte) Kombination von instr und substr (die kurz vor 400 Bytes an ihre Grenzen stösst), sowie der Einsatz von regexp_substr, der - wie alle regexp-Operationen recht kostspielig im Hinblick auf die Nutzung der CPU-Ressourcen ist:

Presenting at Oracle Open World 2016


Just a short post to announce that I’ll be presenting an User Group Forum session. Thanks ODTUG!

SQLcl: A Modern Command Line Interface to the Oracle Database [UGF5641]

“In this session learn about the new Java-based command line interface that takes advantage of Oracle SQL Developer’s scripting engine. It delivers a modern command line interface that is backward compatible with SQL*Plus but also introduces new commands and features that have been missing for a long (more...)

How to debug stuff in Oracle APEX

Recently a fine young gentleman, who shall remain nameless (let's call him Jerry), asked for some assistance he was getting with an error in Oracle APEX.

He had done all the right things in regard to debugging the problem, but didn't know enough about APEX to know which settings to investigate.

I think developers new to APEX need the occasional post like this to give them an idea on how to start looking into a (more...)

ODTUG ADF and Database Community Meetup at Oracle OpenWorld


Tuesday, September 20, 2016
5:00 PM to 7:00 PM
138 Minna Street, San Francisco, CA (map)

Oracle OpenWorld is a time for us to join together and learn all about what Oracle has to offer.  But let’s get real, it’s always nice to get free beer.  To help show us a good time here in San Francisco, our good friends in the Oracle ADF and Database developer group are planning a Oracle Developers (more...)

Real World SQL and PL/SQL: Advice from the Experts

Because my hero is Cary Millsap, I'm going to do what he did and publish my foreword Preface. All joking aside, I consider myself incredibly fortunate to have been included in this project. I learned...a lot, by simply trying to find the author's mistakes (and there were not many). There was a lot more work than I expected, as well. (Technical) Editing is lot easier than writing, to be sure.

Brendan Tierney and (more...)