Lab 11.3 Exercise Answers | Oracle PL[s]SQL by Example

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

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.

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.

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.