Refactoring PL/SQL Table Function demo

"Refactoring is the process of changing a software system in such a way that it does not alter the external behavior of the code yet improves its internal structure." -- Martin Fowler in Refactoring: Improving The Design Of Existing Code

Here is an example how I build PL/SQL table function usually.

Before refactoring the code, we have to write the SQL in 2 places, it is a repetition.

create or replace PACKAGE demo_pkg
AS
  CURSOR gc_data IS select 1 col1, 2 col2 from dual;
  type data_tab is table of gc_data%RowType;
  FUNCTION f1(p_a number)
    RETURN data_tab PIPELINED;
end;
/

CREATE OR REPLACE PACKAGE BODY demo_pkg
AS
  FUNCTION f1(p_a number)
    RETURN data_tab PIPELINED
  is
  BEGIN
    FOR lr in (select 1 col1, 2 col2 from dual where p_a > 5)
    LOOP
      pipe ROW(lr);
    END LOOP;
    RETURN;
  EXCEPTION
  WHEN OTHERS THEN
    /* Don't forget to clean up here, too! */
    /* Standard error logging mechanism */
    --logger.log_error('Unhandled Exception', l_scope);
    raise;
  END;
end;
/

After refactoring the code, we only need to write the SQL(cursor) one time,  removed unnecessary repetition.

create or replace PACKAGE demo_pkg
AS
  CURSOR gc_data (p_a number) IS
    select 1 col1, 2 col2, 3 col3 from dual where p_a > 5;
  type data_tab is table of gc_data%RowType;
  FUNCTION f1(p_a number)
    RETURN data_tab PIPELINED;
end;
/

CREATE OR REPLACE PACKAGE BODY demo_pkg
AS
  FUNCTION f1(p_a number)
    RETURN data_tab PIPELINED
  is
    lr gc_data%rowtype;
  BEGIN
    OPEN gc_data(p_a);
    LOOP
      FETCH gc_data INTO lr;
      pipe ROW(lr);
      EXIT WHEN gc_data%NotFound;
    END LOOP;
    close gc_data;
    RETURN;
  EXCEPTION
  WHEN OTHERS THEN
    /* Don't forget to clean up here, too! */
    /* Standard error logging mechanism */
    --logger.log_error('Unhandled Exception', l_scope);
    raise;
  END;
end;
/

Observed:

This makes the output DRY, but for input, if we add/update/remove parameters, we have to update 2 places.

Reference:

1) http://www.refactoring.com/
2) call example: select col1, col2 from table(demo_pkg.f1(18));