Learning Objectives – after this chapter you will be able to:
- Explain what a view is, why it is called a virtual table / stored query, and why views are needed.
- Create views with
CREATE [OR REPLACE] [FORCE] VIEWand use them like tables. - Use views to simplify queries, present data differently and isolate applications from table changes.
- Describe view dependency (INVALID/VALID status).
- Change base tables through views, including updatable join views and key-preserved tables.
- Use
WITH CHECK OPTIONandDROP VIEW.
What is a View?
A view is a window through which you access a portion of one or more tables. It contains no data of its own; it refers to the data of its base table(s). Because it can be used wherever a table can be used, it is called a virtual table. Data-dictionary objects such as USER_TABLES and USER_CONSTRAINTS are actually views.
Why we need views
- Security / access control – user FIGO owns COURSES and wants RAUL to see everything except FEE. Granting the table would expose FEE; instead FIGO creates a view without FEE and grants the view. No copy of the data is stored and RAUL always sees up-to-date data.
- An extra layer that allows only predetermined rows or columns.
- Store complex queries in the database.
- Present data in different forms – rename, combine or split columns.
- Isolate applications from changes in table definitions.
Creating and Using Views
CREATE [OR REPLACE] [FORCE] VIEW viewname [(column, column, ...)] AS query [WITH CHECK OPTION [CONSTRAINT name]];- OR REPLACE – re-creates an existing view without dropping it and re-granting its privileges.
- FORCE – creates the view even if the base table does not exist yet (it must exist before the view is used).
- The notes state that ORDER BY cannot be used in the view’s query.
- Every expression in the query must have an alias; the alias becomes the view’s column name.
Access control, stored query and a different presentation
create view course_view as
select ccode, name, duration, prerequisite from courses; -- FEE hidden
select name, duration from course_view where duration > 25;
create view coursecount as
select ccode, count(*) nobatches, max(stdate) lastdate -- aliases required
from batches group by ccode;
select * from coursecount where nobatches > 1; -- group result usable in WHERE
create view faculty_names as
select fcode, substr(name, 1, instr(name,' ') - 1) firstname,
substr(name, instr(name,' ') + 1) lastname
from faculty; -- one column presented as twoIsolating Applications & Storage of a View
Programs that read batchdetails (a view joining BATCHES and COURSES for BCODE, CCODE, FEE, STDATE) are unaffected if FEE later moves from COURSES to BATCHES – only the view is re-created with CREATE OR REPLACE VIEW to read BATCHES alone.
- Oracle stores only the view’s query in the data dictionary (
USER_VIEWS.TEXT) – hence a stored query.*is expanded to the column list when stored. - A view may be based on tables, other views or snapshots (replicas of remote data).
Views and dependence
- If the base table is dropped, the view remains but is marked INVALID; using it gives ORA-04063: view has errors. Status is shown in
USER_OBJECTS.STATUS. - When the table is re-created with the required columns (extra columns do not matter, nor do datatypes), the view is recompiled automatically on next use and becomes VALID.
Changing the Base Table through a View
Rows can be inserted, updated and deleted through a view. To be inherently updatable, the view’s query must NOT contain:
- a set operator
- DISTINCT
- an aggregate (group) function
- GROUP BY, ORDER BY, CONNECT BY or START WITH
- a collection expression in the SELECT list
- a subquery in the SELECT list
- joins (with some exceptions – join views)
Columns derived from expressions or pseudo columns cannot be updated.
DML through a simple view
create view orabatches as select bcode, stdate, enddate, timing from batches where ccode = 'ora'; update orabatches set enddate = sysdate where bcode = 'b7'; delete from orabatches where bcode = 'b7'; -- deletes from BATCHES
Updatable join views and key-preserved tables
A table is key-preserved if every key of the table is also a key of the join result. In month_payments (PAYMENTS joined with STUDENTS on ROLLNO) PAYMENTS is key-preserved, STUDENTS is not.
- DML may change only one underlying table.
- UPDATE – all updated columns must come from a key-preserved table (updating NAME gives ORA-01779: cannot modify a column which maps to a non key-preserved table).
- DELETE – the join must have exactly one key-preserved table.
- INSERT – all inserted columns must come from a key-preserved table, and its NOT NULL columns must be in the view unless they have defaults.
USER_UPDATABLE_COLUMNSshows which view columns are updatable / insertable / deletable.
WITH CHECK OPTION and DROP VIEW
Without it, a change made through a view may produce a row that the view itself can no longer retrieve (e.g. setting AMOUNT to 2500 through high_payments which shows AMOUNT > 3000). WITH CHECK OPTION rejects such inserts/updates.
create or replace view high_payments as select * from payments where amount > 3000 with check option constraint high_payment_wco; insert into high_payments values (11, sysdate, 2000); -- ORA-01402: view WITH CHECK OPTION where-clause violation drop view high_payments; -- base table PAYMENTS is not affected
Key Takeaways
- A view stores a query, not data – a virtual table and a stored query.
- Views provide security, simplicity, alternative presentation and independence from table structure.
- Dropping a base table invalidates (not drops) its views.
- Join views can be updated only through key-preserved tables; WITH CHECK OPTION keeps changes visible through the view.
Chapter 10 Notes (PDF)
ch10.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 10 notes (PDF). Click a question to see the answer.
Answer : A view is a window through which a portion of one or more tables is accessed. A view does not contain any data of its own; it refers to the data of the table on which it is based, which is called the base table.
Answer : It is called a virtual table because it can be used wherever a table can be used – it executes its query and presents the data in the form of a table. It is called a stored query because Oracle stores only the query given at creation time in the data dictionary and executes it whenever the view is referred.
Answer : User FIGO owns COURSES and wants user RAUL to access it but not the FEE column. Granting the table would give RAUL complete access. Instead FIGO creates a view on COURSES with all columns except FEE and grants permission on the view only; RAUL sees accurate, up-to-date data without FEE, and no separate copy of data is stored.
Answer : Providing an extra layer over a table that allows only predetermined rows or columns to be accessed; storing complex queries in the database; presenting the data of a table in different forms (renaming, combining or splitting columns); and isolating applications from changes in the definition of tables.
Answer : OR REPLACE allows a view to be created even if one already exists, so a view can be altered without dropping, re-creating and re-granting object privileges. FORCE allows a view to be created even if the base table does not exist; the base table must exist before the view is used.
Answer : The alias of an expression becomes the name of the column in the view, and it is used to refer to the value of that expression, e.g. COUNT(*) NOBATCHES and MAX(stdate) LASTDATE in the COURSECOUNT view.
Answer : The application uses a view (e.g. BATCHDETAILS joining BATCHES and COURSES) instead of the tables. If the structure changes – e.g. FEE moves from COURSES to BATCHES – only the view is re-created with CREATE OR REPLACE VIEW to read the new structure; the application’s SELECT on the view stays the same.
Answer : The view is not dropped; its definition remains but it is marked INVALID and referring to it gives ORA-04063 (view has errors). When the table is re-created with the required columns, Oracle recompiles the view on its next use and its status becomes VALID again (extra columns or different datatypes do not matter). Status is shown in USER_OBJECTS.
Answer : Deleting rows from the base table, updating data of the base table and inserting rows into the base table – provided the view satisfies the conditions for being updatable.
Answer : A set operator, the DISTINCT operator, an aggregate function, a GROUP BY, ORDER BY, CONNECT BY or START WITH clause, a collection expression in the SELECT list, a subquery in the SELECT list, and joins (with some exceptions). Columns derived from expressions or pseudo columns cannot be updated.
Answer : A table is key-preserved if every key of the table can also be a key of the result of the join. In a view joining PAYMENTS and STUDENTS on ROLLNO, PAYMENTS is key-preserved, so its columns can be changed through the view.
Answer : The DML must manipulate only one underlying table; for UPDATE all updated columns must come from a key-preserved table; for DELETE the join must have one and only one key-preserved table; for INSERT all inserted columns must come from a key-preserved table and its NOT NULL columns must be in the view unless they have default values.
Answer : NAME belongs to STUDENTS, which is not key-preserved in the join, so Oracle raises ORA-01779: cannot modify a column which maps to a non key-preserved table. USER_UPDATABLE_COLUMNS shows NAME as not updatable, insertable or deletable.
Answer : It prevents updates and insertions through the view that the view cannot later retrieve. For example, HIGH_PAYMENTS shows payments with AMOUNT > 3000; with WITH CHECK OPTION, inserting a row with amount 2000 through the view fails with ORA-01402: view WITH CHECK OPTION where-clause violation.
Answer : DROP VIEW view_name removes the view. It has no effect on the base tables, but users who accessed the base tables only through the view can no longer access that data.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
- Oracle Corporation. (n.d.). Oracle Database Reference (initialization parameters and data dictionary views), 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/
- Date, C. J. (2003). An Introduction to Database Systems (8th ed.). Addison-Wesley.