function returning object type and print the attributes
Date: Mon, 18 Jul 2016 14:54:18 +0200
Message-ID: <CAJ2-Qb8Hp9mVPznXc3FkwUNgS+e+ZPtkNEtQ9yuy96bfgydYvw_at_mail.gmail.com>
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,
CREATE OR REPLACE FUNCTION get_ot
PRAGMA AUTONOMOUS_TRANSACTION;
SQL> select
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
(
o2 number
);
/
RETURN ot
IS
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;
/
2 x.z.o1,
3 x.z.o2
4 from (select get_ot z from dual)x;
---------- ----------
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 18 2016 - 14:54:18 CEST