This section gives you some suggested answers to the questions in Lab 11.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 11.2.1 Answers
The MODIFED_BY and MODIFIED_DATE columns have check constraints defined on them. These constraints can be viewed by querying one of the data dictionary tables. The data dictionary comprises tables owned by the user SYS. These tables provide the database with information that it uses to manage itself. Consider the following SELECT statement against one of Oracle's data dictionary tables, USER_CONSTRAINTS. This table contains information on various constraints defined on each table of the STUDENT schema. SELECT constraint_name, search_condition FROM user_constraints WHERE table_name = 'COURSE'; CONSTRAINT_NAME SEARCH_CONDITION ------------------------ --------------------------- CRSE_CREATED_DATE_NNULL "CREATED_DATE" IS NOT NULL CRSE_MODIFIED_BY_NNULL "MODIFIED_BY" IS NOT NULL CRSE_MODIFIED_DATE_NNULL "MODIFIED_DATE" IS NOT NULL CRSE_DESCRIPTION_NNULL "DESCRIPTION" IS NOT NULL CRSE_COURSE_NO_NNULL "COURSE_NO" IS NOT NULL CRSE_CREATED_BY_NNULL "CREATED_BY" IS NOT NULL CRSE_PK CRSE_CRSE_FK 8 rows selected. Notice that the last two rows refer to the primary and foreign key constraints, so there are no search conditions specified. Based on the results produced by the preceding SELECT statement, there are six columns having a NOT NULL constraint. However, the INSERT statement INSERT INTO course (course_no, description, created_by, created_date) VALUES (COURSE_NO_SEQ.NEXTVAL, 'TEST COURSE',USER, SYSDATE); has only four columns having NOT NULL constraints. The columns MODIFIED_BY and MODIFIED_DATE are not included in the INSERT statement. Any column of a table not listed in the INSERT statement has NULL assigned to it when a new record is added to the table. If a column has a NOT NULL constraint and is not listed in the INSERT statement, the INSERT statement fails and causes an error.
In this script, you declared the e_constraint_violation exception. Then, using the EXCEPTION_INIT pragma to associate the exception with the Oracle error number ORA-02290, the handler is written for the new exception e_constraint_violation .
Once you define an exception and associate an Oracle error number with it, you can write an exception handler for it. As a result, as soon as the INSERT statement causes an error, control of the execution is transferred to the exception-handling section of the block. Then, the message "INSERT statement..." is displayed on the screen. Notice that once an exception is raised, the execution of the program does not halt. The script completes successfully. |