PTF’s Accept Variables

Prior posts in this series focused on the table/view/SQL passed to the DESCRIBE function. This post demonstrates passing other parameters. This example demonstrates passing a numeric parameter named p_var to the DESCRIBE function.

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

CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,
                      p_var NUMBER )
                      RETURN DBMS_TF.DESCRIBE_T AS
  BEGIN
    -- simply display the value passed in
    DBMS_OUTPUT.PUT_LINE('VAR = ' || p_var);
    RETURN NULL;
  END;

END;

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

And the result is…

SELECT col1
  FROM poly_func(table1,1);
COL1
----------
19
VAR = 1

In addition to scalar parameters PTF’s offer something called Variadic Pseudo-Operators which is a fancy way to say they pass structures defined in the DBMS_TF package. The example below uses the COLUMNS Pseudo-Operator which contains a list of column names.

CREATE OR REPLACE PACKAGE poly_pkg AS

  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,
                      p_cols DBMS_TF.COLUMNS_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,
                      p_cols DBMS_TF.COLUMNS_T )
                      RETURN DBMS_TF.DESCRIBE_T IS
  BEGIN
    FOR counter IN 1..p_tbl.column.count LOOP
      DBMS_OUTPUT.PUT_LINE('Object Column ' ||
           p_tbl.column(counter).description.name);
    END LOOP;
    FOR counter IN 1..p_cols.count LOOP
      DBMS_OUTPUT.PUT_LINE('Passed Column ' ||
           p_cols(counter));
    END LOOP;
    RETURN NULL;
  END;

END;

And the result is…

SELECT *
  FROM poly_func(table1,columns(col2));
COL1 COL2
---- ----
19     99
Object Column "COL1"
Object Column "COL2"
Passed Column "COL2"

This example did not actually do anything with the passed in column list other than display it. In future posts I’ll show more in depth examples of what can be done.

Thanks for reading!

PTF’s Know Their Columns

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.

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.