Wednesday, January 18, 2017

Players for PL/SQL Challenge Championship for 2016

The following players will be invited to participate in the PL/SQL Challenge Championship for 2016, currently scheduled to take place on 23 March at 14:00 UTC.

The number in parentheses after their names are the number of championships in which they have already participated.

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

Name Rank Qualification Country
SteliosVlasopoulos (13) 1 Top 50 Belgium
siimkask (16) 2 Top 50 Estonia
mentzel.iudith (16) 3 Top 50 Israel
li_bao (4) 4 Top 50 Russia
James Su (11) 5 Top 50 Canada
ivan_blanarik (10) 6 Top 50 Slovakia
NielsHecker (17) 7 Top 50 Germany
Rakesh Dadhich (8) 8 Top 50 India
Karel_Prech (6) 9 Top 50 No Country Set
Marek Sobierajski (1) 10 Top 50 Poland
Rytis Budreika (4) 11 Top 50 Lithuania
_tiki_4_ (9) 12 Top 50 Germany
krzysioh (5) 13 Top 50 Poland
Chad Lee (13) 14 Top 50 United States
João Borges Barreto (6) 15 Top 50 Portugal
Andrey Zaytsev (5) 16 Top 50 Russia
coba (1) 17 Top 50 Netherlands
patch72 (3) 18 Top 50 Netherlands
Kuvardin Evgeniy (2) 19 Top 50 Russia
VictorD (3) 20 Top 50 Russia
Vyacheslav Stepanov (15) 21 Top 50 No Country Set
Maxim Borunov (3) 22 Top 50 Russia
tonyC (2) 23 Top 50 United Kingdom
JustinCave (13) 24 Top 50 United States
Chase (2) 25 Top 50 Canada
Joaquin_Gonzalez (10) 26 Top 50 Spain
Pavel_Noga (4) 27 Top 50 Czech Republic
seanm95 (3) 28 Top 50 United States
syukhno (0) 29 Top 50 Ukraine
tonywinn (5) 30 Top 50 Australia
JasonC (1) 31 Top 50 United Kingdom
Andrii Dorofeiev (0) 32 Top 50 Ukraine
Sachi (1) 33 Top 50 India
ratte2k4 (0) 34 Top 50 Germany
Alexey Ponomarenko (1) 35 Top 50 No Country Set
PZOL (2) 36 Top 50 Hungary
Otto Palenicek (0) 37 Top 50 Germany
Jānis Baiža (10) 38 Top 50 Latvia
JeroenR (10) 39 Top 50 Netherlands
Rimantas Adomauskas (3) 40 Top 50 Lithuania
Henry_A (3) 41 Top 50 Czech Republic
Sherry (2) 42 Top 50 Czech Republic
ted (0) 43 Top 50 United Kingdom
MarkM. (0) 44 Top 50 Germany
YuanT (11) 45 Top 50 United States
kbentley1 (1) 46 Top 50 United States
swesley_perth (2) 47 Top 50 Australia
Talebian (3) 48 Top 50 Netherlands
mcelaya (1) 49 Top 50 Spain
berkeso (0) 50 Top 50 Hungary

Emulating a finally clause in PL/SQL

PL/SQL does not support a finally clause, as many other languages do, including Java. Here's a description of the finally block from the Java SE doc:
The finally block always executes when the try block exits. This ensures that the finally block is executed even if an unexpected exception occurs. But finally is useful for more than just exception handling — it allows the programmer to avoid having cleanup code accidentally bypassed by a return, continue, or break. Putting cleanup code in a finally block is always a good practice, even when no exceptions are anticipated.
The first thing to say regarding PL/SQL and finally is that the need for it in PL/SQL is likely less critical than in other languages, precisely because the PL/SQL runtime engine (and the underlying Oracle Database engine) does most of the clean up for you.

Any variables you declare, cursors you open, types you define inside a block are automatically cleaned up (memory released) when that block terminates.

Still, there are exceptions to this rule, including:

>> Changes to tables are not automatically rolled back or committed when a block terminates.

If you include an autonomous transaction pragma in your block, PL/SQL will "insist" (raise an exception at runtime) if you do not  rollback or commit, but that's different.

>> Elements declared at the package level have session scope. They will not be automatically cleaned up when a block in which they are used terminates.

Here's a very simple demonstration of that fact. I declare a cursor at the package level, open it inside a block, "forget" to close it, and then try to open it again in another block:

   CURSOR emps_cur
      SELECT *
        FROM employees;
