How to create view without underlying table

This tip will enable to create a view even if you do not have an underlying table already present in your database.

In this article you will learn to                            

• Create view without a table
• Creating a table for that view
• How to make the view to work

Consider the following example:-

CREATE OR REPLACE FORCE VIEW force_view AS SELECT * FROM force_table;

Now check whether the view is created or not:

SELECT object_name, object_type, status, temporary, generated, secondary FROMuser_objects WHERE object_name='FORCE_VIEW';

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

----------- ----------- --------   ---------   ---------  ---------

FORCE_VIEW  VIEW        INVALID     N           N           N

So this ensures that the view is created, but what about the status? It says the view created is invalid.

Now we will try to execute the view:

SELECT * FROM force_view;

Error starting at line 1 in command:

select * from force_view

Error at Command Line:1 Column:14

Error report:

SQL Error: ORA-04063: view "RND.FORCE_VIEW" has errors

04063. 00000 -  "%s has errors"

*Cause:    Attempt to execute a stored procedure or use a view that has

           errors.  For stored procedures, the problem could be syntax errors

           or references to other, non-existent procedures.  For views,

           the problem could be a reference in the view's defining query to

           a non-existent table.

           Can also be a table which has references to non-existent or

           inaccessible types.

*Action:   Fix the errors and/or create referenced objects as necessary

This is the error which I get when running the view. So now you will understand that we will be just able to create the view, but it is useless until it has an underlying table attached to it.

Now we will create table for this view:

CREATE TABLE force_table (a NUMBER, b VARCHAR2(10));

Now will check again the status for the view.

SELECT object_name, object_type, status, temporary, generated, secondary FROMuser_objects WHERE object_name='FORCE_VIEW';

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

----------- ----------- --------   ---------   ---------  ---------

FORCE_VIEW  VIEW        INVALID     N           N           N

The status is still INVALID. Now we need to re-compile the view to make it VALID.

ALTER VIEW force_view COMPILE;

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

----------- ----------- --------   ---------   ---------  ---------

FORCE_VIEW  VIEW        VALID       N           N           N