• Author / Uploaded
• Madalina Cornea

• Categories
• Pl / Sql
• SQL
• Base de datos Oracle
• Recuperación de información
• Bases de datos

Citation preview

Section 7 Lesson 1: Handling Exceptions Vocabulary Identify the vocabulary word for each definition below: AN EXCEPTION HANDLER is Code that defines the recovery actions to be performed when execution-time errors occur. AN EXCEPTION Occurs when an error occurs during the execution of a program that disrupts the normal operation of the program. EXCEPTION HANLING - Allows clean separation of the error processing code from the executable code so that a program can continue operating in the presence of errors. EXCEPTION PROPAGATING - The exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search in. Try It / Solve It 1. What happens when Oracle encounters a runtime problem while executing a PL/SQL block?

An exception occurs when an error is discovered during the execution of a program that disrupts the normal operation of the program. 2. What do you need to add to your PL/SQL block to address these problems?

When code does not work as expected, PL/SQL raises an exception. When an exception occurs, you say that an exception has been “raised.” When an exception is raised, the rest of the execution section of the PL/SQL block is not executed. When writing code, programmers need to anticipate the types of errors that can occur during the execution of that code. They need to include exception handlers in their code to address these errors. In a sense, exception handlers allow programmers to "bulletproof" their code. 3. List three advantages of handling exceptions within a PL/SQL block.

 Protects the user from errors (Frequent errors can frustrate the user and/or cause the user to quit the application.) • Protects the database from errors (Data can be lost or overwritten.) • Major errors take a lot of system resources (If a mistake is made, correcting the mistake can be costly; users might frequently call the help desk for assistance with errors.)  Code is more readable (Error-handling routines can be written in the same block in which the error occurred.) 4. Run this PL/SQL code and then answer the questions that follow. DECLARE v_jobid employees.job_id%TYPE; BEGIN SELECT job_id WHERE department_id = 80; END; A. What happens when you run the block?

INTO v_jobid

ORA-01422: exact fetch returns more than requested number of rows

0.05 seconds

B. In your own words, explain what you can do to fix this problem. C. Modify the code to fix the problem. Use a TOO_MANY_ROWS exception handler. D. Run your modified code. What happens this time?

FROM employees

5. Run the following PL/SQL block, which tries to insert a new row (with department_id = 50) into the departments table. What happens and why? BEGIN INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (50, 'A new department', 100, 1500); DBMS_OUTPUT.PUT_LINE('The new department was inserted'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('An exception has occurred.'); END; An exception has occurred. 1 row(s) inserted. 0.06 seconds

6. Enter the following PL/SQL block, which tries to SELECT all the employees in a specific department. Run it three times, using department_ids 10, 20, and 30. What happens and why? DECLARE v_employee_id employees.employee_id%TYPE; v_last_name employees.last_name%TYPE; BEGIN SELECT employee_id, last_name INTO v_employee_id, v_last_name FROM employees WHERE department_id = ; DBMS_OUTPUT.PUT_LINE('The SELECT was successful'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); END; The SELECT was successful Statement processed. 0.01 seconds

7. Modify your code from question 6 to add two more exception handlers to trap the possible exceptions individually. Use NO_DATA_FOUND and TOO_MANY_ROWS. Re-run the block three times, using 10, 20, and 30 as before. Observe the message displayed in each case. 8. List three guidelines for trapping exceptions.

 Always add exception handlers whenever there is a possibility of an error ccurring. Errors are especially likely during calculations, string manipulation, and SQL database operations.  Handle named exceptions whenever possible, instead of using OTHERS in exception handlers. Learn the names and causes of the predefined exceptions.  Test your code with different combinations of bad data to see what potential errors arise. 9. Enter and run the following PL/SQL block. Explain the output. Note: the WHEN OTHERS handler successfully handles any type of exception which occurs. DECLARE v_number NUMBER(2); BEGIN v_number := 9999; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); END;

An exception has occurred Statement processed. 0.01 seconds

10. Modify the block in question 9 to omit the exception handler, then re-run the block. Explain the output. 11. Enter and run the following code and explain the output. DECLARE v_number NUMBER(4); BEGIN v_number := 1234; DECLARE v_number NUMBER(4); BEGIN v_number := 5678; v_number := 'A character string'; END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); DBMS_OUTPUT.PUT_LINE('The number is: '||v_number); END; An exception has occurred The number is: 1234 Statement processed. 0.00 seconds

