Tuesday, 21 May 2013

Create view and synonym on non-existing object in Oracle

When you try to create a view on non-existing object oracle will not allow to create. You can attain this by FORCE option in create view statement;

SQL> select count(1) from user_tables where table_name='BASE_TAB';

 COUNT(1)
---------
        0
           
-create view without force option
SQL> create view view_1 as select * from base_tab;
create view view_1 as select * from base_tab
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

-create view with force option
SQL> create force view view_1 as select * from base_tab;

Warning: View created with compilation errors.

-check the status of this view
SQL> set linesize 2000
SQL> col object_name format a20
SQL> select object_name,status from dba_objects where object_name in ('VIEW_1');

OBJECT_NAME          STATUS
-------------------- -------
VIEW_1               INVALID

-When you want to create synonym on non-existing objects we don’t need any force option.
SQL> create synonym syn_1 for base_tab;

Synonym created.

SQL> select object_name,status from dba_objects where object_name in ('SYN_1');

OBJECT_NAME          STATUS
-------------------- -------

SYN_1                VALID