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!

PTF’s Gonna Rick-Roll Ya!

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!

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 shows errors as:

ORA-20000: Badness
ORA-06512: at "D.POLY_PKG", line 17

Note that it may show up multiple times for the CLOSE.

Also note that OPEN, FETCH_ROWS and CLOSE will not throw exceptions if no rows are processed.

That’s the simple part. But what if an error happens while processing rows?

I’ll add some logging to FETCH_ROWS like this:

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Start');
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  DBMS_OUTPUT.PUT_LINE('RC:' || v_rowcount);
  FOR counter IN 1..v_rowcount LOOP
    DBMS_OUTPUT.PUT_LINE('COUNTER:' || counter);
  END LOOP;
END;

And a successful query of three records produces this result.

COL1
----
  19
  19
  19
Start
RC:3
COUNTER:1
COUNTER:2
COUNTER:3

The logging shows that FETCH_ROWS started, queried a rowset with 3 rows and then looped through each row.

Now I’ll force a failure and trap the exception like this:

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Start');
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  DBMS_OUTPUT.PUT_LINE('RC:' || v_rowcount);
  FOR counter IN 1..v_rowcount LOOP
    DBMS_OUTPUT.PUT_LINE('COUNTER:' || counter);
    IF counter = 2 THEN
      RAISE_APPLICATION_ERROR(-20000,'Badness');
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION');
END;

What result will I get? Will the number of rows returned change?

COL1
----
  19
  19
  19
Start
RC:3
COUNTER:1
COUNTER:2
EXCEPTION

The logging shows that FETCH_ROWS started, queried a rowset with 3 rows and then looped through the first 2 rows. Then the exception was raised and trapped. But the only processing that stopped was within FETCH_ROWS. Thus three records were still returned.

Thanks for reading!