Section 7 Lesson 2: Trapping Oracle Server Exceptions Vocabulary Identify the vocabulary word for each definition below: PREDEFINED ORACLE SERVER ERRORS - Each of these has a predefined name. For example, if the error ORA-01403 occurs when no rows are retrieved from the database in a SELECT statement, then PL/SQL raises the predefined exception-name NO_DATA_FOUND. PRAGMA EXCEPTION_INIT - Tells the compiler to associate an exception name with an Oracle error number. That allows you to refer to any Oracle Server exception by name and to write a specific handler for it. SQLERRM - Returns character data containing the message associated with the error number. NON PREDEFINED ORACLE EXCEPTIONS - Each of these has a standard Oracle error number (ORAnnnnn) and error message, but not a predefined name. We declare our own names for these so that we can reference these names in the exception section.

SQLCODE - Returns the numeric value for the error code (You can assign it to a NUMBER variable.) Try It / Solve It 1. What are the three types of exceptions that can be handled in a PL/SQL block? a) Predefined Oracle server error b) Non Predefined Oracle server error c) User-defined error 2. What is the difference in how each of these three types of exceptions is handled in the PL/SQL block? Predefined oracle server error - You need not declare these exceptions. They are predefined by the Oracle server and are raised implicitly (automatically). Non Predefined oracle server error - Declare within the declarative section and allow the Oracle Server to raise them implicitly(automatically). User-defined error - Declare within the declarative section, and raise explicitly. 3. Enter and run the following PL/SQL block. Look at the output and answer the following questions: DECLARE v_number NUMBER(6,2) := 100; v_region_id wf_world_regions.region_id%TYPE; v_region_name wf_world_regions.region_name%TYPE; BEGIN SELECT region_id, region_name INTO v_region_id, v_region_name FROM wf_world_regions WHERE region_id = 1; DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id || ' is: ' || v_region_name); v_number := v_number / 0; END; A. What error message is displayed and why ? ORA-01403: no data found

0.07 seconds

B. Modify the block to handle this exception and re-run your code. Now what happens and why? C. Modify the block again to change the WHERE clause to region_id = 29. Re-run the block. Now what happens and why? D. Modify the block again to handle the latest exception and re-run your code. 4. Enter and run the following PL/SQL block. Look at the output and answer the following questions: DECLARE CURSOR regions_curs IS

SELECT * FROM wf_world_regions WHERE region_id < 20 ORDER BY region_id; regions_rec regions_curs%ROWTYPE; v_count NUMBER(6); BEGIN LOOP FETCH regions_curs INTO regions_rec; EXIT WHEN regions_curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Region: '|| regions_rec.region_id || ' Name: ' || regions_rec.region_name); END LOOP; CLOSE regions_curs; SELECT COUNT(*) INTO v_count FROM wf_world_regions WHERE region_id = 1; DBMS_OUTPUT.PUT_LINE('The number of regions is: ' || v_count); END; A. What happens and why ? ORA-01001: invalid cursor 0.01 seconds

B. Modify the block to handle the exception and re-run your code. C. Modify the block again to add an OPEN statement for the cursor, and re-run your code. Now what happens and why? Remember that region_id = 1 does not exist. 5. Oracle Server Errors: A. Add an exception handler to the following code to trap the following predefined Oracle Server errors: NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX. DECLARE v_language_id wf_languages.language_id%TYPE; v_language_name wf_languages.language_name%TYPE; BEGIN SELECT language_id, language_name INTO v_language_id, v_language_name FROM wf_languages WHERE LOWER(language_name) LIKE ''; -- for example 'ab%' INSERT INTO wf_languages(language_id, language_name) VALUES(80, null); END; ORA-01403: no data found

0.03 seconds

B. Test your block twice using each of the following language substrings: ba, ce. There are several language_names beginning with “Ba” but none beginning with “Ce”.

C. Now test your block a third time using substring: al. There is exactly one language_name beginning with “Al”. Note that language_id 80 (Arabic) already exists. Explain the output. D. Now (keeping the substring as “al”) add a non_predefined exception handler to trap the ORA-01400 exception. Name your exception e_null_not_allowed. Rerun the code and observe the results.

