Switch Pl/Sql logging on/off for specific sessions

As we know Oracle provides several nice tools to debug our PlSql code and even a profiler to get all the details about code execution. But what if you want to use your own debug code and be able to switch it on for specific sessions whenever you want? And I mean, obviously, without changing the code! I’ve seen around some examples in which people use a configuration table to do that: the debug code keeps querying the table and logs the output only if the flag is Y. This way of doing it is absolutely fine and works well but I was looking for something more elegant that does not involve a table. My idea takes advantage of the Oracle application contexts and it’s really simple.

Read More…

DB Unit Testing with SQL Developer: REF CURSORs still having problems [UPDATE!]

Database Unit Testing is underestimated by many (too many!) IT companies. Personally, in my long career, I’ve never seen it properly implemented. In the past there might have been lack of right tools for testing database objects but nowadays the market offers a good choice of software able to fill this gap. Because of my deep love for Oracle products (and I’ve never kept it secret) I’ve recently decided to spend some time analyzing Oracle SQL Developer and give it a shot to see how good is the unit testing support. This post refers to SQL Developer version 18.1 connected to an Oracle database release 12.1.0.2.

The version 18.1, latest release at the moment, has a ton of interesting features but the purpose of this post is to just focus on the db unit testing part, specifically on REF CURSORS. I really like how Oracle implemented this testing module in SQL Developer: it’s clear, it has a guided wizard which makes it easy to create tests, it supports shared repositories and allows to save libraries for reusing code. The only problem I had in the past releases is that REF CURSORS were not properly supported. I was hoping to see an improvement in this latest update but unfortunately it’s not the case and I will show in this post what I mean.

Read More…