Object constructor function

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Tue, 1 Oct 2013 08:19:56 +1000
Message-ID: <CAC-6Hs0YZgrt48=12odUExev9iJU_LXCLW_mJFALUC+9Oo25LQ_at_mail.gmail.com>

Using 11.2.0.3 ...
I have a scenario where I am storing a json-encoded string in a clob column in a table. I have an object type whose constructor function takes a json-encoded clob. Obviously the parsing of the json string takes some time but on a 'one-object-instantiation-per-row' basis it was acceptable.

However, it seems that Oracle is re-executing the constructor function for every object-type.attribute I reference in a query.

Here's a simple example:

create or replace type t1 as object(

    col1        varchar2(10)


    ,col2       number

    ,col3       date

,constructor function t1(i_txt in varchar2) return self as result
deterministic

    );
/

create or replace type body t1 as

constructor function t1(i_txt in varchar2) return self as result deterministic as

    l_p1        number;
    l_p2        number;

begin

    dbms_output.put_line('In t1 constructor');
    l_p1 := instr(i_txt, '|');
    l_p2 := instr(i_txt, '|', l_p1 + 1);
    self.col1 := substr(i_txt, 1, l_p1 - 1);

    self.col2 := to_number(substr(i_txt, l_p1 + 1, l_p2 - l_p1 - 1));     self.col3 := to_date(substr(i_txt, l_p2 + 1), 'YYYYMMDD');     return;
end;

end;
/

From sqlplus:

SQL> with o as ( select t1('hello|123|20131001') obj from dual )   2 select x.obj.col1, x.obj.col2, x.obj.col3 from o x;

OBJ.COL1                                   OBJ.COL2 OBJ.COL3
---------------------------------------- ---------- ------------------
hello                                           123 01-OCT-13

In t1 constructor
In t1 constructor
In t1 constructor

Does anyone have any ideas how I can prevent this once-per-attribute execution of the constructor?

I even tried materialising the 'with' query but no joy ...

SQL> with o as ( select /*+ materialize */ t1('hello|123|20131001') obj from dual )
  2 select x.obj.col1, x.obj.col2, x.obj.col3 from o x; with o as ( select /*+ materialize */ t1('hello|123|20131001') obj from dual )

                                      *

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-00902: invalid datatype

Thanks,

Steve

-- 


------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email..
------------------------------------------------------------------



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

Received on Tue Oct 01 2013 - 00:19:56 CEST