11 Replies Latest reply: Oct 9, 2013 11:06 AM by gaverill RSS

    Object instantiation in a query

    SteveB

      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
          Billy~Verreynne

          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

            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
              Billy~Verreynne

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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;
                              /