In my previous post I have described solution allowing you to obtain count of error rows that get inserted into error table when using Oracle SQL syntax of INSERT INTO ... SELECT ... FROM ... LOG ERRORS.
The solution provided had a bug related to resetting counters.
Oracle SQL has a really neat feature to log the rows that failed to be processed during DML statement (INSERT / UPDATE / DELETE / MERGE ). This is really great feature and you cen read a lot more about it on oracle-base.com The thing I was wondering about is, if I log errors and my DML statement fails, how can I know if that statement had some errored rows. I've checked documentation and asked some experts on Twitter but seems there was no feature to support that.
Some time back I've read Mike Smithers Blog on SQL and PL/SQL standards. I really like reading his blog. He is a great story-teller. Being Oracle developer for over 15 years should make me comply with all of the mostly demanding standards there are. My nature however always tells me to look at the balance the costs and benefits of my actions. Mike pointed out a good amount of issues, when it comes to introducing coding standards and how important it is to keep it simple. Naming conventions can also become a true bottleneck and make the database structures and code change-resistant.
In my previous post I have described the conceptual differences between UTPLSQL and ruby-plsql frameworks for unit testing of Oracle database code. I have used a message_api package and unit tests for that API using both frameworks as an example. In this post I will focus on getting the tests to run and the feedback that we can we get from the tests using both frameworks.
Unit Testing is around for quite a while. Since it started to become more and more popular, quite a few tools became available for Oracle database to allow unit testing of the database. There are the UI based tools like Quest Code Tester (now Dell Code Tester for Oracle), Oracle SQL Developer unit testing. There is DBFit by Goyko Adzic for regression and functional testing of databases (including but not limited to Oracle), there are probably many more of that kind, that I'm not aware of. There are also pure programming language based frameworks like UTPLSQL and ruby-plsql. There are probably (and hopefully) many more of that sort that I'm not aware of. Unit tests are meant to be created and maintained by software developers and are to help developers keep their code clean and valid throughout entire project life cycle. UI based frameworks for unit testing are putting a high abstraction and tight facade between the code and the developer. Those frameworks tend to limit the richness and variety of possible implementations for a test case and therefore are not suited for software developers. The time and cost of development and maintenance for UI-based unit tests is usually way beyond the benefits. Variety of program units that are possible to develop is infinite and the only limitations to what the unit can do are the programming language boundaries and developers creativity. For this reason itself, it is best to use a programming language of similar or higher flexibility to describe and test the behaviour for a unit. In this series of article I will focus on two programming language-based unit testing frameworks for Oralce, that I got familiar with and had opportunity to use.
Oracle database does not support ability to obtain number of rows inserted/updated/deleted by a merge operation.
The only value you can obtain is the total number of rows affected by merge operation.
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.