Category: Table Functions

Polymorphic Table Functions (PTF’s) tracing can be added to the DESCRIBE, OPEN, FETCH_ROWS and CLOSE portions of the package.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
  RETURN DBMS_TF.DESCRIBE_T IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Describe',
                 separator => 'DESC',
                 prefix    => 'DPREFIX');
  RETURN NULL;
END;

PROCEDURE open IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Open',
                 with_id   => TRUE,
                 separator => 'OPEN ',
                 prefix    => 'OPPREFIX' );
END;

PROCEDURE fetch_rows IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Fetch',
                 with_id   => TRUE,
                 separator => 'FETCH ',
                 prefix    => 'FPREFIX' );
END;

PROCEDURE close IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Close',
                 with_id   => TRUE,
                 separator => 'CLOSE ',
                 prefix    => 'CPREFIX' );
END;

And here is the output from a run.

DPREFIXHello From Describe
DESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESC
OPPREFIXHello From Open <id=0500000000000000>
OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OP
FPREFIXHello From Fetch <id=0500000000000000>
FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETC
CPREFIXHello From Close <id=0500000000000000>
CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOS

The trace function is acutely aware of the object types PTF’s use. For example the argument (table, view or query) can be interrogated with a singe call to the trace function as shown here:

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
  RETURN DBMS_TF.DESCRIBE_T IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Describe',
                 separator => 'DESC',
                 prefix    => 'DPREFIX');
  DBMS_TF.TRACE ( tab => p_tbl );
  RETURN NULL;
END;

And here are the results.

DPREFIXHello From Describe
DESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESC
Schema Name:........."D"
Package Name:........."POLY_PKG"
PTF Name:........."POLY_FUNC"
........tab.column[1] =
pass through column
NOT a for read column
__________Type:...............NUMBER
__________Max Length:.........22
__________Name:..............."COL1"
__________Name Length:........6
__________Precision:..........0
__________Scale:..............-127
........tab.column[2] =
pass through column
NOT a for read column
__________Type:...............NUMBER
__________Max Length:.........22
__________Name:..............."COL2"
__________Name Length:........6
__________Precision:..........0
__________Scale:..............-127

OPPREFIXHello From Open <id=0600000000000000>
OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OP
FPREFIXHello From Fetch <id=0600000000000000>
FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETC
CPREFIXHello From Close <id=0600000000000000>
CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOS

Thanks for reading!

Polymorphic Table Functions (PTF’s) are quite powerful and it can sometimes be difficult to follow what is happening. Thankfully they include tracing functions as demonstrated in this post. We’ll start with the simplest version that displays a tracing message during the parse.

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 IS
  BEGIN
    DBMS_TF.TRACE( msg => 'Hello From Describe');
    RETURN NULL;
  END;
END;

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

SELECT *
  FROM poly_func(table1);
COL1  COL2
----- ----
   19   99
   19   98
Hello From Describe

The trace message displays after the execution just like a DBMS_OUTPUT call. But the trace functions in PTF’s offers more than that. For example an execution ID as shown in this OPEN procedure.

PROCEDURE open IS
BEGIN
  DBMS_TF.TRACE( msg     => 'Hello From Open',
                 with_id => TRUE );
END;

And here is the output.

Hello From Open <id=0100000000000000>

The execution ID is a hexidecimal value that increases for every execution of the PTF. It’s only available at execution time – not during the DESCRIBE as shown in this example:

CREATE OR REPLACE PACKAGE BODY poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
    RETURN DBMS_TF.DESCRIBE_T IS
  BEGIN
    DBMS_TF.TRACE( msg     => 'Hello From Describe',
                   with_id => TRUE );
    RETURN NULL;
  END;
END;

This error will display.

ERROR at line 2:
ORA-62565: The Describe method failed with error(s).
ORA-62562: The API Get_Execution_Id can be called only
during execution time of a polymorphic table function.

To clearly differentiate lines in the tracing a separator and prefix can be applied.

PROCEDURE open IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Open',
                 with_id   => TRUE,
                 separator => 'SEPARATOR',
                 prefix    => 'PREFIX' );
END;

Here is the output.

PREFIXHello From Open <id=0200000000000000>
SEPARATORSEPARATORSEPARATORSEPARATORSEPARATORSEPARAT

Thanks for reading!

The DUAL pseudo-table has been part of the Oracle database forever. It is typically used when a query is required but a real table does not apply. For example the following query that loads the current date into a variable.

SELECT SYSDATE new_date
  FROM DUAL;

Use of the DUAL table is rarely required nowadays because functions can be called directly something like this:

:new_date := SYSDATE

The DUAL table contains a single column named DUMMY and it resides in the SYS schema. It only returns a single row.

DESC dual
Name   Type
------ -----------
DUMMY  VARCHAR2(1)

DESC sys.dual
Name   Type
------ -----------
DUMMY  VARCHAR2(1) 

SELECT *
  FROM dual;
D
-
X

Use of the DUAL table in present day code is discouraged because it requires an unnecessary database query. But sometimes developers fall back on old habits and cant resist it. One way to break that habit is to return implausible but entertaining results. How about a Rick-Roll implemented as a Polymorphic Table Function (PTF)!?

CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T AS
    v_new_col DBMS_TF.COLUMN_METADATA_T;
    v_new_cols DBMS_TF.COLUMNS_NEW_T;
  BEGIN
    -- hide all columns selected
    FOR counter IN 1..p_tbl.column.count LOOP
      p_tbl.column(counter).pass_through := FALSE;
    END LOOP;
    -- add the rickroll column
    v_new_col := DBMS_TF.COLUMN_METADATA_T( 
             name => 'RICKROLL' );
    v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col);
    RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols,
                                row_replication => true);
  END;

  PROCEDURE fetch_rows IS
    l_row_set DBMS_TF.row_set_t;
    l_new_col DBMS_TF.tab_varchar2_t;
    l_row_count PLS_INTEGER;
  BEGIN
    -- DUAL has one record but we need 6
    DBMS_TF.ROW_REPLICATION(replication_factor => 6);
    DBMS_TF.get_row_set(l_row_set, row_count => l_row_count);
    -- Bring on those heartfelt lyrics!
    l_new_col(1) := 'Never gonna give you up';
    l_new_col(2) := 'Never gonna let you down';
    l_new_col(3) := 'Never gonna run around and desert you';
    l_new_col(4) := 'Never gonna make you cry';
    l_new_col(5) := 'Never gonna say goodbye';
    l_new_col(6) := 'Never gonna tell a lie and hurt you';
    DBMS_TF.put_col(1, l_new_col);
  END;

END;
/

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE )
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
/
-- Create a view named DUAL that calls the PTF
CREATE OR REPLACE VIEW dual AS
SELECT *
  FROM poly_func(SYS.DUAL)
/

And here is the output. I defy you to not sing along…

SELECT *
  FROM DUAL;
RICKROLL
--------------------------------------
Never gonna give you up
Never gonna let you down
Never gonna run around and desert you
Never gonna make you cry
Never gonna say goodbye
Never gonna tell a lie and hurt you

Thanks for reading!