This discussion is archived
11 Replies Latest reply: Oct 9, 2013 9:06 AM by gaverill RSS

Object instantiation in a query

SteveB Newbie
Currently Being Moderated

I don't know whether or not this is possible but ...

 

I have a table that has a CLOB column.  I have an Oracle type defined that has a Constructor function that takes a CLOB argument.

 

I want to be able to do something like this:

 

with t as (

    select  id_col

            ,my_type(clob_col) data

    from    some_table

    )

select  id_col

        ,data.attrib_1

        ,data.attrib_2

from    t

 

When I try this I get "ORA-00904: "DATA"."ATTRIB_1": invalid identifier" even though attrib_1 is defined as an attribute of my_type.

 

The closest I have got is:

 

with t as (

    select  id_col

            ,my_type(clob_col) data

    from    some_table

    )

select  id_col

        ,treat(data as my_type).attrib_1 attrib_1

        ,treat(data as my_type).attrib_2 attrib_2

from    t

 

However this executes the constructor function (in this case) twice per row.  I really don't want to incur that overhead but had hoped to somehow instantiate the object once per row and then access attributes of that object without incurring any additional overhead.

 

Here's something else I don't understand.  I have added a dbms_output.put_line statement to my constructor so that I can see when it gets called.  If I do something like:

 

(Assume some_table has 1 row)

 

select id_col, my_type(clob_col) from some_table

 

The constructor gets executed once - which is what I expect.

 

If however I do:

 

create table another_table as select id_col, my_type(clob_col) as data from some_table

 

The constructor gets executed 11 times even though some_table has only 1 row !!  (my_type has 10 attributes in case it is relevant)

 

The database version is 11.2.0.3.

 

