Views are wonderful things

Views often get a bad name. Often writers of SQL suffer from view resolution problems (where the entire result of a view is found before external predicates are applied) and thus the opinion "views slow down the database" is formed.

However, views are wonderful means of introducing a functionality layer in your database (particularly in a development environment). By hiding the table and exposing the view to "the public" you suddenly respond to developer requests such as

"I want to add a column but I'm worried about existing code"
No problem - add a column to the table and recreate the view. If things goes wrong, simply remove it from the view and sanity is restored.

"I want to drop a column"
Not such a great problem if you're on 8i, but before then with views its also no problem. Remove the column from the view, and you're done.

Views can be used with particular power in 8.x of Oracle, since we now have INSTEAD OF triggers. Here is an example in which views can be used to implement a SQL Server function - the ability to avoid an error when an attempt to insert a duplicate key is made.

tkyte@8.0> create table t_table ( x int primary key );
Table created.

tkyte@8.0> create or replace view t as select * from t_table;
View created.

tkyte@8.0> create or replace trigger t_trigger
  2  INSTEAD OF INSERT ON T
  3  for each row
  4  begin
  5          insert into t_table values ( :new.x );
  6  exception
  7          when DUP_VAL_ON_INDEX then
  8                  null;
  9  end;
 10  /
Trigger created.

tkyte@8.0> create or replace package globals
  2  as
  3          no_dup_key      boolean default FALSE;
  4  end;
  5  /

Package created.

tkyte@8.0> 
tkyte@8.0> 
tkyte@8.0> create or replace trigger t_trigger
  2  INSTEAD OF INSERT ON T
  3  for each row
  4  begin
  5          insert into t_table values ( :new.x );
  6  exception
  7          when DUP_VAL_ON_INDEX then
  8                  if ( globals.no_dup_key )
  9                  then
 10                          null;
 11                  else
 12                          raise;
 13                  end if;
 14  end;
 15  /

Trigger created.

tkyte@8.0> insert into t values ( 1 );
insert into t values ( 1 )
            *
ERROR at line 1:
ORA-00001: unique constraint (TKYTE.SYS_C007832) violated
ORA-06512: at "TKYTE.T_TRIGGER", line 9
ORA-04088: error during execution of trigger 'TKYTE.T_TRIGGER'


tkyte@8.0> 
tkyte@8.0> exec globals.no_dup_key := TRUE;

PL/SQL procedure successfully completed.

tkyte@8.0> insert into t values ( 1 );

1 row created.