Create Student User

| May 4, 2020

It’s amazing how old some of my students’ computers are. The oldest with least memory are the foreign students. Fortunately, I kept copies of the old Oracle Database 10g XE. I give it to some students who need to run the smallest possible option. Then, again I have students who get emotional about having to use Unix or Linux as an operating system, which means I now also support Oracle Database 18c.

Anyway, I had (more...)

A (Not So) Brief But (Very) Accurate History of PL/SQL

| Apr 29, 2020
Preface PL/SQL is one of the top twenty programming languages in the world. While I’m no Matthew Symonds, and this is no Softwar, the history of PL/SQL deserves a helluva lot more than, “Oracle 7 had an advanced architecture and, like Sybase, was fully programmable via its elegant new PL/SQL programming language.” That’s why, during the process working on […]

Syntax Highlighting With SQL Developer

| Apr 26, 2020


A customer asked me if it is possible show unused identifiers in SQL Developer. Since there is no PL/SQL compile warning for that, you might be tempted to say no. But you can always use PL/SQL Cop for static code analysis. Guideline G-1030 deals with variables and constants and guideline G-7140 with procedures and functions. However, in this case it’s also possible to achieve the same result by tweaking SQL Developer’s preferences for PL/SQL (more...)

Imitation is the Sincerest Form of Flattery

| Apr 21, 2020
Compilers and Interpreters Compatible with PL/SQL The PL/SQL programming language, created by Oracle, is the most well-known database-oriented procedural language in existence. While everyone who knows about PL/SQL knows it’s available in the Oracle Database, what most people don’t know is that a number of companies and individuals have implemented their own compilers and interpreters […]

Well-Testable Code: Dealing with the “Popular Sith”-Sequence

| Apr 21, 2020

After I published my last post, some folks rightly pointed out that the way I implemented the add_popular_sith procedure was not well-testable and that I should rather change my code than trying to control the underlying sequence.

And I totally agree. For several reasons:

  • Controlling the sequence adds a lot of complexity to my testing code
  • It invalidates depending packages (and yes, there are ways around that problem)
  • It also forces me to touch (more...)

Demystifying PL/SQL: The Life of a Compilation Unit

| Apr 21, 2020
With a solid history of use, Oracle’s PL/SQL is one of the top twenty programming languages in the world. OverviewWhile many people tend to think of it strictly as a database procedural language, PL/SQL is far more powerful than most give it credit for. PL/SQL is so popular, in fact, that at least fifteen independent […]

Accessing a website from the database

| Apr 15, 2020

One of the things people want more and more often is to somehow read from a web-resource using a database. It could be to just get the HTML off of a website or access REST via PL/SQL, or grab a JSON-file residing on a public website. No matter which it is we need to declare that it is a website we want the database user to reach.

To have a simple example I’ll just show how to (more...)

Formatting Code With SQL Developer

| Apr 12, 2020


I started using SQL Developer in 2013. Back then version 4.0 was the latest and greatest. But the capabilities of the formatter were disappointing. In 2017 Oracle released version 4.2 with a new formatter and has been improving it ever since. Version 19.2 brought us dynamic JavaScript actions within the parse-tree query language Arbori. And now I must admit that I’m really impressed with the formatting capabilities of the latest versions (more...)

utPLSQL and ORA-04061 – Global State and Package Invalidation

| Apr 8, 2020

A nasty problem has been haunting me for a while now in my codebase: Every time I run my full utPLSQL test suite, one specific test-package fails every test with ORA-04061 - Existing state of Package has been invalidated. But when I run this specific test package, everything works fine.

Today I followed the rabbit hole and to get a better understanding of what’s going on I tried to extract what I learned into a (more...)


| Apr 3, 2020

The other day a friend called me with a question on constraints. “I have a table where the combination of two columns must be unique”. My response was: “Use a Unique Constraint on these two columns”.
That should work, however…

“There is one value for the first column, where the second column doesn’t have to be unique.”
Let’s start with a simple script to play with this issue:

prompt create a simple table
create  (more...)

