This discussion is archived
4 Replies Latest reply: May 24, 2013 12:14 PM by odie_63 RSS

ORA-22895 when scope target is a nested table

odie_63 Guru
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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