PTF’s Are Smooth Pseudo Operators

The previous post introduced the COLUMNS parameter for PTF’s. It’s referred to as a¬†Pseudo-Operator which means it’s a mashup of two famous songs:

1 – Sussudio by Phil Collins

2 – Smooth Operator By Sade

Or…

It’s an object type define in the DBMS_TF package. It’s made up of the COLUMNS_T object which contains objects types ad DBMS_QUOTED_ID.

The DBMS_QUOTED_ID type is just IDs. Quoted. Not very musical at all…

In this post I’ll demonstrate how a list of column names could be passed to the PTF and used to determine what columns get returned. Note that within a PTF the being returned equates to being passed through and every column has a “passed through” attribute of TRUE or FALSE as shown by this code.

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

    -- display the pass through value for all of the
    -- columns
    FOR counter IN 1..p_tbl.column.count LOOP
      DBMS_OUTPUT.PUT_LINE('- Name ' ||
            p_tbl.column(counter).description.name);
      IF p_tbl.column(counter).pass_through THEN
        DBMS_OUTPUT.PUT_LINE('- Pass Thru ');
      ELSE
        DBMS_OUTPUT.PUT_LINE('- Not Pass Thru ');
      END IF;
    END LOOP;
    RETURN NULL;
  END;

END;

CREATE OR REPLACE FUNCTION poly_func( p_tbl IN TABLE, 
                                p_cols DBMS_TF.COLUMNS_T )
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

-- And now to the query
SELECT *
  FROM poly_func(table1,
                 columns(col2));
      COL1       COL2
---------- ----------
        19         99
- Name "COL1"
- Pass Thru
- Name "COL2"
- Pass Thru

Note that the default pass through is TRUE.

Now I’ll augment the PTF to only pass through the desired columns.

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
    -- set all columns to NOT pass through to start with
    FOR counter IN 1..p_tbl.column.count LOOP
      p_tbl.column(counter).pass_through := FALSE;
    END LOOP;
    -- set the requested columns to pass through
    FOR counter IN 1..p_tbl.column.count LOOP
      DBMS_OUTPUT.PUT_LINE('- Name ' || 
              p_tbl.column(counter).description.name);
      FOR counter2 IN 1..p_cols.count LOOP
        IF p_cols(counter2) = 
             p_tbl.column(counter).description.name THEN
           p_tbl.column(counter).pass_through := TRUE;
        END IF;
      END LOOP;
    END LOOP;
    IF p_tbl.column(counter).pass_through THEN
      DBMS_OUTPUT.PUT_LINE('- Pass Thru ');
    ELSE
      DBMS_OUTPUT.PUT_LINE('- Not Pass Thru ');
    END IF;
    RETURN NULL;
  END;
END;

And now some queries!

-- just COL1
SELECT *
  FROM poly_func(table1,
                 columns(COL1));

COL1
----
  19
- Name "COL1"
- Pass Thru
- Name "COL2"
- Not Pass Thru

-- just COL2
SELECT *
  FROM poly_func(table1,
                 columns(COL2));
COL2
----
  99
- Name "COL1"
- Not Pass Thru
- Name "COL2"
- Pass Thru

-- NOT case sensitive
SELECT *
  FROM poly_func(table1,
                 columns(col1));
COL1
----
  19

- Name "COL1"
- Pass Thru
- Name "COL2"
- Not Pass Thru

-- must have at least one column passed through!
SELECT *
  FROM poly_func(table1,
                 columns(NOT_THERE));
ERROR at line 2:
ORA-30732: table contains no user-visible columns
- Name "COL1"
- Not Pass Thru
- Name "COL2"
- Not Pass Thru

-- Multiples allowed
SELECT *
  FROM poly_func(table1,columns(col1,col2))
COL1 COL2
---- ----
  19   99
- Name "COL1"
- Pass Thru
- Name "COL2"
- Pass Thru

The DBMS_QUOTED_ID implies quotes being required but as the code shows that is not the case.

Thanks for reading!

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.