#100CodeExamples – Test updatable views with utPLSQL Cursors

We already looked at utPLSQL’s cursor comparison with user-defined types. If we have to deal with updatable views, we can use an even simpler approach and create nested table collections from %ROWTYPEs.

The scenario is similar to the previous one: we have a number of planets with or without garrisons. Now we want to be able to update the view with a garrison-ID which then automatically creates a new garrison-entry.

Continue reading

#100CodeExamples – Constants in PL/SQL and SQL

We often deal with the same things in several places of our codebase, be it names, IDs, codes or something similiar. Problems arise when we write them slightly different, which we often don’t notice because it won’t create a compiler error.

Additionally, these things should often not be changeable once set, therefore most programming languages provide CONSTANTS.
PL/SQL does so, too, but to get these constants into SQL can be a bit tricky.

Continue reading

#100CodeExamples – Use the force to replace objects

In a previous example we learned about the basic usage of objects in PL/SQL with which we can start object-oriented development in an oracle database.

A more common usage for objects, however, is to use them as a base type for nested table collections (table of objects). That way we can make objects accessible from SQL, which is a very powerful possibility to return table-structures from PL/SQL functions.

The annoyances often start when we want to change an object which is already referenced in a table type: we get ORA-02303 dependency errors. The “force”, as usual, can help here:

Continue reading

#100CodeExamples – utPLSQL Cursor Comparison with user-defined Types

I had the pleasure to present utPLSQL to the local DOAG usergroup in Karlsruhe last thursday and one of the questions after the presentation was how we can control the expected results of a cursor-comparison.

We already had an example using SELECT FROM DUAL and UNION ALL, but sometimes our expected outcomes are more complex.

In such situations, we can use user-defined types and the power of Nested Table-collections:

Continue reading

#100CodeExamples – PL/SQL Objects: Basics of force-sensitive beings

I dived into object-oriented programming in PL/SQL a while ago due to utPLSQL and also used it several times for my normal work meanwhile.

This powerful feature of the PL/SQL language is highly underused and therefore I want to create some examples around it in hopes to show some of the advantages objects in PL/SQL have to offer.

Let’s assume we want to implement some behavior for force-sensitive beings, whose most prominent representatives are, of course, Jedi and Sith:

Continue reading

#100CodeExamples – PL/SQL BOOLEAN to INT

One of the main annoyances of Oracle databases for me has been its lack of a BOOLEAN SQL-Type.

Yes, BOOLEAN exists in Oracle, but only inside of PL/SQL, not in the SQL context, which means that you can’t select a boolean type and also can’t retreive it via JDBC.

The simplest way to overcome this is to have an easy way of converting BOOLEAN to 1/0 INTEGER – and there are several possibilities to do this:

Continue reading

#100CodeExamples – SYSDATE vs. CURRENT_DATE

I ran into a strange error yesterday: when I ran my test-suite from IDE I suddenly got a number of failed tests, but they were completely okay when I ran it from utPLSQL-cli.

I panicked a bit because that’s one of the worst possible situations you can imagine for a testing framework.
After some investigation I found the problem.
Look at the following example-test suite which assures that the current date and time is set for “arrival” when a new entry is inserted into the “starport_flights”-table:

Continue reading

#100CodeExamples – Dynamic values in a String: UTL_LMS

If you want to add dynamic values in a string with PL/SQL, you can concatenate it like this:

l_alertMessage := 'A bunch of "' || l_attacker ||
  '" is attacking with an estimated fleet of ' ||
  to_char(l_numOfShips) || ' ships';

But this is both, hard to read and tedious to write, because we have to cast everything which is not a varchar2/nvarchar2 explicitly with TO_CHAR.

There are, however, several nicer ways to achieve that goal. One way is the usage of UTL_LMS.

Continue reading

DOAG 2018 – my personal review


The DOAG exhibition and conference in Nuremberg, 2018 probably the biggest Oracle conference in Europe with over 2000 visitors, is over. And I had the pleasure and privilege to be part of it.

As a first-time attendee and first-time speaker, the conference was both, very exciting and immensely frightening to me. The great location alone with its 4 floors arranged around a huge hall made me question whether I belonged there right when entering. Nonetheless, I proudly got my speaker shirt and the following three days turned out to be one of the greatest highlights 2018 for me.

19 Sessions in parallel every hour made me really spoilt for choice and in the end, I even skipped several slots because of chats with the amazing people I met – and pure exhaustion.

Some of my personal highlights in no particular order:

Organization, Food, Location, Transport – all of these things were exceptionally well done and made the conference a great and comfortable experience! Excellent job, DOAG e.V.!

My own talk: Introduction to utPLSQL

The talk before mine finished a bit early so I had even more time to prepare, put on my sith robe, setup my notebook and – OH WHAT THE F**K THIS HDMI CABLE DOESN’T FIT INTO MY NOTEBOOK!! WHAT DO I DO NOW? I NEED THIS CAREFULLY PREPARED DEVICE WITH LIVE DEMO AND…
Luckily it was just a display port adapter still attached to the HDMI cable (sorry to the volunteers who had to deal with me while panicking). Maybe you get my nervousness.

I was very glad some people I already knew from the day before showed up pretty soon and I had someone to talk to – until the doors got closed about 3 minutes before the scheduled start.


The topic struck a nerve, the room was completely full (as was the other talk about utPLSQL by Philipp Salvisberg the next day) and people had to be sent away.

I was very glad I had memorized my talk, so I didn’t need much brain capacity to search for words. And after some minutes it felt much more natural to me (my hands were still shaking pretty much when scrolling through the live demos, but people I spoke with afterward said it was not visible).

I got lots of really great feedback and was even asked if I wanted to do the talk at another conference. Which I might do, because after the first five minutes I really started to enjoy presenting what I had prepared for months.

Tips for first-time speakers

For me, a blog article doesn’t feel right if there is nothing actionable I have to share so I will try to point out some things which were immensely helpful for me – maybe they’ll help or encourage other people who want to do a public presentation.
If you aren’t interested in that, just scroll down to the last and by far the most important part of this blog-post.

Continue reading