Skip to main content

RTFM? KISS? Comment? Whatever, just get the code to work right!

We've been hitting a snag of late with new registrants at the PL/SQL Challenge. As with many sites, to ensure that a person's email is not being hijacked, we send an email with a verification URL.

It was working for quite a while, but then we noticed players reporting a bug:

On clicking the verification link am getting an error message like:
The PL/SQL Challenge website is temporarily unavailable.
Please try again later or join the PL/SQL Challenge twitter group:
Sorry for the inconvenience.

Ugh. Well, the site is certainly available. So what's going on? Turns out the verification URL is missing the all-important "/pls/":


should be this:,P34_CODE:...

Looked into the code and found:

   FUNCTION apex_website_url RETURN VARCHAR2
              WHEN qdb_config.prod_state THEN APEX_UTIL.HOST_URL 
              ELSE website_url ()
              || '/apex/';

Now, a few things immediately come to mind: 
  • Huh? Why the CASE statement? Why distinguish between PROD and another instance of the app? Or to put it another way: please document your code!
  • Glad to have that built-in (APEX_UTIL.HOST_URL), but what does it do, really?
  • Does it need to be this complicated?
  • Oh, and where is it used?
Let's take that last item first. Before changing any code currently in production use (or even still in dev, but potentially in use across the app), it is so important to find out where that code is used, and how.

So I did that. I found two "hits" - one that is used to generate the verification URL and a second used in a program that sends out emails with the previous week's quiz results. 

But I took the analysis a little bit further and found that the second usage was "vestigial". Probably did use it at some point, but currently the function is called and assigned to a variable, but that variable is not used.

OK, add that to my clean-up list.

And, very nicely, I now know that this function is only used in the buggy scenario I am trying to fix. That gives me much more latitude to make changes.

So back to the apex_website_url. Since I am relying on a built-in, I suppose it might now hurt to actually look at the documentation. Where I find the following:

Well, woudja look at that....HOST_URL takes a parameter! And one value for that parameter is "SCRIPT" and if I use that, it automatically appends /pls/apex.

Confession: when I saw this I felt dumb. Or maybe it made me feel once again (happens pretty often) like the "closet amateur programmer" I believe myself to be (never studied computer science or algorithms or just about anything CS related in college). 

So I thought to myself: maybe, just maybe, I could change my function to nothing more than:

   FUNCTION apex_website_url RETURN VARCHAR2

I made the change. I tested the change. It worked!

THEN I moved it into production - and immediately tested again. Still worked. (ha! Who's the amateur now?). 

Another day, another problem (of our own making) solved.

Just one mystery remains: why did the verification email STOP working in mid September? Perhaps I will report back to you on that later. :-) 

This little episode was such a great reminder of some of the most important pieces of advice I can give to myself:

1. Read the documentation. Don't assume. Make sure you fully leverage all that the software vendor has done for you.

2. Keep your code as simple as possible. 

3. If for some reason you have to make it complicated, explain that complication with a comment.


  1. Hello Steven,

    Reading this post made me feel happy twice ( to just borrow your feeling from the previous post on this blog :):) )

    1. First of all, you are recommending to Read the Documentation .

    This is my "ALL THE TIMES" credo when I am doing anything,
    since I started working with a computer :) :)

    The downside is, however, that this is maybe the MAIN or even ONLY reason that
    up to this day I don't know APEX :( :(

    The documentation is HUGE, and I should READ IT before typing the first
    letter or opening the first page ....

    I would call myself "a person that knows APEX" when I will know about
    ALL those built-ins and their parameters :) :) :)

    Don't laugh at me ... this is about what I always did with Oracle Forms
    along all my career ... and it always worked wonders :):)

    By the way, I guess that the addition of that argument was probably
    related to some product version upgrade issue ...
    otherwise it would probably not have worked prior to this date also.

    2. Second, you recommend, AGAIN, using comments to document obscure code ...
    ... though, recently enough, in one of our Roundtable discussion threads I got the
    impression that you do favor self-documenting code ...

    Not adding comments can also be deliberate ... sometimes you want
    to trick others, for "good reasons" .... but, otherwise, for yourself and for the
    "good guys" around, it is better to have them included ...

    Iudith Mentzel

  2. Thanks, Iudith. I do like to keep my comments to a minimum, but that is ONLY justified if the code without comments is self-explanatory. Otherwise, yes, comment away! The problem with that guideline - and it is a problem that comes deep from within our brains and how we think - is that once we sort out what the code needs to be, everything seems so obvious. Comments seem superfluous. Perhaps the best way to do this is to set up a short-term feedback loop: Each day, first thing, look at the code you wrote yesterday and see if it's still "obvious". If not, write some comments before you COMPLETELY forget what the heck it is you wrote.


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…