ORDS 3.0.7 more secure by default

Defaulting  PL/SQL Gateway Security Oracle REST Data Services 3.0.7 went out yesterday.  There's an important change that went in to better secure installations by default.  It has always been the case that we recommend customers set the validations for the plsql gateway.  There has always been a validation configuration option to lock down what procedures are accessible which was outlined in

dbms_redefinition mit virtuellen Spalten

Nach längerer Zeit mal wieder etwas selbst Gebasteltes: im OTN Forum General Database Discussions wurde kürzlich ein Fall vorgestellt, in dem die Verschiebung einer Tabelle in einen anderen Tablespace mit Hilfe von dbms_redefinition.redef_table in einen Fehler führt, weil der vereinfachte automatisierte Prozess versucht, eine virtuelle Spalte durch ein Insert zu befüllen. Dazu ein Beispiel, das den OTN Fall noch mal reduziert:

drop table t;
drop table t_int;

create table t (
id number
, (more...)

Adventures in the Cloud

I just got back from a whirlwind tour that included a trip to San Francisco to visit the Snowflake HQ, attend the annual Oracle ACE Directors briefing, meetings and events at Oracle OpenWorld (OOW), speaking at OakTable World (OTW), then off to Chicago to speak at Enterprise Dataversity. Yes it was packed. And lots of […]

Histogramme für Spalten mit PK/UK

Jonathan Lewis hat dieser Tage in einer Diskussion der Oracle-L Mailing-Liste darauf hingewiesen, dass auch eine Spalte mit einem PK oder UK von einem Histogramm profitieren kann - und diese Aussage jetzt in seinem Blog erläutert und mit einem Beispiel versehen. Interessant ist ein solches Histogramm dann, wenn die Werteverteilung zwischen Minimum und Maximum sehr uneinheitlich ist, so dass sich große Bereiche ergeben, in denen fast keine Daten existieren, während in anderen Bereichen gleichen Umfangs (more...)

Celebrate Batman Day with SQL

Because today is Batman Day, it calls for a celebration of the SQL type:

SQL> select listagg (b)
2 within group (order by null) ||' Batman'
3 from (select 0f/0 b
4 from dual
5 connect by level <= 14
6 );

NanNanNanNanNanNanNanNanNanNanNanNanNanNan Batman

NoPlsql vs ThickDB: which one requires a bigger database server?

Slides of my Oaktable World presentation held during OOW 2016 can be found here: NoPlsql vs ThickDB

When would you use a normal table function?

Dear Patrick,

Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?

Erik van Roon

Dear Erik,

Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)

Show report tooltip as notification

Last week I described a method to make the tooltip on information more accessible to the end user.

Here is how you could make the same information available to touchscreen users.

1) First step, as before, is to define the HTML Expression of the column to include the title tag.
In this case I also stored my row identifier as an extra data- attribute.
<span title="#RECENT_NOTE#" data-key="#ROW_KEY#">#MY_COLUMN#</span>

2a) Create a dynamic action on click (more...)

orachk “Could not login to (SID)”

Today I stumbled across a strange error when I tried to run orachk on a customer’s system: $ ./orachk Enter ORACLE_HOME for PRODDB : /opt/oracle/product/rdbms/12.1.0 Could not login to PRODDB using /opt/oracle/product/rdbms/12.1.0. Try again (3 attempts remaining) Enter ORACLE_HOME for PRODDB : a.s.f... A quick internet search revealed nothing useful, so I checked MOS. Document […]

Parallel Hint auf Statement Ebene

