Using REFs

MEMBER FUNCTION give_ref(id IN integer)
RETURN REF emp_t,
PRAGMA RESTRICT_REFERENCES (give_ref,WNPS)
)
/
 
rem
rem Now create the object employees which contains
rem nested tables, types and normal attributes
rem
CREATE TABLE employees OF emp_t
OIDINDEX oid_employees (TABLESPACE oid_index)
NESTED TABLE dependents STORE AS dependentsv8a
       PCTFREE 10 PCTUSED 80
       INITRANS 5 MAXTRANS 255
       TABLESPACE emp_data
       STORAGE (
              INITIAL 20m NEXT 10m
              MINEXTENTS 1 MAXEXTENTS 10
              PCTINCREASE 0
       )
/
ALTER TABLE employees ADD
       CONSTRAINT pk_employees
              PRIMARY KEY (employee_id)
       USING INDEX
              PCTFREE 20
              INITRANS 5
              MAXTRANS 255
              TABLESPACE emp_index
              STORAGE (
                     INITIAL 10m NEXT 10m
                     MINEXTENTS 1 MAXEXTENTS 121
                     PCTINCREASE 0 FREELISTS 5
              )
/

ALTER TABLE employees MODIFY
   name NOT NULL
/
Listing 1: Example Object Creation using REFs

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm