PTF Tracing Introduction

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!