How To CREATE or DROP A VIEW In Oracle

This help is based on examples so it would be easier to understand. Oracle Create View Statement creates a view into Oracle database based on Select statement. The Oracle View is a logical object that doesn’t store any data in itself and it shows a data from other objects or tables. Oracle tables used in a view are called base tables. The syntax of Oracle Create View is :

CREATE [OR REPLACE] VIEW <view_name>
AS
<select_statement>;

To demonstrate how to create a view and how to filter its context we would need a table or a query. To keep the example as simple as possible we will use the following Select statement using the Oracle Dual table that does exist in all Oracle database versions and hierarchical operator CONNECT BY. Please take a look at the query output to understand the following examples better.

 SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
   FROM dual
    CONNECT BY rownum < 11;

From it output you can find a column named ID and it contains mixed values numbers from 1 to 10 and some “X” characters in the middle. The script above will create an Oracle view name MY_DUAL_VW and you can find after the AS keyword the same Select statement as the last one above.

CREATE VIEW MY_DUAL_VW
AS
SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
   FROM dual
    CONNECT BY rownum < 11;

To see the data in Oracle Views you can used the Select statement as with any Oracle table except that the views don’t store the data in itself as tables do. The following Select statement will use the just created view and since this Oracle view is using the exact statement of the first SQL query above the output looks exactly the same as the first query has.

SELECT *
   FROM MY_DUAL_VW;

To see Oracle View columns and their attributes you can use command DESCRIBE. The Oracle view used in this examples doesn’t have many columns so it is very simple to read it but for more complicated views the DESCRIBE command comes quite handy to find out column names and their data types.

describe MY_DUAL_VW;

To remove an Oracle View from a database use command DROP VIEW. The command removes only the view itself and leaves the other objects used in the view as is. Also the DROP VIEW command does not delete lines from the base tables used in the view and it only applies on the view object.

DROP VIEW <view_name>;

The following Oracle DBA example will remove the MY_DUAL_VW view that was recently created and output view MY_DUAL_VW dropped confirms the view removing.

DROP VIEW MY_DUAL_VW;

We are going to create the MY_DUAL_VW view again but this time all “X” will be filtered out from the list. The example demonstrates one of the common reasons why Oracle View are present in Oracle databases and the reason is you can modify the output without deleting any line from the Oracle table. Also you don’t have to copy the lines over into another table and that way your database size remains smaller and less programming will be needed to synchronize both tables.

CREATE VIEW MY_DUAL_VW AS
 SELECT t.ID
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11 ) t
  WHERE t.ID <> 'X';

And the Select statement above shows that all “X” values are gone from view MY_DUAL_VW.

SELECT *
   FROM MY_DUAL_VW;

The Oracle Views allows to filter out lines that are not needed or relevant without removing anything from the base tables and once the logic has changed you do have to modify only the Oracle View itself to apply new rules.

See Also:
Oracle Select Oracle Create Table Oracle Home