Any help much appreciated.

  • 1. Re: Object instantiation in a query
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Aliasing is needed for explicit scope referencing when dealing with properties and member methods of classes.

     

    Example:

    SQL> create or replace type TMyDocument as object(
      2          contents        clob,
      3          mime_type       varchar2(10),
      4  
      5          static function ClassName return varchar2,
      6          constructor function TMyDocument( c clob ) return self as result,
      7          member function Length return integer
      8  );
      9  /
    Type created.
    SQL> 
    SQL> create or replace type body TMyDocument as
      2  
      3          static function ClassName return varchar2 is
      4          begin
      5                  return( $$PLSQL_UNIT );
      6          end;
      7  
      8          constructor function TMyDocument( c clob ) return self as result is
      9          begin
     10                  self.contents := c;
     11                  self.mime_type := 'text/plain';
     12                  return;
     13          end;
     14  
     15          member function Length return integer is
     16          begin
     17                  return( DBMS_LOB.GetLength(self.contents) );
     18          end;
     19  end;
     20  /
    Type body created.
    SQL> 
    SQL> with obj(id,my_document) as(
      2          select
      3                  1,
      4                  TMyDocument( 'some text' )
      5          from    dual
      6  )
      7  select
      8          TMyDocument.ClassName() as CLASS,
      9          o.id,
     10          o.my_document.mime_type as MIME_TYPE,
     11          o.my_document.length()  as LENGTH,
     12          o.my_document.contents  as CONTENTS
     13  from       obj o;
    CLASS                        ID MIME_TYPE      LENGTH CONTENTS
    -------------------- ---------- ---------- ---------- --------------------
    TMYDOCUMENT                   1 text/plain          9 some text
    SQL>
  • 2. Re: Object instantiation in a query
    SteveB Newbie
    Currently Being Moderated

    Thanks a lot for that.

     

    However, try putting a dbms_output.put_line statement in your constructor.

     

    You will see that it gets executed 3 times - which is what I was hoping to avoid.

     

    Do you think this is a bug?

     

    Thanks !!

  • 3. Re: Object instantiation in a query
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    No bug- I think it is caused by context switching.

     

    SQL is one language. PL/SQL is another language (PL language that understands SQL source code and can change this into calls to the SQL engine). E.g. Java/SQL would be Java language where you do not need to use Java SQL/Db classes, but instead write native SQL in Java - where the Java parser and compiler recognise these SQL statements and do all the funky stuff of turning that native SQL statement in the Java code into a call to the database.

     

    Classes in Oracle spans both SQL and PL/SQL languages. The class is defined in SQL. Its implementation (methods, constructors, class methods) are implemented in PL/SQL.

     

    So when you make, for example, a reference in a SQL projection to an object's method, that call needs to switch context from the SQL engine to the PL/SQL engine, in order to access/execute that method.

     

    The object resides in SQL engine memory. For the PL/SQL engine to execute the code, it needs that object in its local memory. This requires the object to be "copied" from one engine to the other as part of the context switch - which is done via that object's class constructor.

     

    The behaviour when testing the code (and changing the SQL projection), seems to confirm this.

     

    12c has even tighter integration between SQL and PL/SQL engines, so perhaps behaviour is different. But for 11g, it seems to me that context switching is the cause of the multiple constructor calls.

  • 4. Re: Object instantiation in a query
    SteveB Newbie
    Currently Being Moderated

    Thanks again Billy.

     

    You could well be correct as to the reason.  As to whether or not this is a bug - I have to say I'm not convinced .

     

    Consider the following script:

     

    drop table object_table;

    drop type t_obj_a;

    drop type t_obj;

     

     

    create or replace type t_obj force as object(

        dummy   varchar2(10)

    )

    not instantiable not final

    ;

    /

     

     

    create or replace type t_obj_a force under t_obj(

        attr1       varchar2(30)

        ,constructor function t_obj_a(i_attr1 varchar2) return self as result

    );

    /

     

     

    create or replace type body t_obj_a as

        constructor function t_obj_a(i_attr1 varchar2) return self as result as

        begin

            self.attr1 := i_attr1;

            dbms_output.put_line('constructor t_obj_a');

            return;

        end;

    end;

    /

     

     

    create table object_table(

        object_type     varchar2(1)

        ,object         t_obj

    );

     

     

    set echo on serveroutput on size unlimited

    --

    -- Insert from SQL

    --

    insert

    into    object_table

    values  ('a', t_obj_a('hello'));

     

     

    --

    -- Insert from PL/SQL

    --

    declare

        l_obj_a     t_obj_a := t_obj_a('hello');

    begin

        insert

        into    object_table

        values  ('a', l_obj_a)

        ;

    end;

    /

     

    When I execute it from sqlplus, it shows the constructor function for t_obj_a being executed 4 times for the insert from SQL and once for the execution from PL/SQL.

     

    Any idea as to why 4 times?

  • 5. Re: Object instantiation in a query
    odie_63 Guru
    Currently Being Moderated

    I think this behaviour is linked to the way Oracle implements Object-Relational storage, i.e. using hidden columns to store object attributes :

     

    SQL> select column_name, data_type, qualified_col_name, hidden_column

      2  from user_tab_cols v

      3  where table_name = 'OBJECT_TABLE';

     

    COLUMN_NAME      DATA_TYPE      QUALIFIED_COL_NAME                       HIDDEN_COLUMN

    ---------------- -------------- ---------------------------------------- -------------

    OBJECT_TYPE      VARCHAR2       OBJECT_TYPE                              NO

    OBJECT           T_OBJ          OBJECT                                   NO

    SYS_NC00003$     RAW            SYS_TYPEID("OBJECT")                     YES

    SYS_NC00004$     VARCHAR2       "OBJECT"."DUMMY"                         YES

    SYS_NC00005$     VARCHAR2       TREAT("OBJECT" AS "T_OBJ_A")."ATTR1"     YES

     

    So when the object is built and inserted by the SQL engine, Oracle maps attributes to columns and call the constructor as many times. The count of 4 is strange indeed but maybe that's because it's a subtype.

     

    To prevent the call overhead, you can declare the constructor deterministic :

    constructor function t_obj_a(i_attr1 varchar2) return self as result deterministic
  • 6. Re: Object instantiation in a query
    SteveB Newbie
    Currently Being Moderated

    Thanks for the idea of using a deterministic constructor.  Unfortunately as far as I can tell it doesn't prevent multiple invocations.  For 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;

    /

     

    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

     

    In the above example the constructor function is trivial but in my 'real world' scenario the constructor function does quite a bit of work so I really want to if possible stop it being re-executed for every attribute.

     

    Thanks again.

     

    Steve

  • 7. Re: Object instantiation in a query
    SteveB Newbie
    Currently Being Moderated

    Sorry for replying to my own post but I think I may have made some progress.

     

    To the object definitions from my previous post I have added the following:

     

    create or replace type t1_t as table of t1;

    /

     

    create or replace package p as

        type t_csr is ref cursor;

    end p;

    /

     

    create or replace function t1_f(i_csr in p.t_csr) return t1_t pipelined as

        l_txt           varchar2(4000);

        l_t1            t1;

    begin

        loop

            fetch i_csr into l_txt;

            exit when i_csr%notfound;

            l_t1 := t1(l_txt);

            pipe row( l_t1 );

        end loop;

        return;

    end;

    /

     

    My select now looks like this:

     

    SQL> select t.col1, t.col2, t.col3 from table(t1_f(cursor(select 'hello|123|20131001' from dual))) t;


    COL1             COL2 COL3

    ---------- ---------- ------------------

    hello             123 01-OCT-13

     

    In t1 constructor

     

    To test for multiple rows:

     

    SQL> select t.col1, t.col2, t.col3 from table(t1_f(cursor(select 'hello|123|20131001' from dual connect by level <= 5))) t;

     

    COL1             COL2 COL3

    ---------- ---------- ------------------

    hello             123 01-OCT-13

    hello             123 01-OCT-13

    hello             123 01-OCT-13

    hello             123 01-OCT-13

    hello             123 01-OCT-13

     

    In t1 constructor

    In t1 constructor

    In t1 constructor

    In t1 constructor

    In t1 constructor

     

    It's a little clunky but I think I can make it work.

  • 8. Re: Object instantiation in a query
    gaverill Journeyer
    Currently Being Moderated

    Throwing ROWNUM into the mix seems to have the desired effect (at least with this last example, against the DUAL table). I'll leave it to the gurus to explain why (it would seem to materialize the view)...

     

    with    o
    as    (
        select    t1('hello|123|20131001') obj
            from    dual
            where    rownum >= 1
            )
    select    x.obj.col1
    ,    x.obj.col2
    ,    x.obj.col3
    from    o x
    ;
    
    

     

    OBJ.COL1    OBJ.COL2    OBJ.COL3
    hello             123              2013-10-01
    In t1 constructor
    
    

     

    Perhaps you can see what effect this has in your actual case.

     

    Gerard

  • 9. Re: Object instantiation in a query
    SteveB Newbie
    Currently Being Moderated

    Thanks Gerard.  Maybe it is version or port specific.  When I execute your example I get the constructor executed 3 times:

     

    SQL> r

      1  with    o

      2  as    (

      3      select    t1('hello|123|20131001') obj

      4              from    dual

      5                      where    rownum >= 1

      6                              )

      7  select    x.obj.col1

      8  ,    x.obj.col2

      9  ,    x.obj.col3

    10* from    o x

     

     

    OBJ.COL1                                   OBJ.COL2 OBJ.COL3

    ---------------------------------------- ---------- ------------------

    hello                                           123 01-OCT-13

     

     

    In t1 constructor

    In t1 constructor

    In t1 constructor

     

    Steve

  • 10. Re: Object instantiation in a query
    gaverill Journeyer
    Currently Being Moderated

    That could be...

     

    SQL> select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production

     

    Gerard

  • 11. Re: Object instantiation in a query
    gaverill Journeyer
    Currently Being Moderated

    I have a utility package, SESSION_CACHE (at end), that I sometimes use for quick-and-dirty caching solutions. Modifying the example to make use of it:

     

    create or replace type t1_cache_entry under TCacheEntry(
        cached_instance t1
    )
    /
    create or replace type body t1 as
    constructor function t1(i_txt in varchar2) return self as result deterministic as
        g_cache     constant varchar2(200)    := 't1 session cache';
        l_key       varchar2(2000);
        l_entry     t1_cache_entry;
        l_p1        number;
        l_p2        number;
    begin
        dbms_output.put_line('In t1 constructor');
        l_key := substr(i_txt, 1, 2000);                                          -- Choose an appropriate cache key
        l_entry := treat(SESSION_CACHE.get(g_cache, i_txt) as t1_cache_entry);
        
        if l_entry is not null then
            dbms_output.put_line('Instance fetched from cache');
            self := l_entry.cached_instance;
            return;
        end if;
        
        dbms_output.put_line('Constructing instance');
        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');
        l_entry := new t1_cache_entry(null, self);
        SESSION_CACHE.put(g_cache, l_key, l_entry);
        return;
    end; 
    end;
    /

     

    Then, although the constructor is still called multiple time, the instance is fetched from the cache after it's initial instantiation:

     

    SQL> exec SESSION_CACHE.flush('t1 session cache');
    PL/SQL procedure successfully completed.
    SQL>
    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
    Constructing instance
    In t1 constructor
    Instance fetched from cache
    In t1 constructor
    Instance fetched from cache
    SQL>

     

    For what it's worth...

     

    Gerard

     

    The SESSION_CACHE package and related types:

     

    create or replace type TCacheEntry
    is object
    (
        -- Attributes
        lastAccessed        date
    )
    not final
    /
    create or replace type TCacheEntryTable
    is table of TCacheEntry
    /
    create or replace type TCacheNameTable
    is table of varchar2(200)
    /
    create or replace type TCacheKeyTable
    is table of varchar2(200)
    /
    create or replace package SESSION_CACHE
    authid definer
    is
        -- Types
        subtype TCacheName            is varchar2(200);
        subtype TCacheKey            is varchar2(2000);
        -- Routines
        function isEnabled(
            aCacheName    in        TCacheName
        )
        return boolean;
        procedure setEnabled(
            aCacheName    in        TCacheName
        ,    aEnabled    in        boolean
        );
        function get(
            aCacheName    in        TCacheName
        ,    aKey        in        TCacheKey
        )
        return TCacheEntry
        deterministic;
        procedure put(
            aCacheName    in        TCacheName
        ,    aKey        in        TCacheKey
        ,    aEntry        in        TCacheEntry
        );
        function pipeNames
        return TCacheNameTable
        deterministic
        pipelined;
        function pipeKeys(
            aCacheName    in        TCacheName
        )
        return TCacheKeyTable
        deterministic
        pipelined;
        function pipeEntries(
            aCacheNames    in        TCacheNameTable        := null
        ,    aKeys        in        TCacheKeyTable        := null
        )
        return TCacheEntryTable
        deterministic
        pipelined;
        procedure flush(
            aCacheName    in        TCacheName
        ,    aKey        in        TCacheKey        := null
        );
    end;
    /
    create or replace package body SESSION_CACHE
    is
        -- Private types
        type TPkgEntryTable    is table of TCacheEntry index by TCacheKey;
        type RPkgCache        is record
        (
            enabled        boolean
        ,    entries        TPkgEntryTable
        );
        type TPkgCacheTable    is table of RPkgCache index by TCacheName;
        -- Private variables
        EMPTY_ENTRIES        TPkgEntryTable;
        caches            TPkgCacheTable;
        -- Procedures
        function isEnabled(
            aCacheName    in        TCacheName
        )
        return boolean
        is
            cache        RPkgCache;
        begin
            if not caches.exists(aCacheName) then
                cache.enabled        := true;
                cache.entries        := EMPTY_ENTRIES;
                caches(aCacheName)    := cache;
            end if;
            return    caches(aCacheName).enabled;
        end;
        procedure setEnabled(
            aCacheName    in        TCacheName
        ,    aEnabled    in        boolean
        )
        is
            enabled        boolean        := nvl(aEnabled, true);
        begin
            if isEnabled(aCacheName) <> enabled then
                caches(aCacheName).enabled := enabled;
            end if;
        end;
        function get(
            aCacheName    in        TCacheName
        ,    aKey        in        TCacheKey
        )
        return TCacheEntry
        is
            result        TCacheEntry;
        begin
            if isEnabled(aCacheName) and caches(aCacheName).entries.exists(aKey) then
                caches(aCacheName).entries(aKey).lastAccessed := sysdate;
                result := caches(aCacheName).entries(aKey);
            end if;
            return    result;
        end;
        procedure put(
            aCacheName    in        TCacheName
        ,    aKey        in        TCacheKey
        ,    aEntry        in        TCacheEntry
        )
        is
        begin
            if isEnabled(aCacheName) then
                caches(aCacheName).entries(aKey)        := aEntry;
                caches(aCacheName).entries(aKey).lastAccessed    := null;
            end if;
        end;
        function pipeNames
        return TCacheNameTable
        pipelined
        is
            result    TCacheName    := caches.first;
        begin
            while result is not null
            loop
                pipe    row (result);
                result := caches.next(result);
            end loop;
            return;
        end;
        function pipeKeys(
            aCacheName    in        TCacheName
        )
        return TCacheKeyTable
        pipelined
        is
            result    TCacheKey    := case when caches.exists(aCacheName) then caches(aCacheName).entries.first end;
        begin
            while result is not null
            loop
                pipe    row (result);
                result := caches(aCacheName).entries.next(result);
            end loop;
            return;
        end;
        function pipeEntries(
            aCacheNames    in        TCacheNameTable        := null
        ,    aKeys        in        TCacheKeyTable        := null
        )
        return TCacheEntryTable
        pipelined
        is
        begin
            for i in (
                select    value(n) cacheName
                from    table(aCacheNames) n
                union    all
                select    value(n)
                from    table(SESSION_CACHE.pipeNames()) n
                where    aCacheNames is null
            )
            loop
                for j in (
                    select    value(k) cacheKey
                    from    table(aKeys) k
                    union    all
                    select    value(k)
                    from    table(SESSION_CACHE.pipeKeys(i.cacheName)) k
                    where    aKeys is null
                )
                loop
                    pipe    row (caches(i.cacheName).entries(j.cacheKey));
                end loop;
            end loop;
            return;
        end;
        procedure flush(
            aCacheName    in        TCacheName
        ,    aKey        in        TCacheKey        := null
        )
        is
        begin
            if caches.exists(aCacheName) then
                if aKey is null then
                    caches(aCacheName).entries := EMPTY_ENTRIES;
                else
                    caches(aCacheName).entries.delete(aKey);
                end if;
            end if;
        end;
    end;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points