Section 7 Lesson 3: Trapping User-Defined Exceptions Vocabulary Identify the vocabulary word for each definition below: RAISE_APPLICATION_ERROR- procedure used to return user-defined error messages from stored subprograms. RAISE STATEMENT - Use this statement to raise a named exception. USER DEFINED ERROR - These errors are not automatically raısed by the Oracle Server, but are defined by the programmer and are specific to the programmer's code. Try It / Solve It All the questions in this exercise use a copy of the employees table. Create this copy by running the following SQL statement: CREATE TABLE excep_emps AS SELECT * FROM employees; 1. Create a PL/SQL block that updates the salary of every employee to a new value of 10000 in a chosen department. Include a user-defined exception handler that handles the condition where no rows are updated and displays a custom message. Also include an exception handler that will trap any other possible error condition and display the corresponding SQLCODE and SQLERRM. Test your code three times, using department_ids 20, 30, and 40. 2. Modify your code from question 2 to handle the condition where no rows are updated using RAISE_APPLICATION_ERROR procedure in the exception section. Use an error number of –20202. Test your code again using department_id 40 and check that the – 20202 error is displayed. 3. Modify your code from question 3 to use RAISE_APPLICATION_ERROR in the executable section instead of the exception section. Test your code again using department_id 40. 4. Before starting this question, disable Autocommit in Application Express. A. Enter and run the following PL/SQL block using department_id = 40, and explain the output.

DECLARE v_dept_id excep_emps.department_id%TYPE; v_count NUMBER; BEGIN v_dept_id := 40; SELECT COUNT(*) INTO v_count FROM excep_emps WHERE department_id = v_dept_id; DBMS_OUTPUT.PUT_LINE('There are ' || v_count || ' employees'); DELETE FROM excep_emps WHERE department_id = v_dept_id; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees were deleted'); ROLLBACK; END; ORA-06550: line 2, column 11: PLS-00201: identifier 'EXCEP_EMPS.DEPARTMENT_ID' must be declared ORA-06550: line 2, column 11: PL/SQL: Item ignored ORA-06550: line 5, column 1: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 5, column 1: PL/SQL: Statement ignored ORA-06550: line 7, column 6: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 6, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 10, column 13: PL/SQL: ORA-00942: table or v 1. DECLARE 2. v_dept_id excep_emps.department_id%TYPE; 3. v_count NUMBER; 4. BEGIN B. Modify your block to include two user-defined exception handlers, one to test whether SELECT returns a value of 0, and the other to test if no rows were DELETEd. Declare the exceptions and RAISE them explicitly before trapping them in the exception section. Do NOT use RAISE_APPLICATION_ERROR. Test your modified block using department_id 40. C. Modify your block again to use RAISE_APPLICATION_ERROR in the executable section. Use error numbers –20203 and –20204. Test your modified block using department_id 40.

Section 7 Lesson 4: Recognizing the Scope of Exceptions Vocabulary Identify the vocabulary word for each definition below: Propagating Exceptions to an Outer Block - The inner block terminates unsuccessfully and PL/SQLpasses (propagates) the to the outer block. The outer block’s EXCEPTION section successfully handles the exception. User-named exceptions (non-predefined Oracle server exceptions and user-defined exceptions) are declared by the programmer as variables of type EXCEPTION. They follow the same scoping rules as other variables. The portion of a program in which the exception is declared and is accessible. Try It / Solve It 1. Enter and run the following code twice, once for each of the two country_ids 5 (which does not exist) and 672 (Antarctica, which does exist but has no currency). DECLARE v_country_name wf_countries.country_name%TYPE; v_currency_code wf_countries.currency_code%TYPE; BEGIN DECLARE e_no_currency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_country_name, v_currency_code FROM wf_countries WHERE country_id = 5; -- repeat with 672 IF v_currency_code = 'NONE' THEN RAISE e_no_currency; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('This country does not exist'); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE('This country exists but has no currency'); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Another type of error occurred'); END; A. Explain the output. Save your code. DECLARE v_country_name wf_countries.country_name%TYPE; v_currency_code wf_countries.currency_code%TYPE; BEGIN

DECLARE e_no_currency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_country_name, v_currency_code FROM wf_countries WHERE country_id = 5; IF v_currency_code = 'NONE' THEN RAISE e_no_currency; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('This country does not exist'); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE('This country exists but has no currency'); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Another type of error occurred'); END; This country does not exist Statement processed. 0.11 seconds

DECLARE v_country_name wf_countries.country_name%TYPE; v_currency_code wf_countries.currency_code%TYPE; BEGIN DECLARE e_no_currency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_country_name, v_currency_code FROM wf_countries WHERE country_id = 672; IF v_currency_code = 'NONE' THEN RAISE e_no_currency; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('This country does not exist'); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE('This country exists but has no currency'); END;

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Another type of error occurred'); END; This country exists but has no currency Statement processed. 0.05 seconds

B. Modify the code to move the two exception handlers to the outer block. Leave the declaration of e_no_currency in the inner block. Execute twice, again using country_ids 5 and 672. Now what happens and why? Save your code. C. Modify the code again to move the declaration of e_no_currency to the outer block. Reexecute again using country_ids 5 and 672. Now what happens and why?