Views Revisited

I received and installed a couple Oracle self-study CR-ROMs. This was very exciting. I was hoping to be enlightened. Instead I spent a lot of time struggling with the software.

It was hard to get used to the navigation system. Then I tried to enter answers for questions it asked me. The darn thing kept marking me wrong because I did not put spaces in at the places it expected them. Now I can understand if Oracle itself required spaces to be a certain way. But they do not matter. This was just poorly written training software.

For now I have went back to my independent study and search for information on the web. I am going to not use the Oracle CD-ROMs even though they cost almost $500. Now let's talk about some details I have learned from the web. Views are based on tables which are called Base Tables in this context. Views can also be based on other views. You need explicit grants to the Base Tables. You cannot obtain the permissions through a role to use them in a view.

A view can be created even if the SQL is erroneous as long as you specify the FORCE option. There are a lot of scenarios where you cannot updates values through a view. They have to do with the nature of the query used to create the view. Any of these prevents the update through the view: set operators such as UNION, DISTINCT, aggregate functions like COUNT, GROUP BY, and ORDER BY.

There are some other restrictions like this. But these are ones I would use myself. You can also specify a WITH CHECK OPTION when creating a view. That means a user cannot update or insert through the view if the resulting rows would not be visible using the view query.