Describe Only PTF’s Don’t Have A Plan

The previous post in this series introduced Polymorphic Table Functions (PTF’s) in a very simplistic way. All they have is a DESCRIBE function. Thus they are referred to as “Describe Only PTF’s”.  Oracle does not include them in the query plan.

Below are all of the SQL statements from the previous post along with their output from the DBMS_UTILITY.EXPAND_SQL_TEXT procedure to display.

-- Query 1 column from the PTF
SELECT col1
  FROM poly_func(table1);

That query translates to this:

SELECT "A1"."COL1" "COL1"
  FROM (SELECT "A2"."COL1" "COL1",
               "A2"."COL2" "COL2"
          FROM (SELECT "A3"."COL1" "COL1",
                       "A3"."COL2" "COL2"
                  FROM "D"."TABLE1" "A3") "A2") "A1";

The next query fails to find a row.

SELECT col1
  FROM poly_func(table1)
 WHERE 1 = 2;

It translated to this SQL:

SELECT "A1"."COL1" "COL1"
  FROM (SELECT "A2"."COL1" "COL1",
               "A2"."COL2" "COL2"
          FROM (SELECT "A3"."COL1" "COL1",
                       "A3"."COL2" "COL2"
                  FROM "D"."TABLE1" "A3") "A2") "A1" 
  WHERE 1=2;

Querying a PTF with the view like this:

SELECT *
  FROM poly_func(view1);

Translates to this:

SELECT "A1"."COL2" "COL2"
  FROM (SELECT "A2"."COL2" "COL2"
          FROM (SELECT "A3"."COL2" "COL2"
                  FROM (SELECT "A4"."COL2" "COL2"
                          FROM "D"."TABLE1" "A4") "A3") "A2") "A1"

And finally the WITH cause:

WITH t1 AS
( SELECT col2 AS c2
    FROM table1 )
SELECT *
  FROM poly_func(t1);

Translates to:

SELECT "A1"."C2" "C2"
  FROM (SELECT "A3"."C2" "C2" 
          FROM (SELECT "A4"."C2" "C2"
                  FROM (SELECT "A2"."COL2" "C2"
                          FROM "D"."TABLE1" "A2") "A4") "A3") "A1";

The optimizer relies heavily on nested queries. The reasons for that will become clearer when we work through more complex examples in later posts.

Thanks for reading.

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!