Skip to content

PLSQL

Testing for exceptions with --%throws

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 starter tips - Annotations configure tests

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.

utPLSQL starter tips - creating test suite

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.

How I got into utPLSQL v3 development

Winter is coming and the 7th season of Game of Thrones now just a memory. While I do love watching TV series it was not them that dragged me away from my blog.

For last 18 months or so, I was heavily involved in design and development of new version of utPLSQL v3. After a year of development, we've published utPLSQL version 3.0.0 in May 2017 and now we are at version 3.0.3.

  • in PLSQL
  • 5 min read

Beware of raise_application_error from within exception when others

Most of use-cases for current Oracle databases involve some kind of application server that is managing database connection pool. The pool is keeping database connections open for long time to avoid the overhead of connect/disconnect handshakes. Taking into consideration, that deployment of database changes, is more and more often required to be done seamlessly, with minimum or even zero down time, the changes must be applied in a way that they do not significantly impact the open connections and do not enforce the connection pool recycling. This is related to the fact that we want to minimize the scale of impact of particular deployment on a working, living environment.

  • in PLSQL, SQL
  • 4 min read

PL/SQL and SQL naming conventions

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.