Skip to main content

Table Functions, Part 4: Streaming table functions

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!

In my last post on table functions, I showed how I was able to reduce many Application Express interactive reports into one by pushing all the complexity into a table function, thereby reducing the query in the report to nothing more than a "parameterized view":

SELECT *
  FROM TABLE (
     qdb_rankings.ir_other_ranking_tf (category_in  => :p443_category,
                                period_type_in      => :p443_period_type,
                                competition_id_in   => :p443_competition_id,
                                period_in           => :p443_period)) pr

I hope you agree that this is a nice trade-off: keep the code in Application Express really simple, move the complexity to the backend.

Another common usage of table functions is to stream data directly from one process or transformation, to the next process without intermediate staging. Hence, a table function used in this way is called a streaming table function.

As you might be able to tell from the reference to transformation above, this technique is most often used in data warehouses. 



Before getting into the details of implementation, here's what such a streaming process might look like in SQL:

INSERT INTO tickertable
   SELECT *
     FROM TABLE (stockpivot (CURSOR (SELECT *
                                       FROM stocktable)))

Here's an explanation of the transformation (I will show below all the details of the database objects referenced):
  1. Take all the data from the stocktable....
  2. Convert it into a cursor variable with the CURSOR expression....
  3. Pass that cursor variable to the stockpivot table function....
  4. The function returns a nested table of object type instances....
  5. The TABLE operator converts that collection into a relational table format....
  6. SELECT all the rows from that pseudo-table....
  7. Insert them into the ticker table.
And as the image indicates, you can perform multiple transformations, as in:

INSERT INTO ticker table
SELECT *
  FROM TABLE (ticker pivot (
                CURSOR (SELECT *
                          FROM TABLE(stockpivot (
                                  CURSOR (SELECT * 
                                            FROM stocktable
       ))))))

Hopefully that is enough to get you thinking about the possibilities. As for the code....here's the fairly artificial example I will implement:

The stocktable contains the open and close prices for each stock market ("ticker") symbol and date:

CREATE TABLE stocktable
(
   ticker        VARCHAR2 (20)
 , trade_date    DATE
 , open_price    NUMBER
 , close_price   NUMBER
)
/

I need to transform each row in stocktable to two rows in tickertable (one for the open price and another for the close price):

CREATE TABLE tickertable
(
   ticker      VARCHAR2 (20)
 , pricedate   DATE
 , pricetype   VARCHAR2 (1)
 , price       NUMBER
)
/

And I want to do it entirely in a single SQL statement.

Now here's where "artificial" comes into play: you do not need to use a table function to implement this transformation. The following SQL statement does the trick:

INSERT ALL
   INTO tickertable (ticker, trade_date, price_type, price) 

        values (ticker, trade_date, 'O', open_price)
   INTO tickertable (ticker, trade_date, price_type, price) 

        values (ticker, trade_date, 'C', close_price)
SELECT * FROM stocktable;


or you could use unpivot (thanks, Chris Saxon @chrisrsaxon, for this technique!):

insert into ticker table 
   (ticker, trade_date, price_type, price)
 select *
 from   stocktable
 unpivot (price for price_type in (
             open_price as 'O', close_price as 'C'))

But please assume for the sake of my blog post that the transformation is way more complex. In fact, let's be clear: the actual implementation of the transformation (table) function is obviously going to be application specific in the extreme. So I keep my transformation logic very simple and will zoom in on the key steps that you would take for whatever real-world transformation you need to implement.

As you surely know by now, a table function returns a collection. And as I explained in a earlier post in this series, when you need to return a collection whose elements contain more than a single scalar value, you need to create an object type for the datatype of the collection. So here goes:

/* Gee, looks just like the table! */

CREATE TYPE tickertype AS OBJECT
   (ticker VARCHAR2 (20)
  , pricedate DATE
  , pricetype VARCHAR2 (1)
  , price NUMBER
   );
/

CREATE TYPE tickertype_nt AS TABLE OF tickertype;
/

So a collection of tickertypeset is what the function will be returning. But what will I pass into the function? When you are building a streaming table function, that generally means that you are feeding into the function the result set of a SELECT statement. Now, you cannot pass a SELECT statement itself as an argument to a function.

So, instead, I will use the CURSOR expression to instantiate a cursor variable that points to the SELECT's result set. To do that, I need to define a REF CURSOR type whose RETURN type matches the SELECT list of the query:

CREATE OR REPLACE PACKAGE refcur_pkg
IS
   TYPE refcur_t IS REF CURSOR
      RETURN stocktable%ROWTYPE;
END refcur_pkg;
/

And now I can write the header of my function:

CREATE OR REPLACE FUNCTION stockpivot (
      dataset refcur_pkg.refcur_t)
   RETURN tickertype_nt

I will also need to declare some local constants and variables:

IS
   /* Avoid hard-coding the BULK COLLECT LIMIT 
      in the fetch statement */
   c_limit CONSTANT PLS_INTEGER := 100;

   /* Container for rows fetched from the cursor variable. */
   TYPE dataset_tt IS TABLE OF stocktable%ROWTYPE
                         INDEX BY PLS_INTEGER;
   l_dataset   dataset_tt;

   /* The nested table that will be returned. */
   retval      tickertype_nt := tickertype_nt ();

The body of function fetches N rows at a time, and then converts each of those single stocktable rows into two object types instances, each of which are added to the retval nested table. The code in purple is my very specific transformation logic. Yours will vary greatly and almost certainly be lots more complicated.

BEGIN
   LOOP
      /* Fetch next 100 rows. */
      FETCH dataset BULK COLLECT INTO l_dataset
         LIMIT c_limit;

      EXIT WHEN l_dataset.COUNT = 0;

      /* Iterate through each row.... */
      FOR l_row IN 1 .. l_dataset.COUNT
      LOOP
         /* First the opening price row */
         retval.EXTEND;         
         retval (retval.LAST) := 
            tickertype (
               l_dataset (l_row).ticker, 
               'O', 
               l_dataset (l_row).open_price,
               l_dataset (l_row).trade_date);         

         /* Next the closing price row */
         retval.EXTEND;
         retval (retval.LAST) := 
            tickertype (
               l_dataset (l_row).ticker, 
               'C', 
               l_dataset (l_row).close_price,
               l_dataset (l_row).trade_date); 
      END LOOP;
   END LOOP;

   CLOSE dataset;

   RETURN retval;
END;

There, that wasn't so hard, was it? :-)

But what if you are transforming lots and lots and LOTS of data, and you've invested in the Parallel Query option to speed things up?

Unfortunately, the default behavior of a PL/SQL function is that when you call it, your session halts, waiting for the function to return its data. In other words, it forces serialization. So if you want to use a table function in a parallelized query, you need to take the next step (in understanding and complexity) re: table functions and explore....

Pipelined table functions: one of the most interesting constructs in PL/SQL

And that, dear reader, is the subject of my next post in this series on table functions.

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