Recognizion for a DB Designer, FINALLY!

Originally posted on HeliFromFinland:
Finally the work of a database designer will be recognized! Oracle has announced the Oracle Database Developer Choice Awards nomination and one of the categories makes me very, very happy: DB Design. It has been too long that the work of a database designer has been ignored. If you know a…

Verwaiste SQL-Server-Dateien

Ein schöner Hinweis von Andrej Kuklin, mit dem ich lange zusammen gearbeitet habe (und noch länger Fußball spiele): da der SQL Server bei der Löschung einer zuvor OFFLINE gesetzten Datenbank die zugehörigen Daten- und Log-Dateien nicht ebenfalls löscht, kann man relativ leicht verwaiste Dateien erzeugen, die von keiner DB mehr benötigt werden. Für den SQL Server 2012 liefert Andrej ein Skript, mit dessen Hilfe diese Überreste ermittelt werden können. Für ältere Releases gibt es (more...)

APEX 5.0 Change page in Search bar

One of my favourite features in APEX 4.x was the search bar, often not actually for searching but for opening another page by typing the page number, particularly coming from shared components.
APEX 4.x application search
 For a while I thought I'd lost that feature, since I was expecting to be able to type into were the page number is reported (when in shared components), but you just need to type the page (more...)

Rounding Amounts, the missing cent

Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
To solve this cent-problem, we decide that the (more...)

APEX blogger from Iran

At Kscope I felt geographically isolated, but I was impressed with all the second language speakers attending.

I can only speak one language, and some would argue I don't do well at that - but people were presenting in a language foreign to their native tongue! Kudos.

The Internet I see and view is predominantly English, and no doubt my search results are skewed accordingly. Occasionally I'll find some bloggers in another language and in (more...)

Modernizing New Jersey: One Forms System at a Time

Last week I had the pleasure of presenting at the New Jersey Government Oracle User Group (NJOUG).

It was incredible to see such a dedicated group of people; almost 50 people came out just from the government to hear all different types of topics with regards to database, middleware, etc.

I was actually asked to speak on a range of topics to basically map out what I see as the future for Oracle Forms from (more...)

Library Cache Verschmutzung

Da ich den Fall hier eher wiederfinde als im OTN-Forum erzähle ich an dieser Stelle kurz einen Thread nach, den ich dort vor einigen Tagen begonnen habe. Meine Frage im Forum lautete: wieso sehe ich in einer RAC-Instanz (, zwei Knoten, Standard Edition) diverse Queries, zu denen zahlreiche child cursor existieren, obwohl die Queries keine Bindewerte enthalten, so dass die Pläne eigentlich problemlos wiederverwendbar sein sollten? Die Antwort auf die Frage haben (more...)

APEX 5 Colour Contrast

During the APEX 5 early adopter I remember a bit of discussion going around regarding the colour contrast.

I would have been one of the complainers, and it's one topic I forgot to raise with the team when I met them at Kscope.

Use of APEX 5 is ramping up at my current site and I couldn't go any further without having a little play. A while ago I used some filters in my ad (more...)

Disable IE Compatibility Mode

Most places I’ve worked at allow employees to use any of the major browsers to do their work, but mandate an “SOE” that only supports IE, presumably because that generates the most amount of work for us developers. I’d conservatively estimate that 99% of the rendering bugs I’ve had to deal with are only reproducible in IE. (cue one of the thousands of IE joke images… nah, just do a Google Image search, there’s plenty!)


How to define a PK using Data Modeler?

Originally posted on HeliFromFinland:
Primary Key (PK) identifies uniquely each row in a table. There can never be two tuples with same values in PK. There are two ways of defining a PK: a natural key or a surrogate key. I would say that if you can find a natural key that is always better.…

Deploying Apex: showing an “Under Maintenance” web page


I’ve added this script to our toolbelt for future upgrades. We have a friendly “System is under maintenance, sorry for any convenience” web page that we want to show to users while we run upgrades, and we want it to be shown even if we’re just doing some database schema changes.

So I took the script from here and adapted it slightly, here’s our version:

  v_workspace CONSTANT VARCHAR2(100) := 'MYSCHEMA';
  v_workspace_id NUMBER;

PL/SQL: The Good Parts

Somewhat inspired by Douglas Crockford's Javascript: The Good Parts presentation (and book), I have created a presentation called PL/SQL: The Good Parts.

This is not intended as an introduction to PL/SQL, but rather an overview of features you should consider if you want to maximize your enjoyment of programming in the Oracle Database. Also, this is not an exhaustive list of all features, it's simply the ones I personally use frequently.

Note that unlike (more...)

Bizarre Plandarstellung mit dbms_xplan.display_cursor

Vor einigen Tagen ist mir aufgefallen, dass in einer Datenbank (, an deren Wartung ich seit kurzem beteiligt bin, für eine harmlose Nagios-Check-Query mit dbms_xplan.display_cursor höchst merkwürdige Pläne generiert wurden, die die einzelnen Schritte des Ausführungsplans in schier endloser Folge wiederholten. Ein Blick in v$sql_plan zeigte, dass hier tatsächlich extrem viele Plan-Duplikate vorlagen, aus v$sql_shared_cursor war zu ersehen, dass die Begründung der neuen Cursor in der Regel mit OPTIMIZER_MISMATCH oder PX_MISMATCH angegeben (more...)

Thursday Thought: Boredom

Do you ever get bored? I do.

What do you do when you're bored? I eat. I think. I watch.

Sometimes I play on my phone. I whip it out, play some quizzes, read some feeds, send a text.

What I prefer to do is watch the world. There's plenty going on, I walk through a nearby bush regularly and sometimes it's almost like I'm absorbing pleasantness, contentment.

I saw a commercial a few months (more...)

Row pattern matching nested within hierarchy

I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12.

Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches.

But can there be uses where we want (more...)

Estimating Apex Development

You’ve finished the design for an Apex application, and the manager asks you “when will you have it ready to test”. You resist the temptation to respond snarkily “how long is a piece of string” – which, by the way, is often the only appropriate answer if they ask for an estimate before the design work has started.


Since you have a design and a clear idea of what exactly this application will do, you can build a reasonable (more...)

Indizierung für LIKE-Operationen in postgres

Ein interessanter Hinweis von Daniel Westermann im DBI Services Blog: durch die Verwendung der pg_trim extension ist es in postgres möglich, (GIN oder GiST) Indizes zur Unterstüzung von Like-Einschränkungen mit führendem (oder auch im Vergleichsstring enthaltenen) Platzhalter(n) zu erstellen. Laut Dokumentation gilt:
The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. These index types support (more...)

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();

About using APEX_COLLECTIONS in a tabular form with INSTEAD OF triggers

Always check out the original article at for latest comments, fixes and updates.   In case you dreamed of updating an Apex tabular form built on top of a view based on APEX_COLLECTIONS by means of INSTEAD OF triggers, you must be ready to grant the following rights to the schema user attached to the workspace, otherwise you'll get an ORA-01031: Insufficient privileges

Perth – Free 1/2 day Education Event

Thanks to Sage Computing Services, AUSOUG are hosting a free 1/2 day education event for it's members.

Join us Wednesday 19th August, 2015 at the Duxton Hotel from 2:30pm to be regaled with tales of tuning, APEX, SQL and PL/SQL.

  • Penny - Practical Tuning Tips for Developers
  • Kylie - Snazzy SQL: Titbits to advance your code
  • Katie - PL/SQL: Are you making the most of it?
  • Scott - APEX 5 isn't just a sexy (more...)