Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Benjamin Rosenzweig

نسخه متنی -صفحه : 289/ 145

Lab 11.3 Exercise Answers

This section gives you some suggested answers to the questions in Lab 11.3, 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.3.1 Answers

A1:

Answer: Your output should look like the following:

BEGIN

ERROR at line 1:

ORA-00001: unique constraint (STUDENT.ZIP_PK) violated

ORA-06512: at line 2

The INSERT statement

INSERT INTO ZIPCODE (zip, city, state, created_by,

created_date, modified_by, modified_date)

VALUES ('10027', 'NEW YORK', 'NY', USER, SYSDATE, USER, SYSDATE);

causes an error because a record with zipcode 10027 already exists in the ZIPCODE table. Column ZIP of the ZIPCODE table has a primary key constraint defined on it. Therefore, when you try to insert another record with the value of ZIP already existing in the ZIPCODE table, the error message "ORA-00001: unique constraint..." is generated.

A2:

Answer: Your script should resemble the script shown. All changes are shown in bold letters.

-- ch11_3b.sql, version 2.0 SET SERVEROUTPUT ON BEGIN INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date) VALUES ('10027', 'NEW YORK', 'NY', USER, SYSDATE, USER, SYSDATE); COMMIT;

EXCEPTION

WHEN OTHERS THEN

DECLARE

v_err_code NUMBER := SQLCODE;

v_err_msg VARCHAR2(100) := SUBSTR(SQLERRM, 1, 100);

BEGIN

DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);

DBMS_OUTPUT.PUT_LINE ('Error message: '||

v_err_msg);

END; END;

In this script, you add an exception-handling section with the OTHERS exception handler. Notice that two variables v_err_code and v_err_msg, are declared, in the exception-handling section of the block, adding an inner PL/SQL block.

A3:	Answer: Your output should look similar to the following: Error code: -1 Error message: ORA-00001: unique constraint (STUDENT.ZIP_PK) violated PL/SQL procedure successfully completed.

Because the INSERT statement causes an error, control is transferred to the OTHERS exception handler. The SQLCODE function returns -1, and the SQLERRM function returns the text of the error corresponding to the error code -1. Once the exception-handling section completes its execution, control is passed to the host environment.