PLSQL pipelining tests

1-basic-record.sql

/**
 * Basic usage of types, records and pipelining
 */
CREATE OR REPLACE PACKAGE gt_test AS
  TYPE gt_mav IS RECORD(code varchar2(9), year varchar2(7));
  TYPE gt_table IS TABLE of gt_mav;
    
  FUNCTION tab_test(i_mod_code VARCHAR) RETURN gt_table PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY gt_test AS
  FUNCTION tab_test(i_mod_code VARCHAR)
    RETURN gt_table
  PIPELINED IS
    mav gt_mav;
  BEGIN
    FOR r in (
      SELECT mod_code, ayr_code
      FROM sits.cam_mav
      WHERE mod_code = i_mod_code
    )
    LOOP
      mav.code := r.mod_code;
      mav.year := r.ayr_code;
      PIPE ROW(mav);
    END LOOP;

    RETURN;
  end tab_test;
END;
/
SELECT *
FROM TABLE(gt_test.tab_test(i_mod_code => 'ELCH10005'));

2-basic-varchar.sql

/**
 * This is simpler, because it doesn't use records. Instead it just
 * returns a table of varchars which are stuffed with comma-separated
 * data.
 * 
 * Note the use of "nvarchar2" in the table to match the type on the
 * fields of cam_mav.
 */
CREATE OR REPLACE PACKAGE gt_test AS
  TYPE gt_table IS TABLE of nvarchar2(100);
    
  FUNCTION tab_test(i_mod_code VARCHAR) RETURN gt_table PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY gt_test AS
  FUNCTION tab_test(i_mod_code VARCHAR)
    RETURN gt_table
  PIPELINED IS
  BEGIN
    FOR r in (
      SELECT mod_code || ',' || ayr_code mav_data
      FROM sits.cam_mav
      WHERE mod_code = i_mod_code
    )
    LOOP
      PIPE ROW(r.mav_data);
    END LOOP;

    RETURN;
  end tab_test;
END;
/
SELECT *
FROM TABLE(gt_test.tab_test(i_mod_code => 'ELCH10005'));