In the previous article, I've briefly demonstrated creation of a very simple unit test using a tool built-in to the SQL Developer. Let's see what issues you'll have to face when using this tool in a real-world project - not just traditional bugs (fixable) but consequences of decisions made when designing the system (thus inherent, not easily fixable, problems).
When writing PL/SQL procedure or function, you may want to create a unit test - something that tests the piece of code automatically. There are several projects intended for writing unit tests of a PL/SQL code, e.g. the well-established utPLSQL or a newer PLUTO, but since version 2.1 the SQL Developer contains it's own unit testing solution. As we have used it for several months on a real project, I'll take the liberty to present my experiences - maybe you're considering using it on a project too.
It is said that "A road to hell is paved with good intentions," and it's true even for SQL queries. A lot of problems is caused by developers who strive to optimize the database, frequently without necessary knowledge of the internals.
Let's check another source of problems - structure of a table. Again, this is a very broad topic, so I'll present just one very common example, I guess you'll be able to come up with many variants.
Last week I've posted a first puzzler related to generating random numbers or rather selecting random rows in PostgreSQL, believing that it's first and maybe the last puzzler for a long time. Well, today I've found a similar feature in Oracle - so lets begin!
And now let's check some problems with the SQL queries and indexes. There are two basic problems related to indexes - using an index when the index should not be used (false positive) and not using an index when it should be used (false negative). If you don't know what indexes are or if you're not sure how they work, read something about it - for example my article basic principles of database indexes describes basics of indexes.
First (and so far last) SQL puzzler, related to some basic concepts from relational databases, as for example primary keys. Note - this puzzler is quite closely adherent to PostgreSQL database (it uses some PL/pgSQL functions to generate example data, but that's not all).
It may be surprising, but many of the performance problems with SQL statements are originally caused by mistakes of the administrators. Some of these problems are closely related to the particalar database system. Thera are infinitely many ways to misconfigure a system or a database - let's check at least two quite frequent problems.
