Skip to main content


Showing posts from December, 2015

PL/SQL Brain Teaser: Find all the hard-codings!

We all know that hard-coding is a bad thing (well, maybe not all of us. At one training several years past, I asked the audience "Does anyone think hard-coding is a good idea?" and one person raised his hand. Um, OK).

You know hard-coding: when you say to yourself "This is never going to change." and so you put the "value" directly in your code, over and over again.

I put "value" in quotes, because many developers think simply of hard-coded literal values when they think of hard-coding. But I think there are many more ways that hard-coding can seep into our programs.

So I invite you to help find all the hard-codings in the procedure below.

Here's the rule: you can only identify ONE HARD-CODING in each comment. I will delete a submission with > 1. It'll be more fun that way. Promise!

I will give everyone a couple of days to submit your ideas, then offer my own views on the subject.

LOG ERRORS: Suppress row-level errors in DML

Use LOG ERRORS to suppress row-level errors from within the SQL engine when executing non-query DML statements (like inserts, updates and deletes). Instead of raising an error, the SQL engine will insert a row into your error log table - which you then must, really must check after statement execution to see if there were any problems.

Or you will be hiding under the pillows, and your users will really not appreciate that.

In contrast, if you use SAVE EXCEPTIONS with FORALL, you will suppress statement-level errors, but all changes made to rows identified by that statement are rolled back.

Suppose I execute the following statements:
CREATE TABLE plch_employees ( employee_id INTEGER PRIMARY KEY, last_name VARCHAR2 (100), salary NUMBER (3) ) / BEGIN INSERT INTO plch_employees VALUES (100, 'Sumac', 100); INSERT INTO plch_employees VALUES (200, 'Birch', 50); INSERT INTO plch_employees VALUES (300, 'Alder', 20…

SQL%ROWCOUNT: What/how much did my SQL statement do?

This post is courtesy of the PL/SQL Challenge quiz ending 27 November 2015:

If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query.

Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore: When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point. When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.

Here's the code for the quiz - see how you do!

And of course sign up to take each weekly quiz as it is released (you can even compete for international rankings).

I execute the following statements (which you can easily run yourself on LiveSQL):