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
| a) | What output is printed on the screen? | 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.
| b) | Modify the script so that the script completes successfully, and the error number and message are displayed on the screen. | 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.
| c) | Run the new version of the script. Explain the output produced by the new version of the script. | 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.
|
No comments:
Post a Comment