Power Of Object Oriented Mechanism With Object Views In Oracle

In Oracle database, it is possible to create virtual object tables from relational tables. With this fashion you can select as if you are selecting an object table. So, you need not to convert relational data to object oriented data if you tend to use it. Follow the example:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> create type t_telephone_number is object( no varchar2(12));
  2  /
Type created
SQL> create type t_telephone_number_list is varray(5) of t_telephone_number;
  2  /
Type created
SQL> create type worker_typ is object( id number, name varchar2(16), telephone_list t_telephone_number_list );
  2  /
Type created
SQL> alter type worker_typ add attribute x date;
Type altered
SQL> describe worker_typ;
Element        Type                   
-------------- -----------------------
ID             NUMBER                 
NAME           VARCHAR2(16)           
TELEPHONE_LIST T_TELEPHONE_NUMBER_LIST
X              DATE                   
SQL> alter type worker_typ drop attribute x ;
Type altered
SQL> create table workers(i number, n varchar2(16) );
Table created
SQL> create table tels(i number, nm varchar2(12) );
Table created
SQL> create view vw_workers of worker_typ with object identifier(id) as
  2    select w.i, w.n,cast(
  3       multiset(select nm from tels t where t.i=w.i) as t_telephone_number_list)
  4     from workers w;
View created
SQL> insert into workers values(1,'Mennan');
1 row inserted
SQL> insert into tels values(1, '2122122122');
1 row inserted
SQL> insert into tels values(1, '2122221133');
1 row inserted
SQL> select v.id,v.name, v.telephone_list from vw_workers v where v.id = 1;
        ID NAME             TELEPHONE_LIST
---------- ---------------- --------------
         1 Mennan           [object]

Creating(NOFORCE, WITH CHECK OPTION parameters) And Inserting Views In Oracle

Views are some kind of storing queries in database. If you have a big query and want use it elsewhere, you can create a view. Normally views do not permit to inserts via themselves. In Oracle it can be possible. Additional features of views in Oracle described below:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> drop table t;
Table dropped
SQL> create table t(a number, z number);
Table created
SQL> create or replace view t_v as select a as aa,z as zz from t;
View created
SQL> select * from t_v;
        AA         ZZ
---------- ----------
SQL> insert into t_v values(1,2);
1 row inserted
SQL> insert into t_v values(2,3);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t;
         A          Z
---------- ----------
         1          2
         2          3
SQL> select * from t_v;
        AA         ZZ
---------- ----------
         1          2
         2          3

SQL> create or replace  noforce view  no_existed_table_v  as select a as aa,z as zz from no_existed_table;
create or replace  noforce view  no_existed_table_v  as select a as aa,z as zz from no_existed_table
ORA-00942: table or view does not exist
SQL> create or replace  force view  no_existed_table_v  as select a as aa,z as zz from no_existed_table;
Warning: View created with compilation errors
SQL> select * from no_existed_table_v;
select * from no_existed_table_v
ORA-04063: view "HR.NO_EXISTED_TABLE_V" has errors

SQL> drop view t_v;
View dropped
SQL> create view t_v as select a as aa,z as zz from t with read only;
View created
SQL> insert into t_v values(1,2);
insert into t_v values(1,2)
ORA-01733: virtual column not allowed here

SQL> create or replace view t_v as select a as aa,z as zz from t where a = 1;
View created
SQL> SELECT * FROM t_v;
        AA         ZZ
---------- ----------
         1          2
SQL> insert into t_v values(3,4);
1 row inserted
SQL> create or replace view t_v as select a as aa,z as zz from t where a = 1 with check option constraint ck_v;
View created
SQL> insert into t_v values(3,4);
insert into t_v values(3,4)
ORA-01402: view WITH CHECK OPTION where-clause violation