Log errors

| Apr 1, 2020

Just a quick note of something I ran into at my current assignment. I have to import data from external tables into persistent tables. This proces normally is just inserting data into the table, but sometimes a file can be sent in more than once.

Originally I thought I would add the /*+ APPEND */ hint to the statement to speed things up. This works flawlessly as long as you are just inserting new records. (more...)

Taming The Queue – when the third party can only handle so many connections

| Mar 25, 2020

We had a problem with the awesome power that the Oracle database offers... or to be more specific the third party had a problem with that awesome power.

Setting the stage

Our applications handles many, many orders per day and mostly they are processed in waves during the day. Meaning there are peaks and valleys in the amount of orders that need to be processed. When the orders are processed they are send to a (more...)

LUHNs algoritm – Three ways with SQL and PL/SQL

| Feb 25, 2020

Have you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day.

It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Grace, and Israel to credit card numbers and IMEI numbers and misc other things.

It is a very simple checksum function not intended to be cryptographically secure hash (more...)

Caching von PL/SQL Funktionsaufrufen

| Dec 20, 2019
Mohamed Houri zeigt in seinem Blog einen nützlichen Trick: er stellt den Fall einer Query vor, in der ein Wert mit dem Ergebnis eines Funktionsaufrufs verglichen wird:
a.xy_bat_id = f_get_id('BJOBD176')
Dieser Zugriff ruft beim Abrufen von 18605 Datensätzen aus der zugehörigen Tabelle 18605 recursive calls hervor - und darüber hinaus sehr viele consistent gets. Eine Untersuchung mit SQL Trace zeigt, dass fast die gesamte Laufzeit auf diesen wiederholt ausgeführten Funktionsaufruf entfällt. Offenbar (more...)

Where would we be if we just believe?

| Dec 10, 2019
As a science aficionado, there are certain phrases that ... catch the eye.

Recently on twitter there was an interesting thread that continued from Michelle Skamene's post on Top 15 Tuning Tips for APEX.  Michelle provided a wonderful follow-up post summarising the outcomes of the thread.

Point 9 suggests we avoid HTML in our queries, and use HTML expressions. This is undeniably good practice, but there was a question regarding how much performance is gained. (more...)

Interpreted code in APEX

| Nov 28, 2019
A few years ago I posted a comparison between plugin code left in the source attribute, vs code that has been transferred to a PL/SQL package.

In the interests of good science, and I wanted to chat about it at next week's Office Hours, I wanted to repeat this test.

I had a little difficulty working out how I got the metrics, I think APEX debugging has changed a little since I ran (more...)

On the PL/SQL you don’t write when using APEX.

| Nov 26, 2019
Fancy joining in on a discussion with PL/SQL and Oracle APEX community members from around the world?

I'm honoured join Karen Cannell and Scott Spendolini, to be hosted by Steven Feuerstein in the next AskTom PL/SQL Office Hours on December 3, 2019.

It seems a few people haven't heard of these "office hours" sessions, but they're worth a go - more than just your average webinar. And they're all recorded for later viewing.

This (more...)

Agile Oracle Database Modeling and Development (#AgileOracleDatabase) – I’m a speaker at #DOAG2019

| Nov 18, 2019

We try to be agile in developing and merging our features into branches when they are ready for use.

Test Driven Development is on everyone’s lips with Java and other languages.

Whether we develop ADF or JET or Spring, we try to apply these techniques in many places.

But what is the real basis of most of our applications?
This is the Oracle database with its data model and programming language PL/SQL!

Again and again (more...)

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

| Oct 10, 2019

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

OGB Appreciation Day: Explore a utPLSQL test scenario with “force-manual-rollback”

| Oct 9, 2019

Most developers have – at some point – to deal with code they don’t know. Sometimes they don’t even have a clue what something is doing, but are expected to understand and probably change that something quickly. The situation gets worse if the functionality contains some side-effects or non obvious functionality, triggers for example in the database sphere.

I often encourage people to use unit tests to explore an unknown scenario or functionality, and utPLSQL (more...)