Another way to learn something new every day

The PL/SQL ChallengeMy morning routine for the last few weeks has had a small addition – just a few minutes to log on The PL/SQL Challenge at 8am (WST) and answer a quiz question.

This is a great way to test the depth and breadth of your knowledge of Oracle PL/SQL. Whether you’re a beginner in the field or have years of experience, I’m certain you’ll occasionally learn something new – plus, it’s completely free!

Some of the questions are fairly general, others are trivial; while they’re never “trick” questions, they can still trip you up if you’re not observant!

I think I have an unfair advantage, being in this timezone – early morning is when I am most alert and ready to tackle any challenge.

A couple of things I’ve learned recently:

  • UTL_FILE.putf can be used if you want to use C-style “printf” syntax, e.g. '\n'
  • SIGNTYPE is a standard subtype that can only take values of NULL, -1, 0 or 1

(I’ve learned a few other things as well, but I’ve forgotten what they were :|)

Two sides of the same fence?

In my daily reading these two articles came fairly close together, and I have a strong feeling they are describing exactly the same project. *

From one side of the fence: A total rewrite: costly, time-consuming, but worth it?

“Our CMS was developed using Active Server Pages, and consisted of around 80,000 lines of VBScript code.”

“as we continued to develop the rest of the framework, we saw that it took a lot longer than anticipated”

From the other side: The Homegrown CMS

“If one were to create a list of adjectives to describe this monstrosity, “stable,” “reliable,” and “accurate” would be strikingly absent.”

“…nearly 200 tables (mostly imported from Microsoft Access) and not a single stored procedure. Boolean-type values were represented as a CHAR(5) field, holding possible values of ‘true’, ‘false’, or NULL…

“There were also no indexes. Nor any primary key columns… For inserts, an ID was assigned via in-line SQL by requesting the Max(ID) field of any particular table and adding 1.”

My point of view: this sounds like a good counter-example to the (very good, but general) advice offered by Joel Spolsky: don’t rewrite from scratch.

* EDIT: Vidar Langberget has advised that these are not the same project, proving that I’m not such a great prognosticator 😐

ORA-01481 Invalid number format model

My code worked perfectly fine in Oracle 9i, but after our upgrade to 11g all of a sudden it was failing with ORA-01481 “Invalid number format model”.

The cause? A mixed-up number format model – I’d accidentally mixed locale-specific (“.“) and locale-safe (“G“) codes in the number format. I don’t know if this is due to a bug in 9i or an enhancement in 11g.

Connected to:
Oracle Database 11g Enterprise Edition Release
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select to_char(12345.678,'99G990.0') from dual;
select to_char(12345.678,'99G990.0') from dual
ERROR at line 1:
ORA-01481: invalid number format model
SQL> select to_char(12345.678,'99,990.0') from dual;
SQL> select to_char(12345.678,'99G990D0') from dual;

“Action: Consult the manual… in retrospect, not of much help in this instance, unfortunately – however, these sorts of problems may usually be worked out by experimentation.

One insert or multiple inserts?

A colleague asked me a trick* question today which I failed 🙂

* whether it was a “trick” question is probably in the eye of the beholder, though…

“What are the differences, if any, between the following two approaches to inserting multiple rows (assume v1 and v2 have different values):

INSERT INTO mytable (a)
INSERT INTO mytable (a)


INSERT INTO mytable (a)

I quickly answered:

  1. The first approach requires one extra parse;
  2. The second approach requires a Sort Distinct (albeit of only two rows)
  3. A UNION ALL would be better, which would not require a Sort, nor would require the extra parse.

My colleague responded, there’s one very important thing I missed: Triggers! The first approach would execute the BEFORE STATEMENT and AFTER STATEMENT triggers once for each row. The second approach would only execute these triggers once.

What’s sad is that the application we’re working on has row-level logic in the BEFORE/AFTER statement triggers. If we try to optimise the code to insert more than one row in one INSERT statement, the application only runs the row-level logic for the first row inserted. Bad code! Very very bad!