7.3 How do I…Determine if a view can be updated?Problem
We use views to make querying data easier. I know many views can be updated, but I need a method to determine which ones they are. How do I determine if a view can be updated?
Technique
A view cannot be updated in some cases if it joins tables, uses a DISTINCT operator, or contains a GROUP BY clause or group functions. How-To 7.4 fully describes how to update a join view. You can use the TEXT column in the ALL_VIEWS data dictionary view to determine if any of these limitations exist in the query. An easier way to determine if the view can be updated is to perform a data management operation and trap the error if it occurs.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP7_6.SQL, shown in Figure 7.4, contains the statements used to build two tables and two views that will test the code segment developed in this How-To.
The two tables, TAB_A07 and TAB_B07, are the source tables for the two views. The first view, VIEW0_A07, selects all of the columns from the TAB_A07 table and can be updated. The second view, VIEW_B07, joins the two sample tables and cannot be updated due to the join condition.
2. Run the statement to create the tables and views.
SQL> START CHP7_6.sql
Table created.
Table created.
View created.
View created.
3. Load CHP7_7.SQL into the SQL buffer. The file contains an INSERT statement that tests if a data management statement can be performed on a view. Because the INSERT statement uses a query of the view, it is not necessary to know the structure of the view. The technique shown in this step will test all views.
SQL> GET CHP7_7
1 INSERT INTO VIEW_B07
2 SELECT * FROM VIEW_B07
3* WHERE 1 = 0<>
Line 1 specifies that records are inserted into VIEW_B07. Line 2 specifies that the records inserted are also queried from VIEW_B07. The WHERE clause in line 3 doesn’t insert any rows, because 1 is never equal to 0. If an INSERT statement cannot be executed on the view, an error will occur when the statement is run.
4. Run the statement to attempt to insert a record into the view.
SQL> /
insert into VIEW_B07 select * from VIEW_B07
* ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
The Oracle error ORA-01779 is returned when a data manipulation operation is attempted on a view where it is not allowed.
5. Load CHP7_8.SQL into the SQL buffer. The statement in the file creates a stored function to determine if a data manipulation operation can be performed on a view. For more information about the creation of stored functions, see How-To 10.4.
SQL> GET CHP7_8.sql
1 CREATE OR REPLACE FUNCTION CHECK_VIEW
2 (VIEW_NAME IN VARCHAR2) RETURN BOOLEAN IS
3 TMP VARCHAR2(60);
4 CURSOR_HANDLE INTEGER;
5 CNT INTEGER;
6 BEGIN
7 TMP := ‘INSERT INTO ‘ | | VIEW_NAME;
8 TMP := TMP | | ’ SELECT * FROM ‘ | | VIEW_NAME;
9 TMP := TMP | | ’ WHERE 1 = 0’;
10 CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
11 DBMS_SQL.PARSE(CURSOR_HANDLE, TMP, DBMS_SQL.V7);
12 CNT := DBMS_SQL.EXECUTE(CURSOR_HANDLE);
13 RETURN TRUE;
14 EXCEPTION
15 WHEN OTHERS
16 THEN
17 RETURN FALSE;
18* END;
Lines 1 and 2 declare a stored function that is passed a view name and returns a Boolean value. Lines 3 through 5 create the temporary variables used by the function. Lines 7 through 10 put the type of INSERT statement shown in the prior steps into the TMP variable. Line 10 uses the OPEN_CURSOR function from the DBMS_SQL stored package to open a cursor that will execute the INSERT statement. The DBMS_SQL package allows dynamic SQL to be created and executed against the database. Because the view name is passed as a parameter, a fixed SQL statement cannot be created in the PL/SQL function. Line 11 parses the INSERT statement contained in the TMP variable by executing the PARSE procedure in the DBMS_SQL package. Line 12 executes the INSERT statement, using the EXECUTE function from the same package. Line 13 returns TRUE if an error does not occur. Lines 14 through 17 handle an error created if the INSERT statement fails, by returning FALSE.
6. Execute the statement to create the stored function.
SQL> /
Function created.
7. Load CHP7_9.SQL into the SQL buffer. The file contains a PL/SQL statement to test the operation of the stored function created in the previous step.
SQL> GET CHP7_9.sql
1 DECLARE
2 X BOOLEAN;
3 BEGIN
4 X := CHECK_VIEW(‘VIEW_A07’);
5 IF X THEN
6 DBMS_OUTPUT.PUT_LINE(‘Can update VIEW_A07’);
7 ELSE
8 DBMS_OUTPUT.PUT_LINE(‘Cannot update VIEW_A07’);
9 END IF;
10 X := CHECK_VIEW(‘VIEW_B07’);
11 IF X THEN
12 DBMS_OUTPUT.PUT_LINE(‘Can update VIEW_B07’);
13 ELSE
14 DBMS_OUTPUT.PUT_LINE(‘Cannot update VIEW_B07’);
15 END IF;
16* END;
Line 4 executes the TEST_CHECK function for TEST_VIEW1. Lines 5 through 9 display the results of the function call. Line 10 executes the TEST_CHECK function for TEST_VIEW2. Lines 11 through 15 display the results of the function call.
8. Set the SERVEROUTPUT system variable to ON with the SET command and execute the statement to test the function. TEST_VIEW1 is updatable, and TEST_VIEW2 is not.
SQL> SET SERVEROUTPUT ON
SQL> /
Can update VIEW_A07
Cannot update VIEW_B07
PL/SQL procedure successfully completed.
How It Works
Steps 1 and 2 create the two sample tables and two views used in the example. Steps 3 and 4 present a method for testing a view by inserting records into it with a SELECT statement. The INSERT statement will not create any rows in the table if successful and will fail if the operation is not allowed. Step 4 attempts to execute the INSERT statement and fails because VIEW_B07 cannot be updated. Steps 5 and 6 create a stored function that is passed a view name, returning TRUE if the view data can be modified, FALSE if it cannot. The function uses the DBMS_SQL package to execute a dynamic SQL statement that tests the INSERT statement on the view. Steps 7 and 8 test the operation of the stored function created in steps 5 and 6.
Comments
The steps shown in this How-To test a view to determine if data management operations can be performed on the view. When the data in the view can be modified, views can provide benefits over using base tables in your applications. For more information, see How-To 7.4.
Also, by querying the DBA_UPDATABLE_VIEWS data dictionary view, you can determine all views that are and are not updatable.