Postgres Print Debug Notes

A student asked how you print output from PL/pgSQL blocks. The student wanted to know if there was something like the following in Oracle’s PL/SQL programming language:

dbms_output.put_line('some string');

or, in Java programming the:

System.out.println("some string");

The RAISE NOTICE is the equivalent to these in Postgres PL/pgSQL, as shown in the following anonymous block:

do $$
  raise notice 'Hello World!';

It prints:

NOTICE:  Hello World!

I hope (more...)

Postgres SQL Nuance

I ran across an interesting nuance between Oracle and Postgres with the double-pipe operator. I found that the following query failed to cross port from Oracle to Postgres:

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN  (more...)

Show null for switch items

An application I maintain needed a checklist feature added. I wanted to show a “Yes / No” switch for a list of checklist items. Initially, when the record is created, the checklist is populated with the questions along with a NULL for the response.

I generated the switches in an ordinary Classic report using code like this:

select as risk_category
         (p_idx        => 10
         ,p_value      => i.response
         ,p_on_value   => 'Yes'
         ,p_on_label    (more...)

OGB Appreciation Day : Oracle Reports Server Queue Monitoring

Today is a day to celebrate what's great about our Oracle 'Groundbreakers' community. Our favourite stuff, what we're learning, horror stories, or just a little thanks.

I missed the second year because of holiday prep - so I decided to beat that this year by writing this the day I saw Tim's call-out on Twitter. Especially since I'm flying out to Melbourne for AUSOUG Connect, plus a little R&R.

I knew I had a (more...)

OGB Appreciation Day: APEX_DATA_PARSER: Flexible, Powerful, Awesome (#ThanksOGB #OrclAPEX)

For this years OGB Appreciation Day I wanted to highlight the very awesome APEX_DATA_PARSER.

At my current project one of the requirements is that CSV-files are to be uploaded by the user and the application has to figure out what type of file it is and process accordingly.
So the challenge with this is:

  • Which type of file is it?
  • What delimiter was used?

Uploading the file is straight forward enough with Application Express (APEX) (more...)

Postgres Foreign Constraints

You can’t disable a foreign key constraint in Postgres, like you can do in Oracle. However, you can remove the foreign key constraint from a column and then re-add it to the column.

Here’s a quick test case in five steps:

  1. Drop the big and little table if they exists. The first drop statement requires a cascade because there is a dependent little table that holds a foreign key constraint against the primary key column (more...)

OGB Appreciation Day : How I failed to appreciate NULL

On the annual OGB Appreciation Day (#ThanksOGB has been renamed multiple times, see Tim Hall's blog post) I am going back in time to when I was a newbie developer, relating a story about me making a mistake that had bad consequences in the customer ERP system. Luckily I had experienced colleagues to repair the damage, and later in my career I learned about NULL and how it could have been so different back (more...)

Postgres Remove Constraint

You can’t disable a not null constraint in Postgres, like you can do in Oracle. However, you can remove the not null constraint from a column and then re-add it to the column.

Here’s a quick test case in four steps:

  1. Drop a demo table if it exists:


  2. Drop a demo table if it exists:

    ( demo_id    SERIAL
    , demo_text  VARCHAR(20) NOT NULL );

  3. Insert a compliant (more...)

Postgres Check Constraints

The Postgres 11 database documentation says that it supports naming constraints. While you can create a table with named constraints inside the CREATE TABLE statement, the names are not assigned to the not null check constraint.

Here’s a quick test case in three steps:

  1. Drop a demo table if it exists:


  2. Drop a demo table if it exists:

    ( demo_id    SERIAL
    , demo_text  VARCHAR(20) CONSTRAINT nn_demo_1 NOT  (more...)

So you want to learn Oracle APEX?

Are you involved with Oracle? Perhaps your a DBA, or a PL/SQL data master, possibly know a bit about Oracle Forms, and you want to learn what all the fuss is about regarding Oracle Application Express (APEX)?

My best elevator description? It's a low code development tool that lives in the browser. Yep, the IDE is a web page that allows you to add SQL, PL/SQL, modify a few attributes - and you've got a (more...)

Java Development with Autonomous Transaction Processing Dedicated (ATP-D)

The Oracle Autonomous Transaction Processing Dedicated (ATP-D) is a database Cloud service which allows implementing a private database Cloud service running on dedicated Exadata Infrastructure within the Oracle Public Cloud. The goal of this blog article is to help you, Java developer or architect, build and deploy fast, scalable, and reliable Java applications with ATP-D, using plain Java, Java Servlets, or Java Microservices with WebLogic, Helidon, WebSphere, Liberty, Tomcat, WildFly (JBoss), Spring, and (more...)

Oracle JDBC drivers on Maven Central

At last!

Yes, you asked for it, and with some delay (better late than ..), we did it!
Maven Central becomes a distribution center for the Oracle JDBC drivers. We started with the latest release but will soon add previous and supported releases.
Read the full post @

LISTAGG() as a Cumulative Function

LISTAGG() can be used as an analytic function, but cannot be cumulative. What does that mean, and is there a workaround?

Session Variables

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.


Thursday Thought: Look all around you

A current pleasure of mine after getting home while the sun is still pleasant is to take my (currently) nine eighteen month 5 year old for a stroll in her pram. It's been a while since I drafted this, time to tidy up the backlog...

We were lucky enough to live opposite some bushland, big enough where you can do a 30 minute loop and enjoy a mostly native tree/scrub area. There are obvious (more...)

Oracle Groundbreakers Tour Nordic 2019

In 4 weeks my first Oracle Groundbreakers Tour as ACE Director will begin. 4 Nordic capitals in 4 days, speaking about my favorite topic - SQL. SQL is as important as ever to work with data - even (or maybe especially) in this cloud era, where you can spin up, develop and deploy databases and apps without needing anything but a browser.

Our group of ACE Directors will give presentations at these events:

Python-Postgres Query

As I committed to a student, here are sample programs for writing a Python query against the Postgres 11 database. The first one returns rows or tuples. The latter formats the text returned as columns.

The first one simply returns the entire row from a new_hire table with two rows:

import psycopg2

  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")

  # Open a cursor.

pgAdmin4 on Fedora 30

While attempting an install of pgAdmin and updating a Fedora 30 environment, I encountered a conflict on the upgrade of MySQL 8.0.17-1 to The community-mysql-8.0.17-2.fc30.x86_64 had conflicts with:

  • mysql-community-client-8.0.17-1.fc30.x86_64 package
  • mysql-community-server-8.0.17-1.fc30.x86_64 package

I tried to update the system before install pgadmin4 with the following syntax:

dnf -y update && dnf -y install pgadmin4

The dnf utility raise (more...)

Change label dynamically in Oracle APEX

I was developing with Oracle Forms for an awesome project when I first heard about Twitter, and it was described to me by Jeff as a 'micro-blogging' site.

I think I did what could be my smallest blog post, as a tweet. A micro-blog, if you will.

Here is the snippet that dynamically updates a (more...)

Keine non-pdb Systeme mehr mit Oracle 20

Es kommt nicht überraschend, sei hier aber erwähnt: mit Oracle 20 wird die non-CDB Architektur nicht mehr zur Verfügung stehen. Dafür ist mit Oracle 19 in SE2 und EE die Verwendung von drei (vom User erzeugten) pluggable databases auch ohne Multitenant-Lizenz erlaubt. So nachzulesen bei Mike Dietrich.