Exforsys.com
 
Home Tutorials Oracle 10g
 

Oracle 10g Tutorials - Creating Views, Altering, Dropping and Replacing Views

 

Oracle 10g Tutorials - Creating Views, Altering, Dropping and Replacing Views

Page 1 of 3

.Oracle 10g Free Training - Creating Views, Altering, Dropping and Replacing Views

In this training session you will learn about Views. This section describes aspects about Views, Creating Views - Join Views, Expansion of Defining Queries at View Creation Time and Creating Views with errors. Altering Views and Dropping Views. Replacing Views and different ways with which you can replace views.



Views

About Views

A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables.


Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.


Creating Views

To create a view, you must meet the following requirements:


  • To create a view in your schema, you must have the CREATE VIEW privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege. You can acquire these privileges explicitly or through a role.
    .
  • The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the functionality of the view is dependent on the privileges of the view owner. For example, if the owner of the view has only the INSERT privilege for Scott's emp table, the view can only be used to insert new rows into the emp table, not to SELECT, UPDATE, or DELETE rows.
    .
  • If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION.

You can create views using the CREATE VIEW statement. Each view is defined by a query that references tables, materialized views, or other views. As with all subqueries, the query that defines a view cannot contain the FOR UPDATE clause.


The following statement creates a view on a subset of data in the emp table:


CREATE VIEW sales_staff AS
SELECT empno, ename, deptno
FROM emp
WHERE deptno = 10
WITH CHECK OPTION CONSTRAINT sales_staff_cnst;




Figure 13. Creating Views with check option


The query that defines the sales_staff view references only rows in department 10. Furthermore, the CHECK OPTION creates the view with the constraint (named sales_staff_cnst) that INSERT and UPDATE statements issued against the view cannot result in rows that the view cannot select. For example, the following INSERT statement successfully inserts a row into the emp table by means of the sales_staff view, which contains all rows with department number 10:


INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);


However, the following INSERT statement returns an error because it attempts to insert a row for department number 30, which cannot be selected using the sales_staff view:


INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);



Figure 14. Error because can not select specified row



The view could optionally have been constructed specifying the WITH READ ONLY clause, which prevents any updates, inserts, or deletes from being done to the base table through the view. If no WITH clause is specified, the view, with some restrictions, is inherently updatable.


Next Page: Oracle 10g Tutorials - Creating Views, Altering, Dropping and Replacing Views - Page 2


Read Next: Oracle 10g Tutorials- Updating Views



 

 

Comments


Jay said:

  I am trying to create a view with MINUS between two SELECT queries .
Say for example,
CREATE OR REPLACE VIEW DEPT AS
(SELECT DISTINCT emp_num from emp)
MINUS
(SELECT DISTINCT emp_num FROM managers)
/

But an exception is raised saying
SP-0042 - unkown command "MINUS"

Can anyone please let me if MINUS is a valid operation while creating a view?

Thanks in advance,
Jay
February 11, 2009, 1:24 am

Proprietario said:

  CREATE OR REPLACE VIEW DEPT AS
(SELECT DISTINCT emp_num from emp
MINUS
SELECT DISTINCT emp_num FROM managers);
March 25, 2009, 1:19 pm

abhishek kumar said:

  CREATE or REPLACE view DEPT as (select DISTINCT emp_num from emp MINUS select DISTINCT emp_num from managers);
as when you gave separate bracket for each querry oracle could'nt understand the operator 'MINUS' so u must write the subquerry into one arguments........
December 15, 2009, 1:34 pm

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape