How To CREATE or DROP VIEW
  Home
 

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;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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';

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

And the following SQL query shows that all “X” values are missing from the new MY_DUAL_VW view.

SELECT *
   FROM MY_DUAL_VW;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*