I was about to optimise some code from using FORALL to work set based, but a small test changed my mind.
Read More »
Tag: PL/SQL
Yet another reason to unit test your (PL/SQL) code
The last few months, I’ve had the pleasure of using utPLSQL v3, a unit testing framework for PL/SQL, on a new project that I’m working on. Sure – writing tests takes a bit extra time, but it’s such a huge help when you’re constantly refactoring a system that grows from zero.
LOVs or Lists of values – one solution fits quite a few
Applications often have the need to have selection lists present and/or limit the possible choices for entry in a field. Some lists need to be static, and others need to be a bit more flexible in terms of adding new values. I’ve seen several solutions to this, and having been “stuck” on 11g for a long time, I thought I’d test out a “new” way of doing it using a composite foreign key and hidden columns.
Debug domino
We found a bug in some old code. And we fixed it. Then other stuff started failing. It turned out that there were some other code, using the same function, that worked because of the bug. That forced us to roll back the fix and thereby re-introduce the initial bug that weren’t as critical.
So – we had to find a way to find out which code-paths were working because of the bug so we could fix those.
Reviewing statements with hints, using PL/Scope
In my previous post, I showed how you can add a fake hint to tag the origin of a duplicated statement and as a side-effect make it unique. What if you regret and can’t remember where it was. Or you want to review old hinted statements after an upgrade to a newer version of the database/optimizer.
Differentiating between duplicated statements
Finding duplicate SQL statements using PL/Scope is easy. If you cannot merge them, how can you differentiate between which source is being run?
Find bad performing queries or code with PL/Scope
On our production system we’ve enabled the collection of PL/Scope metadata. Since this is a SmartDB/PinkDB-application (business logic and queries in the database), this makes it really easy to find, inspect and modify the source code of queries that doesn’t run efficiently. Now it’s even easier using reports in Oracle SQL Developer.
Scripts from my PL/Scope presentation at Code One
Thanks to all of you that attended my PL/Scope session at Code One in San Fransisco!
You can find the scripts from my presentation about using PL/Scope to find your way through a PL/SQL code base in my github repository here: https://github.com/nicetheory/PL-Scope
Function result cache and application context – don’t get burned
Oracles function result cache (FRC) can in certain cases give a considerable performance boost. Application context is another useful feature. How does FRC work when the function result relies on context-settings? And how can we make them play well together?
Finding dead code using PL/Scope
Can we use PL/Scope to find dead and potentially dead PL/SQL code? Yes! And it takes less than a second!
PL/Scope records declarations and usages of procedures and functions, so it should be easy to do a quick comparison of what’s declared and what’s actually used.
- nice theory... 