Alex Vinall

The project I'm currently working on involves a large number of different technologies. Whilst the main application I'm developing is in Java, we have to (manually) interact with Oracle databases a fair amount. Most of the Oracle tasks are scripted via Bash, and *shudders* Windows batch files.

One of our project KPIs is performance. In order to measure performance, we need to capture runtimes for various tasks. In Java this is trivial - we use Apache Log4j so can see exact timestamps against log entry, and the applications we run output performance metrics into a database for future analysis.

Gathering this data is harder when running SQL scripts - by default you don't get a log file containing runtimes. Oracle does, however, provide the ability to SELECT the current datetime. And if you can SELECT it, you can log it. As a simple solution to this problem, you can use current_timestamp:

This returns the following:

You could then use this to contribute to your log output in order to track runtimes, or to INSERT records to a database that holds runtime statistics, for example.

Whilst it's not nearly as nice an experience to work with SQL in this way compared to working with Java, for us this does the job. Done is better than perfect.

​Alex

Recently I needed to perform a merge of two Oracle database schema. Both schema held the same tables, with the same columns, but due to the different ways that these schema had been created, there was no guarantee that the columns of a table in one schema were in the same order as the same columns in the same table in the other schema.

You'd maybe hope that when performing an INSERT INTO SELECT * FROM that Oracle would cleverly perform some wizardry to map column names explicitly by name. Sadly, this isn't the case, and it would be pretty catastrophic for the Amount column to end up in the VatAmount column of the merged schema.

So, in order to do this I needed to quickly generate INSERT statements that mapped the columns explicitly. Thankfully, the LISTAGG function in Oracle since 11gR2 allows us to do this in combination with the USER_TAB_COLS table that holds this data, with very few lines of code. Here's how to use SQL with an Oracle database schema to get a list of columns per table.

Alex