END serial_package;

   OPEN serial_package.emps_cur;

   OPEN serial_package.emps_cur;

   OPEN serial_package.emps_cur;

ORA-06511: PL/SQL: cursor already open
ORA-06512: at "STEVEN.SERIAL_PACKAGE", line 5
ORA-06512: at line 2

Try it out yourself in LiveSQL.

Since there is no finally clause, you have to take care of things yourself. The best way to do this - and I am not claiming it is optimal - is to create a nested cleanup procedure and invoke that as needed.

Here we go - no more error when I attempt to open the cursor the second time.

   CURSOR emps_cur
      SELECT *
        FROM employees;
END serial_package;

   PROCEDURE cleanup
      /* If called from exception section log the error */
      IF SQLCODE <> 0
         /* Uses open source Logger utility:
         logger.log_error ('use_packaged_cursor');
      END IF;
      IF serial_package.emps_cur%ISOPEN
         CLOSE serial_package.emps_cur;
      END IF;
   END cleanup;
   OPEN serial_package.emps_cur;
      /* Clean up but do not re-raise (just to show that you might want
         different behaviors for different exceptions). */


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

(also available in LiveSQL)

Now, I am not, repeat NOT, claiming that this is as good as having a finally clause. I am just saying: this is how you can (have to) achieve a similar effect.

Tuesday, January 10, 2017

Rely on the Wisdom of Friends and Strangers - Ask for Help!

[originally published as part of my ODTUG Confessions of a Quick and Dirty Programmer column]

Surely the best way to write a ton of quick and dirty code, and never, ever stop writing such code, is to be firmly convinced that you know all there is to know about something/anything, and stop learning from others.

Consider me - and PL/SQL.

There seems to be an impression among PL/SQL developers "out there" that I know everything there is to know about the PL/SQL language and (even more amazing) Oracle technology more generally.

Ha, I say, ha!

Certainly, I know more about this language than almost every other human being on the planet. Yet I am reminded, in just about every training and presentation I do, of how much I don't know and how much I can learn from others. For, when it comes to programming, the gap between "book knowledge" and "experience knowledge" is vast. It's one thing to know about syntax and features; it is quite another thing to know how to apply those features.

Accepting the limitations of experience, though, is just one part of the escape from smug belief in one's own "know-it-all-ness." Another key ingredient to developing a deep humility towards one's specialty is visibility. The more one's peers see, read and try to use your code, the more weaknesses are exposed in that code and your expertise.

Of late, the most intense and, well, challenging reminder of my limitations is the PL/SQL Challenge.

I decided back in 2010 to build a database of quiz questions on PL/SQL, and then create a website around that database to offer my daily quiz. Gosh, that doesn't sound so difficult, does it? And certainly the easiest part of that would be writing the quizzes. Surely I know how to do that well!
Or not, as the case may be.

Well, I wrote over 600 questions, and then I worked with my friends at then-Sumneva (Dimitri Gielis, John Scott and Paul Broughton) to design and build the website. I was also assisted beyond easy description by Finn Ellebaek Nielsen, one of the smartest and most knowledgeable Oracle technologists I have ever met.

We launched the site in April 2010 and since then I have been taught by PL/SQL Challenge players just how difficult it is to write unambiguous and 100% accurate quizzes. All too often, players have delved into the nooks and crannies around my questions and answers -  and discovered outright errors; exceptions to the "rule" (that I state or assume in my question); features or behavior unknown to me; assumptions I have been making about PL/SQL that are wrong.

Some players have commented on how addictive the PL/SQL Challenge - I sometimes wonder if they are not getting addicting to proving me fallible!

This widespread laser-like focus on what I am doing has had a noticeable impact. Since I could not afford to allow mistakes to continue to creep into the quiz process, I instituted changes in process as follows:

1. Complete a checklist for every quiz. I no longer have to remember all the key steps in my head (such as "Never use words like never and always. Absolutes are very dangerous.").

2. Generate a verification script from the question and choices, to validate all code examples and claims made in the answers. After all, if it's not tested, you don't know for sure that it is correct.

3. Have at least one PL/SQL developer (not anadmin and not someone who plays the daily quiz) review each quiz.

Does that sound like common sense? Absolutely. Does that sound exactly like what we should all be doing with our application code? Absolutely.

So in this case, I not only learned directly from others (players in the PL/SQL Challenge), but I also learned from this "other" scenario (publishing quizzes). That is, the steps required to ensure high quality quizzes are (some of) the same steps I should be taking to ensure high quality application code.

Learn From Others Even If They Are Not Programmers

