Thursday, February 23, 2012

Create Force View

You cannot create a view of a table which does not exist:

SQL> create or replace view blah
  2  as select * from missing_table
  3  /
as select * from missing_table
                 *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL>

... but, if you have no choice, you can use the force option:

SQL> create or replace force view blah
  2  as select * from missing_table
  3  /

Warning: View created with compilation errors.

SQL>

Of course, such a view will not work:

SQL> select * from blah
  2  /
select * from blah
              *
ERROR at line 1:
ORA-04063: view "SYS.BLAH" has errors

SQL>

... until you have created the missing table:

SQL> create table missing_table
  2  as select 'Dial 999' how_to_call_the_police
  3  from dual
  4  /

Table created.

SQL>

Even then, the view is still invalid:

SQL> select status from user_objects
  2  where object_name = 'BLAH'
  3  /

STATUS
-------
INVALID

SQL>

... but, if you try to use it, Oracle recompiles it and it works:

SQL> select * from blah
  2  /

HOW_TO_CALL_THE_POLICE
----------------------
Dial 999

SQL>

... and the next time you check its status, it is valid:

SQL> select status from user_objects
  2  where object_name = 'BLAH'
  3  /

STATUS
-------
VALID

SQL>

No comments: