rem -----------------------------------------------------------------------
rem Filename:   objopt.sql
rem Purpose:    Demonstrate Oracle database types and object tables
rem Date:       12-Feb-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

drop type employee_typ;

create type employee_typ as object (
        empno NUMBER,
        emp_name varchar2(30),
        hiredate date,
        member function days_at_company return NUMBER,
        pragma restrict_references(days_at_company, WNDS)
)
/

create type body employee_tye is
begin
        member function days_at_company return number is
        begin
                return (SYSDATE-hiredate);
        end;
end;
/
show errors

drop type department_typ;

create type department_typ as object (
        deptno NUMBER(5),
        manager ref employee_typ
)
/

select * from user_types
where predefined = 'NO';

-- Create a object table
create table emp1 as employee_typ;

create table employee (emp_no NUMBER, emp employee_typ);

insert into employee values (1, employee_typ(1, 'Frank Naude', SYSDATE));

commit;

select * from employee;

select x.emp.emp_name from employee x;