Remove duplicate from APEX collection

One of my favourite SQL analytic functions is row_number(), and I've used it in the past to identify, then remove duplicates.

In this case, I have an APEX collection that represents a session based view history of products/people/events, or whatever your users might be browsing.

I've created an option to consolidate that view history, and remove any record you might have opened more than once.

Collections are a little hard to play with outside (more...)

ORA-01723 für DATE-Angaben

Nach langer Zeit mal wieder etwas Selbsterlebtes, das einen Eintrag zu verdienen scheint - wie man eine CTAS-Operation über das nls_date_format torpedieren kann:

SQL> alter session set nls_date_format = ' hh24:mi:ss';

Session altered.

SQL> create table t as select to_date(null) col1 from dual;
create table t as select to_date(null) col1 from dual
ERROR at line 1:
ORA-01723: zero-length columns are not allowed

SQL> alter session set nls_date_format = 'DD-MON-RR';

SQL> create table (more...)

Oracle Challenge #3 – Solution

This is the solution for Oracle Challenge #3. If you haven’t read the challenge, go and check it before you read the solution here. So, the challenge was to write different SQL statements to return the list of employees that earn the most in their department. As I said, there are 2 assumptions here, we … Continue reading Oracle Challenge #3 – Solution

JSON, BLOB column and Check Constraint

Last week I attended Neil Chandler's session on JSON during the POUG conference in beautiful Sopot, Poland.
The JSON Developer's Guide recommends using BLOB for storing data, and this is what Neil also recommended.
I was under the (FALSE!) impression that it was not possible to put an IS JSON check constraint on a BLOB column, simply because I tried once and got an exception. After the session I asked Neil if this was (more...)

Oracle Challenge #3 – SQL

I debated quite a lot before writing this post. When I wrote the post about interviewing a DBA, in the “technical questions I do ask” part I just gave a general explanation of what I ask, but didn’t reveal the real questions. Now, more than 3 years later, I decided to give one of the … Continue reading Oracle Challenge #3 – SQL

Many happy birthdays

Last year I *(not pictured) celebrated my 42nd circuit around the sun. In accordance with time-honoured tradition, it has been celebrated some time around the same day each September with variations on the following theme:

  • a get-together with friends and/or family
  • my favourite meal (usually lasagne)
  • my favourite cake (usually a sponge coffee torte, yum)
  • a gift or two
  • the taking of photographs, to document how much I’ve grown since the last one

Each year, (more...)

Index Hints and Distributed Queries

There are two ways to specify indexes in optimizer hints: by the index name, or by the index’s (leading) columns:

The latter is usually preferred, as the writer’s intention is clearer, and it’s immune to changes of the index name.

Note: one can present the opposite argument, that specifying the index name is immune to changes of column names

Recently I’ve used the option that I prefer – specifying the column names and not the (more...)

Translating Chinese to English in SQL with Microsoft Translator

In Oracle, I had a table of data which had some Chinese words that I needed to translate into English on the fly, in SQL…this is how I did that…

Microsoft have a translator facility here with the Translator Text API v3.0 to allow you to call it programmatically. I’m using Microsoft as I’m currently working on Azure – of course, there are other translation facilities available.

The API has a translate method which one (more...)

Optimistic Locking 8: double-checking with flashback

Some optimistic locking methods, including the SCN method, can detect intervening updates even when column values have not really changed. With the SCN method, we can use flashback queries to confirm that real changes occurred.

The importance of being aliased

| Jul 20, 2018
Always check out the original article at for latest comments, fixes and updates. I was writing a query returning the indexes to be monitored for a list of schemas for those tables having fewer than 5 indexes (not counting LOB indexes) and I was surprised to see that it returned no rows. After checking what's wrong I realized that I had specified the wrong column for

Index Monitoring in Oracle Database

Applies to ONLY Oracle Database 10gR1 through to 12cR1.
In Oracle Database 12cR2, this feature is replaced.  A new blog post of this feature will be posted in due course.

Since Oracle 10g, you can monitor indexes to see if they are being used or not.  Which is very useful as indexes, consume unnecessary CPU and I/O on DML activity if not used.  Therefore, it’s recommended to monitor indexes and any unused (more...)

Optimistic Locking 7: Restartability

When we UPDATE, Oracle may find a discrepancy between the read-consistent and "current" versions of the data. If so, Oracle "restarts" the process. Our optimistic locking solution must make that happen when appropriate.

EBR – Part 10: Data Dictionary Views for Editioning Views

This is part 10 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series


In a previous post I wrote about Editioning Views and their significant role in online application upgrades using EBR.
In this post we’ll see how editioning views are represented in the data dictionary views.

I’ll discuss only the USER_* views, but everything is true for the corresponding ALL_*, DBA_* and CDB_* (more...)

CSV from CLOB with field enclosures

After my post about extracting CSV without enclosures from a CLOB, here is my solution for CSV with enclosures. It wasn't easy...

Extract from CLOB with JSON objects

On AskTOM, Kim Berg Hansen recently used JSON_OBJECT to parse CLOB data in "name=value" format. I added a variant based on my work with CSV data.

ANSI dates make life easier

This post is one of a series on what I learned while not at Kscope18.

Dimitri mentioned that he learned about the ANSI date format that allows you to return a date with the expression.

Which means this
date '2018-06-10'

Is the same as

And you'll never want to type (more...)

Extract CSV from CLOB with JSON arrays

Marc Bleron blogged about CSV CLOBs and JSON_TABLE two years ago. Here's my contribution to improve on a great idea.

Read CLOBs fast with less memory

Reading a big CLOB is like trying to eat a burger all at once: it will take you forever if you don't choke. Why not cut that CLOB into bite-size chunks? It's faster, uses less memory - and it's good table manners...

Generate JOINs among related tables

After finding the shortest "foreign key" path between two tables, I'll try to generate a SELECT statement that joins all the related tables.

Split a string again and again

I recently needed to gather multiple columns from multiple rows into one string, then split them out again. JSON_TABLE did both splits at the same time!