Programmers are like any other defined group of human beings. We have our own language (mostly acryonyms), our own diet (Diet Coke and chips), our own forms of entertainment ("Wow, what a cool algorithm!"). That's all well and good, as long as we don't develop the attitude that we have nothing to learn from non-programmers. This attitude of "exceptionalism" (we are different - with an unspoken implication of  "better" - than others; they have nothing to teach us) is both arrogant and self-defeating.

Last year, we decided to go "off the grid" by installing a photovoltaic array of solar panels on the roof of our house, and attaching that array to a whole bunch of batteries. I got a very reasonable quote from a person who claimed to know what he was doing. And he did it - he installed 12 180kw panels and 24 batteries, hooked it all together, flipped the switch - and we had electricity from the sun. Here's a picture of the inside of the PV "shed" in which the batteries were located:

So we were happy. We had power from the sun. A few weeks later, another electrician (Noel) came to the house to install some light fixtures. He asked to see what the first electrician (Jose) had done, since he also does this work.

He went into the PV shed - and came out angry. He felt that Jose had done some very sloppy and dangerous work. He said that the installation did not follow code, and he pointed out that this fuse:

said on it "Audiophone." It was a fuse for a car stereo system, definitely not the kind of fuse needed to handle multiple kilowatts of energy. I was, to say the least, dismayed. I thought about demanding that Jose fix his earlier work, but realized that Jose clearly didn't know what he was doing. Why would I expect to be able to fix the problem he introduced?

[Hmmm. Now there's an interesting thought to apply to fixing bugs in software!]

Instead, I asked Noel to refactor the existing implementation. And when he was done, the wall of the PV shed looked like this:

Notice how neat and clean it is; all the electrical wires and now enclosed, so they cannot be easily damaged. And everything has a label - in fact, when I open the door of the gray box, I see:

It wasn't until several weeks after Noel had finished his work that I realized that:

1. Jose's approach was a classic "quick and dirty" job that was cheap but impossible to maintain and likely to break or cause problems.

2. Noel's approach represented a "best practices" style in programming: encapsulate or hide the details of the implementation; document the architecture and key features of the system so that everyone (including the original "author") can come back later and understand what is going on.

How delightful to learn that the same principles that drive best practice programming also apply to electrical work! Once you can see this (and surely those same principles apply to many other endeavors), your eyes will be open to so many more opportunities for learning how to improve your code.

You Can't Learn If You Don't Ask

When you are stuck trying to sort out a bug, when you are not sure about how to solve a problem or implement an algorithm, when you wonder if there might be a new feature of Oracle that could help you, ask someone for advice or help.

It is much, much harder to learn if you don't ask for help, if you don't actively seek learning opportunities.

If you are one of those unfortunate programmers who mostly codes in isolation (a team of one, or a team of more than one, but with very little interaction), you might not have someone to turn to for help.

In that case, I suggest that when you are stuck on a problem, write yourself an email, explaining the problem and what you see as possible solutions. Simply getting that information out of your head will change the way you look at it and make it easier to find a solution.

The bottom line, though, is that no matter how much we know, or think we know, we can always learn more and different from others. Leverage that fact or you will be stuck in a rather deep rut constructed with your own limitations.

Wednesday, January 4, 2017

12.2: Avoid hard-coding maximum length of VARCHAR2 (and more)

Starting with Oracle Database 12c Release 2 (12.2), we can now use static expressions* where previously only literal constants were allowed. Here are some examples (also available in this LiveSQL script):

   c_max_length constant integer := 32767;
   SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length);

   l_big_string1 VARCHAR2 (pkg.c_max_length) := 'So big....';
   l_big_String2 pkg.maxvarchar2 := 'So big via packaged subtype....';
   l_half_big VARCHAR2 (pkg.c_max_length / 2) := 'So big....';
   DBMS_OUTPUT.PUT_LINE (l_big_string1);
   DBMS_OUTPUT.PUT_LINE (l_big_string2);

As you can see from this code, static expressions can now be used in subtype declarations.

The definition of static expressions is expanded to include all the PL/SQL scalar types and a much wider range of operators. Character operands are restricted to a safe subset of the ASCII character set. Operators whose results depend on any implicit NLS parameter are disallowed

Expanded and generalized expressions have two primary benefits for PL/SQL developers:
  • Programs are much more adaptable to changes in their environment
  • Programs are more compact, clearer, and substantially easier to understand and maintain
