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 … Read more

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 … Read more

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 … Read more

PTF Error Handling

So what happens to un-handled exceptions in Polymorphic Table Function’s (PTF’s)? I’m glad you asked. This post covers that topic by introducing an exception with the following code: RAISE_APPLICATION_ERROR(-20000,’Badness’); When added to the DESCRIBE function it produces this error at run time. ORA-62565: The Describe method failed with error(s). ORA-20000: Badness OPEN, FETCH_ROWS and CLOSE … Read more

PTF’s Can Open, Fetch And Close Too

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 … Read more

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 … Read more