MAKE_REF and user view : View Sturcutre « View « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. View
3. View Sturcutre

SQL>
SQL>
SQL> create type addressType as object
  2  (Street  VARCHAR2(50),
  3   City    VARCHAR2(25),
  4   State   CHAR(2),
  5   Zip     NUMBER);
  6  /
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL> create table emp
  2  (cid NUMBER   primary key,
  3   Name        VARCHAR2(25),
  4   Street      VARCHAR2(50),
  5   City        VARCHAR2(25),
  6   State       CHAR(2),
  7   Zip         NUMBER);
SQL>
SQL>
SQL> create or replace type empType as object
  2  (cid NUMBER,
  3   Name        VARCHAR2(25),
  4   Street      VARCHAR2(50),
  5   City        VARCHAR2(25),
  6   State       CHAR(2),
  7   Zip         NUMBER);
  8  /
SQL>
SQL> create view empView of empType
  2  with object OID (cid) as
  3  select cid, Name, Street, City, State, Zip
  4  from emp;
SQL>
SQL> create table emp_CALL
  2  (cid   NUMBER,
  3   Call_Number   NUMBER,
  4   Call_Date     DATE,
  5   constraint emp_CALL_PK primary key (cid, Call_Number),
  6   constraint emp_CALL_FK foreign key (cid)references emp(cid));
SQL>
SQL>
SQL> create view empCallView as
  2  select MAKE_REF(empView, cid) cid,
  3         Call_Number,
  4         Call_Date
  5    from emp_CALL;
SQL>
SQL> select DEREF(CCOV.cid)
  2    from empCallView CCOV
  3   where Call_Date = TRUNC(SysDate);
SQL>
SQL>
SQL> drop view empCallView;
SQL> drop view empView;
SQL> drop table emp_CALL;
SQL> drop table emp;
SQL>
SQL>
SQL> drop type personType;
SQL>
SQL> drop type addressType;
SQL>