Hi
One of my developer wrote a function to return an object type, composed of
2 attributes, he then print the attribute in a query.
It looks like this (example code):
CREATE OR REPLACE TYPE ot AS OBJECT
(
o1 number,
o2 number
);
/
CREATE OR REPLACE FUNCTION get_ot
RETURN ot
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_ot ot;
BEGIN
l_ot := ot(NULL, NULL);
SELECT 1, 2
INTO l_ot.o1, l_ot.o2
FROM dual;
INSERT into t1 values (systimestamp);
commit;
RETURN l_ot;
END get_ot;
/
SQL> select
2 x.z.o1,
3 x.z.o2
4 from (select get_ot z from dual)x;
Z.O1 Z.O2
---------- ----------
1 2
It turns out that the function is execute twice, once per attribute. I
wonder if there is any other more efficient way to accomplish this?
This is going to return a row only so I dont think we need to dig into
plsqsl table functions.
Thanks
