KSCOPE Answer 2 of 2 – Part 3

This post concludes the series on imitating a CONNECT BY SQL query with a PTF. It calculates the CONNECT BY values (dates in this case) and outputs them as a TRACE column.

First I add the trace column in the DESCRIBE function.

  v_new_col DBMS_TF.COLUMN_METADATA_T;
  v_new_cols DBMS_TF.COLUMNS_NEW_T;
...
  v_new_col := DBMS_TF.COLUMN_METADATA_T( 
         type    => DBMS_TF.TYPE_VARCHAR2,
         name    => 'TRACE',
         max_len => 100 );
  v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col);
...
  RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );

Then in the FETCH_ROWS procedure I add the TRACE column…

  v_dt DATE;
  v_trace VARCHAR2(500);
  v_trace_cols DBMS_TF.TAB_VARCHAR2_T;

…and populate it.

FOR row_counter IN 1..v_rowcount LOOP

  -- get the date value
  v_dt := v_rowset(col_counter).tab_date(row_counter);
  v_trace := v_trace || v_dt || '-';

  v_trace_cols(row_counter) := v_trace;

END LOOP; -- every row

DBMS_TF.PUT_COL( columnid => 1,
                 collection => v_trace_cols );

Here is the simplistic test data set I use.

SELECT partno,
       sdate,
       edate
  FROM prices;

PARTNO SDATE     EDATE
------ --------- ---------
     1 24-AUG-19 27-AUG-19
     1 27-AUG-19 30-AUG-19
     2 24-AUG-19 27-AUG-19
     2 27-AUG-19 30-AUG-19

And here is the result with the PTF.

SELECT partno,
       trace
  FROM ptf_func( prices
                 PARTITION BY partno
                 ORDER     BY sdate );

PARTNO TRACE
------ ----------------------------------------
     1 24-AUG-19-
     1 24-AUG-19-27-AUG-19-
     2 24-AUG-19-27-AUG-19-24-AUG-19-
     2 24-AUG-19-27-AUG-19-24-AUG-19-27-AUG-19-

That seems pretty straight forward because I make a some assumptions:

The date ranges are already exclusive in the table
- - I don’t believe this code will handle overlaps
There are less that 1024 rows in the table
- - See other posts in this PTF series about the 1024 rows feature

Thanks for reading.

Leave a Comment Cancel reply