1 Reply Latest reply on Jul 2, 2015 4:40 PM by gaverill

    System-generated table type: expected behavior or bug?

    gaverill

      I understand that Oracle, in the course of executing a SQL statement, will on occasion automatically generate SQL objects not explicitly created by the user. One such example is with pipelined table functions defined to return a PL/SQL (as opposed to SQL) collection type: in this case Oracle will automatically create an equivalent SQL collection type for the PL/SQL collection type. All well and good...

       

      However, here is an example where it would appear Oracle is unnecessarily creating a SQL collection type, even when there is an explicit CAST to an already existing SQL collection type. The issue seems to be related to the use of the COLLECT function (http://docs.oracle.com/database/121/SQLRF/functions034.htm#SQLRF06304).

       

      First the test script...

       

      prompt    -- The database version...
      
      select    *
      from    v$version
      /
      
      prompt    -- Create the test object and collection types...
      
      create or replace type OMyObject
      is object
      (
          a integer
      )
      /
      
      create or replace type NMyObjectTable
      is table of OMyObject
      /
      
      prompt    -- Check that no system-generated table types currently exist...
      
      select    C.COLL_TYPE, C.ELEM_TYPE_MOD, C.ELEM_TYPE_NAME, C.TYPE_NAME, C.UPPER_BOUND
      from    USER_COLL_TYPES c
      where    c.TYPE_NAME like 'SYS%'
      and    c.ELEM_TYPE_NAME = 'OMYOBJECT'
      /
      
      prompt    -- CAST with MULTISET does not create a system-generated table type...
      
      select    cast(multiset(
              select    new OMyObject(level)
              from    DUAL
              connect by
                  level <= 10
          ) as NMyObjectTable) myObjects
      from    DUAL
      /
      
      select    C.COLL_TYPE, C.ELEM_TYPE_MOD, C.ELEM_TYPE_NAME, C.TYPE_NAME, C.UPPER_BOUND
      from    USER_COLL_TYPES c
      where    c.TYPE_NAME like 'SYS%'
      and    c.ELEM_TYPE_NAME = 'OMYOBJECT'
      /
      
      prompt    -- COLLECT without a CAST creates a new system-generated table type...
      
      select    collect(value(o)) myObjects
      from    table(cast(multiset(
              select    new OMyObject(level)
              from    DUAL
              connect by
                  level <= 10
          ) as NMyObjectTable)) o
      /
      
      select    C.COLL_TYPE, C.ELEM_TYPE_MOD, C.ELEM_TYPE_NAME, C.TYPE_NAME, C.UPPER_BOUND
      from    USER_COLL_TYPES c
      where    c.TYPE_NAME like 'SYS%'
      and    c.ELEM_TYPE_NAME = 'OMYOBJECT'
      /
      
      prompt    -- (Drop the system-generated table type...)
      
      set    feedback off
      
      spool    "temp_drop_table_types.sql"
      
      select    'drop type "' || C.TYPE_NAME || '";'    as "-- Drop statement"
      from    USER_COLL_TYPES c
      where    c.TYPE_NAME like 'SYS%'
      and    c.ELEM_TYPE_NAME = 'OMYOBJECT'
      /
      
      spool    off
      
      set    feedback on
      
      @"temp_drop_table_types.sql"
      
      prompt    -- COLLECT with a CAST also creates a system-generated table type...
      
      select    cast(collect(value(o)) as NMyObjectTable) myObjects
      from    table(cast(multiset(
              select    new OMyObject(level)
              from    DUAL
              connect by
                  level <= 10
          ) as NMyObjectTable)) o
      /
      
      select    C.COLL_TYPE, C.ELEM_TYPE_MOD, C.ELEM_TYPE_NAME, C.TYPE_NAME, C.UPPER_BOUND
      from    USER_COLL_TYPES c
      where    c.TYPE_NAME like 'SYS%'
      and    c.ELEM_TYPE_NAME = 'OMYOBJECT'
      /
      
      

       

      which outputs...

       

      -- The database version...

       

      BANNER                                                                               CON_ID

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

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

      PL/SQL Release 12.1.0.2.0 - Production                                                    0

      CORE    12.1.0.2.0    Production                                                        0

      TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0

      NLSRTL Version 12.1.0.2.0 - Production                                                    0

       

      5 rows selected.

       

      -- Create the test object and collection types...

       

      Type created.

       

      Type created.

       

      -- Check that no system-generated table types currently exist...

       

      no rows selected.

       

      -- CAST with MULTISET does not create a system-generated table type...

       

      MYOBJECTS(A)                                     

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

      NMYOBJECTTABLE(OMYOBJECT(1),OMYOBJECT(2),OMYOBJECT(

      3),OMYOBJECT(4),OMYOBJECT(5),OMYOBJECT(6),OMYOBJECT

      (7),OMYOBJECT(8),OMYOBJECT(9),OMYOBJECT(10))     

                                                                                                                                                                                                            

      1 row selected.

       

      no rows selected.

       

      -- COLLECT without a CAST creates a new system-generated table type...

       

      MYOBJECTS(A)                                     

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

      SYSTPUBsptM+HT5OlTJUtIcUfgg==(OMYOBJECT(1),OMYOBJEC

      T(2),OMYOBJECT(3),OMYOBJECT(4),OMYOBJECT(5),OMYOBJE

      CT(6),OMYOBJECT(7),OMYOBJECT(8),OMYOBJECT(9),OMYOBJ

      ECT(10))                                         

                                                                                                                                                                                                            

      1 row selected.

       

      COLL_TYPE  ELEM_TYPE_MOD ELEM_TYPE_NAME                 TYPE_NAME                      UPPER_BOUND

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

      TABLE                    OMYOBJECT                      SYSTPUBsptM+HT5OlTJUtIcUfgg==           

       

      1 row selected.

       

      -- (Drop the system-generated table type...)

       

      -- Drop statement                                                                                                                          

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

      drop type "SYSTPUBsptM+HT5OlTJUtIcUfgg==";

       

      Type dropped.

       

      -- COLLECT with a CAST also creates a system-generated table type...

       

      MYOBJECTS(A)                                     

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

      NMYOBJECTTABLE(OMYOBJECT(1),OMYOBJECT(2),OMYOBJECT(

      3),OMYOBJECT(4),OMYOBJECT(5),OMYOBJECT(6),OMYOBJECT

      (7),OMYOBJECT(8),OMYOBJECT(9),OMYOBJECT(10))     

                                                                                                                                                                                                            

      1 row selected.

       

      COLL_TYPE  ELEM_TYPE_MOD ELEM_TYPE_NAME                 TYPE_NAME                      UPPER_BOUND

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

      TABLE                    OMYOBJECT                      SYSTPds/KlYeDTnaiTKLsbcLJpg==           

       

      1 row selected.

       

      Is this expected behavior, or should it be considered a bug? Thoughts?

       

      Gerard