16 December 2006

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

No comments: