As a preparation for an upcoming workshop, I had to setup an Oracle instance with utPLSQL for demos and testing purposes. With today’s possibilities of running an Oracle 21c in a container (thank you Gerald Venzl) and great utPLSQL installation scripts (thank you Jacek Gebal), it was a way simpler Read more…
PL/SQL provides three different types of collections to hold multiple items of data. They all come with different capabilities and limitations and since these are hard to remember, I provide a condensed showcase of the different collection types in one single code example.
I already wrote about how much I like the XML generating capabilities of modern SQL, but most examples come only with one or two levels of depth.
What if you want to create deeply nested XML and JSON directly from SQL?
Recursive CTEs (WITH-clause) is one addition to the SQL standard that makes the language incredibly powerful. Besides other things it can be used to generate data rows “from thin air” – for example a list of quarters by year.
SQL is your friend!
One of my favorite features of (Oracle-)SQL is how easy it is to turn relational data into other data formats like JSON or XML.
But what if you need to add a namespace to some of the nodes in your generated XML?
I struggled with that until I made a realization about the nature of the xlmns part in XML.
One of the probably most unnerving things in databases is dealing with NULLs, especially when comparing data. The necessary logic to deal with NULLs can lead to very verbose code – but if you’re using Oracle, there is a very neat function you can use.
Ever tried to sort Version-Strings with pure SQL – for example when you want to find the highest Flyway version installed in your database?
Let me show you how you can!
Sometimes we have data that is not split into columns and rows, but SQL can only really thrive on structured data.
But thanks to recursive queries we can split any string into rows – with pure SQL.
It is a common practice to eliminate possible NULL values from your method’s parameters, and in the past I advocated to use subtypes to do this in PL/SQL.
However, it doesn’t work from SQL…
Automated self-tests are a fundamental part of application- and web-development
nowadays, even to the point where one can’t imagine modern software development
without them. Yet they are still surprisingly uncommon in the database.
This article shows how easy it is to start developing self-tests in Oracle with utPLSQL
version 3 and what benefits these tests will bring to your project
and your software.