Polymorphic Table Functions – Introduction

Polymorphic Table Functions (PTF’s) work with input that is not predefined and provide output that is not predefined. Thus they are very flexible. I encourage you to Google the phrase “Oracle Polymorphic Table Functions” to find information on this feature introduced in Oracle 18c.

in this series of posts I’ll work through some examples of what PTF’s can and cannot do. Along the way I’ll explain concepts and constraints you need to understand to make things work.

I’ll start at the beginning of any query – the parse – which is the first thing Oracle does when executing a query. Parsing is extremely complex so for the purposes of this post I’ll limit the discussion to one factor – evaluating the object being queried. For example how does the PTF shown below get information about the structure of table1?

SELECT col1
  FROM poly_func(table1);

PTF’s accept the object being queried as a parameter. Oracle could simply parse that. But PTF’s allow developers to interrogate and influence the parse using¬† a DESCRIBE function. The code snippet below defines a package with a simple DESCRIBE function augmented to show the objects columns.

CREATE OR REPLACE PACKAGE poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T;
END;

CREATE OR REPLACE PACKAGE BODY poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T AS
  BEGIN
    -- Display all of the columns in tab
    FOR counter IN 1..p_tbl.column.count LOOP
       DBMS_OUTPUT.PUT_LINE('Column ' || 
             tab.column(counter).description.name);
    END LOOP;
    RETURN NULL;
  END;
END;

I’ll discuss details like the TABLE_T type in later posts.

Next I’ll define the PTF and associate it with the package.

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

I now have all that I need to query the PTF except for data. I’ll take care of that now.

CREATE TABLE table1
( col1 NUMBER,
  col2 NUMBER );
INSERT INTO table1
VALUES(19,99);

Next I’ll query the PTF with the table I just created. During the parse the DESCRIBE function is executed showing the column names.

SELECT col1
  FROM poly_func(table1);
COL1
----
  19
Column "COL1"
Column "COL2"

Note that all columns in table1 are listed even though I only queried one. The DESCRIBE function sees the whole object – not just what is being queried.

Even if the query returns no records the parse still occurs.

SELECT col1
  FROM poly_func(table1)
 WHERE 1 = 2;
no rows selected
Column "COL1"
Column "COL2"

Even failed queries require a parse…and a call to the DESCRIBE function.

SELECT notthere
  FROM poly_func(table1);
SELECT notthere
*
ERROR at line 1:
ORA-00904: "NOTTHERE": invalid identifier
Column "COL1"
Column "COL2"

Now I’ll create a view and query it. The DESCRIBE function only sees the columns in the view.

CREATE VIEW view1 AS
SELECT col2
  FROM table1;
SELECT *
  FROM poly_func(view1);
COL2
----
  99
Column "COL2"

PTF’s can also handle Common Table Expression (aka the WITH clause). The DESCRIBE function sees the columns in the sub-query.

WITH t1 AS
( SELECT col2 AS c2
    FROM table1 )
SELECT *
 FROM poly_func(t1);
C2
--
99
Column "C2"

Thanks for reading any I hope you enjoy this series!

PLSQL JSON Parsing 3

The previous post in this series moved recursively through a JSON structure to list all keys and structure types. The code worked but the output was an ugly concatenated string. This post shows how PL/SQL table functions can make it more useful.

I’ll post the code separately. Here are the important snippets.

-- Create a database type (object) that defines
-- the rows to return
CREATE OR REPLACE TYPE v_vc2_t AS TABLE OF VARCHAR2(100);
/

-- set the return type of the function to the above object
CREATE OR REPLACE FUNCTION what_are_you
 ( p_json CLOB )
 RETURN v_vc2_t IS
...
 -- Assemble rows as the function executes
 v_ret_val.EXTEND;
 v_ret_val(v_ret_val.LAST) := 
       v_key_list(counter) || ' ' ||
       v_object.get_type(v_key_list(counter));
...
-- Return the set of assembled rows
 RETURN(v_ret_val);
...
END;
/
-- the function is run like this
DECLARE
 v_json CLOB;
BEGIN
 v_json := '{"Name" : "Marty",
 "Reason" : [ { "Medium" : "Movie",
 "Title" : "Madagascar",
 "Year" : 2005 },
 { "Medium" : "Movie",
 "Title" : "Madagascar Escape 2 Africa",
 "Year" : 2008 },
 { "Medium" : "Movie",
 "Title" : "Madagascar 3 : Europes Most Wanted",
 "Year" : 2012 } ] }';
 -- SELECT rows from the function and display them
 FOR x IN (
     SELECT *
       FROM TABLE(what_are_you(v_json)) ) LOOP
   DBMS_OUTPUT.PUT_LINE(x.column_value);
 END LOOP;
END;

And the results look like this:

Name SCALAR
Reason ARRAY
Medium SCALAR
Title SCALAR
Year SCALAR
Medium SCALAR
Title SCALAR
Year SCALAR
Medium SCALAR
Title SCALAR
Year SCALAR

That output looks a lot better and can be easily processed. The code is available here.

Thanks for reading!

Segue From Table Functions To JSON SQL

Its been a while since my last post. There are two reasons for that; one – I’ve said all I wanted to about table functions and such and two – I’ve started on a new direction (tangent?). ¬†JavaScript Object Notation (JSON) and SQL. This post is my segue from the table functions series into a JSON SQL series.

-- create a table with a JSON column
CREATE TABLE json_table
( json_column CLOB,
 CONSTRAINT must_be_json
 CHECK ( json_column IS JSON ) );

-- insert some json data
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "1 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "2 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "3 and 2"} }');

-- create object types for use in the table function
CREATE TYPE json_o AS OBJECT ( json_text VARCHAR2(30) );
/
CREATE TYPE json_t AS TABLE OF json_o;
/

-- and now for the table function
/*--------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION get_json_one ( p_curs SYS_REFCURSOR )
                  RETURN json_t
                  PIPELINED AS
/*--------------------------------------------------------------------*/
  v VARCHAR2(30);
BEGIN
  LOOP
    FETCH p_curs INTO v;
    EXIT WHEN p_curs%NOTFOUND;
    PIPE ROW(json_o(v));
  END LOOP;
END;
/

Then the function is called from a query like this:

SQL> SELECT JSON_VALUE(j.json_text,'$.fieldtwo') two
 2     FROM TABLE(get_json_one(CURSOR(SELECT jt.json_column.fieldone
 3                                      FROM json_table jt))) j
 4 /

TWO
--------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

I’ve already covered table functions a lot in other posts so I’ll just explain the JSON portions of the code. First up is the REF Cursor. When run in SQL*Plus it looks like this:

SQL> SELECT jt.json_column.fieldone
 2 FROM json_table jt;

FIELDONE
------------------------------
{"fieldtwo":"1 and 2"}
{"fieldtwo":"2 and 2"}
{"fieldtwo":"3 and 2"}

3 rows selected.

It uses dot notation to get the value of fieldone from the json column in the table returning it as a JSON Object (squiggly brackets}. The JSON object is then passed to the JSON_VALUE function which extracts the value for fieldtwo.

JSON_VALUE(j.json_text,'$.fieldtwo')

You are probably thinking that is a lot of code to do such a simple thing and you are right. But its a nice way to change over from table functions to JSON SQL.

Here is a much simpler way to do the same thing as the table function.

SQL> SELECT JSON_VALUE(j.f1,'$.fieldtwo') f2
  2    FROM ( SELECT jt.json_column.fieldone AS f1
  3             FROM json_table jt ) j;0

F2
------------------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

The next few posts will explore JSON SQL in more detail.

Thanks for reading!