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

a)

What output is printed on the screen?

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  
b)

Explain why the script does not execute successfully.

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 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.

c)

Add a user-defined exception to the script, so that the error generated by the INSERT statement is handled.

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 .

d)

Run the new version of the script. Explain the output produced by the new version of the script.

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.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net