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!