Skip to main content


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…
Recent posts

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…

Tips on Writing PL/SQL in APEX Apps

On December 3 for our AskTOM Office Hours session, three deeply experienced and highly respected APEX prosshared their wisdom on how best to write and manage PL/SQL code for Oracle Application Express projects.

You can watch the recording and follow links to related resources here.

Our experts were:
Karen Cannell Karen promotes APEX topics and best practices through local, regional and national user group presentations and papers. A devoted user group volunteer since 2007, she is especially active in ODTUG, where she serves as the editor of the ODTUG Technical Journal. She is former Associate Editor of IOUG SELECT Magazine. She is co-author of Agile Oracle Application Express, Expert Oracle Application Express and Beginning Oracle Application Express 4. Her most recent presentation at Kscope15 focused on APEX Interactive Reports with a Deep Dive: APEX 5 New Features and Upgrade Cheat Sheet.

Karen has delivered Application Express solutions since its Web DB and HTMLDB beginnings, and c…

PL/SQL Puzzle: No extra code please!

I published yet another PL/SQL puzzle on Twitter yesterday. Generated lots of interest and interesting replies. I don't think any single person caught everything, but as usual the community came through.

I will repeat the puzzle here. If you haven't already seen it on Twitter, please try to solve it yourself before looking at my answer.
What text can be removed from lines 3 though 12 in the code below so that after the anonymous block is executed, "121212" is still displayed on the screen?

White space

so you do not immediately

see my answers. 


OK, let's dive in.

Notice, first of all, that I asked about text that can be removed, not lines. So you can remove entire lines or portions of lines. I refuse to accept that whitespace is text, so blank lines don't count. :-)

Here are the opportunities for removal that I found:

4 - Remove the IN keyword. That's the default for parameters (though I generally always include it in my code).

5 - Remove AUTHID DEFINER. …

PL/SQL Puzzle: What code can be removed?

I published a PL/SQL puzzle on Twitter on November 6 2019. I asked the following question:
Which lines of code can be removed (either entirely or in part) from the block below and not affect the output of the program in any way? I neglected to mention in my original tweet a few important assumptions:
You are running this code on Oracle Database 10g or higher.Server output is turned on.Whitespace (spaces, tabs, new-lines) don't count. Here's the code. I will publish it as an image, just as I did on Twitter, so that you can give it a go yourself, before taking a look at the answers from me and others below that.
Check out the Twitter conversation for all the answers that were submitted. It's a fun read!

Here are the full lines that I believe can be removed:

2 - There is not need to declare the iterator used in a FOR loop, numeric or cursor versions.

7 - There is no need to declare an "empty" collection to be used to initialize l_objects.

10 - Collections are empty a…

PL/SQL Puzzle: Add one statement to stop exceptions

OK, folks, here's a PL/SQL puzzle. It was originally posted on Twitter. I give you the link to that thread at the end of this post. But first....don't you want to try to solve the puzzle yourself? :-)

Please note that the solutions to the puzzle have absolutely NOTHING to do with writing good code. They are simply exercising various features of the PL/SQL language.
The puzzle Can you come up with just ONE STATEMENT to add to plsqlpuzzle_proc so that it can execute without terminating with an unhandled exception? Use this LiveSQL script as a starting point for your attempted solutions.
CREATE TABLE plsqlpuzzle (n NUMBER) / CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc IS r plsqlpuzzle%ROWTYPE; TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE INDEX BY PLS_INTEGER; t r_t; BEGIN SELECT * INTO r FROM plsqlpuzzle; DBMS_OUTPUT.put_line (r.n); DBMS_OUTPUT.put_line (t (1).n); END; / BEGIN plsqlpuzzle_proc; END; / ORA-01403: no data found I …