CREATE OR REPLACE PACKAGE BODY kscope_ptf AS
FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
RETURN DBMS_TF.DESCRIBE_T IS
v_new_col1 DBMS_TF.COLUMN_METADATA_T;
v_new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
FOR counter IN 1..p_tbl.column.count LOOP
p_tbl.column(counter).for_read := TRUE;
END LOOP;
v_new_col1 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_NUMBER,
name => 'YEARS_AGO',
max_len => 10 );
v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1 );
-- return a cursor and kindly advise of the new columns
RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
END;
/*--------------------------------------------------------*/
PROCEDURE fetch_rows IS
/*--------------------------------------------------------*/
v_env DBMS_TF.ENV_T;
v_put_cols DBMS_TF.TABLE_METADATA_T;
v_get_cols DBMS_TF.TABLE_METADATA_T;
BEGIN
v_env := DBMS_TF.GET_ENV;
v_put_cols := v_env.put_columns;
v_get_cols := v_env.get_columns;
FOR counter IN 1..v_get_cols.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Get ' || v_get_cols(counter).name);
END LOOP;
FOR counter IN 1..v_put_cols.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Put ' || v_put_cols(counter).name);
END LOOP;
END;
END; SELECT *
FROM kscope_func(kscope)EXEC NULLCREATE OR REPLACE PACKAGE BODY kscope_ptf AS
FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
RETURN DBMS_TF.DESCRIBE_T IS
v_new_col1 DBMS_TF.COLUMN_METADATA_T;
v_new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
FOR counter IN 1..p_tbl.column.count LOOP
p_tbl.column(counter).for_read := TRUE;
END LOOP;
v_new_col1 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_NUMBER,
name => 'YEARS_AGO',
max_len => 10 );
v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1 );
-- return a cursor and kindly advise of the new columns
RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
END;
/*--------------------------------------------------------*/
PROCEDURE fetch_rows IS
/*--------------------------------------------------------*/
v_rowset DBMS_TF.ROW_SET_T;
v_rowcount NUMBER;
v_colcount NUMBER;
v_years_ago NUMBER;
v_n_t DBMS_TF.TAB_NUMBER_T; -- table of numbers
BEGIN
DBMS_TF.GET_ROW_SET( rowset => v_rowset,
row_count => v_rowcount,
col_count => v_colcount);
-- for every get column...
FOR col_counter IN 1..v_colcount LOOP
-- if the column is YEAR then...
IF v_rowset(col_counter).description.name = '"YEAR"' THEN
-- for every row in the row set...
FOR row_counter IN 1..v_rowcount LOOP
-- calculate years ago and do some math
v_years_ago := 2019 - v_rowset(col_counter).tab_number(row_counter);
v_n_t(row_counter) := v_years_ago;
END LOOP; -- every row
END IF; -- column is YEAR
END LOOP; -- every column
-- the "puts" always go on the right hand side
-- of the row set
DBMS_TF.PUT_COL( columnid => 1,
collection => v_n_t);
END;
END; SELECT *
FROM kscope_func(kscope)EXEC NULL