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

Last week, several hundred players tested their knowledge of the LOG ERRORS feature, and associated with it, the DBMS_ERRLOG package. Check out the quiz here, but feel free to explore the topic below!

Use LOG ERRORS to suppress row-level errors from within the SQL engine, instead writing information to the error log table. 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', 200); COMMIT; END; / BEGIN DBMS_ERRLOG.create_error_log (dml_table_name => 'PLCH_EMPLOYEES…

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):