The previous post used a PTF to populate row number one from a one row table. It also did this in row set number one from the first, and only execution of the FETCH_ROWS procedure. This post shows the simplest way to increase executions of FETCH_ROWS — increase the number of rows to process.
I’ll use this query to vaidate the changes. It shows the count of rows with each value in the ADDED_COL. Here’s the results using the code from the previous post.
SELECT added_col, COUNT(*) FROM poly_func(table1) GROUP BY added_col; ADDED_COL COUNT(*) --------------- ---------- Added Col 1
There is only 1 row so it shows with a count of 1.
Next I’ll add another row.
INSERT INTO table1 VALUES(2018);
And query the counts again.
SELECT added_col, COUNT(*) FROM poly_func(table1) GROUP BY added_col; ADDED_COL COUNT(*) --------------- ---------- Added Col 1 1
It shows 1 row (the very first one in the rowset) with an ADDED_COL value of “Added Col” and 1 row with a NULL value.
Next I’ll set the table up with 1025 rows.
DELETE table1; BEGIN FOR counter IN 1..1025 LOOP INSERT INTO table1 VALUES(counter); END LOOP; END;
And run the count query again.
SELECT added_col, COUNT(*) FROM poly_func(table1) GROUP BY added_col; ADDED_COL COUNT(*) --------------- ---------- Added Col 2 1023
The results show 2 rows with an ADDED_COL value of “Added Col” – that must mean two row sets from two executions of the FETCH_ROWS procedure. I’ll augment the FETCH_ROWS procedure to verify it.
PROCEDURE fetch_rows IS v_rowset DBMS_TF.ROW_SET_T; v_added DBMS_TF.COLUMN_DATA_T; v_env DBMS_TF.ENV_T; BEGIN v_env := DBMS_TF.GET_ENV; DBMS_OUTPUT.PUT_LINE('ROWCOUNT = ' || v_env.row_count); v_added.tab_varchar2(1) := 'Added Col'; v_rowset(1) := v_added; DBMS_TF.PUT_ROW_SET( rowset => v_rowset); END;
And run the query again.
SELECT added_col, COUNT(*) FROM poly_func(table1) GROUP BY added_col ADDED_COL COUNT(*) --------------- ---------- Added Col 2 1023 ROWCOUNT = 1024 ROWCOUNT = 1
Sure enough FETCH_ROWS was run twice. Why was 1025 the number of rows that caused this? At this point I don’t know. But stay tuned for more details of row sets.
Thanks for reading.