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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

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!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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 perspective…