Category: Table Functions

Previous posts in this series focused on influencing the PARSE operation of a PTF. This post demonstrates how they influence the OPEN, FETCH and CLOSE operations as well. Here is some simple code to introduce things.

CREATE OR REPLACE PACKAGE poly_pkg AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T;

  -- executes when the cursor opens when the
  -- PTF is queried
  PROCEDURE open;

  -- executes when a row (or set of rows) is queried
  PROCEDURE fetch_rows;

  -- executes when the cursor closes
  PROCEDURE close;

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
    -- identify when the DESCRIBE executes
    DBMS_OUTPUT.PUT_LINE('DESCRIBE');
    RETURN NULL;
  END;

  PROCEDURE open IS
  BEGIN
    -- identify when the OPEN executes
    DBMS_OUTPUT.PUT_LINE('OPEN');
  END;

  PROCEDURE fetch_rows IS
  BEGIN
    -- identify when the FETCH executes
    DBMS_OUTPUT.PUT_LINE('FETCH');
  END;

  PROCEDURE close IS
  BEGIN
    -- identify when the close executes
    DBMS_OUTPUT.PUT_LINE('CLOSE');
  END;

END;

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

And the query results are:

SELECT col1
  FROM poly_func(table1);
COL1
----
  19
DESCRIBE
OPEN
FETCH
CLOSE

One thing the FETCH_ROWS procedure can do is interrogate the values that are fetched. Here is an example that grabs the rowset and outputs the count.

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
  v_colcount PLS_INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('FETCH');
  -- grab the rowset, count of rows and count
  -- of columns
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount,v_colcount);
  DBMS_OUTPUT.PUT_LINE('Rows = ' || v_rowcount);
END;

And now the query…

SELECT col1
  FROM poly_func(table1);
COL1
----
  19
DESCRIBE
OPEN
FETCH
Rows = 1
CLOSE

Future articles will delve more into what FETCH_ROWS can do as well as the ROW_SET_T structure.

Thanks for reading!

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!

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!