exec dbms_output.enable(1000000);
SET SERVEROUTPUT ON
CREATE OR REPLACE
TYPE table_rec FORCE AS OBJECT (
person_code number(10)
,visa_type varchar2(40)
,prm number(10)
);
/
CREATE OR REPLACE
TYPE my_table FORCE AS table of table_rec;
/
CREATE OR REPLACE FUNCTION get_table_rows(
p_person_code IN NUMBER
) RETURN my_table PIPELINED AS
v_my_table my_table;
BEGIN
SELECT table_rec(TBL.person_code
,TBL.visa_type
,TBL.p_person_code
)
BULK COLLECT INTO v_my_table
FROM
(SELECT person_code
,visa_type
,p_person_code
FROM visas
where person_code = p_person_code
) TBL;
FOR i IN v_my_table.FIRST .. v_my_table.LAST LOOP
PIPE ROW(v_my_table(i));
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
END get_table_rows;
/
The script below runs this function:
DECLARE
CURSOR C IS select person_code
,registration_no
,forename
,surname
from table(get_table_rows(182));
l_rec C%ROWTYPE;
l_start date;
l_finish date;
BEGIN
l_start := sysdate;
OPEN C;
LOOP
FETCH C INTO l_rec;
DBMS_OUTPUT.PUT_LINE(l_rec.person_code);
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE C;
l_finish := sysdate;
dbms_output.put_line('Time difference = ' || (l_finish - l_start) * (24 * 60 * 60));
END;
If you find this script useful please donate generously.
