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

August 2018 update: please do feel encourage 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!

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

Table Functions, Part 1: Introduction and Exploration

Table Functions, Part 2: Returning complex (non-scalar) datasets

Table Functions, Part 3a: table functions as parameterized views in the PL/SQL Challenge website

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

Table Functions, Part 4: Streaming table functions

Table Functions, Part 5a: An introduction to pipelined table functions