1 Reply Latest reply on Aug 7, 2017 2:51 AM by gaverill

    Complex object view identifiers and Oracle metadata

    gaverill

      I was playing around with various approaches to defining object views and realized there is more flexibility in defining object identifiers than previously thought.

       

      I've known  (and, IIRC, seen it somewhere mentioned in the documentation) that Oracle allows for nested attributes to be specified in the "with object identifier" clause. For example, for the given object types...

       

      Edit: Oracle version 12.1

       

      create or replace type OMyEmbeddedObject
      is object
      (
          id        integer
      ,
          member function getId
          return integer
          deterministic
      )
      not final;
      
      create or replace type body OMyEmbeddedObject
      is
          member function getId
          return integer
          is
          begin
              return    id;
          end;
      end;
      
      create or replace type OMyObject
      is object
      (
          embedded    OMyEmbeddedObject
      );
      

       

      ... one can define an object view using the embedded object's id as the view's identifier, and query from that view for a referenced object...

       

      create or replace view V_MY_OBJECT
      of OMyObject
      with object identifier (embedded.id)
      as
      select    OMyObject(
              new OMyEmbeddedObject(
                  1
              )
          )
      from    DUAL;
      
      select    value(o)
      from    V_MY_OBJECT o
      where    ref(o) = make_ref(V_MY_OBJECT, 1);
      
      O(EMBEDDED(ID))
      --------------------------------------------------------------------------------
      OMYOBJECT(OMYEMBEDDEDOBJECT(1))
      

       

      Recently I wondered how one would access an embedded attribute of a subtype, not thinking that use of the "treat" function would be allowed. However, upon experimenting, that assumption proved to be wrong...

       

      create or replace type OMyEmbeddedObject2
      under OMyEmbeddedObject
      (
          id2        integer
      ,
          overriding member function getId
          return integer
          deterministic
      );
      
      create or replace type body OMyEmbeddedObject2
      is
          overriding member function getId
          return integer
          is
          begin
              return    (self as OMyEmbeddedObject).getId() + id2;
          end;
      end;
      
      create or replace view V_MY_OBJECT_2
      of OMyObject
      with object identifier (treat(embedded as OMyEmbeddedObject2).id2)
      as
      select    OMyObject(
              new OMyEmbeddedObject2(
                  1
              ,    2
              )
          )
      from    DUAL;
      
      select    value(o) o
      from    V_MY_OBJECT_2 o
      where    ref(o) = make_ref(V_MY_OBJECT_2, 2);
      
      O(EMBEDDED(ID))
      --------------------------------------------------------------------------------
      OMYOBJECT(OMYEMBEDDEDOBJECT2(1, 2))
      

       

      Playing some more, it seems an object identifier "attribute" can also include an embedded object function method result...

       

      create or replace view V_MY_OBJECT_3
      of OMyObject
      with object identifier (treat(embedded as OMyEmbeddedObject2).getId())
      as
      select    OMyObject(
              new OMyEmbeddedObject2(
                  1
              ,    2
              )
          )
      from    DUAL;
      
      select    value(o) o
      from    V_MY_OBJECT_3 o
      where    ref(o) = make_ref(V_MY_OBJECT_3, 3);
      
      O(EMBEDDED(ID))
      --------------------------------------------------------------------------------
      OMYOBJECT(OMYEMBEDDEDOBJECT2(1, 2))
      

       

      In fact, it seems that it can be almost any expression, even a literal...

       

      create or replace view V_MY_OBJECT_4
      of OMyObject
      with object identifier (ltrim(to_char(4, '09')))
      as
      select    OMyObject(
              new OMyEmbeddedObject2(
                  1
              ,    3
              )
          )
      from    DUAL;
      
      select    value(o) o
      from    V_MY_OBJECT_4 o
      where    ref(o) = make_ref(V_MY_OBJECT_4, '04');
      
      O(EMBEDDED(ID))
      --------------------------------------------------------------------------------
      OMYOBJECT(OMYEMBEDDEDOBJECT2(1, 3))
      

       

      Now, as far as I can tell the only meta-data Oracle provides about object view identifiers is in DBA|ALL|USER_VIEWS, where it simply provides the entire text the object identifier clause...

       

      select    view_name
      ,    oid_text
      from    SYS.USER_VIEWS v
      where    view_name like 'V_MY_OBJECT%';
      
      VIEW_NAME                      OID_TEXT
      ------------------------------ --------------------------------------------------
      V_MY_OBJECT                    embedded.id
      V_MY_OBJECT_2                  treat(embedded as OMyEmbeddedObject2).id2
      V_MY_OBJECT_3                  treat(embedded as OMyEmbeddedObject2).getId()
      V_MY_OBJECT_4                  ltrim(to_char(4, '09'))
      

       

      Does anyone know if Oracle maintain attribute-specific meta-data anywhere, for example position (for multi-attribute OIDs) and datatype?

       

      Gerard

        • 1. Re: Complex object view identifiers and Oracle metadata
          gaverill

          I spent some more time looking around in the various data dictionary views and noticed some additional useful information about object view identifiers.

           

          First, looking in USER_OBJECTS, I noticed that for each of my object views Oracle created a system-generated TYPE incorporating the OBJECT_ID of the view in its name...

           

          select    v.object_name view_name
          ,    v.object_id view_object_id
          ,    o.object_name oid_type_name
          from    USER_OBJECTS v
          join    USER_OBJECTS o on
                  o.object_type = 'TYPE'
              and    o.object_name = 'SYS_YOID' || ltrim(to_char(v.object_id, '0000000000')) || '$'
          where    v.object_type = 'VIEW'
          and    v.object_name like 'V_MY_OBJECT%';
          
          VIEW_NAME                      VIEW_OBJECT_ID OID_TYPE_NAME                 
          ------------------------------ -------------- ------------------------------
          V_MY_OBJECT                            114428 SYS_YOID0000114428$           
          V_MY_OBJECT_2                          114432 SYS_YOID0000114432$           
          V_MY_OBJECT_3                          114434 SYS_YOID0000114434$           
          V_MY_OBJECT_4                          114454 SYS_YOID0000114454$           
          

           

          Not seeing this TYPE in ALL_TYPES, I looked at the source code of the view and saw that system-generated TYPES were excluded (similarly for ALL_TYPE_ATTRS), so I created additional views ALL_SYS_GENERATED_TYPES and ALL_SYS_GENERATED_TYPE_ATTRS, allowing me to at least get datatype information for each of the object view identifier keys...

           

          select    v.view_name
          ,    a.ATTR_TYPE_MOD        key_type_mod
          ,    a.ATTR_TYPE_OWNER    key_type_owner
          ,    a.ATTR_TYPE_NAME    key_type_name
          ,    a.LENGTH
          ,    a.PRECISION
          ,    a.SCALE
          ,    a.CHARACTER_SET_NAME
          ,    a.ATTR_NO        key_no
          ,    a.CHAR_USED
          from    USER_OBJECTS vo
          join    USER_VIEWS v on
                  v.view_name = vo.object_name
          join    ALL_SYS_GENERATED_TYPE_ATTRS a on
                  a.owner = user
              and    a.type_name = 'SYS_YOID' || ltrim(to_char(vo.OBJECT_ID, '0000000009')) || '$';
          
          VIEW_NAME                      KEY_TYPE_MOD KEY_TYPE_OWNER                 KEY_TYPE_NAME                      LENGTH  PRECISION      SCALE CHARACTER_SET_NAME     KEY_NO CHAR_USED
          ------------------------------ ------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ ---------- ---------
          V_MY_OBJECT                                                                NUMBER                                                                                      1 B        
          V_MY_OBJECT_2                                                              NUMBER                                                                                      1 B        
          V_MY_OBJECT_3                                                              NUMBER                                                                                      1 B        
          V_MY_OBJECT_4                                                              VARCHAR2                                3                       CHAR_CS                     1 B        
          

           

          Perhaps there's an Oracle Database Ideas somewhere here, as exposing this additional information allows one to dynamically generate any object view REF given an object instance (without having to parse the individual key expressions) using an SQL query of this form...

           

          -- Assumes a table collection type, here NBaseObjectTable, able to contain the specified object instance...
          
          with    obj_attrs            --> decompose all attributes and let Oracle eliminate those not used
              (
                  <attr-1>
              ,    ...
              ,    <attr-n>
              )
          as    (
              select    treat(value(o) as <object-type>).<attr-1>
              ,    ...
              ,    treat(value(o) as <object-type>).<attr-N>
              from    table(NBaseObjectTable(:o)) o
              )
          ,    key_exprs
              (
                  key_1
              ,    ...
              ,    key_k
              )
          as    (
              select    <oid-text>        --> from ALL_VIEWS... if multiple keys are used, <oid-text> is a comma-separated list of key expressions
              from    obj_attrs
              )
          select    make_ref(<object-view>, key_1, ..., key_k)
          from    key_exprs;
          

           

          In case anyone else may find this of interest...

           

          Gerard