-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TYPE t1_row_type AS OBJECT (
    f1 INTEGER,
    f2 VARCHAR2(50)
);
/
CREATE TYPE t1_table_type AS TABLE OF t1_row_type;
/
CREATE FUNCTION get_interval_t1 (
  first_f1 IN INTEGER,
  last_f1 IN INTEGER
) RETURN t1_table_type AS res t1_table_type := t1_table_type();
BEGIN
    FOR cur IN (SELECT f1,f2 FROM t1 WHERE f1 BETWEEN first_f1 AND last_f1)
    LOOP
        res.EXTEND;
        res(res.LAST) := t1_row_type(cur.f1, cur.f2);
    END LOOP;
    RETURN res;
END;
/
SELECT * FROM TABLE(get_interval_t1(1,2));
(SELECT * FROM TABLE(get_interval_t1(1,2)))
UNION
(SELECT * FROM TABLE(get_interval_t1(2,3)));
DROP FUNCTION get_interval_t1;
CREATE FUNCTION get_interval_t1 (
  first_f1 IN INTEGER,
  last_f1 IN INTEGER
) RETURN t1_table_type PIPELINED AS
BEGIN
    FOR cur IN (SELECT f1,f2 FROM t1 WHERE f1 BETWEEN first_f1 AND last_f1)
    LOOP
        PIPE ROW(t1_row_type(cur.f1, cur.f2));
    END LOOP;
    RETURN;
END;
/
SELECT * FROM TABLE(get_interval_t1(1,2));
(SELECT * FROM TABLE(get_interval_t1(1,2)))
UNION
(SELECT * FROM TABLE(get_interval_t1(2,3)));
DROP FUNCTION get_interval_t1;
DROP TYPE t1_table_type;
DROP TYPE t1_row_type;
