PTF Tracing Part 3

Polymorphic Table Functions (PTF’s) have tracing functions customised to interrogate object types such as fetched rowsets. Here is an example:

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
  v_another_col DBMS_TF.TAB_VARCHAR2_T;
BEGIN
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  DBMS_TF.TRACE( rowset => v_rowset );
END;

And here are the results for a rowset containing 2 rows of 2 columns. Looks like somethings is missing though…

.... [1] = {}
.... [2] = {}

Where are the column values in the rows? It turns out it is not the fault of the trace functions – the solution is to flag the columns as for_read in the DESCRIBE like this:

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
  RETURN DBMS_TF.DESCRIBE_T IS
BEGIN
  FOR counter IN 1 .. p_tbl.column.count LOOP
    p_tbl.column(counter).for_read := TRUE;
  END LOOP;
  RETURN NULL;
END;

Now the column values show up.

.... [1] = {"COL1":19, "COL2":99}
.... [2] = {"COL1":19, "COL2":98}

Thanks for reading!

PTF Tracing Part 2

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!

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!