rem -----------------------------------------------------------------------
rem Filename:   objopt.sql
rem Purpose:    Demonstrate Oracle database types and object tables
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, 'Singh', SYSDATE));

commit;

select * from employee;

select x.emp.emp_name from employee x;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips