Last week I revealed the numbers standing behind the overhead of calling a Pl/SQL function from within an SQL statement. I've left two questions open: - Is it always a performance issue, when you call a PL/SQL function from a SQL statement? - What can be done to maintain the function encapsulation (have the code DRY) and keep high performance?
In my previous post I've shown and measured the performance loss on passing the parameters to and from procedure/function call inside PL/SQL code. In this article I'm about to reveal another bottleneck that is often forgotten and not so easy to overcome.
When writing a PL/SQL code, I usually use following strategy:
There are however some situations, when I tend to break the rule, and this is due to performance.
Today I came up with idea to overcome the issue: SQLPlus ERRORLOGGING does not keep error log on rollback. The resolution is to use autonomous transactions to log the errors reported by SQL Plus. What we need to do is to somehow catch the error that is about to be logged and wrap it in an autonomous transaction.
Before I started working at Pragmatists I never actually took time or effort to research the web for automating the testing process in Oracle databases. My previous job was more about delivering solutions, advisory, analysis, design and implementation using the traditional cascade product delivery approach. I don't want to get into too many details on how it happened, that me, being a Oracle database developer, landed in Pragmatist, which is an Agile Software Development company specializing in Java, but all I need to say, is that nothing else but good came out of it for me.
While working heavily on script automation I came across the issue of having an SQLPlus script with optional parameter. Oracle does not allow that by default, and one needs to use a dirty trick to make it work. Vladimir made a great article on how to achieve that. Thank you Vlad, this is really helpful. Vladimir's Diary: On SQL*Plus Defines.
Recently I'm heavily involved in SQL*Plus scripting automation tasks. I must admit that despite all the settings you may set and retrieve with SQL*Plus, it is a real pain to do some good automation with this tool. I'm very glad that Oralce continues to extend Oracle SQL*Plus features, but i must say that it seems like the design details for those new features are not well thought, which gives me a headache when working with this tool and forces me to look for tricks and workarounds for badly designed functionality. In this post I'll write about the downsides of the ERRORLOGGING feature introduced with SQL*Plus 11.1 and above.