Skip to content

Blog

  • 7 min read

BUG in INSERT ... SELECT with nested objects

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 SQL
  • 10 min read

INSERT ... LOG ERRORS and SQL%ROWCOUNT

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.

  • 5 min read

Test Driven Development in PLSQL with utPLSQL v3 - intro

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:

  • unit tests don't bring any value
  • unit testing is too time consuming / too hard
  • the functionality is too complex to be unit tested
  • why should I write unit tests, my code already works as expected

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.

utPLSQL v2 vs. ruby-plsql – Oracle unit testing frameworks for developers comparison

UTPLSQL_vs_RSpec Last two months I was blogging quite a lot about UTPLSQL vs ruby-plsql. There are lots of aspects that I did not manage to cover so far. I've had a ambitious plan to go through all of the details and dig into the darkest corners to show all the differences. Time is however one thing I'm really short on recently, so instead of going into all the details as planned I've decided to give a high level overview of main differences between UTPLSQL and ruby-plsql. This will be a summary of the series for now, as I feel like moving into other topics. I might get back to it later if I find good reasons for doing so.