Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create package pkg_nvl as
  /*Package to implement PTF*/

  function describe(
    tab in out dbms_tf.table_t
  ) return dbms_tf.describe_t
  ;
  
  procedure fetch_rows;
end pkg_nvl;
/

create package body pkg_nvl as

  function describe(
    tab in out dbms_tf.table_t
  ) return dbms_tf.describe_t
  as
    modif_cols dbms_tf.columns_new_t;
    new_col_cnt pls_integer := 0;
  begin
    /*Mark input columns as used and as modifiable for subsequent row processing*/
    for i in 1..tab.column.count loop
      if tab.column(i).description.type in (
        dbms_tf.type_number,
        dbms_tf.type_varchar2
      ) then
        /*Modifiable*/
        tab.column(i).pass_through := FALSE;
        /*Used in the PTF context*/
        tab.column(i).for_read := TRUE;
        /* Propagate column to the modified*/
        modif_cols(new_col_cnt) := tab.column(i).description;
        new_col_cnt := new_col_cnt + 1;
      end if;
    end loop;
    
    /*Return the list of modified cols*/
    return dbms_tf.describe_t(
      new_columns => modif_cols
    );
  end;
  
  procedure fetch_rows
  /*Process rowset and replace nulls*/
  as
    rowset dbms_tf.row_set_t;
    num_rows pls_integer;
    in_col_vc2 dbms_tf.tab_varchar2_t;
    in_col_num dbms_tf.tab_number_t;
    new_col_vc2 dbms_tf.tab_varchar2_t;
    new_col_num dbms_tf.tab_number_t;
  begin
    /*Get rows*/
    dbms_tf.get_row_set(
      rowset => rowset,
      row_count => num_rows
    );
    
    
    for col_num in 1..rowset.count() loop
    /*Loop through the columns*/
      for rn in 1..num_rows loop
        /*Calculate new values in the same row*/
        /*Get column by index and nvl the value for return column*/
        if rowset(col_num).description.type = dbms_tf.type_number then
          dbms_tf.get_col(
            columnid => col_num,
            collection => in_col_num
          );
          new_col_num(rn) := nvl(in_col_num(rn), -1);
        elsif rowset(col_num).description.type = dbms_tf.type_varchar2 then
          dbms_tf.get_col(
            columnid => col_num,
            collection => in_col_vc2
          );
          new_col_vc2(rn) := nvl(in_col_vc2(rn), 'tn/a');
        end if;
      end loop;

      /*Put the modified column to the result*/
      if rowset(col_num).description.type = dbms_tf.type_number then
        dbms_tf.put_col(
          columnid => col_num,
          collection => new_col_num
        );
      elsif rowset(col_num).description.type = dbms_tf.type_varchar2 then
        dbms_tf.put_col(
          columnid => col_num,
          collection => new_col_vc2
        );
      end if;
    end loop;

  end;
end pkg_nvl;
/
create function f_replace_nulls(tab in table)
/*Function to replace nulls using PTF*/
return table pipelined
row polymorphic using pkg_nvl;
/
/* Formatted on 27/06/2022 07:50:12 (QP5 v5.374) */
WITH
    a(aa1,aa2,aa3)
    AS
        (SELECT 1, '2', SYSDATE FROM DUAL
         UNION ALL
         SELECT NULL, NULL, NULL FROM DUAL)
SELECT *
  FROM TABLE (f_replace_nulls(a))


AA3
AA1
AA2


27-JUN-22
1
2

null
-1
t