Manejo de Excepciones
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname
FROM employees
WHERE first_name='John';
DBMS_OUTPUT.PUT_LINE ('El apellido de John es : ' ||lname);
END;
En el bloque PL/SQL del ejemplor, la sentencia SELECT retorna más de una fila por lo tanto se produce un error Oracle que no está controlado en el bloque PL/SQL terminando la ejecución de éste en forma errónea.
Una excepción es un error PL/SQL que se genera durante la ejecución del programa.
Una excepción se puede generar:
- Implícitamente por el Servidor Oracle:
- Un error Oracle ocurre y la excepción asociada se ejecuta automáticamente.
- Por ejemplo un error ORA-01403 ocurre cuando no se recuperan filas desde la Base de Datos a través de una sentencia SELECT gatillándose la excepción NO_DATA_FOUND.
- Estos errores son convertidos en excepciones predefinidas.
- Explícitamente por el programa PL/SQL:
- Las excepciones se deben generar explícitamente.
- Una excepción se genera explícitamente usando la sentencia RAISE dentro del bloque la cual puede generar excepciones definidas por el usuario o excepciones predefinidas.
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname
FROM employees
WHERE first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('La sentencia SELECT recupera múltiples filas. Considere usar un Cursor Explícito.');
END;
Ahora en el bloque PL/SQL del ejemplo se ha definido la Sección de Excepciones para poder controlar cuando una sentencia SQL retorne más de una fila.
Controlando una Excepción:
Se debe incluir una sección EXCEPTION en el bloque PL/SQL para que controle las excepciones.
Si la excepción se genera en la sección ejecutable del bloque, el control pasa al manejador de esa excepción definido en la Sección de Excepciones del bloque PL/SQL.
Si el bloque PL/SQL maneja satisfactoriamente la excepción, entonces la excepción no se propaga al bloque superior (si es que existe) o devuelve el control al Sistema.
El bloque PL/SQL termina satisfactoriamente.
Propagando una Excepción:
Si la excepción se genera en la sección Ejecutable del bloque y ésta no tiene un correspondiente manejador de excepción, el bloque PL/SQL termina con fallas y la excepción se propaga al bloque superior o devuelve el control a la aplicación que ejecutó el programa PL/SQL.
Tipos de Excepciones
Predefinidas del Servidor Oracle:
Son generadas implícitamente.
No necesitan ser definidas pues son predefinidas por el Servidor Oracle.
Son aproximadamente 20 excepciones que controlan errores particulares que pueden ocurrir en un bloque PL/SQL (excepto OTHERS que controla cualquier tipo de error).
No Predefinidas del Servidor Oracle:
Son generadas implícitamente.
Corresponden a cualquier otro error estándar de Oracle pero que no posee una excepción predefinida asociada.
Son declaradas dentro de la sección Declarativa y permiten que el Servidor Oracle las genere implícitamente.
Definidas por el usuario:
Es una condición que el desarrollador determina como anormal.
Se Declaran en la sección Declarativa y generadas explícitamente.
Manejando Excepciones
Se puede manejar cualquier error al incluir un controlador correspondiente dentro de la sección de control de excepciones del bloque PL/SQL. Cada controlador consiste en una cláusula WHEN, que especifica un nombre de excepción, seguido por una secuencia de sentencias que se ejecutan cuando se produce esta excepción. Se pueden incluir las excepciones que se deseen dentro de una sección de excepciones para controlar excepciones específicas. Sin embargo, no se puede tener varios controladores para una misma excepción.
EXCEPTION
WHEN excepción1 [OR excepción2 . . .] THEN
sentencia1;
sentencia2;
. . .
[WHEN excepción3 [OR excepción4 . . .] THEN
sentencia1;
sentencia2;
. . .]
[WHEN OTHERS THEN
sentencia1;
sentencia2;
. . .]
En la sintaxis:
excepción : es el nombre estándar de una excepción predefinida o el nombre de una excepción definida por el usuario declarada en la sección Declarativa.
sentencia : es una o más sentencias PL/SQL o SQL que se ejecutarán si ocurre la excepción controlada.
OTHERS: es una cláusula opcional de manejo de excepciones que controla cualquier excepción que no se está manejando explícitamente.
Controlando Excepciones
La palabra clave EXCEPTION comienza la sección de manejo de excepciones.
Se debe especificar un nombre a la excepción seguido de una secuencia de sentencias que se ejecutarán cuando la excepción se genere.
Se puede incluir un número indefinido de gestores dentro de la sección EXCEPTION para manejar excepciones específicas.
Sólo un gestor de excepciones es procesado antes de que la ejecución del bloque finalice.
WHEN OTHERS debe ser la última cláusula en la sección de manejo de excepciones. La excepción OTHERS controla cualquier error que se produce en un bloque PL/SQL y que no está controlado en la sección de Excepciones del bloque. Debe ir asociada a las funciones SQLCODE y SQLERRM.
Se puede puede tener sólo una cláusula OTHERS.
Si se desea manejar la misma excepción para diferentes sentencias SQL, entonces se debe generar un bloque por separado para cada sentencia y cada bloque debe tener su propia sección de Excepción.
Controlando Excepciones Predefinidas del Servidor Oracle
Se debe referenciar el nombre predefinido del excepción en la sección que maneja la excepción.
Las excepciones predefinidas son:
ACCESS_INTO_NULL (ORA-06530) : Se ha intentado asignar valores a los atributos de un objeto que no se ha inicializado.
CASE_NOT_FOUND (ORA-06592) : Ninguna de las opciones en la cláusula WHEN de una sentencia CASE se ha seleccionado y no se ha definido la cláusula ELSE.
COLLECTION_IS_NULL (ORA-06531) : Se intentó asignar valores a una tabla anidad o varray aún no inicializada.
CURSOR_ALREADY_OPEN (ORA-06511) : Se intentó abrir un cursor que ya se encuentra abierto.
DUP_VAL_ON_INDEX (ORA-00001) :Se intentó ingresar un valor duplicado en una columna(s) definida(s) como Clave Primaria o Unique en la tabla.
INVALID_CURSOR (ORA-01001) : Se ha intentado efectuar una operación no válida sobre un cursor.
INVALID_NUMBER (ORA-01722) : La conversión de una cadena de caracteres a número ha fallado cuando esa cadena no representa un número válido.
LOGIN_DENIED (ORA-01017) : Se ha conectado al servidor Oracle con un nombre de usuario o password inválido.
NO_DATA_FOUND (ORA-01403) : Una sentencia SELECT no retornó valores o se ha referenciado a un elemento no inicializado en una tabla indexada.
NOT_LOGGED_ON (ORA-01012) : El programa PL/SQL efectuó una llamada a la Base de Datos sin estar conectado al servidor Oracle
PROGRAM_ERROR (ORA-06501) : PL/SQL tiene un problema interno.
STORAGE_ERROR (ORA-06500) : PL/SQL se quedó sin memoria o la memoria está corrupta.
SUBSCRIPT_BEYOND_COUNT (ORA-06533) : Se ha referenciado un elemento de una tabla anidad o índice de varray mayor que el número de elementos de la colección.
SUBSCRIPT_OUTSIDE_LIMIT (ORA-06532) : Se ha referenciado un elemento de una tabla anidada o índice de varray fuera del rango (Ej. -1).
SYS_INVALID_ROWID (ORA-01410) : Fallo al convertir una cadena de caracteres a un tipo ROWID.
TIMEOUT_ON_RESOURCE (ORA-00051) : Se excedió el tiempo máximo de espera por un recurso de Oracle.
TOO_MANY_ROWS (ORA-01422) : Una sentencia SELECT INTO retorna más de una fila.
VALUE_ERROR (ORA-06502) : Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo cuando se intenta almacenar un valor muy grande en una variable más pequeña.
ZERO_DIVIDE (ORA-01476) : El programa intentó hacer una división por cero.
DECLARE
v_lname VARCHAR2(15);
BEGIN
SELECT last_name
INTO v_lname
FROM employees
WHERE first_name = 'Juanito';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' || v_lname);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('La sentencia SELECT recupera múltiples filas');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('La sentencia SELECT no recupera fila');
END;
En el ejemplo, el bloque obtiene el apellido del empleado con nombre Juanito. Como no existe un empleado con ese nombre se genera el error ORA-01403 asociado a la excepción NO_DATA_FOUND. Como esa excepción está controlada en el bloque PL/SQL se ejecutan las sentencias definidas después del THEN de esa excepción y el bloque finaliza en forma correcta.
CREATE SEQUENCE seq_errores;
CREATE TABLE errores
(correlativo NUMBER(10) CONSTRAINT PK_ERRORES PRIMARY KEY,
nombre_proceso VARCHAR2(80),
mensaje_error VARCHAR2(255));
BEGIN
INSERT INTO departments(department_id, department_name, manager_id, location_id)
VALUES(10, 'Depto Nuevo', 200, 1700);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
INSERT INTO errores
VALUES(seq_errores.NEXTVAL, 'Bloque PL/SQL Inserta Departamento',
'Insertando un valor de Clave Primaria que ya existe');
COMMIT;
END;
En el bloque del ejemplo se está insertando un nuevo departamento con identificación 10. La clave de esa tabla es la identificación del departamento (department_id). Como el departamento 10 ya existe se genera un error Oracle ORA-00001 asociado a la excepción DUP_VAL_ON_INDEX. Como esa excepción está controlada en el bloque PL/SQL se ejecutan las sentencias definidas después del THEN de esa excepción que son insertar en la tabla errores información del error (que es la que se visualiza) y el bloque finaliza en forma correcta.
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname
FROM employees
WHERE first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Se ha producido un error en el Bloque PL/SQL' || SQLCODE);
END;
En el ejemplo, el bloque obtiene el apellido del empleado con nombre Juanito. Como no existe un empleado con ese nombre se genera el error ORA-01403 asociado a la excepción NO_DATA_FOUND. Como esa excepción está controlada en el bloque PL/SQL se ejecutan las sentencias definidas después del THEN de esa excepción y el bloque finaliza en forma correcta.
Funciones SQLCODE y SQLERRM
Cuando ocurre una excepción, se puede identificar el código de error asociado o el mensaje de error usando funciones predefinidas.
Las funciones que permiten aclarar al usuario la situación de error son:
SQLCODE: retorna el número de error Oracle para excepciones internas.
SQLERRM: retorna el mensaje de error asociado con el número de error.
Las funciones SQLCODE y SQLERRM no se pueden utilizar directamente en una sentencia SQL, por ejemplo en una sentencia INSERT. Para ello se deben asignar los valores a funciones locales y usar esas variables en la sentencia SQL. Sus valores se pueden visualizar directamente.
DECLARE
v_cod_error NUMBER(10);
v_men_error VARCHAR2(255);
BEGIN
INSERT INTO departments (department_id, department_name)
VALUES (280, NULL);
EXCEPTION
WHEN OTHERS THEN
v_cod_error := SQLCODE;
v_men_error := SQLERRM;
INSERT INTO errores
VALUES(seq_errores.NEXTVAL, 'Bloque PL/SQL Inserta Depto Nulo. Código Error: ' || v_cod_error,
v_men_error);
COMMIT;
END;
En el bloque PL/SQL del ejemplo, la excepción OTHERS controla cualquier error que se produzca durante la ejecución del bloque. En este caso al querer insertar un nuevo departamento, se inserta NULL en la columna department_name que en la tabla está definido como NOT NULL. Por esta razón de genera el error Oracle -01400. Como se va a insertar en la tabla errores, se debe almacenar antes en las variables locales definidas el resultado de las funciones SQLCODE y SQLERRM. Posterior es esto se inserta en la tabla errores (que es lo que se visualiza en el ejemplo).
Bloques Anidados para controlar Excepciones
DECLARE
nombre employees.first_name%type;
registro countries%ROWTYPE;
BEGIN
SELECT first_name INTO nombre
FROM employees
WHERE employee_id = 100;
SELECT * INTO registro
FROM countries
WHERE country_id='ZZ';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No hay filas seleccionadas');
END;
En el bloque posee una sección de Control de Excepciones para controlar el error cuando la sentencia SELECT no retorne filas. En este caso, es la segunda sentencia en donde no se encuentran filas, pero con el mensaje que muestra el bloque no se sabe que sentencia es la que falló.
DECLARE
nombre employees.first_name%type;
registro countries%ROWTYPE;
BEGIN
SELECT first_name INTO nombre
FROM employees
WHERE employee_id = 100;
BEGIN
SELECT * INTO registro
FROM countries
WHERE country_id='ZZ';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No hay fila en countries');
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No hay fila en employees');
END;
Basados en el ejemplo anterior, se genera un bloque anidado para la segunda sentencia SELECT. Por lo tanto ahora cada bloque posee su propia sección de Control de Excepciones permitiendo controlar en forma independiente cuando no se encuentren filas en las tablas employees o countries.
DECLARE
CURSOR c_empleados IS
SELECT employee_id, department_id, salary
FROM employees;
v_nom_depto VARCHAR2(35);
BEGIN
FOR reg_empleados IN c_empleados LOOP
BEGIN
SELECT department_name
INTO v_nom_depto
FROM departments
WHERE department_id = reg_empleados.department_id;
DBMS_OUTPUT.PUT_LINE('El empleado ' || reg_empleados.employee_id || ' trabaja en el departamento' || v_nom_depto);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('El departamento del empleado ' || reg_empleados.employee_id || ' No existe');
END;
END LOOP;
END;
En el ejemplo, el bloque interno creado para la sentencia que obtiene el nombre del departamento de cada empleado, controla si el departamento leído no existe en la tabla departments (que es el caso del empleado 178) muestra el mensaje de la excepción. Esto permite, que a pesar de que el departamento leído desde el cursor no exista en la tabla departments el proceso continué en forma normal para procesado todos los empleados almacenados en el cursor. (El resultado del bloque se muestra en la siguiente página). Si no se genera un bloque interno para que controle la excepción, la ejecución del bloque finalizaría cuando se procese el empleado 178.
Controlando Excepciones No Predefinidas del Servidor Oracle
Estas excepciones son similares a las excepciones predefinidas sin embargo no se encuentran definidas como excepciones en el servidor Oracle. Son errores estándares de Oracle pero que no poseen una excepción definida en Oracle (nombre de excepción).
Se pueden crear excepciones con errores estándares de Oracle utilizando la función PRAGMA EXCEPTION_INIT. PRAGMA es la palabra clave que significa que la declaración es una directiva del compilador. En PL/SQL, EXCEPTION_INIT es una PRAGMA que le dice al compilador PL/SQL que se asocia un nombre de excepción con un error Oracle. Esto permite interpretar todas las presencias del nombre de excepción dentro del bloque como el número de error del servidor Oracle asociado. (SQLCODE)
DECLARE nombre_excepción EXCEPTION; PRAGMA EXCEPTION_INIT (nombre_excepción, número_error); BEGIN ……………………………………………………………….... ……………………………………………………………….... EXCEPTION WHEN nombre_excepción THEN ……………………………………………………………….... END;
En la sintaxis:
nombre_excepción EXCEPTION:
nombre_excepción es el nombre de la excepción a crear
PRAGMA EXCEPTION_INIT(nombre_excepción, número_error);
nombre_excepción es la excepción previamente declarada
número_error es un número de error standard de Oracle
WHEN excepción THEN
nombre_excepción es la referencia a la excepción declarada en la sección Declarativa y que fué asociada a la función PRAGMA_EXCEPTION_INIT
DECLARE
excepcion_insert EXCEPTION;
PRAGMA EXCEPTION_INIT(excepcion_insert, -01400);
BEGIN
INSERT INTO departments (department_id, department_name)
VALUES (280, NULL);
EXCEPTION
WHEN excepcion_insert THEN
DBMS_OUTPUT.PUT_LINE('OPERACIÓN DE INSERT HA FALLADO');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
En el ejemplo, el bloque PL/SQL controla el error del Servidor Oracle -01400, no puede insertar NULL:
1 : se declara excepción excepcion_insert.
2 : con la función PRAGMA EXCEPTION_INIT se asocia la excepción declarada con el número de error standard de Oracle.
3 : se referencia la excepción declarada. La función SQLERRM es usada para recuperar el mensaje de error Oracle.
Controlando Excepciones Definidas por el Usuario
PL/SQL permite definir excepciones propias del usuario según se requiera. Las excepciones PL/SQL definidas por el usuario se deben:
Declarar en la sección de Declaración del bloque PL/SQL.
Generar explícitamente con la sentencia RAISE en la sección Ejecutable.
Manejar en la sección de manejo de Excepciones (EXCEPTION).
SET SERVEROUTPUT ON
DECLARE
nombre_excepción EXCEPTION;
………………………………….…………….
BEGIN
………………………………….…………….
IF condición THEN
RAISE nombre_excepción ;
………………………………….…………….
………………………………….…………….
EXCEPTION
WHEN nombre_excepción THEN
………………………………….…………….
END;
En la sintaxis:
nombre_excepción EXCEPTION:
nombre_excepción es el nombre de la excepción definida por el usuario.
RAISE nombre_excepción se utiliza para generar explícitamente la excepción definida por el usuario;
WHEN excepción THEN
nombre_excepción es la referencia a la excepción definida por el usuario en la sección Declarativa.
DECLARE
depart_invalido EXCEPTION;
BEGIN
UPDATE departments
SET department_name = 'Depto nuevo'
WHERE department_id = 999;
IF SQL%NOTFOUND THEN
RAISE depart_invalido;
END IF;
COMMIT;
EXCEPTION
WHEN depart_invalido THEN
DBMS_OUTPUT.PUT_LINE('No existe departamento.');
END;
En el bloque del ejemplo, se define una excepción para controlar cuando no existan filas que actualizar. A diferencia de la sentencia SELECT, que genera un error Oracle asociado a la excepción NO_DATA_FOUND cuando no se encuentran filas que seleccionar, la sentencia UPDATE no genera un error Oracle si no existen filas que actualizar. Por ello, de debe usar una excepción definida por el usuario para controlar esta situación.
Procedimiento RAISE_APPLICATION_ERROR
Se puede utilizar el procedimiento RAISE_APPLICATION_ERROR para visualizar una excepción predefinida devolviendo un código de error no estándar y el mensaje de error. Con RAISE_APPLICATION_ERROR, se puede informar de errores en la aplicación y evitar volver excepciones no controladas.
El procedimiento RAISE_APPLICATION_ERROR puede ser utilizado tanto en la sección ejecutable o la sección de excepción de un programa de PL/SQL o en ambas secciones. El error devuelto es coherente con la forma en que el servidor Oracle produce un error predefinido, no predefinida o definida por el usuario.
raise_application_error (número_de_error,mensaje_error [, {TRUE | FALSE}]);
En la sintaxis:
Permite emitir mensajes de error definidos por el usuario desde subprogramas almacenados.
Se utiliza en dos lugares diferentes:
Sección Executable
Sección Exception
En la sintaxis:
número_de_error : Es un número especificado por el usuario para la excepción entre -20000 y -20,999 (rango de número permitidos a usar).
mensaje_error : Es el mensaje especificado por el usuario para la excepción; es una cadena de caracteres de hasta 2048 bytes de largo.
TRUE | FALSE : Es un parámetro booleano opcional (Si es TRUE, se coloca el error en junto a los errores anteriores. Si es falso, que es el valor por defecto, el error sustituye a todos los errores anteriores.)
DECLARE
v_mgr NUMBER(2):=99;
BEGIN
DELETE FROM employees
WHERE manager_id = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20201,'No existe el jefe que se desea eliminar');
END IF;
END;
En el ejemplo, su utiliza RAISE_APPLICATION_ERROR en la sección de ejecución del bloque PL/SQL para controlar si hubieron filas afectadas por la sentencia Delete. Si no se eliminaron filas, entonces se mostrará un error personalizado en formato Oracle.
DECLARE
v_apellido NUMBER(2);
BEGIN
SELECT last_name INTO v_apellido
FROM employees
WHERE employee_id = 99;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20202,'El empleado que se consulta no es válido');
END;
En el ejemplo, su utiliza RAISE_APPLICATION_ERROR en la sección de excepción del bloque PL/SQL para controlar si la consulta retornó filas. Si no la consulta no retorna filas entonces se mostrará un error personalizado en formato Oracle.