Christian Antognini erläutert in seinem Blog die unterschiedlichen Ausprägungsformen, der seit 11.2 verfügbaren Variante des parallel Hints auf Statement-Ebene, die neben den Hint auf Objekt-Ebene getreten ist, der es seit 11.1 erlaubt, den dem Objekt zugeordneten DOP (degree of parallelism) zu überschreiben. Für den parallel Hint auf Statement-Ebene gibt es dabei folgende Möglichkeiten:
  • parallel (default): erzwingt die Verwendung des allgemeinen (also von den beteiligten Objekten unabhängigen) default DOP (der sich errechnet als: (more...)


Brendan and me at AT&T Park

Brendan and me at AT&T Park

Interesting beginning to OpenWorld/JavaOne yesterday. I had the chance to visit with Brendan Tierney and he gave me a copy of his new Oracle R Enterprise: Harnessing the Power of R in Oracle Database, which I went through quickly last night. It looks well written and I liked the examples.

Brendan also gave me a copy of the group effort on Real World SQL and PL/SQL: Advice from (more...)

What’s the difference between SEMI-JOIN and ANTI-JOIN?

Dear Patrick,

What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?

Lillian Sturdey

Dear Lillian,

First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:

[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
               FROM dept d
               SEMI JOIN emp e ON (e.deptno = d.deptno)

to get all the (more...)

Oracle Developer Cloud Service at Oracle OpenWorld and JavaOne 2016

You'll have many opportunities to catch up with Oracle Developer Cloud Service at Oracle OpenWorld and JavaOne this year.

The team will be at the Developer Cloud Service demo booth in the Moscone south exhibition hall, and we'll also be at the Oracle area at JavaOne.


There are multiple sessions and labs featuring DevCS here is a partial list:


A Cloud Platform for Developers: A Tour of Oracle Developer Cloud Service [CON1949]


Extend column tooltip to table cell

Quite sometime ago while still on 3.x I described a simple way to add tooltips in a report using standard HTML in a HTML Expression.
<span title="#RECENT_NOTE#">#MY_COLUMN#</span>

However, it will only appear when hovering over the span content, not anywhere within the table cell.

To do so we can add a dynamic action to execute some JavaScript after refresh of the relevant region.
$('td[headers=my_column] span').each( // for every data cell in the column

Find first n gaps in integer primary key


sokrates@12.1 > create table t( pk int primary key check(pk > 0));

Table created.

sokrates@12.1 > insert /*+ignore_row_on_dupkey_index(t(pk)) */ into t
sokrates@12.1 > select trunc(dbms_random.value(1, 1e5)) from dual
sokrates@12.1 > connect by level <= 1e5 
sokrates@12.1 > /

63187 rows created.

Finding the first n gaps

sokrates@12.1 > variable n number
sokrates@12.1 > exec :n := 1000

PL/SQL procedure successfully completed.

sokrates@12.1 > set autotr  (more...)

Template Datenbanken in Postgres

Daniel Westermann erklärt im DBI Blog die Rolle der Template-Datenbanken in postgres, über die ich mir bisher nicht allzu viele Gedanken gemacht habe. Im Artikel erläutert er folgende Punkte:
  • in einem neu installierten postgres-Cluster existieren zunächst drei Datenbanken: postgres, template0 und template1.
  • die Datenbank postgres ist der default-Einstiegspunkt für viele Applikationen (etwa den pgadmin), kann aber - wie jede andere Datenbank - gelöscht werden, wenn keine Connections zur Datenbank existieren.
  • man kann sich anschließend aber (more...)

Oracle Developer Cloud Service September 2016 Release

Oracle Developer Cloud Service 16.3.5 introduces new enhancements for Deployments and integration with Oracle Social Network.


Deploy WebLogic Server Shared Libraries to Java Cloud Service


When selecting build artifacts to deploy to a Java Cloud Service instance, new options are available to deploy as a WebLogic Shared Library. The shared library can then be referenced by other running applications in the JCS domain.


Oracle Social Network Webhooks


New webhooks (more...)

Getting from http to https

The world is moving to https, but that was not the reason for the move.

Initially I was happy to use whatever Digital Ocean (DO) supplied in the WordPress droplet. But as I explained in my last post, I had some problems with moving from wordpress.com to my self hosted site at DO.

In short the problem turned out to be Chrome caching sites that accepts https making my site unavailable to every (more...)

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