Friday, May 15, 2015

Shameless, I know. But it feels so good...

We all like to get patted on the back, stroked, egos fed, right?

I am no exception.

And the very best kind of stroking for me is when a developer tells me things like:

"Your book changed my life."

"That code you posted on your blog saved me a whole day's work."

and so on. Honestly, I don't think the endorphin flow is from some sort of personal pride. It's more that the enormous chunk of my life that I have devoted to PL/SQL can help other individuals improve their quality of life and personal happiness in some way (I am much less enamored with helping the bottom line of corporations).

In that vein, this morning, I received the following wonderful email:

Subject: Error trapping - THANK YOU

Steven,

Your Oracle article about error trapping in PL/SQL saved my bacon this morning. I woke up to a production error with commercial software that we wrote. 

I had already implemented many aspects of your recommendations. When I started troubleshooting at 0600, I had tools and plans in hand (the solution didn't come for 2 hours) BUT I had tools and plans!!!!

I debated posting my efforts publicly because I simply parroted your work. 

After the firefight this morning, I recognized that echoing your words can only help the cause.

Again, Thank you!!!!


Ah....it doesn't get better than that. Well, there is something better than that: spending time with my granddaughter, Loey, who at 3.5 years of age is a true fashionista:




Tuesday, May 12, 2015

Program doing the impossible? Check your assumptions!

Have you ever had one of those days when you run your program and it does the impossible?

As in, you stare at the screen and say: "No way, that's impossible. My program couldn't have done that. Couldn't be doing that."

It's a funny thing for a programmer to say, when you think about it.

We are logical people - or at least we are expected to use logic in our jobs.

And that is flat out a most illogical statement - contradictory on the face of it.

Because it wasn't impossible. Obviously. It just happened.

So what's a programmer to do?

Check your assumptions!

You must have made an assumption that is not valid. Assumed that data was in the table that was not - or vice versa. Assumed that you saved a change to your program that you did not. Assumed that you recompiled your program, when you had not. Etc., etc.

Here's an embarrassing true story from my own long, sordid history of programs doing the impossible due to my stubborn refusal to go back and revisit my assumptions.

Hopefully you've heard of the PL/SQL Challenge, a website offering weekly quizzes on SQL, PL/SQL, Database Design and Logic.

I built the website with lots of help from John Scott, Dimitri Gielis and Paul Broughton (all with APEX Evangelists back then). We planned to launch the site on 1 April 2010. And I promoted the site heavily. There was lots of excitement in the Oracle developer community.

New project by Steven! Let's check it out!

We started the site with a daily PL/SQL quiz (that's right - a daily quiz, which went on for four years. Lots of quizzes!), and each day started one second after midnight UTC time (formerly known as Greenwich Mean Time). So John and Dimitri and Finn (my co-founder of the PL/SQL Challenge) were waiting at their machines at midnight. It was 6 PM in Chicago. The second hand swept past 12 and the PL/SQL Challenge was born!

Hundreds of eager developers clicked the Start button to take the quiz - and the entire website froze. Like glacial ice. Sweat broke out on my forehead. I was frantic. Skype messages and calls flew furiously back and forth across the Atlantic.

What the heck was going on? We checked this, we checked that. We agonized. For hours.

And how could it be that with all these Oracle experts on hand, especially the amazing Steven Feuerstein, that we couldn't sort out the problem?

Well, the main problem, it turned out, is that John and Dimitri and Finn all trusted me to not be an idiot.

And at 4 AM, I was able to confirm that I was an idiot. Because I had issued a command to insert a row into a table, a row that was necessary for the site to work properly.

But I had not issued a commit.

I executed "COMMIT;" and the website unfroze. Bottlenecks disappeared. Quizzes could be played. And life did go on. The PL/SQL Challenge went on to attract thousands of developers, with between 500 and 1200 people playing each day for four years.

[We've since "settled down" to an array of 10+ weekly quizzes.]

And looking back on it, it's hard to believe that we couldn't diagnose the problem more quickly. Hard to believe that I could sit there insisting that I'd done everything I needed to do. And that there was no possibility of a data error.

Hard to believe that it happened, except that it did.

And it was a great reminder that when we are under lots of pressure, we don't always think as clearly as we could and should.

So what's a developer to do?

Make a checklist of all the assumptions you have made, and then go over those assumptions: True? Valid?

And once you've gone through that list, without getting to a resolution, ask yourself: what other assumptions am I making that I don't even realize I am making?

Hey, who ever said that programming was easy?

Monday, May 11, 2015

Table Functions, Part 3b: implementing table functions for PL/SQL Challenge reports

So the public-facing side of my function is done. Time to dive inside! Of course, as with almost any piece of code, most of the details are very application-specific and not all that critical to the lesson at hand. So I will take liberties will code to focus in on the most relevant aspects, but I will also use this opportunity to suggest some ideas about how best to build such functions to maximize readability and make it easy to maintain.

First, the declaration section; what you see here are the usual constants (mostly, to execute "setup" functionality once and not repeat it) and variables. But then you see a number of nested subprograms. In case you were not aware, you can define functions and procedures within other functions and procedures.

For a table function that constructs a complex dynamic query, it was crucial for me to hide various construction steps behind a function interface to make it easier to see how the pieces fit together.

   FUNCTION ir_other_ranking_tf (
      category_in         IN VARCHAR2,
      period_type_in      IN VARCHAR2,
      competition_id_in   IN INTEGER,
      period_in           IN VARCHAR2)
      RETURN ir_other_ranking_nt
   IS
      c_competition    CONSTANT qdb_competitions%ROWTYPE
         := qdb_competition_mgr.one_competition (
               competition_id_in) ;

      c_freq_column    CONSTANT VARCHAR2 (100)
         := CASE period_type_in
               WHEN qdb_config.c_daily THEN 'YYYY_DDD'
               WHEN qdb_config.c_weekly THEN 'YYYY_IW'
               WHEN qdb_config.c_monthly THEN 'YYYY_MM'
               WHEN qdb_config.c_quarterly THEN 'YYYY_Q'
               WHEN qdb_config.c_yearly THEN 'YYYY'
            END ;
      c_select_list1   CONSTANT qdb_config.maxvarchar2
         := ',
            "Player Count" ,
            NVL (top20.top20, 0) ,
            NVL (top100.top100, 0) ,
            "Score" ,
            "Weighted Score" ,
            "Pct Correct" ,
             qdb_utilities.seconds_to_duration (answer_time, 0),
            "Max Score" ,
            "Max Weighted Score" ,
            "Quizzes Taken" ,
            "% Taken" ,
            "Score per Quiz" ,
            RANK () OVER (
              ORDER BY "Weighted Score" DESC, answer_time ASC) ,
            RANK () OVER (
              ORDER BY "Score per Quiz" DESC, answer_time ASC))' ;

      c_select_list2   CONSTANT qdb_config.maxvarchar2
         := ',
          COUNT (*) "Player Count" ,
          SUM (user_score) "Score" ,
          SUM (user_wscore) "Weighted Score" ,
          ROUND (SUM (pct_correct_answers) / 
             COUNT (*), 1) "Pct Correct" ,
          SUM (user_seconds) answer_time ,
          SUM (best_possible_score) "Max Score" ,
          SUM (best_possible_wscore) "Max Weighted Score" ,
          SUM (total_quizzes) "Quizzes Taken" ,
          ROUND (SUM (total_quizzes) / 
             SUM (potential_quizzes) * 100) "% Taken" ,
          ROUND (SUM (user_wscore)   / 
             SUM (total_quizzes)) "Score per Quiz"' ;

      l_query                   qdb_config.maxvarchar2;
      l_return                  ir_other_ranking_nt;

      PROCEDURE trc (context_in   IN VARCHAR2,
                     text_in      IN VARCHAR2)
      IS
      BEGIN
         qdb_utilities.trace_activity_force (
            'ir_other_ranking_tf ' || context_in,
            text_in);
      END;

      FUNCTION period_qualifier (if_not_lifetime_in IN VARCHAR2)
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN CASE period_type_in
                   WHEN qdb_config.c_lifetime THEN NULL
                   ELSE if_not_lifetime_in
                END;
      END;

      FUNCTION period_qualifier_col (
         add_comma_in   IN BOOLEAN DEFAULT TRUE)
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN period_qualifier (
                      'mv.'
                   || c_freq_column
                   || CASE
                         WHEN add_comma_in THEN ','
                         ELSE NULL
                      END);
      END;

      FUNCTION detail_tables
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN CASE category_in
           WHEN 'organization_name'
           THEN
              ' qdb_memberships mb, qdb_organizations cat '
           ELSE
              ' qdb_users cat'
           END;
      END;

      FUNCTION detail_where
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN    CASE period_type_in
                      WHEN qdb_config.c_lifetime
                      THEN
                         NULL
                      ELSE
                            period_qualifier_col (
                               add_comma_in   => FALSE)
                         || ' = '''
                         || period_in
                         || ''' AND '
                   END
                || CASE category_in
                      WHEN 'organization_name'
                      THEN
                         ' mv.user_id = mb.user_id AND
                             mb.organization_id = 
                             cat.organization_id '
                      ELSE
                         ' mv.user_id = cat.user_id '
                   END;

      END;

And now it's time to construct the query. Note that I include tracing both before and after query construction. I imagine it will be pretty obvious why: executing a dynamically-constructed statement isn't hard (I show that next). Putting together something as complicated as the query needed for this report is, on the other hand, a real treat; what's the change I will get it correct the first thirty or so iterations? Being able to see the string I put together is a big help.

I don't think it makes a whole lot of sense to go through the query construction in detail. The bottom line is that I was dealing with different materialized views (so: different name of view), and to some extent different names for columns in each view. I used the constants and nested functions (shown above) to avoid repetition and improve readability (perhaps just a little bit, anyway).


   BEGIN
      IF qdb_utilities.trace_is_on
      THEN
         trc (
            ' period_type, competition, c_freq_column, period',
               period_type_in
            || '-'
            || competition_id_in
            || '-'
            || c_freq_column
            || '-'
            || period_in);
      END IF;

      l_query :=
            'SELECT ir_other_ranking_ot('
         || CASE period_type_in
               WHEN qdb_config.c_lifetime THEN '''Lifetime'''
               ELSE 'mv.' || c_freq_column
            END
         || ', mv.'
         || category_in
         || c_select_list1
         || ' FROM (SELECT '
         || CASE period_type_in
               WHEN qdb_config.c_lifetime THEN NULL
               ELSE 'mv.' || c_freq_column || ','
            END
         || 'cat.'
         || category_in
         || c_select_list2
         || ' FROM mv_qdb_rankings_'
         || period_type_in
         || ' mv,'
         || detail_tables ()
         || ' WHERE competition_id = :competition_id_in AND '
         || CASE category_in
               WHEN 'company_name'
               THEN
                  ' cat.company_name IS NOT NULL AND '
               ELSE
                  NULL
            END
         || detail_where ()
         || ' GROUP BY '
         || period_qualifier_col ()
         || 'cat.'
         || category_in
         || ') mv , (SELECT '
         || period_qualifier_col ()
         || category_in
         || ',COUNT (*) top100 FROM mv_qdb_rankings_'
         || period_type_in
         || ' mv,'
         || detail_tables ()
         || ' WHERE overall_rank <= 100 AND '
         || detail_where ()
         || ' AND competition_id  = :competition_id_in GROUP BY '
         || period_qualifier_col ()
         || category_in
         || ') top100 , (SELECT '
         || period_qualifier_col ()
         || category_in
         || ', COUNT (*) top20 FROM mv_qdb_rankings_'
         || period_type_in
         || ' mv, '
         || detail_tables ()
         || '  WHERE overall_rank <= 20 AND '
         || detail_where ()
         || ' AND competition_id  = :competition_id_in GROUP BY '
         || period_qualifier_col ()
         || category_in
         || ') top20 WHERE top100.'
         || category_in
         || '(+) = mv.'
         || category_in
         || ' AND top20.'
         || category_in
         || '(+) = mv.'
         || category_in
         || period_qualifier (
                  ' AND top100.'
               || c_freq_column
               || '(+) = mv.'
               || c_freq_column
               || ' AND top20.'
               || c_freq_column
               || '(+) = mv.'
               || c_freq_column);

      IF qdb_utilities.trace_is_on
      THEN
         trc (' Other Ranking query', l_query);
      END IF;

OK, and now it's time to execute the query, collect the result set with a BULK COLLECT clause, and then send it out to the invoking SELECT.

And, of course, if something goes wrong, I send the value of l_query to the error log, so I can eyeball it and - hopefully - quickly identify the dumb mistake I made (left out a comma, didn't leave a space between keywords in the query, etc.)

      EXECUTE IMMEDIATE l_query
         BULK COLLECT INTO l_return
         USING competition_id_in,
               competition_id_in,
               competition_id_in;

      RETURN l_return;
   EXCEPTION
      WHEN OTHERS
      THEN
         qdb_utilities.syslog_error (
            'ir_other_ranking_tf',
            'Failure executing dynamic query',
            l_query); 
         RAISE;
   END;

So now you believe me, right? The implementation of a table function is....application-dependent. You are unlikely to build something exactly like this, but hopefully the approach I took (declaring lots of constants and nested subprograms to isolate repeated logic and improve readability) will offer you ideas on optimizing your own table function implementation.

Now I must finish up my four hour workshop for the GLOC conference next week.

And write another quiz for the PL/SQL Challenge.

And start writing the first draft of my Project Solver Call to Action.

:-)

Links to Table Function Series

Tip for excellent presentations: don't be afraid to appear ridiculous!

End of last week, I recorded a series of videos offering tips for giving excellent presentations. I will, of course, let you all know when they are posted on YouTube.

But one of those tips is:

Don't be afraid to take some risks!

And I talk specifically about using humor in our presentations, while also making sure that the humor is directed at oneself or one's product/technology. NEVER MAKE FUN OF YOUR AUDIENCE.

I also suggested, in that same vein, that you should not be afraid to appear ridiculous, silly, whatever. People learn from other people, and the less you are like a robot, the more people will like to listen to and learn from you.

To prove beyond a doubt that I really, truly mean that, on my most recent two minute tech tip with Bob Rubhart of OTN, I decided to sing, live, the lyrics to PLSQLville, sung to the tune of Jimmy Buffet's Margaritaville.

Enjoy!


Friday, May 1, 2015

Best laid plans or why no new post on table functions this week

I was doing so well: 1 post in my series on table functions each week.

I planned to complete a description of the table function I used in the PL/SQL Challenge rankings report, which I began here.

But then, well, things got in the way. Things like:

  • Write my next article for Oracle Magazine, only one week past due
  • Write five daily tips on PL/SQL via @sfonplsql
  • Produce two new quizzes for the PL/SQL Challenge.
  • Record a video for ODTUG to promote the YesSQL! Day at Kscope15
  • Record a "train the trainer" video to pass on some tips on how to give excellent (or at least highly entertaining) presentations.
  • Donate a small bucket of white cells and plasma to LifeSource.
  • Waaay too many conference call/meetings and waaaaaay too much email.
  • Finish up work on a root canal. Fun!
  • Cut down a bunch of invasive buckthorn (I so love that the sun sets so late in the day now!).
  • Help design and test some exciting new features for Oracle Learning Library
So my apologies, dear readers. Next week. Next week I am certain, I pledge, I promise, I will share you with my amazing table function, filled with a really complicated dynamic SQL query.

In the meantime, have a great weekend!