* A static expression is an expression whose value can be determined at compile time. This means the expression cannot include character comparisons, variables, or function invocations. An expression is static if it is any of the following:
  • the NULL literal
  • a character, numeric, or boolean literal
  • a reference to a static constant
  • a reference to a conditional compilation variable begun with $$ 
  • an operator is allowed in static expressions, if all of its operands are static, and if the operator does not raise an exception when it is evaluated on those operands
Read more in the doc

Tuesday, January 3, 2017

Learn to hate repetition - a lesson from APEX LOVs

If I was forced to pick just one piece of advice any developer should follow when writing code it would be: 

Avoid Repetition!
a.k.a, DRY - Don't Repeat Yourself
a.k.a., SPOD - Single Point of Definition

When the same code (business rule, formula, "magic" value, SQL statement, etc.) appears in more than one place, you create opportunities for bugs to creep into your code. How? You fix the problem in one place, but what about all the other places?

Here's an example of repetition in the PL/SQL Challenge APEX application and how I was able to get rid of the redundancy. 

I needed to add an LOV (list of values) to an item. I soon discovered that we had three LOVs already defined that were very similar to what I needed:

Now, just looking at the names of those LOVs made me shudder. They differ only by what appears to be the page number on which they are used. That didn't make much sense to me. So I drilled down in an attempt to gain clarify, and found that I was, indeed, correct. The only differences between these LOVs was the use of a different page item in the WHERE clause.

I had been using APEX for well over a year now by this time, but I still consider myself a novice and certainly lack an understanding of many of the nuances and limitations of the tool. Still, my quick glance at this situation had me thinking as follows:

1. It really would be better if page-specific item references were not stuck inside a named LOV. These LOVs do not "live" inside a single page and can, theoretically, be used across an entire application.

2. Surely there's got to be a way to generalize the query so that I can have a single LOV that can be used in all these locations.

So I clicked on the link in one of the LOVs to get some help and found:

Well, gee, that looks like a very useful approach. So I created a new LOV that does not contain the page number in its name, and does not contain a hard-coded page item reference in the query:

Now the only requirement for using this LOV is that the name of the item contain the domain ID is of the form:


where NNN is the page number. So it's not quite completely generic, but it's a lot closer than before, and I was able to replace three LOVs with just one.

Goodbye (the worst of the) hardcoding, goodbye repetition!

And another nice reminder of how easy it is to build and execute dynamic SQL statements via PL/SQL. And you can see here, I changed a static query to a PL/SQL block that returns a string. That string will then be executed by the APEX engine via an EXECUTE IMMEDIATE call.

Note also that even though I concatenate text to put together my where clause, I do not introduce a SQL injection vulnerability. The :app_page_id bind variable is set by APEX itself. And the end result of the concatenation is a string that contains a bind variable.


Oracle Application Express (v5.1 was just released last month!)
Get Rid of Hard Coding in PL/SQL (a Practically Perfect PL/SQL YouTube playlist)

Tuesday, December 20, 2016

PL/SQL Brain Teaser: When is NO_DATA_FOUND not?

Here goes:

I execute this statement:


We all know what that function is going to do, right? #Fail, as one might say on Twitter.

So the brain teaser is: 

In the block below, replace <statement> with a single statement that does not contain any exception handling, but does call the NDF function, so that after the block executes, "NDF? What NDF?" is displayed on the screen.

   n   NUMBER;

I will wait a bit to post my answer. I encourage you to post yours as a comment to this blog post.

Wait for it....


After a couple of days of collecting responses on this post and also in LinkedIn, it's time to publish the answer:

You can replace <statement> with any DML statement that executes the function, and the failure of the function with an unhandled NO_DATA_FOUND exception will not cause the SQL statement to terminate with said exception.

Instead, the SQL engine swallows up that exception and simply returns NULL to the statement.

Why, you might wonder, would the SQL engine do this?

NO_DATA_FOUND is, on the one hand, an exception like any other. And on the other hand, it is different, in that the lack of data often does not indicate any kind of actual error, but simply a data condition. And so it was decided that when a function executed within a SQL statement fails with an unhandled NO_DATA_FOUND, that NULL would simply be returned.

You might not like that answer or decision, but there it is.

Now, there is another way to both invoke the NDF function in a single statement and not have the exception terminate the block, as Edwin points out in the comments: Call the function inside a COALESCE function call.

COALESCE offers the very cool feature of not evaluating an expression in its least until it needs to (in contrast, for example, to NVL, which always evaluates the second argument, even if the first argument is not NULL.

Update 12-21: Jeff Kemp (@jeffreykemp) notes on LinkedIn that since the text in the brain teaser says "call the NDF function", COALESCE is not a valid answer, since you never call the function. Strictly speaking he is correct.