Invalid Identifier error for scope

Question

I have created customer_ty object which includes a nested table called "deposits".

CREATE TYPE deposit_ty as object(
depNo number,
depCategory ref depcategory_ty,
amount number,
period number
)
/

This 'depCategory' reference depcategory_ty in another table called 'depcategory_tbl'.

CREATE TYPE deposit_ntty as table of depcategory_ty
/

CREATE TYPE address_ty as varray(3) of varchar2(20)
/

CREATE TYPE customer_ty as object(
custId varchar2(4),
custName varchar2(10),
address address_ty,
dob date,
deposits deposit_ntty
)
/

CREATE TABLE customer_tbl of customer_ty(
custId primary key)
nested table deposits store as cli_deposit_tbl
/

alter table cli_deposit_tbl
add scope for (depCategory) is depcategory_tbl
/

The problem appears when I try to add a scope for table. it says;

add scope for (depCategory) is depcategory_tbl
               *
ERROR at line 2:
ORA-0094:"DEPCATEGORY":Inavalid Identifier

All the identifiers are correct. What is wrong with this?

Answer 1

It looks like you've defined deposit_ntty incorrectly, and meant it to be:

CREATE TYPE deposit_ntty as table of deposit_ty
/

With that change, your alter now gets:

alter table cli_deposit_tbl
add scope for (depCategory) is depcategory_tbl
/

ORA-22892: scoped table "DEPCATEGORY_TBL" does not exist in schema "PUBLIC"

You may already have that but it isn't shown; with it there the alter works:

CREATE TABLE depcategory_tbl of depcategory_ty;

Table DEPCATEGORY_TBL created.

alter table cli_deposit_tbl
add scope for (depCategory) is depcategory_tbl
/

Table CLI_DEPOSIT_TBL altered.