Friday , October 4 2024

Oracle Views

In this article, we will discuss the functions and types of Views in Oracle Database in detail.

View is a virtual table that does not physically exist. It is stored in Oracle data dictionary and do not store any data. It can be executed when called.

They are used to simplify complex SQL data and limit the data that users can access.

Information can be obtained by querying DBA/ALL/USER_VIEWS views about views.

Views can be created on a table or another view.

In order to create a view in our own schema, it is necessary to have “CREATE VIEW” system authority, and to create a view in another user’s schema, it is necessary to have “CREATE ANY VIEW” system authority.

If a view is created with one of the following structures, delete and update operations cannot be performed from the view.

A Set Operator (Union, union all, intersect, minus)
A DISTINCT Operator
An aggregate or analytic function
A Group by, Order by, Model, Connect by, Start with.

Views are created with “CREATE VIEW” or “CREATE OR REPLACE VIEW” sentences.

Views are divided into 2 groups.

SIMPLE VIEWS

Allows DML operations.

Data entry made via view is recorded in the view’s table.

They are views created on a single table. They do not contain any functions.

Data cannot be deleted from a view created with DISTINCT.

For example,

COMPLEX VIEWS

They are views that contain functions and tables that are related to each other.

No DML operations are performed.

We use the WITH CHECK OPTION clause to prevent insertion of data other than the data we have the authority to see.

When the With Check Option is used, the expression specified in the WHERE clause must be in the Select list.

When creating views, the existence of tables is first checked. If the table does not exist, an error is thrown. In this case, the view is not created. If we still want to create it, then we add the FORCE word.

If our view is a simple view but we don’t allow DML operations on it, we make the view read-only.

An invalid view can be recompiled with the “ALTER VIEW” statement.

The name of the View is changed as follows.

The view is DROP as follows. However, before dropping the view, a dependency check must be performed. Because when the view is dropped, the objects it depends on (procedure, function, ..) become invalid.

DEPENDENCE CONTROL

Dependency check is done as follows.

In the DBA_DEPENDENCIES view, you can see which object is dependent on which object.

The sample query is as follows.

In the query above, we see which objects the ACCOUNT object is dependent on.

If we delete the ACCOUNT object, the resulting objects will also become invalid.

Loading

About Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories