Mega Code Archive
MAKE_REF and user view
<![CDATA[
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> 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> 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> drop view empCallView;
SQL> drop view empView;
SQL> drop table emp_CALL;
SQL> drop table emp;
SQL>
SQL> drop type personType;
SQL>
SQL> drop type addressType;
SQL>
]]>