Re: function returning object type and print the attributes

Thanks

That made sense which was rownum doing as well. The sqlplus extra fetch confused even more the matter though because it made the rownum looked like non-working :-)

Thanks again

On Mon, Jul 18, 2016 at 9:28 PM, Nicholas Krasnov <nicholaskrasnov_at_gmail.com
> wrote:

> View merging is the reason why the function gets executed each time an
> object field is being referenced. So, as another workaround use
> /*+no_merge */ hint.
>
> Here the snippet of trace filed made with the 10053 trace event enabled
>
> 1) Without /*+no_merge */ hint
>
> Registered qb: SEL$F5BB74E1 0x147924b8 (VIEW MERGE SEL$1; SEL$2)
>
> query block SEL$1 transformed to SEL$F5BB74E1 (#0)
>
> SELECT SYS_OP_ATG("NK"."F1"(),1,2,2) "S.I1"
> , SYS_OP_ATG("NK"."F1"(),2,3,2) "S.I2" --- we end up
> calling the function as many times as many references to the object's
> FROM "SYS"."DUAL" "DUAL" --- fields we
> make in the select list
>
>
>
> 2) With /*+no_merge*/ hint
>
> final query after transfomation with /*+no_merge*/ hint
>
> SELECT "X"."S"."I1" "S.I1"
> , "X"."S"."I2" "S.I2"
> FROM (SELECT /*+ NO_MERGE */ "NK"."F1"() "S"
> FROM "SYS"."DUAL" "DUAL") "X"
>
> The function is being called only once no matter how many times we
> reference object's fields in the select list.
>
> On Mon, Jul 18, 2016 at 3:54 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
>> 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
>>
>>
>>
>
>
> --
> Best regards,
> Nicholas Krasnov
>

--
http://www.freelists.org/webpage/oracle-l