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:
PLSQLChallenge
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/":

This:

http://www.plsqlchallenge.com/apex/f?p=QDB_PROD:34:::::P34_USER,P34_CODE:...

should be this:

http://www.plsqlchallenge.com/pls/apex/f?p=QDB_PROD:34:::::P34_USER,P34_CODE:...

Looked into the code and found:

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

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
   IS
   BEGIN
      RETURN APEX_UTIL.HOST_URL ('SCRIPT');  
   END;

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.





Comments

  1. iudithSeptember 18, 2014 at 10:33 AM

    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 ...

    Cheers,
    Iudith Mentzel

    ReplyDelete
  2. Steven FeuersteinSeptember 18, 2014 at 12:58 PM

    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.

    ReplyDelete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more