4 Replies Latest reply: May 24, 2013 2:14 PM by odie_63 RSS

    ORA-22895 when scope target is a nested table

    odie_63
      Hi,

      DB version : 11.2.0.2

      Test case :
      create or replace type ChartPoint as object ( refId integer, val   number );
      create or replace type ChartPointSet is table of ChartPoint;
      create or replace type ChartPointRefSet is table of ref ChartPoint;
      create or replace type ChartDimension as object ( id integer, name varchar2(30), points  ChartPointRefSet);
      create or replace type ChartDimensionSet is table of ChartDimension;
      create or replace type ChartEntry as object (points  ChartPointSet);
      create or replace type ChartEntrySet is table of ChartEntry;
      
      create or replace type ChartObject as object (
        id         integer
      , name       varchar2(30)
      , dimensions ChartDimensionSet
      , entries    ChartEntrySet
      );
      
      create table chart_table of ChartObject
        nested table dimensions store as chart$dimension_set 
        (
          nested table points store as chart$dimension_point_set
        )
      , nested table entries store as chart$entry_set
        (
          nested table points store as chart$entry_point_set
        )
      ;
      
      alter table chart$dimension_point_set add (scope for (column_value) is chart$entry_point_set);
      I'm trying to scope a REF column in a nested table to an object (ChartPoint) whose instances are stored in another nested table (CHART$ENTRY_POINT_SET), but the ALTER DDL is giving :
      ORA-22895: referenced table "CHART$ENTRY_POINT_SET" in schema "<SCHEMA>" is not an object table
      I know this works when the scope target is a top-level object table, but apparently a nested object table is not recognized as a valid target.
      I didn't find anything related to this in the Object-Relational Dev Guide.

      Any ideas?

      Thanks.
        • 1. Re: ORA-22895 when scope target is a nested table
          gaverill
          Correct, object references (REFs) can only refer to row objects, not nested column objects.

          Gerard
          • 2. Re: ORA-22895 when scope target is a nested table
            odie_63
            gaverill wrote:
            Correct, object references (REFs) can only refer to row objects, not nested column objects.
            Well, I'm not trying to reference a column object.
            Technically speaking, the target table (CHART$ENTRY_POINT_SET) is an object table (albeit a nested table), isn't it?
            SQL> select object_id_type, table_type
              2  from user_object_tables
              3  where table_name = 'CHART$ENTRY_POINT_SET';
             
            OBJECT_ID_TYPE   TABLE_TYPE
            ---------------- ------------------------------
            SYSTEM GENERATED CHARTPOINT
             
            However, there doesn't seem to have any "SYS_NC_OID$" column, so I guess you're right :
            SQL> select column_name, data_type
              2  from user_nested_table_cols
              3  where table_name = 'CHART$ENTRY_POINT_SET';
             
            COLUMN_NAME                    DATA_TYPE
            ------------------------------ --------------------------------------------------------------------------------
            NESTED_TABLE_ID                RAW
            SYS_NC_ROWINFO$                CHARTPOINT
            REFID                          NUMBER
            VAL                            NUMBER
             
            • 3. Re: ORA-22895 when scope target is a nested table
              gaverill
              odie_63 wrote:
              gaverill wrote:
              Correct, object references (REFs) can only refer to row objects, not nested column objects.
              Well, I'm not trying to reference a column object.
              Technically speaking, the target table (CHART$ENTRY_POINT_SET) is an object table (albeit a nested table), isn't it?
              It would appear that, technically speaking, the table is a table of objects, but not an object table!

              Why do you have both ChartPointSet and ChartPointRefSet? Can you change your data model to only use REFs?

              Gerard
              • 4. Re: ORA-22895 when scope target is a nested table
                odie_63
                Why do you have both ChartPointSet and ChartPointRefSet? Can you change your data model to only use REFs?
                The initial idea was to only store data in a ChartEntrySet collection (stored as CHART$ENTRY_SET nested table), and then use REF pointers to some rows in that table for the dimensions.
                It was a bad idea, for the reason we see here, and from a maintainability and performance point of view as well.

                Background info if you're interested : I'm studying some modelling options to store chart definitions and data with multiple dimensions and multiple measures. The ultimate goal is to perform multilinear interpolation.