c# barcode image generation library SQL> update dept20_v 2 set deptno = 30 3 where job ='TRAINER'; 4 rows updated. in Java

Encoding ECC200 in Java SQL> update dept20_v 2 set deptno = 30 3 where job ='TRAINER'; 4 rows updated.

SQL> select * from dept20_v; EMPNO ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO ----- -------- ----- -------- ----- ----------- ----- ----- -----7566 JONES JM MANAGER 7839 02-APR-1967 2975 20 SQL> rollback; Rollback complete. SQL> Apparently, the updates in Listing 10-15 are propagated to the underlying EMPLOYEES table. All trainers from department 20 don t show up anymore in the DEPT20_V view, because their DEPTNO column value is changed from 20 to 30.

The second curious scenario is shown in Listing 10-16. You insert a new row for employee 9999, and you get the message 1 row created. However, the new employee does not show up in the query. Listing 10-16. INSERT Rows Without Seeing Them in the View SQL> insert into dept20_v 2 values ( 9999,'BOS','D', null, null 3 , date '1939-01-01' 4 , '10', null, 30); 1 row created. SQL> select * from dept20_v; EMPNO ----7369 7566 7788 7876 7902 ENAME -------SMITH JONES SCOTT ADAMS FORD INIT ----N JM SCJ AA MG JOB MGR BDATE MSAL COMM DEPTNO -------- ----- ----------- ----- ----- -----TRAINER 7902 17-DEC-1965 800 20 MANAGER 7839 02-APR-1967 2975 20 TRAINER 7566 26-NOV-1959 3000 20 TRAINER 7788 30-DEC-1966 1100 20 TRAINER 7566 13-FEB-1959 3000 20

5 rows selected. SQL> rollback; Rollback complete. SQL> Listing 10-16 shows that you can insert a new employee via the DEPT20_V view into the underlying EMPLOYEES table, without the new row showing up in the view itself.

If the view behavior just described is undesirable, you can create your views with the WITH CHECK OPTION clause (see Figure 10-1). Actually, the syntax diagram in Figure 10-1 is not complete. You can assign a name to WITH CHECK OPTION constraints, as follows: SQL> create [or replace] view ... with check option constraint <cons-name>; If you don t provide a constraint name, the Oracle DBMS generates a rather cryptic one for you. Listing 10-17 replaces the DEPT20_V view, using WITH CHECK OPTION, and shows that the INSERT statement that succeeded in Listing 10-16 now fails with an Oracle error message. Listing 10-17. Creating Views WITH CHECK OPTION SQL> create or replace view dept20_v as 2 select * from employees where deptno = 20 3 with check option constraint dept20_v_check; View created. SQL> insert into dept20_v 2 values ( 9999,'BOS','D', null, null 3 , date '1939-01-01' 4 , '10', null, 30); , '10', null, 30) * ERROR at line 4: ORA-01402: view WITH CHECK OPTION where-clause violation SQL>

In the old days, when the Oracle DBMS didn t yet support referential integrity constraints (which is a long time ago, before Oracle7), you were still able to implement certain integrity constraints by using WITH CHECK OPTION when creating views. For example, you could use subqueries in the view definition to check for row existence in other tables. Listing 10-18 gives an example of such a view. Nowadays, you don t need this technique anymore, of course. Listing 10-18. WITH CHECK OPTION and Constraint Checking SQL> 2 3 4 5 6 7 create select from where and or replace view reg_view as r.* registrations r r.attendee in (select empno from employees) r.course in (select code from courses)

and r.evaluation in (1,2,3,4,5) with check option;

View created. SQL> select constraint_name, table_name 2 from user_constraints 3 where constraint_type = 'V'; CONSTRAINT_NAME -------------------SYS_C005979 DEPT20_V_CHECK SQL> Via the REG_VIEW view, you can insert registrations only for an existing employee and an existing course. Moreover, the EVALUATION value must be an integer between 1 and 5, or a null value. Any data manipulation command against the REG_VIEW view that violates one of the above three checks will result in an Oracle error message. CHECK OPTION constraints show up in the data dictionary with a CONSTRAINT_TYPE value V; notice the system generated constraint name for the REG_VIEW view. TABLE_NAME -------------------REG_VIEW DEPT20_V