Skip to main content


PL/SQL Puzzle: Getting the "right" error message to appear

I posted the following puzzle on Twitter:
What change(s) can you make to this code so that "ORA-00001: unique constraint" appears on the screen after execution? Try it yourself before reading the rest of the post!

White space

so you do not immediately

see my answer. 


Here are the answers from the TwitterSphere:

Change line 5's assignment to dbms_sql.number_table(1=>1,2=>1)

In other words, try to insert the same value twice. Since there is a unique index on the column, that will cause ORA-00001 to be raised.

So that will do it, right?

Wrong. Hans and Dirk both point out why that is not enough, and offer the second part of the solution:

The value deposited in the error_code field of the SQL%BULK_EXCEPTIONS array is unsigned. In other words, 1 rather than -1 is stored. Unfortunately, the SQLERRM function assumes that the error code you pass it will be signed (negatively). So you must multiply the value in the pseudo-collection by -1. Then SQLERRM will return the right …
Recent posts

Oracle Dev Gym PL/SQL Championship for 2019 Players

Throughout 2019, over 1200 Oracle Database developers participated in the Oracle Dev Gym PL/SQL Challenge weekly tournament. The top 50 ranked players shown below will compete for top honors in a championship on February 18, 2020.

The number in parentheses after their names are the number of championships in which they have already participated. As you can see, there are some very dedicated players here!

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

NameRankStelios Vlasopoulos (16)1mentzel.iudith (19)2NielsHecker (20)3Chad Lee (16)4Peterman (6)5siimkask (19)6_tiki_4_ (12)7Chase Mei (5)8Ludovic Szewczyk (4)9li_bao (7)10MarkusId (0)11Andrey Zaytsev (8)12Michal P. (3)13Arjun Barath (0)14Ivan Blanarik (13)15Maxim Borunov (6)16lmikhailov (0)17mcelaya (4)18tonyC (5)19patch72 (6)20Karel_Prech (9)21Sandra99 (3)22Oleksiy Ponomarenko (4)23Mike Tessier (3)24Vyacheslav Stepanov (18)25Jan Šerák (5)26seanm95 (6)27msonkoly (4)28Rakesh Dadhic…

PL/SQL Office Hours: DB Setup and Teardown for Automated Testing

On January 14, 2020 at 9 AM Eastern, I am very pleased to hold a PL/SQL Office Hours session on one of the biggest challenges faced by developers setting up automated tests for database code: setup and teardown.

No application will ever have zero bugs, but you sure want to keep them to a minimum. The best way to do this is to implement automated regression tests of your code, but "best" as usual does not equate to "easiest." Building and managing tests can be a big challenge, so in this Office Hours session, we will hear from developers who are doing just that. Learn from your peers about the obstacles they faced and how they overcame them. Bring your own stories and your questions, and let's all work together on improving our code quality!

For this session, we have two presenters: Deepti Bandari and Jasmin Fluri.

Deepti Bandari is a senior software engineer at Fidelity Investments since 2013. Her focus areas include database design and development, test automa…

PL/SQL Puzzle: when implicit conversions come calling

I posted the following puzzle on Twitter:
A quick little #PLSQL puzzle: I set serveroutput on. After running the code you see in the block below, what will be displayed on the screen? Try it yourself before reading the rest of the post!

White space

so you do not immediately

see my answer. 


The output is:


I expect most of you got the first two right and maybe the third one wrong. Note also that the block does not fail with any kind of exception, such as VALUE_ERROR. The reason for all this can be summed up in one phrase: implicit conversion.

As noted by several people, this is a collection indexed by strings, not integers. Only associative arrays (INDEX BY) types support this. And that makes all the difference in this puzzle.

The value being used in the assignment of 100 to elements in the array is an integer (indx).

Since the index type is a string, the PL/SQL engine implicitly converts integers 1 through 10 yto strings "1", "2" ... "9", and finall…

Dynamic Polymorphism - Why, What, How

Dynamic means "run-time."

Polymorphism means "multiple shapes."

Synonyms for dynamic polymorphism include "runtime polymorphism" and "dynamic method dispatch."

If you are a "traditional" relational database developer, these terms might sound unfamiliar. But how about overloading? Are you familiar with that?

Overloading occurs when you have more than one subprogram (procedure and/or function) with the same name in the declaration section of a block, package specification or package body. These subprograms need to differ by parameter list or type (procedure vs function) in a way that is sufficient for the compiler to distinguish.

Well, guess what? Another name for overloading is "static polymorphism."

Static means "compile-time."

Polymorphism means "multiple shapes."

Why, you might be wondering, does the Oracle Database need to wait till runtime to determine which method in which type in the hierarchy shoul…

Wait, that's NOT a reserved word?

When it comes to PL/SQL puzzles via Twitter, I decided to change things up this week. I presented it as multiple choice this time.

Here's the puzzle:
After executing the code shown in the image what will be displayed on the screen (serveroutput is on!)? a. "No dummy"
b. Unhandled VALUE_ERROR exception
c. Unhandled NO_DATA_FOUND exception
d. Compilation error
e. Your guess is as good as mine.

Before I unveil the amazing, mind-boggling answer....I will give you a moment to try to solve it yourself.
OK. So the first inclination you might have as regards the output from this block is, quite logically, "No dummy!".

After all, there are no rows in the dual table (or any other table for that matter) for which 1 is equal to 2.

So that SELECT-INTO is going to raise a NO_DATA_FOUND exception. No doubt about that at all.

And there's an exception handler for NO_DATA_FOUND (well, no_data_found, wait they are the same thing in PL/SQL! :-) ). So "No dummy" it is…

PL/SQL Puzzle: what assumptions am I making?

Almost certainly, whenever you write a procedure or function, you make certain assumptions. Some of them are quite reasonable, such as "I assume my database is up and running." Some of them are scary, such as "I assume my users will never change their minds."

But many simply go unnoticed. You don't even realize you are making an assumption until it smacks you in face, most likely in production, when an unexpected error exposes the assumption.

So in this PL/SQL puzzle, as I state on Twitter:
The procedure shown below compiles without error. What assumptions am I making so that when it executes, it does not terminate with an exception?

White space

so you do not immediately

see my answers. 


OK, let's dive in. I provide below all of the assumptions I was aware, and also some others that were provided in Twitter on the very active discussion that followed. As usual, I learned something new from the community!

Line 3: by hardcoding the datatype to VARCHAR2(100) w…