implestrat.com

Continuing with Polymorphic Tabe Functions (PTF’s) this post delves further into the DESCRIBE function – specifically the table, package and function involved and the columns of the passed in object. I’ll let the code to the talking…

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

    -- what schema is the table from?
    DBMS_OUTPUT.PUT_LINE('Table Schema ' || 
                          p_tbl.schema_name);
    -- what package is being run?
    DBMS_OUTPUT.PUT_LINE('Table Package ' || 
                          p_tbl.package_name);
    -- What PTF is being run?
    DBMS_OUTPUT.PUT_LINE('Table PTF ' || 
                          p_tbl.ptf_name);

    -- the column array in the DBMS_TF_TABLE_T
    -- structure contains details of each column
    FOR counter IN 1..p_tbl.column.count LOOP

      DBMS_OUTPUT.PUT_LINE('Column ' || counter);
      -- output the column name, the length of the
      -- column name, the maximum value length allowed
      -- and the data type of the column
      DBMS_OUTPUT.PUT_LINE('- Name ' || 
              p_tbl.column(counter).description.name);
      DBMS_OUTPUT.PUT_LINE('- Name Len ' || 
              p_tbl.column(counter).description.name_len);
      DBMS_OUTPUT.PUT_LINE('- Max Len ' || 
              p_tbl.column(counter).description.max_len);
      DBMS_OUTPUT.PUT_LINE('- Datatype ' || 
              p_tbl.column(counter).description.type);

    END LOOP;

    RETURN NULL;
  END;
END;

And here are the results.

SELECT *
  FROM poly_func(table1);
COL1 COL2
---- ----
  19   99

And DBMS_OUTPUT produces this:

Table Schema "D"
Table Package "POLY_PKG"
Table PTF "POLY_FUNC"
Column 1
- Name "COL1"
- Name Len 6
- Max Len 22
- Datatype 2
Column 2
- Name "COL2"
- Name Len 6
- Max Len 22
- Datatype 2

Note the length of the column (NAME_LEN) includes the surrounding double quotes so both “COL1” and “COL2” are considered 6 characters long.

Thanks for reading.

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 (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!