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!

SODA – More Than Just A Table

This post begins investigating what goes on behind the scenes to implement a SODA collection in Oracle. Lets dive right in and create a SODA collection named CLCT and put a JSON document in it using the DBMS_SODA PLSQL package.

DECLARE
  v soda_collection_t;
  d soda_document_t;
  t NUMBER;
BEGIN
  v := DBMS_SODA.CREATE_COLLECTION ('CLCT');
  d := SODA_DOCUMENT_T(
  b_content => utl_raw.cast_to_raw('{"KEY1" : "VALUE1"}'));
  t := v.insert_one(d);
END;

What objects get created to support this collection?  A quick query of the USER_OBJECTS view reveals the following:

SELECT object_name,
       object_type
  FROM user_objects;

OBJECT_NAME               OBJECT_TYPE
------------------------- -----------
CLCT                      TABLE
SYS_C007444               INDEX
SYS_IL0000073558C00005$$  INDEX
SYS_LOB0000073558C00005$$ LOB

The CLCT table stores data for the CLCT collection. It has an index as well as a LOB and LOB index to store JSON data in a Binary Large Object (BLOB) field.

Here is what the CLCT table looks like.

SQL> DESC clct
Name            Null?    Type
--------------- -------- -------------
ID              NOT NULL VARCHAR2(255)
CREATED_ON      NOT NULL TIMESTAMP(6)
LAST_MODIFIED   NOT NULL TIMESTAMP(6)
VERSION         NOT NULL VARCHAR2(255)
JSON_DOCUMENT            BLOB

The JSON_DOCUMENT field contains the JSON document. This means it can be queried using Oracle’s SQL JSON functions such as JSON_VALUE shown here.

SELECT JSON_VALUE(json_document,'$.KEY1')
  FROM clct;

JSON_VALUE(JSON_DOCUMENT,'$.KEY1')
----------------------------------
VALUE1

To investigate what JSON keys exist in the JSON document we access it’s Data Guide like this:

SELECT JSON_DATAGUIDE(json_document)
  FROM clct;

JSON_DATAGUIDE(JSON_DOCUMENT)
--------------------------------------------------
[{"o:path":"$.KEY1","type":"string","o:length":8}]

And since we are working in the meta-data crazy world of JSON there must be some meta-data for the collection as well! Here’s how we access that.

Note the use of the JSON_QUERY SQL function to show the results.

DECLARE
  collection SODA_COLLECTION_T;
BEGIN
  collection := DBMS_SODA.open_collection('CLCT');
  DBMS_OUTPUT.PUT_LINE(
        JSON_QUERY(collection.get_metadata,'$' PRETTY));
END;

And here is the meta-data for the collection.

{
"schemaName" : "D",
"tableName" : "CLCT",
"keyColumn" :
{
"name" : "ID",
"sqlType" : "VARCHAR2",
"maxLength" : 255,
"assignmentMethod" : "UUID"
},
"contentColumn" :
{
"name" : "JSON_DOCUMENT",
"sqlType" : "BLOB",
"compress" : "NONE",
"cache" : true,
"encrypt" : "NONE",
"validation" : "STANDARD"
},
"lastModifiedColumn" :
{
"name" : "LAST_MODIFIED"
},
"versionColumn" :
{
"name" : "VERSION",
"method" : "SHA256"
},
"creationTimeColumn" :
{
"name" : "CREATED_ON"
},
"readOnly" : false
}

I’ll discuss details of the meta-data in a later post. For now just notice that the values for the name keys match the keys in the CLCT table.

For more posts about Oracle SQL and JSON click here or for a comprehensive set of training videos click here

Thanks for reading!

No Implicit Commits

Committing a transaction means the transaction cannot be rolled back. There are two types of commits:

  • Explicit – when the COMMIT command is executed
  • Implicit – when DDL (Data Definition Language) is executed

DDL includes SQL such as CREATE TABLE. When DDL is executed all un-committed work in the current session is implicitly committed.

The code below shows an implicit commit. First it creates a table and inserts three rows into it. At this point the the insert of three rows can be rolled back. Then another table is created which performs an implicit commit. This means the three inserts can no longer be rolled back.

CREATE TABLE abc
( col1 NUMBER );
INSERT INTO abc VALUES(1);
INSERT INTO abc VALUES(2);
INSERT INTO abc VALUES(3);

Then create another table.

CREATE TABLE def
( col1 NUMBER );

Check the records in the first table.

SELECT *
  FROM abc;

COL1
----
   1
   2
   3

Attempt to roll back.

ROLLBACK;

But the rows are still there…

SELECT *
  FROM abc;

COL1
----
   1
   2
   3

And now with SODA…

Create a table and insert 3 rows.

CREATE TABLE abc
( col1 NUMBER );
INSERT INTO abc VALUES(1);
INSERT INTO abc VALUES(2);
INSERT INTO abc VALUES(3);

Create a SODA collection.

DECLARE

  v soda_collection_t;
  d soda_document_t;
  t NUMBER;

BEGIN

  v := DBMS_SODA.CREATE_COLLECTION ('ABC_COLLECT');
  d := SODA_DOCUMENT_T(
  b_content => utl_raw.cast_to_raw('{"KEY1" : "VALUE1"}'));
  t := v.insert_one(d);

END;

Check for the rows in the first table.

SELECT *
  FROM abc;

COL1
----
   1
   2
   3

Check for the row in the SODA table created for the collection.

SELECT json_document
  FROM abc_collect;

JSON_DOCUMENT
--------------------------------------
7B224B45593122203A202256414C554531227D

Try to rollback.

ROLLBACK;

The rollback worked! On the ABC table and the SODA table.

SELECT *
  FROM abc;
no rows selected

SELECT json_document
  FROM abc_collect;
no rows selected

So the SODA table was created without an implicit commit.

Conversely an explicit commit may be required when dropping a SODA collection (including its underlying table). If there are any outstanding transactions when trying to drop a collection Oracle will raise ORA-40626 as shown in this example.

BEGIN
DBMS_OUTPUT.PUT_LINE(
     DBMS_SODA.DROP_COLLECTION('ABC_COLLECT'));
END;
*
ERROR at line 1:
ORA-40626: The table or view underlying the collection
           cannot be dropped.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SODA_UTIL", line 162
ORA-06512: at "XDB.DBMS_SODA_ADMIN", line 1857
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SODA", line 24
ORA-06512: at line 1

-- commit the transactions first
COMMIT;

Commit complete.

-- then drop the collection
BEGIN
  DBMS_OUTPUT.PUT_LINE(
    DBMS_SODA.DROP_COLLECTION('ABC_COLLECT')); 
END;

1

In the next few posts I’ll look further into the “table or view underlying the collection”.

Thanks for reading!