Skip to main content

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

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!

Waaaay back in April 2010, I decided (without really consciously deciding) that I wasn't busy enough. What I needed was the responsibility of support a 24x7 website that offered quizzes on SQL, PL/SQL and more. Well, actually, I decided that a while before April 2010, but April 2010 was when we launched the PL/SQL Challenge.

Over 900,000 answers to thousands of quizzes later, I thank my lucky stars that my original co-founder of the site, Finn Ellebaek Nielsen, suggested that perhaps my daily PL/SQL quiz should take a break on Saturday and Sunday (I was pushing for 7 quizzes a week, because I can be well, quite, insane).

In any case, we have had great fun building the site on Oracle Application Express, packed full of PL/SQL APIs. And hundreds of developers have told us how much they have enjoyed and benefited from what I call "active learning" - not just reading a book or watching a video, but challenging oneself to test one's knowledge (and, if you like that sort of thing, competing with others).

Yes, competing. Humans love to compete (some of them, mostly I suppose men). They love to be ranked. They love to win. And we give them that opportunity on the PL/SQL Challenge.

We offer lots of different kinds of rankings: by player, by country, by company, by affiliation (e.g., user group). You can choose the competition, the date range, etc. Because of this wide variety, we decided to utilize APEX's fantastic Interactive Reports feature.

One downside of Interactive Reports, however, is that they do not support a dynamic (constructed at runtime) query. Ranking reports run off of a series of materialized views, such as mv_qdb_rankings_q (quarterly rankings), so in our first pass we ended up with lots of different reports built on views that were built on top of the materialized views:


The report query itself was simple enough:


Still, you had to create a report, format the columns, etc. It was tedious work but somebody had to do it. Oh, not me. No, no. My son, Eli, who was the main APEX developer on the project. :-)

So "we" got the reports done, users used them, users were happy. But then....then it was time to enhance the reports. And then we both stared at those over-two-dozen reports and thought about having to go through each one of them apply the same changes to each one of them....

And suddenly we (with Eli definitely taking the lead here) were sure (desperate) that there had to be a better way, and we'd better figure out what that better way was.

Ideally, we would have just one report that would handle all of the variations we needed, in a way that the users would find easy to use.

Ideally, we would have a single view and just pass "parameters" to it, to alter its behavior and change the result set returned.

Sadly, views do not take parameters. But, wait....functions take parameters. If only we could call a function in the query.....well, we can! We can call a table function!

[Note to those who have never seen me present: those exclamation marks are real. I actually do get all excited about writing cool code in PL/SQL, even after multiple decades of doing so.]

Now, some of you might be thinking: why would you need a table function? Why not just use a series of "mutually exclusive" queries combined with a UNION ALL?

That is certainly a possibility, and in fact we did this for our player (as opposed to company, organizational , etc.) rankings, because the code required to construct the dataset via the table function turned out to be not all that complicated. The mutually exclusive UNION ALL query looks (in part) like this (all the MV-specific elements in red):

 SELECT yyyy,
       qdb_user_mgr.published_name_no_points (u.user_id),
       qdb_user_mgr.published_name (u.user_id),
       u.country,
       u.company_name,
       qdb_utilities.expertise_level (expertise_level_id),
       overall_rank,
       previous_rank,
       rank_improvement,
       overall_percentile,
       ROUND (pct_correct_answers, 1),
       user_score,
       user_wscore,
       qdb_utilities.seconds_to_duration (user_seconds, 0),
       best_possible_score,
       best_possible_wscore,
       total_quizzes,
       ROUND (total_quizzes / potential_quizzes * 100)
  FROM mv_qdb_rankings_y mv, qdb_users u
 WHERE     mv.user_id = u.user_id
       AND mv.competition_id = :p442_competition_id
       AND yyyy = :p442_period
       AND :p442_period_type = 'Y'
UNION ALL
SELECT yyyy_q,
       qdb_user_mgr.published_name_no_points (u.user_id),
       qdb_user_mgr.published_name (u.user_id),
       u.country,
       u.company_name,
       qdb_utilities.expertise_level (expertise_level_id),
       overall_rank,
       previous_rank,
       rank_improvement,
       overall_percentile,
       ROUND (pct_correct_answers, 1),
       user_score,
       user_wscore,
       qdb_utilities.seconds_to_duration (user_seconds, 0),
       best_possible_score,
       best_possible_wscore,
       total_quizzes,
       ROUND (total_quizzes / potential_quizzes * 100)
  FROM mv_qdb_rankings_q mv, qdb_users u
 WHERE     mv.user_id = u.user_id
       AND mv.competition_id = :p442_competition_id
       AND yyyy_q = :p442_period
       AND :p442_period_type = 'Q'

I expect you get the idea from there. It's a long query (well, 143 lines, not all that crazy long), but it's fairly straightforward. No need for a table function.

When it came to the other reports, however, the table function came in very, very handy - because the logic and variations between the reports was more substantial.

In this post, I will explore how we applied table functions in our application, and go as far as the specification of the table function. In my next post, I will dive into the implementation of that function. 

Before looking at specification of the function, allow me to share with you the single report query that replaced sixteen  interactive reports:


Hopefully you can see why we talk about queries based on table functions as a kind of parameterized view. I am passing argument values to the parameters of the function. The function then returns the dataset as rows and columns. So from the standpoint of a developer building the report, we have kept (made) things very simple. All the complexity has been moved inside the function.

So let's look at the function. First, as explained in the 2nd post in this series, if I want to pass back more than a single scalar value from my table function, I need to create an object type and a nested table type based on that object type. Here goes:

CREATE OR REPLACE TYPE ir_other_ranking_ot IS OBJECT
(
   period VARCHAR2 (500),
   type_name VARCHAR2 (300),
   num_of_players INTEGER,
   top20 INTEGER,
   top100 INTEGER,
   score INTEGER,
   wscore INTEGER,
   pct_correct_answers NUMBER,
   answer_time VARCHAR2 (500),
   best_possible_score INTEGER,
   best_possible_wscore INTEGER,
   total_quizzes INTEGER,
   perc_taken NUMBER,
   score_per_quiz INTEGER,
   overall_rank_wscore INTEGER,
   overall_rank INTEGER
);
/

CREATE OR REPLACE TYPE ir_other_ranking_nt
   IS TABLE OF ir_other_ranking_ot
/

Then I define a function that accepts the necessary parameters and returns a collection of that type.

CREATE OR REPLACE PACKAGE BODY qdb_rankings
IS
   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;
END;
/

So the public-facing side of my function is done. Time to dive inside! Unfortunately, I lead a busy, busy life at Oracle and right now I am on deadline to complete my next article for Oracle Magazine. Consequently, please accept my apologies for publishing this post without getting into the "good stuff." I promise that it is coming right along!

Links to Table Function Series

Comments

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