[PL/SQL] View

😎😎

In Oracle we have 2 types of View

• Normal view : Simple view and Complex view
• Materialized view

1. What is the View?

2. What is Simple view and Complex view ?

3. What is Materialized view ?

Example :

• Create 2 views : normal and materialized view

• Now if we DELETE data in table emp, then the normal view is empty, but the m-view still remains data.

• To refresh m-view, we could use “dbms_mview.refresh(‘mview_jobhis_50’)”

4. FORCE keyword

• If we create a view on a table which doesn’t exist yet, then it’ll throw an error. In a few scenarios, we still want to create a view, because the underlying table will be created later, so we don’t want to bother about the order of object creation, so we could use FORCE view option.

• For checking if the view is created sucessfully, we can check in tables “user_views” and “user_objects”

• We see that the view is created sucessfully, but it’s INVALID status. If we try to query this view, we’ll receive the error because the underlying table doesn’t exist.

• Now, if we create a table and insert some data into table then commit. Then the view will become VALID and we can query on it now.

5. WITH READ ONLY keyword

As you might know, it’s possible to do DML on simple view. In case you don’t want this, you can disable it by using “WITH READ ONLY” keyword.

6. WITH CHECK OPTION keyword

If you want to restrict the view to DML on a specific data only, then you could use “WITH CHECK OPTION” keyword.

7. Constraint of view

• Optionally, you can specify “constraint” name. If you don’t specify a constraint name, Oracle will by default create a system generated constraint name.

• You can specify constraint names when create a view, and make sure that constraint names are unique, else you’ll receive the error.

Additional notes

1. Write a procedure to create a view for all tables in the given schema. The view name should be table name + “_V”

2. Oracle View related questions

Q1 : No, the moment we create the view with the “*”, Oracle will replace the “*” with available columns at that time only. You can check it in the dictionary table “user_views”

Q2 : The view will face error

Q3 : using dictionary table “user_dependencies”

3. Oracle View related questions

Q1 : for example, when we drop the column in base table, then thew view will be invalid.

If we re-create the column again, the view won’t be VALID automatically, so we need to recompile it.

There are 3 options to recompile : alter view, re-creating the view or just simply query on the view

NOTE

When the view is INVALID, but we don’t recompile the view. Instead, we call directly the view which in INVALID state, then Oracle will automatically try to recompile it at the first execution. Then if the recompile is succesfully, the the view is back to VALID. In case of the compile error, then it’ll throw an error.

Q2 : The same with Q1, we have 3 options to recompile the force view

Q3 : for example, we can create all the views with forcing option, then create tables. At the end of installation, you can recompile of all invalid objects. By this way, we no need to worry about dependencies accross installation.