The Oracle Certified Professional DBA Certifcation Exam Guide is wrong

From: <suisum_at_ecn.ab.ca>
Date: 4 Jan 99 21:27:10 GMT
Message-ID: <3691322e.0@ecn.ab.ca>

I refer to the recent discussion on views creation - order by clause. However, I found that the Chapter 4 Creating Simple and Complex Views sections are wrong. It mentioned two times the ORDER BY clause can be used in the SELECT statement for view creation.

Creating Simple and Complex Views

One example statement for creating a view has already been identified. To delve further into the requirements for creating views, the following discussion is offered. Creating a view is accomplished by using the create view statement. Once created, views are owned by the user who created them. They cannot be reassigned by the owner unless the owner has the create any view system privilege. More about privileges will be covered in a later section of this chapter.

There are different types of views that can be created in Oracle. The first type of view is a simple view. This type of view is created from the data in one table. Within the simple view, all single-row operations are permitted. In addition, data can be placed in specific order or into

groups by the group by or order by clause of the select statement. The
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

only option that is not allowed for a simple view is reference to more than one table. The following code block demonstrates creation of a simple view.

CREATE VIEW employee_view
AS (SELECT empid, lastname, firstname, salary FROM employee
WHERE empid = 59495);

Users of a simple view can insert data in the underlying table of the view if the creator of the view allows them to do so. A few restrictions apply. First, the data that the user attempts to insert into an underlying table via the view must be data that the user would be able to select via the view if the data existed in the table already. However, updating or inserting data on rows or columns on a table that the view itself would not allow the user to see is only permitted if the with check option is not used. The following statement demonstrates data change via a view.

UPDATE employee_view
SET salary = 99000
WHERE empid = 59495;

The restrictions on inserting or updating data to an underlying table through a simple view are listed below:

. The user may not insert, delete, or update data on the table underlying
the simple view if the view itself is not able to select that data for the user if the with check option is used.

. The user may not insert, delete, or update data on the table underlying

the simple view if the select statement creating the view contains group
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
by or order by, or a single-row operation.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

. No data may be inserted to simple views that contain references to any
virtual column such as ROWID, CURRVAL, NEXTVAL, and ROWNUM.

. No data may be inserted into simple views that are created with the read
only option. Received on Mon Jan 04 1999 - 15:27:10 CST