Skip to main content

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

Comments

  1. Steven, came across this post and noticed the seemingly duplicate declaration of c_select_list1. Whilst the declaration can be 'overloaded' as soon as you reference this it should throw a PLS-00371.

    I am probably lacking caffeine and fail to spot the obvious as I am just reading the code rather than compiling it....

    ReplyDelete
  2. Thanks, Chris, that is definitely a typo. Not sure how it crept in. Will fix now!

    ReplyDelete
  3. Hi Steven
    I have to say, this is a terrible way to try to teach, explain and illustrate a concept (here, table functions as parameterized views). It's something that is done time-to-time by other folks on t'interweb but it's the first time I've seen it done by your good self.
    There is so much code in your example that the reader, unless a genius (perhaps like your good self or someone like Jonathan Lewis) is left struggling to discern or distill what the key points are.
    I should say, for fear of inciting hatred, that I love your PL/SQL tutorials; this is the only one that I can remember in all the years that I've come across them that I'm left thinking, 'WT....' I think this is reflected in the lack of comments on this article.
    Best regards
    J

    ReplyDelete
    Replies
    1. Thanks for writing, Jon (and sorry about the very delayed response).

      My only excuse is that I was showing code from a "real world example" - and they can be quite complicated.

      I will update this post to direct readers to my new(er) class at the Dev Gym on table functions, which includes a set of tutorials in LiveSQL. I would very much appreciate it if you would check it out and let me know if they provide an easier, smoother way in to this subject.

      Delete

Post a Comment

Popular posts from this blog

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 p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel