OTN Appreciation Day: Oracle Express Edition (XE)

Tim Hall of oracle-base.com fame suggested we should all do an "OTN Appreciation" day in honor of the Oracle Technology Network (OTN), by blogging about our favorite Oracle product or feature.

My personal favorite is Oracle Express Edition (XE), the free version of the world's best relational database.

 Here's what's great about it:

OTN Appreciation Day : GeoJSON and SDO_GEOMETRY marriage in Oracle 12.2

The native JSON functionality in Oracle Database version 12.2 has evolved quite a bit since the JSON functions first appeared in Oracle Database version Just one example is JSON_TABLE can be used as a bridge between the open standard GeoJSON format and the database internal SDO_GEOMETRY format, making it very simple to use externally available geocoding datasources within spatial applications in the database.

You have been able to do this (more...)

OTN Appreciation Day: Analytic Functions

This is my contribution to the OTN Appreciation Day, which was initiated by Tim Hall.

One of my favorite features of the Oracle Database are Analytic Functions. They were introduced with Oracle Database 8.1.6 Enterprise Edition, and have been in the Standard Edition since version 9.

With analytic functions you can add inter-row calculations, aggegrate over multiple dimensions, rank assignments based on values. All this without a GROUP BY clause.
The syntax might (more...)

OTN Appreciation Day : APEX Dynamic Actions

I'm going to take advantage of the fact I live in a city so remote to many others in this amazing community, and schedule this post for my local 8am time. It's seems my schedule didn't work. Not the first fail from blogger... This might get me as one of the first posts in what's hopefully an interesting day amongst Oracle bloggers.

Tim Hall, a great producer of resources for Oracle technologists, suggested bloggers new (more...)

Export APEX application with SQLcl

APEXExport has been around a long time for exporting an application and anything else like images, feedback,websheets,.. into a file system so that they can be version controlled.  This is a must if there is ever a need to rollback or see what the application was X days ago.  This is a java program that is part of the apex distribution.  The catch for some folks is that it's a java program and

4 Things To Spend Money On for Work

There are many things that your workplace will provide as part of your employment. Notebooks, pens, computer, and a chair. There are some other things that, if you spend money on, they can really improve the quality and efficiency of your work. 1 – A Decent Pen Every office I’ve worked in has had a […]

Oracle Developer Cloud Service October Release

Oracle Developer Cloud Service 16.4.1 has a fresh new UI and over 30 new features and improvements.


New Navigation List


Developer Cloud Service includes a more convenient vertical navigation list. Some features like the Maven repository browser have been moved to top level of the navigation list. The navigation list is collapsible to save on horizontal space when you are working with a specific feature area. The Home tab which shows (more...)

Oracle OpenWorld 2016: Mobilizing San Fran was a blast

Oracle OpenWorld2016 was a giant success for AuraPlayer! The response to our solution was amazing. Flocks of people gathered at our booth for live demos, attended AuraPlayer CEO Mia Urman’s session on Digital Transformation, gathered at Grant Ronald’s talks on “Forms to Mobile without writing a single line of code,” and heard first hand from AuraPlayer customer Rogers Group Inc. how fast and easy it is to create apps from their EBS modules to deliver (more...)

Iconic Breadcrumbs in APEX

Normally when you use a Breadcrumb in an APEX application you enter the data and that's it. The breadcrumb will look something like this:

Or when you reduce the screen, the Breadcrumb will look slightly different:

It's trivial to replace the text that you enter in the Breadcrumb with a little bit of markup and show a Font Awesome (or with APEX 5.1 Font APEX) icon.

<span class="fa fa-home"></span>
Now the text will be (more...)

Wissenswertes über Pivot und Unpivot

Eine schöne und ausführliche Darstellung dessen, was man mit den Funktionen PIVOT und UNPIVOT anstellen kann, liefert Chris Saxon im AllThingsSQL Blog. Ich neige immer noch dazu, diese Transformationen manuell zu erzeugen, aber irgendwann werde ich mich an die Funktionen gewöhnt haben - hoffe ich ...

Testing with JUnit and Developer Cloud Service

In a new blog entry on the Oracle Cloud Developer Solutions

blog Abhishek Gupta-Oracle shows you how to include unit testing as part of your build process and how to see the results in your dashboard.

Learn more here:


Tracking JUnit test results in Developer Cloud service

Oracle EMPTY_BLOB Function with Examples

In this article, I’ll explain what the Oracle EMPTY_BLOB function does and show you an example of how to use it. Purpose of the Oracle EMPTY_BLOB Function The EMPTY_BLOB function is used to initalise a LOB column to EMPTY. It can be used in several places: In an INSERT statement In an UPDATE statement Initialising […]

How can we add custom code to existing triggers?

Dear Patrick,

We have bought an application that runs on an Oracle database. There are triggers defined on the tables, but we want to add our own code to these triggers, but we don’t have access to the source code. What is the approach we should follow to accomplish this?

Collin Bratforth

Dear Collin,

There are two types of DML triggers. Statement level triggers and row level triggers. Then for these two types there are (more...)

500 Byte Limit für Hints in SQL Profiles und SQL Patches

Mohamed Houri weist in einem aktuellen Artikel darauf hin, dass (Outline) Hints mit einer Länge von mehr als 500 Bytes zu Problemen bei der Erzeugung von SQL Profiles führen können, da der zu ihrer Erzeugung üblicherweise verwendete Typ sys.sqlprof_attr als "varray(2000) of varchar2(500)" definiert ist. Glücklicherweise akzeptiert die Prozedur dbms_sqltune.import_sql_profile statt sqlprof_attr Angaben auch CLOBs, so dass die Beschränkung an dieser Stelle umgangen werden kann. In einem Kommentar zum Artikel weist Jonathan Lewis (more...)

Synchronise Sequence value with 12c Identity Column

My journey into 12c continues with the use of identity columns, this time regarding data that had been imported from another database, but sequences haven't been updated.

Ensuring the next number returned from a sequence matches the current value from the table appears to be a common problem, my thoughts are described here. The biggest trouble is linking up the sequence to the column so we could automate the process.


We can now (more...)

Restriction when column default is sequence.nextval

Oracle 12c introduced the ability to specify sequence.nextval as the default on a column, which is really nice – including the fact that it eliminates one of your excuses why you don’t decommission those old triggers.

Unfortunately it doesn’t work as you might expect if you use an INSERT ALL statement; it evaluates the default expression once per statement, instead of once per row.

Test case:

create sequence test_seq;

create table test_tab
( id number default  (more...)

Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.

Well, it's one thing to say 'create all new tables like this', (more...)

Top 3 Tips for Staying Current in the Evolving World of Data Warehousing

The world of data is changing and changing fast. How do you keep up? Here are my Top 3 tips.

Tech Tip: Quick Start for Getting Your Data into Snowflake

Want to learn how to load data into a cloud data warehouse? This post will show you one way with Snowflake

What is overloading and how and when do I use it

Dear Patrick,

Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?

Ramesh Cumar

Dear Ramesh,

Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)