This tutorial is based on examples to be easier to follow. The Oracle Create View Statement allows creating a view object in Oracle database. The Oracle View is a logical object that doesn’t store any data in itself and it uses other objects or tables to link or to get its data. Tables used in view are called base tables. The Oracle Create View syntax is following:
CREATE [OR REPLACE] VIEW <view_name> AS <select_statement>;
To demonstrate a view creation and 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. Please take a look at the 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 the ID column with mixed values number from 1 to 10 and some “X” characters in the middle. The next script will create an Oracle view name MY_DUAL_VW and as you see after the AS keyword the Select statement is exactly the same as above.
CREATE VIEW MY_DUAL_VW
AS
SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
FROM dual
CONNECT BY rownum < 11;
The all Oracle Views can be used as Oracle tables except that they don’t store the data in itself, so you can use the view name in the SQL query. The following Select statement is using the just created view and since the Oracle view is using the first query above the output looks exactly the same as the first query has.
SELECT * FROM MY_DUAL_VW;
To see an Oracle View columns and their attributes you can use command DESCRIBE. The view used in this examples doesn’t have many column so it is very simple to read it but for bigger views the DESCRIBE command comes quite handy to find out column names or the column data types.
describe MY_DUAL_VW;
To remove an Oracle View from a database use command DROP. The command removes only the view itself and leaves the other objects used in the view as is. Also the DROP command does not delete lines in the Oracle tables used in the view and it only applies on the view object.
DROP <view_name>;
The following Online Tech Support example will remove the MY_DUAL_VW view that was created above.
DROP VIEW MY_DUAL_VW;
We will create the MY_DUAL_VW view again but this time all “X” will be removed from the list. This example show one of the reasons why Oracle View are present in Oracle databases and the reason is you can modify the output without deleting any line in the Oracle table. Also you don’t have to copy the lines over to 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 following SQL query shows that all “X” values are missing from the new MY_DUAL_VW view.
SELECT * FROM MY_DUAL_VW;
The Oracle Views allowing us 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 change only the Oracle View to apply new rules.
See Also:
Oracle Select Home