SCOPED REFs:

In declaring a column type, collection element or object type attribute to be a REF, you can constraint it to hold only references to a specified object table. Like a REF is known as a SCOPED REF.  The SCOPED REFs needs less storage space and permits more efficient access than UNSCOPED REFs.

A SCOPE clause restricts the scope of references to a single table. The scoped reference is used when the intention is to limit the reference to a particular table.   If the SCOPE option is selected when a table is created, then all additional instances of data in the table must reference the scoped table.

Example

CREATE TABLE EMPLOYEES (

ENAME VARCHAR2(20),

DEPTINF REF DEP_TYPE SCOPE IS DEPTAB

) ;

The given example denotes in which the values must be obtained from the object table DEPTAB that is described as a type of DEP_TYPE. Utilizing the SCOPE clause decrease space requirements and speeds access since the target table is isolated.

The REF values can be stored with or without ROWIDs.  The Storing REF values with ROWID can enhance the performance of the dereference operation but takes up more space.

EXAMPLE : ( REF WITH ROWID)

CREATE TABLE employees

(

ename varchar2(20), deptinf REF dep_type SCOPE IS deptab WITH ROWID

) ;

OR

CREATE TABLE employees

(

ename varchar2(20), deptinf REF dep_type SCOPE IS deptab,

REF(deptinf) WITH ROWID

) ;

To store REF the default behavior is values without the ROWID.

Whenever records are inserted the scope is checked for and if the scope is violated and an error is displayed as given below:

CREATE TABLE deptab1 OF dep_type;

Table created.

insert into deptab1 values(12,'fin','chennai');# insert query#

1 row created.

INSERT INTO employees SELECT 'hema',ref(x) FROM deptab1 x where depno=10;

insert into employees select 'hema',ref(x) from deptab1 x where depno=10

*

ERROR at line 1:

ORA-22889: the REF value doesn't point to scoped table

In case of any restriction, that scope checks for the table.