I am now studying for my Oracle Certification exam. The first one I plan to take is “Introduction to Oracle: SQL and PL/SQL”. I have experience in some of these topics such as basic SQL and cursors. However there are a number of topics that I have little to no background in. These include creating views.My plan was to purchase a self study CD course from Oracle to learn the material well. However that requires approval from my company as they will be paying for it. In the mean time, I just started Googling Oracle views. This did not turn out to be an optimal method. But it was better than nothing.
I took the top 10 Google results web sites for Oracle view. After studying them, I had a lot of questions. The best way I found to answer these questions is to try things out. I have my Oracle Express Edition installed on the home PC. Here are some things I learned about views that I could not tell for sure from browsing the web.
When you create a view WITH CHECK OPTION, it is supposed to restrict your ability to insert or update rows that would not be visible through the view query. I was not entirely sure if that restriction applied only to actions done through the view, or on the base tables themselves. Sure enough you can do whatever you want with the base tables. Only inserts and updates using the view get affected by the WITH CHECK OPTION.
I also read about complex views. These are views which join more than one table with their query. Apparently you can only update one table at a time through the view. That did not seem right. So I created a complex view and tried it out myself. Sure enough, when I tried to update two tables through the view, I got an exception.
It has been slow going. Trying to learn by just using Google is a painful process. My immediate solution was to buy a book to help me. I got the “OCP Oracle 8i DBA SQL and PL/SQL Study Guide”. To my surprise, I did pretty good on the assessment test in that book. That made me feel good. However I am going to go through the entire book to get better.
In more good news, I just found out that my company approved the purchase of the Oracle self study CD course. Now I know I can pass this first test.
