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.