Use SYS_REFCURSOR as parameter type
SQL>
SQL>
SQL> CREATE TABLE orders( order_number NUMBER,
2 create_date DATE,
3 assign_date DATE,
4 close_date DATE);
SQL>
SQL> BEGIN
2 FOR counter IN 1..3 LOOP
3 INSERT INTO orders
4 VALUES(counter,
5 SYSDATE,
6 SYSDATE + 1,
7 SYSDATE + 2);
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> CREATE OR REPLACE TYPE order_date_o AS OBJECT ( order_number NUMBER,
2 date_type VARCHAR2(1),
3 year NUMBER,
4 quarter NUMBER,
5 month NUMBER );
6 /
SQL> CREATE TYPE order_date_t AS TABLE OF order_date_o;
2 /
SQL>
SQL> CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURSOR )
2 RETURN order_date_t AS
3 v_order_rec orders%ROWTYPE;
4 v_ret_val order_date_t := order_date_t( );
5
6 BEGIN
7 LOOP
8
9 FETCH p_curs INTO v_order_rec;
10 EXIT WHEN p_curs%NOTFOUND;
11 v_ret_val.EXTEND(3);
12 v_ret_val(v_ret_val.LAST - 2) := order_date_o(v_order_rec.order_number,'O',
13 TO_CHAR(v_order_rec.create_date,'YYYY'),
14 TO_CHAR(v_order_rec.create_date,'Q'),
15 TO_CHAR(v_order_rec.create_date,'MM'));
16 v_ret_val(v_ret_val.LAST - 1) := order_date_o(v_order_rec.order_number,'A',
17 TO_CHAR(v_order_rec.assign_date,'YYYY'),
18 TO_CHAR(v_order_rec.assign_date,'Q'),
19 TO_CHAR(v_order_rec.assign_date,'MM'));
20 v_ret_val(v_ret_val.LAST) := order_date_o(v_order_rec.order_number,'C',
21 TO_CHAR(v_order_rec.close_date,'YYYY'),
22 TO_CHAR(v_order_rec.close_date,'Q'),
23 TO_CHAR(v_order_rec.close_date,'MM'));
24 END LOOP;
25
26 RETURN(v_ret_val);
27
28 END;
29 /
Function created.
SQL>
SQL> SELECT *
2 FROM TABLE(date_PARSE(CURSOR(SELECT * FROM orders)))
3 /
ORDER_NUMBER D YEAR QUARTER MONTH
------------ - ---------- ---------- ----------
1 O 2008 2 6
1 A 2008 2 6
1 C 2008 2 6
2 O 2008 2 6
2 A 2008 2 6
2 C 2008 2 6
3 O 2008 2 6
3 A 2008 2 6
3 C 2008 2 6
1 O 2008 2 6
1 A 2008 2 6
1 C 2008 2 6
2 O 2008 2 6
2 A 2008 2 6
2 C 2008 2 6
3 O 2008 2 6
3 A 2008 2 6
3 C 2008 2 6
18 rows selected.
SQL>
SQL>
Related examples in the same category