Throwing exceptions is a common practice of handling situations when the program meets unsupported combination of data or conditions. Oracle PL/SQL language provides several mechanisms for raising and capturing exceptions.
utPLSQL provides the --%throws annotation that allows for verification of expected exceptions.
Exception testing in utPLSQL is done through this annotation, not through ut.expect(...).
utPLSQL can compare results of two queries in single line call to ut.expect(). Within your test, define the SQL queries as SYS_REFCURSOR variables.
utPLSQL provides expectations syntax through the ut.expect() methods. This post covers the most commonly used expectation matchers and how failure output works.
You configure utPLSQL test suite in special comments — called annotations — right inside the PL/SQL package specification.
Annotations are comment lines starting with --%. They tell the framework what is a suite, what is a test, what procedures need to be called for setup or cleanup logic. The framework reads them at runtime so no extra configuration is needed.
Testing PL/SQL code does not need to mean writing throwaway scripts or using a complicated graphical user interface.
utPLSQL gives you a proper test framework — right inside database.
A utPLSQL test is just a package annotated as --%suite with procedures annotated as --%test. The framework finds them automatically.
I came across a very nasty bug in Oracle SQL engine while working on some new code. It took me a while to figure out what the problem is as the SQL code I was working on was acting weird. The work was related to refactoring and consolidating an existing code. As part of the effort we aimed to achieve two things. First, simplify the code and second, improve performance. The code was already there and it was very well tested with unit tests using utPLSQL. Test cases covered all the join conditions and all column transformations done as part of delivered functionality. Thanks to the testing, we've managed to capture the issue while developing new code. It took me quite some time and head scratching to figure that it's actually an oracle bug. Creating an isolated test-case to reproduce the unexpected behavior really helped. With that I could confirm with 100% certainty that it's not coding issue but an actual BUG. I must mention that within my ~20 years of career as an SQL and PL/SQL developer I've never seen a bug like that.
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.
In utPLSQL v2 you had to use 'quoted text to compare tables / queries. utPLSQL v3 allows you to compare table data using native refcursors without usage of dynamic SQL.
The biggest challenge around unit testing is to make it work for you (as an engineer), not against you.
Many developers struggle, when writing unit tests. This is mainly due to the fact that while we are educated in design and implementation of database software, we are not mentored on unit testing. The struggle leads to dissatisfaction, frustration, poor test quality and lack of confidence in values of unit testing as a practice. Common statements and questions raised in regards to unit testing are: