Lab 11.2 Exercise Answers
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
A1:
| Answer: Your output should look like the following: BEGIN
*
ERROR at line 1:
ORA-02290: check constraint (STUDENT.CRSE_MODIFIED_DATE_NNULL) violated
ORA-06512: at line 2
| A2:
| Answer: The script does not execute successfully because a NULL is inserted for the MODIFIED_BY and MODIFIED_DATE columns. |
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 statementINSERT 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.A3:
| Answer: Your script should look similar to the script shown. All changes are shown in bold letters. -- ch11_2b.sql, version 2.0
SET SERVEROUTPUT ON
DECLARE
e_constraint_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_constraint_violation, -2290);
BEGIN
INSERT INTO course
(course_no, description, created_by, created_date)
VALUES
(COURSE_NO_SEQ.NEXTVAL, 'TEST COURSE', USER, SYSDATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('One course has been added');
EXCEPTION
WHEN e_constraint_violation THEN
DBMS_OUTPUT.PUT_LINE ('INSERT statement is '||
'violating a constraint');
END;
|
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.A4:
| Answer: Your output should look similar to the following: INSERT statement is violating a constraint
PL/SQL procedure successfully completed.
|
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. |
لطفا منتظر باشید ...
