Database Programming with PL/SQL 7-3: Trapping User-Defined Exceptions Practice Activities Vocabulary Identify the vocabulary word for each definition below:
RAISE_APPLICATION_ERROR RAISE
USER-DEFINED-ERROR
A procedure used to return user-defined error messages from stored subprograms. Use this statement to raise a named exception.
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 Al l t he ques q uesti ti ons on s in i n thi t his s exerc ex ercis is e use us e a cop y of o f the t he em ploy pl oyees ees t able. abl e. Create t his hi s cop c opy y by runni ng the followi ng SQL SQL statement: statement: CREATE CREATE TABL E excep_emps excep _emps AS SELECT * FROM FROM emp emplo loyees; yees; Create a PL/SQL PL/SQL bloc k that u pdates the salary of every emp loyee loy ee to a new new value of 1. Create 1000 10000 0 in a c hosen department. Include a user-defined exception handler th at handles the conditi on where no rows are updated and and dis plays a custom message. message. Also Al so in cl ude ud e an exc eptio ept ion n hand h andler ler that th at wil w il l t rap any other ot her poss po ss ible ib le er ro r con c ondi diti ti on and display the corresponding SQLCODE and SQLERRM. Test your code three tim es, usin g departm ent_ids 20, 20, 30, 30, and 40.
or its affiliates. Other names may be trademarks of their
DECLARE e_emp_excep EXCEPTION; v_emp_salary NUMBER := 10000; v_dep_id NUMBER := 40; BEGIN UPDATE employees SET salary = v_emp_salary WHERE department_id = v_dep_id; IF SQL%ROWCOUNT = 0 THEN RAISE e_emp_excep; END IF; EXCEPTION WHEN e_emp_excep THEN DBMS_OUTPUT.PUT_LINE('No se actualizó'); DBMS_OUTPUT.PUT_LINE('Código de erro r: ' || SQLCODE || ' | ' || ' Mensaje: ' || SQLERRM); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); DBMS_OUTPUT.PUT_LINE('Código de erro r: ' || SQLCODE || ' | ' || ' Mensaje ' || SQLERRM); END; 2. Modify your c ode from questi on 1 to handle the condi tion wh ere no rows are updated using RAISE_APPLICATION_ERROR proc edure in the excepti on s ection. Use an error n umber of –20202. Test your code again us ing department _id 40 and check that the –20202 error is d isplayed.
3. Modif y you r code fr om q uesti on 2 to use RAISE_APPLICATION_ERROR in t he executable section in stead of the exceptio n section . Test your co de again us ing department_id 40.
DECLARE e_emp_excep EXCEPTION; v_emp_salary NUMBER := 10000; v_dep_id NUMBER := 40; BEGIN UPDATE employees SET salary = v_emp_salary WHERE department_id = v_dep_id; IF SQL%ROWCOUNT = 0 THEN RAISE_APPLICATION_ERROR(-20202, 'No rows updated' ); END IF; END 4. Be careful incorporating DELETE statements in PL/SQL blocks. If you make a mist ake, you may inadvertently d elete data that you didn 't m ean to delete.
A. Ent er and run th e foll owing PL/SQL bl ock usi ng department_id = 40, and explain the output. DECLARE v_dept_id excep_emps .department_id %TYPE; v_coun t 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 || ' empl oyees were deleted'); ROLLBACK; END; Al corer se muestra el error “There are 0 employees. 0 employees were deleted” porque department _id = 40 no ex is te
B. Modify your code to incl ude two user-defined exception handlers, one to test whether SELECT returns a value of 0, and t he other to test if no row s were DELETEd. Declare the exceptions and RAISE them explicitly before trapping them i n t he EXCEPTION section. Do NOT use RAISE_APPLICATION_ERROR. Test your modif ied block usin g department_id 40.
DECLARE v_dept_id excep_emps.department_id%TYPE; v_cont NUMBER; e_emp_excep EXCEPTION; e_rows_delete EXCEPTION; BEGIN v_dept_id : = 40; SELECT COUNT(*) INTO v_cont FROM excep_emps WHERE department_id = v_dept_id; IF v_con t = 0 THEN RAISE e_emp_excep; END IF; DBMS_OUTPUT.PUT_LINE('Empleados: ' || v_cont); DELETE FROM excep_emps WHERE department_id = v_dept_id; IF SQL%NOTFOUND THEN RAISE e_rows_delete; END IF; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' em pleados eliminados'); ROLLBACK; EXCEPTION WHEN e_emp_excep THEN DBMS_OUTPUT.PUT_LINE('El departamento no tiene empleados'); WHEN e_rows_delete THEN DBMS_OUTPUT.PUT_LINE('No se encontraron fil as eli min adas'); END; C. Modif y your b loc k again t o use RAISE_APPLICATION_ERROR in t he executable section. Use error numbers –20203 and –20204. Test your m odif ied bloc k usi ng department_id 40.
IF v_cont = 0 THEN RAISE_APPLICATION_ERROR(-20203, ' El departamento no existe'); END IF; DBMS_OUTPUT.PUT_LINE('Empleados: ' || v_cont); DELETE FROM excep_emps WHERE department_id = v_dept_id; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20204, 'No se eliminaron filas'); END IF; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' em pleados eliminados'); ROLLBACK; END;
