CREATE OR REPLACE PACKAGE BODY kscope_ptf AS
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;
/*--------------------------------------------------------*/
PROCEDURE fetch_rows IS
/*--------------------------------------------------------*/
v_rowset DBMS_TF.ROW_SET_T;
v_rowcount NUMBER;
v_colcount NUMBER;
BEGIN
-- get the rowset for interrogation later...
DBMS_TF.GET_ROW_SET( rowset => v_rowset,
row_count => v_rowcount,
col_count => v_colcount);
DBMS_OUTPUT.PUT_LINE(v_rowcount || ' rows and ' || v_colcount || ' columns');
FOR counter IN 1..v_colcount LOOP
DBMS_OUTPUT.PUT_LINE(v_rowset(counter).description.name || ' ' ||
DBMS_TF.COLUMN_TYPE_NAME(v_rowset(counter).description));
END LOOP;
DBMS_OUTPUT.PUT_LINE('TRACE');
DBMS_TF.TRACE( rowset => v_rowset);
DBMS_OUTPUT.PUT_LINE('BY HAND');
-- for every row...
FOR rcounter IN 1..v_rowcount LOOP
-- for every column...
FOR ccounter IN 1..v_colcount LOOP
-- output the column name which only requires ccounter
IF ccounter = 1 THEN
DBMS_OUTPUT.PUT('.... [' || rcounter || '] = {' ||
v_rowset(ccounter).description.name || ':');
ELSE
DBMS_OUTPUT.PUT(', ' ||
v_rowset(ccounter).description.name || ':');
END IF;
-- if the datatype of the column (ccounter) is 1 (varchar2)
-- the display the correct row (rcounter) value from tab_varchar2
IF v_rowset(ccounter).description.type = 1 THEN
DBMS_OUTPUT.PUT('"' || v_rowset(ccounter).tab_varchar2(rcounter) || '"');
END IF;
-- if the datatype of the column (ccounter) is 2 (number)
-- the display the correct row (rcounter) value from tab_number
IF v_rowset(ccounter).description.type = 2 THEN
DBMS_OUTPUT.PUT(v_rowset(ccounter).tab_number(rcounter));
END IF;
END LOOP; -- every column
DBMS_OUTPUT.PUT_LINE('}');
END LOOP; -- every row
END;
END;