Package
CREATE OR REPLACE PACKAGE kscope_ptf AS
FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
RETURN DBMS_TF.DESCRIBE_T;
PROCEDURE fetch_rows;
END; CREATE OR REPLACE PACKAGE BODY kscope_ptf AS
/*--------------------------------------------------------*/
FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
RETURN DBMS_TF.DESCRIBE_T IS
/*--------------------------------------------------------*/
-- variables for new columns
v_new_col1 DBMS_TF.COLUMN_METADATA_T;
v_new_col2 DBMS_TF.COLUMN_METADATA_T;
-- variables for new column set
v_new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
-- STEP 1 : Tell the cursor about the new columns
-- varchar2(10) named new_col1
v_new_col1 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_VARCHAR2,
name => 'NEW_COL1',
max_len => 10 );
-- number(10) named new_col2
v_new_col2 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_NUMBER,
name => 'NEW_COL2',
max_len => 10 );
-- add to column list
v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1,
2 => v_new_col2 );
-- 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
BEGIN
NULL;
END;
END; SELECT *
FROM kscope_func(kscope)CREATE OR REPLACE PACKAGE BODY kscope_ptf AS
FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
RETURN DBMS_TF.DESCRIBE_T IS
-- variables for new columns
v_new_col1 DBMS_TF.COLUMN_METADATA_T;
v_new_col2 DBMS_TF.COLUMN_METADATA_T;
-- variables for new column set
v_new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
-- varchar2(10) named new_col1
v_new_col1 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_VARCHAR2,
name => 'NEW_COL1',
max_len => 10 );
-- number(10) named new_col2
v_new_col2 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_NUMBER,
name => 'NEW_COL2',
max_len => 10 );
-- add to column list
v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1,
2 => v_new_col2 );
-- 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_vc2_t DBMS_TF.TAB_VARCHAR2_T; -- table of vc2
v_n_t DBMS_TF.TAB_NUMBER_T; -- table of numbers
BEGIN
-- STEP 2 : Populate the columns
-- method 1 - straight put
-- use if column names and structure are guaranteed
/*- - - - - - - - - - - - - - - - - - - - - - - - - - -
Add values using PUT_COL for each column
1) define plsql table (eg: TAB_NUMBER_T)
2) populate values in the plsql table
3) add plsql table to row set
- - - - - - - - - - - - - - - - - - - - - - - - - - -*/
-- environment for this fetch including row count
v_env := DBMS_TF.GET_ENV;
-- for every row in this rowset...
FOR counter IN 1..v_env.row_count LOOP
-- add a numeric value for NEW_COL1 in the row
v_n_t(counter) := 10 * counter;
-- add a vc2 value value for NEW_COL2 in the row
v_vc2_t(counter) := CHR(65 + counter);
END LOOP;
-- add NEW_COL1 to the rowset
DBMS_TF.PUT_COL( columnid => 1,
collection => v_vc2_t);
-- add NEW_COL2 to the rowset
DBMS_TF.PUT_COL( columnid => 2,
collection => v_n_t);
END;
END; SELECT *
FROM kscope_func(kscope)CREATE OR REPLACE PACKAGE BODY kscope_ptf AS
FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
RETURN DBMS_TF.DESCRIBE_T IS
-- variables for new columns
v_new_col1 DBMS_TF.COLUMN_METADATA_T;
v_new_col2 DBMS_TF.COLUMN_METADATA_T;
-- variables for new column set
v_new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
-- varchar2(10) named new_col1
v_new_col1 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_VARCHAR2,
name => 'NEW_COL1',
max_len => 10 );
-- number(10) named new_col2
v_new_col2 := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_NUMBER,
name => 'NEW_COL2',
max_len => 10 );
-- add to column list
v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1,
2 => v_new_col2 );
-- 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_vc2_t DBMS_TF.COLUMN_DATA_T; -- table of columns
v_n_t DBMS_TF.COLUMN_DATA_T; -- table of columns
v_rowset DBMS_TF.ROW_SET_T;
BEGIN
-- STEP 2 : Tell the cursor about the new columns
-- method 2 - use a row set
-- use if you need more control of things and/or want to
-- calculate multiple columns at once
-- eg: v_n_t.tab_number(1) := 10;
-- v_n_t.tab_varchar2(1) := 'ABC';
-- environment for this fetch including row count
v_env := DBMS_TF.GET_ENV;
-- for every row in this rowset...
FOR counter IN 1..v_env.row_count LOOP
-- add a numeric value for NEW_COL1 in the row
v_n_t.tab_number(counter) := 10 * counter;
-- add a vc2 value value for NEW_COL2 in the row
v_vc2_t.tab_varchar2(counter) := CHR(65 + counter);
END LOOP;
v_rowset(1) := v_vc2_t;
v_rowset(2) := v_n_t;
DBMS_TF.PUT_ROW_SET( rowset => v_rowset);
END;
END; SELECT *
FROM kscope_func(kscope)SELECT *
FROM kscope_func(kscope)
WHERE new_col1 = 'C';SELECT *
FROM kscope_func(kscope)
WHERE not_there = 'C';