Manejo de Excepciones Oracle PL/SQL
Un programa robusto no es aquel que no falla, sino aquel que controla adecuadamente sus excepciones.
Una excepción es el resultado de una ejecución anormal del programa, una condición que no debería darse, que esperamos que no ocurra o un imprevisto no contemplado.
Puede parecer bastante obvio el uso de las excepciones y la teoría correspondiente. Pero si bien la explicación inicia con lo básico que debemos conocer todos, en la parte final se explica el funcionamiento que puede ocasionar problemas en los programas, por el no entendimiento de la teoría básica y la mala utilización del manejo de excepciones.
Existen dos tipos de excepciones en Oracle:
• Excepciones pre-definidas en el sistema.
• Excepciones definidas por el usuario.
Excepciones pre-definidas en el sistema
Son aquellas definidas y presentes implícitamente en el servidor de Oracle. Se encuentran definidas en el paquete “STANDARD” de Oracle el cual contiene la definición de todas las funciones básicas del motor (Funciones de una sola fila, procesamiento de transacciones, definición de tipos de datos, entre otros).
Las excepciones pre-definidas más ampliamente utilizadas son:
ORA-00001 DUP_VAL_ON_INDEX
ORA-01403 NO_DATA_FOUND
ORA-01422 TOO_MANY_ROWS
ORA-01476 ZERO_DIVIDE
Como ejemplo se va a asumir que existe una tabla que posee los valores de los salarios mínimos mensuales por cada año. Un campo es ANIO numérico de cuatro posiciones que posee el valor del año, y un campo SALARIO numérico de ocho posiciones que posee el valor del salario mínimo mensual.
El siguiente programa muestra el valor del salario mínimo para el año 2014.
Desde mi punto de vista no existe LA MANERA de resolver el problema. Existen múltiples formas de atacar y resolver un problema, algunas mejores que otras.
DECLARE
v_salario_minimo minimos_anuales.salario%type;
BEGIN
SELECT salario
INTO v_salario_minimo
FROM minimos_anuales
WHERE anio = 2014;
DBMS_OUTPUT.PUT_LINE('EL VALOR DEL SALARIO MINIMO PARA 2014 ES '||v_salario_minimo);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO ESTA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('SE ENCUENTRA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014 MAS DE UNA VEZ');
END;
Se podría en principio pensar que no es necesario manejar la excepción TOO_MANY_ROWS, dado que la tabla debe (o debería), tener una llave primaria o única por el campo ANIO. Y muy posiblemente exista. Pero ¿qué sucede si esa llave se borra y por un error en el programa que inserta permite duplicar el registro?
Lo normal es que el código nunca pase por esa excepción, pero precisamente la excepción es para controlar una condición inesperada que no debería darse. De la misma manera podría no manejarse la excepción NO_DATA_FOUND, dado que se espera que el valor del salario mínimo para un nuevo año sea ingresado a final del anterior.
Existe otra excepción que se utiliza “en algunas ocasiones mal”, denominada OTHERS. El programa anterior perfectamente podría ser reemplazado por el siguiente:
DECLARE
v_salario_minimo minimos_anuales.salario%type;
BEGIN
SELECT salario
INTO v_salario_minimo
FROM minimos_anuales
WHERE anio = 2014;
DBMS_OUTPUT.PUT_LINE('EL VALOR DEL SALARIO MINIMO PARA 2014 ES '||v_salario_minimo);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXISTE UN PROBLEMA CON EL SALARIO PARA EL AÑO 2014');
END;
En este caso se puede utilizar aunque no es del todo correcto, puesto que el mensaje no es claro. “Existe un problema.”, pero ¿cuál? Cuando se detalla el manejo de excepciones a un nivel más bajo, se puede ser más claro.
No está definido el salario mínimo para el año.
Se encuentra definido el salario mínimo para el año más de una vez.
Entre más claro el mensaje, más sencillo va a ser para el usuario encontrar la solución.
Posteriormente se detallará el uso inadecuado de la excepción OTHERS.
Excepciones definidas por el usuario
Algunas veces los programas deben manejar errores propios de la lógica del negocio, estandarizar mensajes entre aplicaciones, definir el resultado de invocación a interfaces, en fin, personalizar mensajes del negocio.
Oracle permite definir excepciones, tanto códigos como mensajes de error, para manejar de forma personalizada las excepciones.
El código del ejemplo anterior podría ser re-definido de la siguiente manera.
DECLARE
v_salario_minimo minimos_anuales.salario%type;
v_cantidad NUMBER(2);
NO_SALARIO EXCEPTION;
MUCHOS_SALARIOS EXCEPTION;
BEGIN
SELECT COUNT(*)
INTO v_cantidad
FROM minimos_anuales
WHERE anio = 2014;
IF v_cantidad = 1 THEN
SELECT salario
INTO v_salario_minimo
FROM minimos_anuales
WHERE anio = 2014;
DBMS_OUTPUT.PUT_LINE('EL VALOR DEL SALARIO MINIMO PARA 2014 ES '||v_salario_minimo);
ELSIF v_cantidad = 0 THEN
RAISE NO_SALARIO;
ELSE
RAISE MUCHOS_SALARIOS;
END IF;
EXCEPTION
WHEN NO_SALARIO THEN
DBMS_OUTPUT.PUT_LINE('NO ESTA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014');
WHEN MUCHOS_SALARIOS THEN
DBMS_OUTPUT.PUT_LINE('SE ENCUENTRA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014 MAS DE UNA VEZ');
END;
Esto permite personalizar las excepciones del negocio, para nuestro caso como:
NO_EXISTE_AGENTE
NO_EXISTE_POLIZA
Espero posteriormente detallar ventajas de esta utilización.
Existe el procedimiento RAISE_APPLICATION_ERROR, el cual permite generar excepciones sin definirlas y dispararlas como en el ejemplo anterior, y es más ampliamente utilizado, pero es una de las razones por las cuales el manejo de OTHERS puede llegar a presentar grandes problemas. La sintaxis del procedimiento es la siguiente:
RAISE_APPLICATION_ERROR(numero,mensaje);
El número corresponde a un valor entero entre -20000 y -20999 y el mensaje corresponde a lo que se desea mostrar. Existe un tercer parámetro que no se va a mencionar en este documento.
Una nueva versión del programa es la siguiente:
DECLARE
v_salario_minimo minimos_anuales.salario%type;
v_cantidad NUMBER(2);
BEGIN
SELECT COUNT(*)
INTO v_cantidad
FROM minimos_anuales
WHERE anio = 2014;
IF v_cantidad = 1 THEN
SELECT salario
INTO v_salario_minimo
FROM minimos_anuales
WHERE anio = 2014;
DBMS_OUTPUT.PUT_LINE('EL VALOR DEL SALARIO MINIMO PARA 2014 ES '||v_salario_minimo);
ELSIF v_cantidad = 0 THEN
RAISE_APPLICATION_ERROR(-20000,'NO ESTA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014');
ELSE
RAISE_APPLICATION_ERROR(-20001, 'SE ENCUENTRA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014 MAS DE UNA VEZ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Existen dos funciones útiles para el manejo de excepciones las cuales son:
SQLCODE recupera el código del último error presentado.
SQLERRM recupera el código y mensaje del último error presentado.
Propagación de las excepciones
Dada la estructura de bloques de PL/SQL, las excepciones se propagan desde el bloque más interno, hasta el bloque más externo buscando ser manejadas.
Continuando con nuestro ejemplo del salario mínimo, tenemos un bloque mayor que calcula tres veces el salario mínimo.
DECLARE
v_salario_minimo minimos_anuales.salario%type;
v_tres_salarios minimos_anuales.salario%type;
NO_SALARIO EXCEPTION;
BEGIN
BEGIN
SELECT salario
INTO v_salario_minimo
FROM minimos_anuales
WHERE anio = 2014;
DBMS_OUTPUT.PUT_LINE('EL VALOR DEL SALARIO MINIMO PARA 2014 ES '||v_salario_minimo);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO ESTA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014');
RAISE NO_SALARIO;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('SE ENCUENTRA DEFINIDO EL SALARIO MINIMO PARA EL AÑO 2014 MAS DE UNA VEZ');
RAISE NO_SALARIO;
END;
v_tres_salarios := v_salario_minimo * 3;
DBMS_OUTPUT.PUT_LINE('EL VALOR DE TRES SALARIOS MINIMOS PARA 2014 ES '||v_tres_salarios);
EXCEPTION
WHEN NO_SALARIO THEN
DBMS_OUTPUT.PUT_LINE('NO SE PUEDE CALCULAR EL VALOR DE TRES SALARIOS');
END;
La excepción se genera en el bloque interno, pero es atrapada en el bloque externo. Dada la estructura modular de PL/SQL, es permitido que un bloque PL/SQL invoque un procedimiento o función, y que estos a su vez invoquen internamente otros.
Por este motivo el no manejar adecuadamente las excepciones puede llegar a ocasionar grandes problemas.
Como ejemplo, tenemos un programa principal que invoca una función que retorna el valor del salario mínimo, otra función que retorna el número de veces (podría asemejarse a primas o deducciones, en general un número de veces) y el programa principal muestra el resultado de la operación.
CREATE FUNCTION FUN_SAL_MINIMO(P_ANIO NUMBER)
RETURN NUMBER
IS
v_salario_minimo minimos_anuales.salario%type;
BEGIN
SELECT salario
INTO v_salario_minimo
FROM minimos_anuales
WHERE anio = P_ANIO;
RETURN(v_salario_minimo);
END;
CREATE FUNCTION FUN_NUM_VECES(P_DATO NUMBER)
RETURN NUMBER
IS
v_veces NUMERO_VECES.VECES%TYPE;
BEGIN
SELECT veces
INTO v_veces
FROM numero_veces
WHERE DATO = P_DATO;
RETURN(v_veces);
END;
DECLARE
V_MINIMO NUMBER;
V_VECES NUMBER;
V_DATO NUMBER;
BEGIN
V_MINIMO := FUN_SAL_MINIMO(2014);
V_VECES := FUN_NUM_VECES(1);
V_DATO := V_MINIMO * V_VECES;
DBMS_OUTPUT.PUT_LINE('VALOR SOLICITAD0 '||V_DATO);
END;
El programa principal a veces funciona y a veces no. Genera un error ORA-01403, en algunas ocasiones, de manera que se soluciona de la siguiente manera:
DECLARE
V_MINIMO NUMBER;
V_VECES NUMBER;
V_DATO NUMBER;
BEGIN
V_MINIMO := FUN_SAL_MINIMO(2014);
V_VECES := FUN_NUM_VECES(1);
V_DATO := V_MINIMO * V_VECES;
DBMS_OUTPUT.PUT_LINE('VALOR SOLICITAD0 '||V_DATO);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
De manera que el programa NO VUELVE A FALLAR.
Pero ¿qué pasa si en lugar de mostrar el valor solicitado, el programa almacena el resultado en una tabla?
Y ¿qué sucede si el programa es invocado por otro que recorre una cantidad de pólizas para calcular primas?
Esta es la razón por la cual no se puede decir que un buen programa es aquel que no falla, ni presenta excepciones. Es preferible que aparezca un NO_DATA_FOUND en la línea 2435 del programa X a que el programa no muestre mensajes de error pero no haga lo que se espera.
Cuando se trabaja modularmente, se debe definir los errores que se pueden presentar y la manera en que estos son tratados al interior del programa y propagados hacia el exterior.