clear markdown help donate comments/suggestions/bugs a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith?
By using dbfiddle, you agree to license everything you submit by Creative Commons CC0

CREATE PACKAGE skip_col_pkg AS -- OVERLOAD 1: Skip by name FUNCTION skip_col(tab TABLE, col columns) RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg; FUNCTION describe(tab IN OUT dbms_tf.table_t, col dbms_tf.columns_t) RETURN dbms_tf.describe_t; -- OVERLOAD 2: Skip by type -- FUNCTION skip_col(tab TABLE, type_name VARCHAR2, flip VARCHAR2 DEFAULT 'False') RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg; FUNCTION describe(tab IN OUT dbms_tf.table_t, type_name VARCHAR2, flip VARCHAR2 DEFAULT 'False') RETURN dbms_tf.describe_t; END skip_col_pkg; //

CREATE PACKAGE BODY skip_col_pkg AS /* OVERLOAD 1: Skip by name * NAME: skip_col_pkg.skip_col * ALIAS: skip_col_by_name * * PARAMETERS: * tab - The input table * col - The name of the columns to drop from the output * * DESCRIPTION: * This PTF removes all the input columns listed in col from the output * of the PTF. */ FUNCTION describe(tab IN OUT dbms_tf.table_t, col dbms_tf.columns_t) RETURN dbms_tf.describe_t AS new_cols dbms_tf.columns_new_t; col_id PLS_INTEGER := 1; BEGIN FOR i IN 1 .. tab.column.count() LOOP FOR j IN 1 .. col.count() LOOP tab.column(i).pass_through := tab.column(i).description.name != col(j); EXIT WHEN NOT tab.column(i).pass_through; END LOOP; END LOOP; RETURN NULL; END; /* OVERLOAD 2: Skip by type * NAME: skip_col_pkg.skip_col * ALIAS: skip_col_by_type * * PARAMETERS: * tab - Input table * type_name - A string representing the type of columns to skip * flip - 'False' [default] => Match columns with given type_name * otherwise => Ignore columns with given type_name * * DESCRIPTION: * This PTF removes the given type of columns from the given table. */ FUNCTION describe(tab IN OUT dbms_tf.table_t, type_name VARCHAR2, flip VARCHAR2 DEFAULT 'False') RETURN dbms_tf.describe_t AS typ CONSTANT VARCHAR2(1024) := upper(trim(type_name)); BEGIN FOR i IN 1 .. tab.column.count() LOOP tab.column(i).pass_through := CASE upper(substr(flip,1,1)) WHEN 'F' THEN dbms_tf.column_type_name(tab.column(i).description) !=typ ELSE dbms_tf.column_type_name(tab.column(i).description) =typ END /* case */; END LOOP; RETURN NULL; END; END skip_col_pkg; //

CREATE TABLE t AS SELECT 'a' AS col1, 1 AS col2, SYSDATE AS col3, 10.5 AS col4 FROM dual;
1 rows affected

SELECT * FROM t;
COL1 COL2 COL3 COL4
a 1 23-OCT-18 10.5

SELECT * FROM skip_col_pkg.skip_col(t, 'number');
COL1 COL3
a 23-OCT-18