Primera parte
Oracle permite gestionar las excepciones de un modo muy simple y práctico. En esta primera parte del artículo se van a explicar los recursos de los que Oracle dispone para el tratamiento de excepciones. En la segunda parte se explicará un modo de gestionarlas para, por una parte programar de forma cómoda, y por otra mostrar o registrar toda la información posible para saber en qué línea de código se ha producido el error y qué tipo de error se ha producido.
Creamos la tabla con la que trabajaremos:
create table metales as
select 1 id_metal, 'Aluminio' metal from dual union all
select 2, 'Plomo' from dual union all
select 3, 'Acero' from dual union all
select 4, 'Hierro' from dual union all
select 5, 'Mercurio' from dual union all
select 6, 'Latón' from dual;
Presentamos un procedimiento donde dado un identificador realizamos una consulta simple sobre la tabla METALES que recupera la descripción del identificador que se le pasa como parámetro y muestra por la ventana de salida DBMS Output su descripción:
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(60);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
end;
Ejecutamos el procedimiento desde una consola SQL para obtener la descripción del metal con identificador 2:
PL/SQL procedure successfully completed.
En la ventana de salida DBMS Output vemos que muestra la palabra:
Plomo
Provocamos un error
Ahora pasémosle un identificador que no existe para provocar una excepción
ORA-01403: no data found
ORA-06512: at "DB_TEST.DESCRIPCION_METAL", line 4
ORA-06512: at line 2
Observamos que el log de error contiene toda la traza, para ello repasamos el log de abajo a arriba para ir obteniendo los saltos desde donde se han hecho las llamadas hasta dar con el error.
ORA-06512: at line 2 -> hace referencia al bloque de código anónimo que hemos escrito en la consola SQL, nos está indicando que el error se ha producido en la línea 2, es decir: descripcion_metal(8);
ORA-06512: at "DB_TEST.DESCRIPCION_METAL", line 4 -> nos informa que en un segundo nivel, y en este caso último, el error se ha producido dentro del procedimiento DESCRIPCION_METAL del esquema DB_TEST, concretamente en la línea 4, que es donde está la instrucción SELECT.
ORA-01403: no data found -> por último, se indica el tipo de error propiamente dicho; en este caso se trata de un error “no data found”. Está indicando que la consulta de la línea 4 del procedimiento DESCRIPCION_METAL no ha encontrado el registro y ha provocado la excepción.
Sin dar ningún tratamiento a la excepción observamos que el log que muestra la consola SQL es muy detallado y nos informa de todos los saltos desde el punto inicial del programa hasta donde se produce el error.
Tratamiento del error
Vamos ahora a dar un tratamiento a esta excepción, para que si la consulta no encuentra información, se controle el error y que no aborte la ejecución del proceso. Para ello añadimos el apartado de excepciones al final del procedimiento del siguiente modo.
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(60);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
exception
when no_data_found then
dbms_output.put_line('No se ha encontrado ningún metal con identificador: '||p_id_metal);
end;
Si ahora volvemos a ejecutar el bloque de código anónimo:
PL/SQL procedure successfully completed.
La llamada al procedimiento acaba bien, y en la ventana de salida DBMS Output se observa el siguiente mensaje:
No se ha encontrado ningún metal con identificador: 8
Cabe destacar que si en el ejemplo anterior el error o excepción que se da no es del tipo NO_DATA_FOUND, entonces la ejecución aborta sacando el mensaje de error que se ha producido. Para ilustrar esto vamos a forzar que se produzca un error por desbordamiento. Para ello dimensionamos la variable V_DESCRIPCION del procedimiento de manera que no tenga suficiente espacio para almacenar el nombre del metal de identificador 2:
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(3);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
exception
when no_data_found then
dbms_output.put_line('No se ha encontrado ningún metal con identificador: '||p_id_metal);
end;
Ahora la variable v_descripcion está dimensionada a tan solo tres caracteres, veamos qué ocurre al invocar al procedimiento:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB_TEST.DESCRIPCION_METAL", line 4
ORA-06512: at line 2
Observamos como el error se produce por tener la variable dimensionada con un tamaño insuficiente, y que el error se produce en la línea 4 del procedimiento, es decir, donde está la SELECT, que es la instrucción que intenta actualizar la variable v_descripcion con un dato mayor que el que puede almacenar.
Tratamiento de varios tipos de excepciones
Es posible tratar varios tipos de error en el apartado de excepciones de un bloque de código, para ilustrar esto modificamos el procedimiento añadiendo una excepción para tratar el error por desbordamiento añadiendo la excepción VALUE_ERROR.
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(3);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
exception
when no_data_found then
dbms_output.put_line('No se ha encontrado ningún metal con identificador: '||p_id_metal);
when value_error then
dbms_output.put_line('Se ha producido un error numérico o de valor. p_id_metal = '||p_id_metal);
end;
Si ahora invocamos el procedimiento:
PL/SQL procedure successfully completed.
La llamada al procedimiento acaba bien, y en la ventana de salida DBMS Output se observa el siguiente mensaje:
Se ha producido un error numérico o de valor. p_id_metal = 2
En general es preferible no tratar este tipo de errores, dado que obtenemos menos información que si no lo tratamos. La idea es que se deben tratar las excepciones con las que contamos, es decir, si yo sé qué hacer cuando una consulta no recupere ningún registro, trato la excepción NO_DATA_FOUND e implemento el código que trata la excepción, por ejemplo puedo decidir insertar el registro con la siguiente descripción: “alta desde la importación”. Pero si siempre se espera que encuentre el registro y en caso contrario el programa no puede continuar porque por ejemplo no tiene sentido, entonces es preferible no tratar la excepción a tratarla de un modo que perdamos información. Se verá el porqué de esto en la segunda parte del artículo.
Tipos de excepciones
Hay diversos tipos de excepciones que se pueden tratar en el apartado de excepciones de un bloque de código PL/SQL, estos son algunos ejemplos:
- 100 NO_DATA_FOUND: Cuando no se encuentran datos en una consulta SQL. Al acceder a un elemento inexistente de una colección. Al intentar leer una línea de un archivo del disco después de leer la última.
- -1 DUP_VAL_ON_INDEX: Al intentar insertar un registro que duplicaría la clave primaria de una tabla o duplicaría los valores de los campos que componen un índice único.
- -1422 TOO_MANY_ROWS: Cuando una consulta de la que se espera un solo registro (select into) selecciona más de un registro.
- -1476 ZERO_DIVIDE: Al calcular una división entre cero.
Consulte la documentación Oracle para obtener más información al respecto.
WHEN OTHERS THEN
Oracle proporciona un modo de tratar cualquier excepción que se pueda dar, de modo que es posible tratar varios tipos de excepciones y, si no es ninguna de ellas la que ha provocado el error entonces se cuela por el “WHEN OTHERS” y es aquí donde se implementa la lógica que trata la excepción. Para ilustrar esto vamos a modificar el procedimiento que venimos usando de ejemplo donde trataremos la excepción no_data_found y si no es esta excepción la que provoca el error, entonces se ejecuta la lógica del when others:
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(3);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
exception
when no_data_found then
dbms_output.put_line('No se ha encontrado ningún metal con identificador: '||p_id_metal);
when others then
dbms_output.put_line('Se ha producido el siguiente error: '||sqlerrm);
end;
Si ahora invocamos el procedimiento:
PL/SQL procedure successfully completed.
La llamada al procedimiento acaba bien, y en la ventana de salida DBMS Output se observa el siguiente mensaje:
Se ha producido el siguiente error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Observe cómo en este caso se pierde información si lo comparamos con el error por defecto que muestra la consola SQL si no lo tratamos:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at " DB_TEST.DESCRIPCION_METAL", line 4
ORA-06512: at line 2
SQLCODE y SQLERRM
Estas funciones nos permiten obtener información relativa al error que se ha producido, SQLCODE devuelve el número o código de error, SQLERRM devuelve el mensaje de error. De modo que podemos usarlas en el tratamiento que se le da a la excepción. Para ello vamos a sacar por la ventana de salida DBMS Output sus valores cuando ocurre la excepción vista con anterioridad:
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(3);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
exception
when others then
dbms_output.put_line('SQLCODE: '||sqlcode);
dbms_output.put_line('SQLERRM: '||sqlerrm);
end;
Si ahora invocamos el procedimiento:
PL/SQL procedure successfully completed.
La llamada al procedimiento acaba bien, y en la ventana de salida DBMS Output se observa el siguiente mensaje:
SQLCODE: -6502
SQLERRM: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Pasémosle ahora al procedimiento un identificador de metal que no exista:
PL/SQL procedure successfully completed.
La llamada al procedimiento acaba bien, y en la ventana de salida DBMS Output se observa el siguiente mensaje:
SQLCODE: 100
SQLERRM: ORA-01403: no data found
Propagación del error
Vamos a ver ahora cómo podemos darle un tratamiento a la excepción y que el error se propague. La idea es capturar el error mostrando por ejemplo un mensaje por la salida DBMS Output y acto seguido propagar el error para que la ejecución aborte. Esto se consigue con la instrucción RAISE. Véase el procedimiento de ejemplo, observe cómo después del tratamiento a la excepción se coloca la instrucción RAISE para propagar el error:
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(3);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
exception
when others then
dbms_output.put_line('Se ha producido el siguiente error: '||sqlerrm);
raise;
end;
Pasémosle ahora al procedimiento un identificador de metal que no exista:
ORA-01403: no data found
ORA-06512: at "DB_TEST.DESCRIPCION_METAL", line 14
ORA-06512: at line 2
La llamada al procedimiento aborta, y en la ventana de salida DBMS Output se observa el siguiente mensaje, es de hecho el tratamiento que se le ha dado a la excepción antes de propagarla.
Se ha producido el siguiente error: ORA-01403: no data found
Si miramos el log de error que muestra la consola SQL tras propagarlo, observamos que dentro del procedimiento DESCRIPCION_METAL, el log informa que el error se produce en la línea 14:
ORA-06512: at "DB_TEST.DESCRIPCION_METAL", line 14
Esta línea es justamente donde está la instrucción RAISE, por tanto tras propagar el error se ha perdido la línea de código donde se produce el error original, es decir, la línea 4, que es donde empieza la sentencia SELECT. Esto se entiende del siguiente modo: como se ha colocado un apartado de excepciones en el procedimiento, el proceso salta a este apartado cuando ocurre una excepción. En este caso se cuela por el WHEN OTHERS, por tanto el error no se propagará al punto que se llamó al procedimiento salvo que se fuerce con ayuda de la instrucción RAISE, en cuyo caso es en este punto del procedimiento, es decir, donde está la instrucción RAISE, donde se considera que el programa ha fallado.
Centralización de excepciones
El tratamiento de excepciones se puede realizar en cualquier bloque de código añadiendo el apartado de excepciones debidamente. De modo que en lugar de hacerlo dentro del procedimiento es posible hacerlo en el bloque de código anónimo que lo llama. Esto es especialmente útil para no tener que andar creando apartados de excepciones en procedimientos que pueden usarse desde muchos otros algoritmos. Hacerlo en el programa que usa estos procedimientos es presumiblemente más oportuno, dado que para un algoritmo no encontrar la descripción de un metal puede implicar abortar la ejecución, pero para otro puede no ser tan crítico y seguir su ejecución. Para ilustrar esto vamos a quitar el tratamiento de excepciones del procedimiento:
create or replace procedure descripcion_metal(p_id_metal in number) is
v_descripcion varchar2(3);
begin
select METAL
into v_descripcion
from METALES
where ID_METAL = p_id_metal;
dbms_output.put_line(v_descripcion);
end;
Y ahora construimos la llamada del siguiente modo:
begin
descripcion_metal(8);
exception
when others then
dbms_output.put_line('Se ha producido el siguiente error: '|| SQLERRM);
end;
La ejecución del bloque de código anónimo acaba bien, y en la ventana de salida DBMS Output se observa el siguiente mensaje:
Se ha producido el siguiente error: ORA-01403: no data found
Es posible incluso encapsular varias instrucciones, o llamadas a procedimientos y funciones, para darles un tratamiento de error concreto y poder seguir la ejecución del algoritmo sin que aborte:
begin
-- líneas de código previas al encapsulado
begin
descripcion_metal(8);
exception
when no_data_found then
null;
end;
-- líneas de código posteriores al encapsulado
end;
PL/SQL procedure successfully completed.
En el bloque de código anterior se implementa la llamada al procedimiento DESCRIPCION_METAL de modo que si ocurre un error de tipo NO_DATA_FOUND el proceso sigue como si nada, dado que se ha indicado la instrucción NULL como tratamiento a tal excepción, sin embargo si el error es de otro tipo, al no tener un tratamiento la excepción se propaga y aborta la ejecución. Para probar esto llamémoslo pasando el identificador de metal 2:
begin
-- líneas de código previas al encapsulado
begin
descripcion_metal(2);
exception
when no_data_found then
null;
end;
-- líneas de código posteriores al encapsulado
end;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB_TEST.DESCRIPCION_METAL", line 4
ORA-06512: at line 5
Ahora, si se quiere, es posible darle un tratamiento a cualquier error añadiendo el apartado de excepciones en el bloque de código principal:
begin
-- líneas de código previas al encapsulado
begin
descripcion_metal(2);
exception
when no_data_found then
null;
end;
-- líneas de código posteriores al encapsulado
exception
when others then
dbms_output.put_line('Se ha producido el siguiente error: '|| SQLERRM);
end;
PL/SQL procedure successfully completed.
La ejecución del bloque de código anónimo acaba bien, y en la ventana de salida DBMS Output se observa el siguiente mensaje:
Se ha producido el siguiente error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Excepciones definidas por el usuario
Vamos a ver ahora cómo definir excepciones. Este recurso se usa para forzar el salto desde la lógica del algoritmo al apartado de excepciones. Para ilustrar esto vamos a realizar una función que calcule la raíz cuadrada de un número dado.
create or replace function raiz_cuadrada(p_valor in number) return number is
begin
return sqrt(p_valor);
end;
La invocamos desde el siguiente bloque anónimo:
En la ventana de salida DBMS Output se muestra:
Resultado: 3
Ahora pasémosle un número negativo:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DB_TEST.RAIZ_CUADRADA", line 3
ORA-06512: at line 2
La ejecución aborta puesto que la raíz cuadrada de un número negativo no tiene solución.
Vamos a definir una excepción que llamaremos NUMERO_NEGATIVO, para que si el número que le pasamos a la función es negativo forzar que salte hasta el apartado de excepciones, evitando así que se intente calcular la raíz de un número negativo. En tal caso la función retornará un valor nulo.
Primero debe definirse la excepción en el apartado de definiciones, y después debe tratarse la excepción en el apartado de excepciones:
create or replace function raiz_cuadrada(p_valor in number) return number is
NUMERO_NEGATIVO exception;
begin
if p_valor < 0 then
raise NUMERO_NEGATIVO;
end if;
return sqrt(p_valor);
exception
when NUMERO_NEGATIVO then
return null;
end;
Ahora pasémosle un número negativo:
PL/SQL procedure successfully completed.
Y en la ventana de salida DBMS Output se muestra:
Resultado:
Es decir, un valor nulo o indeterminado.
El procedimiento nativo RAISE_APPLICATION_ERROR
Vamos a ver un modo de generar nuestras excepciones forzando a que la ejecución aborte. Este recurso es de gran ayuda dado que permite interrumpir la ejecución de un proceso dotando a la excepción de un código y un mensaje personalizado. Por tanto a la función RAISE_APPLICATION_ERROR se le pasan dos parámetros, en primer lugar el código de error con el que queramos catalogar el tipo de error, y en segundo lugar el mensaje de error.
Veámoslo con un ejemplo usando la función que resuelve la raíz cuadrada de un número:
create or replace function raiz_cuadrada(p_valor in number) return number is
begin
if p_valor < 0 then
raise_application_error(-20000,'No es posible calcular la raíz cuadrada de un número negativo');
end if;
return sqrt(p_valor);
end;
Ahora pasémosle un número negativo:
ORA-20000: No es posible calcular la raíz cuadrada de un número negativo
ORA-06512: at "DB_TEST.RAIZ_CUADRADA", line 4
ORA-06512: at line 2
No podrá usar como códigos de error valores superiores a -20000, dado que están reservados para la catalogación de excepciones nativas del SGBD Oracle.
Modifiquemos ahora el bloque anónimo de código para ver qué valores devuelven en este caso las funciones nativas SQLCODE y SQLERRM:
begin
dbms_output.put_line('Resultado: '||raiz_cuadrada(-9));
exception
when others then
dbms_output.put_line('SQLCODE: '||sqlcode);
dbms_output.put_line('SQLERRM: '||sqlerrm);
end;
PL/SQL procedure successfully completed.
Y en la ventana de salida DBMS Output se muestra:
SQLCODE: -20000
SQLERRM: ORA-20000: No es posible calcular la raíz cuadrada de un número negativo
Mensajes de error sin tratar
A lo largo de este artículo se ha explicado cómo la consola SQL muestra los errores cuando no se les da un tratamiento mediante el apartado de excepciones de un bloque de código. Bien, el mensaje de error por defecto o sin tratar que arroja la consola SQL no es algo que podamos generalizar. Todos los mensajes de error por defecto mostrados en este artículo los ha devuelto un software herramienta que está diseñado para conectarse a una BD Oracle e interactuar con el sistema. En este caso se ha usado el programa TOAD, pero hay muchos otros modos de conectarse a una BD Oracle, por ejemplo desde otras herramientas parecidas al TOAD, o desde una aplicación JAVA, .NET, PHP, etc…
No podemos contar con que los mensajes de error que arrojan estas aplicaciones sean tan detallados como lo es en el TOAD, entre otras cosas porque el TOAD es una herramienta de desarrollo y por tanto está diseñada para sus usuarios que son desarrolladores y necesitan el máximo de información cuando trabajando y probando sus desarrollos un proceso aborta.
Lo normal es que los mensajes de error sin tratar mostrados desde una aplicación JAVA por ejemplo, se limiten a indicar el tipo de error que ha provocado la llamada al núcleo Oracle y por tanto la información es orientativa pero no concreta, es decir, no nos indicará en qué línea del código PL/SQL se ha dado la excepción.
El TOAD, entre otros programas, muestra toda la información relativa al error, no porque sean adivinos, sino que aprovechan mejor los recursos que el sistema Oracle proporciona.
En la segunda parte del artículo aprenderá cómo obtener toda la información que se obtiene desde la consola SQL en sus aplicaciones cuando un proceso Oracle aborte. También se expondrá una estrategia de tratamiento de errores que le permita programar de forma cómoda y al mismo tiempo acotar y localizar los errores de forma ágil.
Segunda Parte
A lo largo de mi experiencia como profesional visitando clientes para mantener o evolucionar aplicaciones con tecnología Oracle, me sorprende observar lo mal que suele abordarse en general el control de errores en la programación, y en particular en la programación PL/SQL. En ocasiones son los propios estándares de las compañías los que enfocan la cuestión de un modo, digamos, poco práctica. Suelen reinventar la rueda y al final esta acaba funcionando peor que la que proporcionan los propios entornos de programación. Otras veces es la falta de estándares lo que provoca una anarquía en cuestión del control de errores dejando en manos de cada desarrollador cómo abordarlos.
La consecuencia de gestionar mal el control de errores es que cuando algún proceso provoca un error, cuesta acotarlo. Es habitual que el técnico no pueda reproducirlo fácilmente debido a que el error que le reportan como incidencia se ha dado en un entorno de producción al cual no tiene acceso o lo tiene limitado, y debe reproducirlo en un entorno de pruebas o de desarrollo donde los datos no son los mismos y por tanto es complicado sacar el agua clara.
Por tanto gestionar de forma práctica los errores es importante, puesto que le permitirá ser ágil acotando el problema cuando se dé un error en algún proceso. Lo ideal es saber de forma rápida qué ha provocado el error y dónde, para posteriormente tomar decisiones al respecto.
Si usted no tiene claro cómo tratar los errores, no lo haga. Es preferible no tratarlos que hacerlo mal, de este modo NO se corre el riesgo de que nadie se entere si se produce un error. No tratar los errores podría ser perfectamente un estándar de control de errores de una compañía, y a mi entender, mucho mejor que otras estrategias o estándares que he visto implantados.
Malas prácticas
Algunos colegas afirman que es muy “feo” ver un mensaje de error sin tratar en una aplicación, que es poco profesional y que da mala imagen. No podría estar más en desacuerdo, en primer lugar porque no creo que haya ninguna aplicación que se libre de mostrar en alguna ocasión un mensaje de error sin tratar (los he visto en aplicaciones web de bancos online serios y profesionales), y en segundo lugar porque tratando los errores es posible que nadie se entere que se ha producido un error si no se hace bien, precisamente por querer evitar ver un mensaje “feo” por pantalla.
En ocasiones tratar el error significa ver por pantalla el siguiente mensaje:
Se ha producido un error, contacte con el soporte.
Y entonces empieza la odisea de saber qué ha pasado.
Francamente, para mostrar mensajes como el anterior es mejor ver errores sin tratar como el siguiente:
ORA-00001: unique constraint PEDIDOS_PK violated
Con este mensaje de error al menos el técnico que debe resolver la incidencia sabrá que se encuentra ante el intento de duplicar una clave primaria o un índice único, también sabrá en qué restricción o constraint: PEDIDOS_PK. Pero no sabrá en qué punto del código se ha producido el error, y podemos estar hablando de miles de líneas de código.
Algunos desarrolladores son realmente buenos en esto, son expertos en tratar errores, de modo que mientras están desarrollando un producto lo prueban al mismo tiempo, y les resulta sorprendentemente fácil tratar las excepciones no haciendo nada, no les importa si la funcionalidad queda cubierta, han solucionado el problema, ahora su programa ya no aborta. Otros estándares y/o desarrolladores, no tan buenos como los anteriores tratando errores, asignan a un parámetro de salida: P_ERROR, un número, si vale cero es que todo ha ido bien, de lo contrario se ha producido un error:
Mensaje:
Se ha producido un error, código: 7
No sabemos el tipo de error, pero tenemos un número que deberemos buscar por el código y rezar para que solo esté en un punto y el error 7 no sea ambiguo. Además es posible que debamos modificar el programa con el propósito de obtener más información y saber el tipo de error, mostrándose entonces un mensaje como el siguiente:
Mensaje:
Se ha producido un error, código: 7 -> ORA-00001: unique constraint PEDIDOS_PK violated
Ni que decir tiene que los desarrolladores realmente buenos tratando errores ignoran los parámetros de salida que los desarrolladores no tan buenos se preocupan en retornar en sus programas, y el proceso sigue corriendo como si nada.
Estándares de control de errores
Y yo me pregunto, ¿por qué tengo que preocuparme de reinventar la acotación de errores cuando el propio SGBD Oracle lo hace por mí? Véase el siguiente procedimiento de ejemplo que usa el recurso del parámetro de salida P_ERROR:
create or replace procedure buscar_mejor_precio(p_id_origen in number, p_id_destino in number, p_error out number) is
v_error number;
v_importe_vuelo number;
v_importe_hotel number;
v_importe_coche number;
begin
v_error := 1;
v_importe_vuelo := buscar_mejor_vuelo(p_id_origen, p_id_destino);
v_error := 2;
v_importe_hotel := buscar_mejor_hotel(p_id_destino);
v_error := 3;
v_importe_coche := buscar_mejor_alquiler(p_id_destino);
v_error := 4;
insert into mejor_precio(IMP_VUELO, IMP_HOTEL, IMP_COCHE)
values (v_importe_vuelo, v_importe_hotel, v_importe_coche);
p_error := 0;
exception
when others then
p_error := v_error;
end;
Supongamos que se llama a este procedimiento desde el siguiente bloque anónimo de código:
declare
v_error number;
begin
buscar_mejor_precio(3434, 23323, v_error);
if v_error != 0 then
mostrar_error('Se ha producido un error en procedimiento buscar_mejor_precio, código: '||v_error);
else
mostrar_mejor_precio;
end if;
end;
¿Qué gano con esta estrategia de control de errores? Pues poco, al contrario, nos estamos complicando la vida sobremanera. Si lo analizamos se observa que todo son inconvenientes y no hay prácticamente ninguna ventaja:
- El desarrollador que programa o mantiene el procedimiento debe mantener el parámetro de salida p_error.
- El desarrollador que programa la invocación a este procedimiento debe preocuparse de evaluar el parámetro de salida p_error y generar un mensaje de error. Si no lo hace, cosa más que posible, el proceso fallará seguramente más adelante, y cuesta mucho acotar el origen del error en algoritmos de cientos de líneas de código.
- En el caso de evaluar el parámetro de salida y mostrar el mensaje de error, este solo es orientativo:
Se ha producido un error en procedimiento buscar_mejor_precio, código: 3
Ahora programemos lo mismo sin tratar los posibles errores.
create or replace procedure buscar_mejor_precio(p_id_origen in number, p_id_destino in number) is
v_importe_vuelo number;
v_importe_hotel number;
v_importe_coche number;
begin
v_importe_vuelo := buscar_mejor_vuelo(p_id_origen, p_id_destino);
v_importe_hotel := buscar_mejor_hotel(p_id_destino);
v_importe_coche := buscar_mejor_alquiler(p_id_destino);
insert into mejor_precio(IMP_VUELO, IMP_HOTEL, IMP_COCHE)
values (v_importe_vuelo, v_importe_hotel, v_importe_coche);
end;
Y lo invocamos del siguiente modo:
Y el mensaje de error sin tratar que se muestra ahora supongamos que es:
ORA-01403: no data found
Mensaje típico de una aplicación de gestión que se conecta a una BD Oracle.
En ninguno de los casos me ahorro tener que acotar el error, en el primer caso tendré que empezar a estirar del hilo a partir del mensaje:
Se ha producido un error en procedimiento buscar_mejor_precio, código: 3
No sé qué tipo de error es, pero sé en qué procedimiento ha ocurrido, y también tengo un número 3 que no sé exactamente qué significa hasta que mire el programa.
En el segundo caso:
ORA-01403: no data found
Sé qué tipo de error ha ocurrido pero no dónde, por tanto deberé pedirle al usuario que me explique qué ha hecho para reproducir el error y empezar a estirar por ahí del hilo.
Ninguno de los dos ejemplos me convence porque ninguno de los dos métodos permite acotar el error sin investigación, por lo tanto prefiero el segundo estándar al primero, es decir, para reinventar el control de errores y no acotarlo, mejor no darle tratamiento, con ello consigo lo siguiente:
- Me resulta mucho más cómodo programar sin preocuparme de gestionar un parámetro de salida para controlar los errores.
- Evito dejar en manos de los desarrolladores, con la fuente de errores que ello supone, tener que evaluar constantemente si las llamadas a las funciones provocan error. Por tanto es más cómodo programar las llamadas a estas funciones sabiendo que no tratan las excepciones sino que las propagan.
- No se corre el riesgo de que si por descuido no se evalúa el parámetro de retorno para la gestión de errores en las llamadas a procedimiento y funciones, el proceso corra como si nada.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Hemos visto que el segundo método que se ha presentado con anterioridad ofrece muchas más ventajas que el primero. Vamos a mejorarlo con ayuda de la función nativa que el SGBD Oracle proporciona: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. Esta función permite obtener toda la traza del error, es una función de suma utilidad, puesto que permite generar un mensaje de error con toda la información y los detalles. Para ilustrar esto véase el siguiente procedimiento al que no se le da ningún tratamiento a los posibles errores, tampoco los tienen las funciones que se invocan en él:
create or replace procedure buscar_mejor_precio(p_id_origen in number, p_id_destino in number) is
v_importe_vuelo number;
v_importe_hotel number;
v_importe_coche number;
begin
v_importe_vuelo := buscar_mejor_vuelo(p_id_origen, p_id_destino);
v_importe_hotel := buscar_mejor_hotel(p_id_destino);
v_importe_coche := buscar_mejor_alquiler(p_id_destino);
insert into mejor_precio(IMP_VUELO, IMP_HOTEL, IMP_COCHE)
values (v_importe_vuelo, v_importe_hotel, v_importe_coche);
end;
y ahora programemos la invocación del siguiente modo:
begin
buscar_mejor_precio(3434, 23323);
mostrar_mejor_precio;
exception
when others then
raise_application_error(-20000, SQLERRM||' -> traza: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end;
El programa aborta, y el mensaje de error que se muestra ahora desde la aplicación es:
ORA-20000: ORA-01403: no data found -> traza: ORA-06512: at "DB_TEST.BUSCAR_MEJOR_ALQUILER", line 4
ORA-06512: at "DB_TEST.BUSCAR_MEJOR_PRECIO", line 9
ORA-06512: at line 2
ORA-06512: at line 6
Observe cómo solo con dotar al bloque principal, o de nivel superior, de un apartado de excepciones usando debidamente los recursos que el SGBD Oracle proporciona, obtenemos la localización exacta del error, y además, al programar toda la lógica que compone el algoritmo no debemos preocuparnos en ningún momento de los posibles errores. Lo centralizamos en el procedimiento principal, es decir, el que se invocará desde la aplicación. En este caso el error se produjo exactamente en:
"DB_TEST.BUSCAR_MEJOR_ALQUILER", line 4
En cierto modo se está personalizando el mensaje de error con ayuda de la función “raise_application_error”. Y el mensaje que generamos es: tipo de error + toda la traza de los saltos que se han producido hasta darse la excepción. Por tanto tenemos toda la información de lo ocurrido, esto nos permitirá ser mucho más ágiles para solucionar el problema.
Eso sí, el mensaje es “feo” para el usuario que trabaja con la aplicación, pero estos mensajes no los tiene que entender el usuario, sino el técnico responsable de solucionar las incidencias. No se preocupe por ello, cuando un usuario se enfrente a tal mensaje será plenamente consciente de que se ha producido un error, y reportará el mensaje al servicio técnico para que lo solucione.
Tratamiento de excepciones
Las excepciones deben tratarse por algo, por algún motivo, y no solamente porque se ha producido un error. En el ejemplo anterior ese motivo es: que el proceso aborte mostrando un mensaje de error detallado. Cuando usted cree un apartado de excepciones en un bloque de código, hágalo con un propósito que no sea únicamente lo que ya hace el SGBD por defecto. No reinvente la rueda. Véase el siguiente procedimiento:
create or replace procedure insertar_vehiculo(p_vehiculo in vehiculos%rowtype) is
begin
insert into vehiculos(ID_VEHICULO, DESCRIPCION)
values (p_vehiculo.id_vehiculo, p_vehiculo.descripcion);
exception
when dup_val_on_index then
update vehiculos
set descripcion = p_vehiculo.descripcion
where id_vehiculo = p_vehiculo.id_vehiculo;
end;
El anterior procedimiento inserta un nuevo registro en la tabla VEHICULOS. En el caso de que el identificador ya exista en la tabla se produce una excepción de clave primaria duplicada, en tal caso el algoritmo no debe abortar, sino que debe actualizar la descripción del vehículo con ese identificador. Por eso se ha implementado un tratamiento a tal excepción. Y cualquier otra excepción que se pueda dar debe propagarse, por lo que no se trata.
Si el programa que invoca a este procedimiento considera que no es crítico que falle, puede encapsular la llamada en un bloque de código y tratar cualquier excepción para que no aborte.
declare
v_vehiculo vehiculos%rowtype;
begin
v_vehiculo.id_vehiculo := 4;
v_vehiculo.descripcion := 'Super buga';
begin
insertar_vehiculo(v_vehiculo);
exception
when others then
null;
end;
-- resto de líneas del algoritmo
end;
Fíjese que esta filosofía es justamente opuesta a la de programar procedimientos con parámetros de salida que informen si la ejecución ha ido bien y su evaluación post llamada. En este caso no debe evaluarse si ha ido bien o no, sino que deben encapsularse las llamadas o instrucciones para que en lugar de abortar se le dé el tratamiento que proceda a esa parte del código. Este modo de proceder, aparte de ser mucho más cómodo, es más seguro, dado que si descuidamos encapsular la parte de código afectado para que no aborte, no es ni de largo tan crítico como descuidar evaluar el parámetro de salida para que aborte si dicho parámetro así lo indica. Recuerde que solo hay una cosa peor que un programa que no hace lo que debe: un programa que hace lo que no debe.
Por tanto mi consejo es que base su estándar de control de errores en no tratar las excepciones salvo para evitar que el proceso aborte ya que sabe cómo tratarlas. Y no las trate salvo que quiera capturar el error o mostrar un mensaje. En cuyo caso incluya toda la información con ayuda de las funciones nativas de Oracle. El servicio de incidencias se lo agradecerá.
Registro de errores
Si usted decide darle un tratamiento a la excepción de modo que el error quede registrado en una tabla de la BD, recuerde que no puede realizar un commit tras realizar la inserción en la tabla de errores. Quizás se le ocurra ejecutar un rollback antes de la inserción, pero quizás tampoco sea lo correcto, ya que está tomando decisiones que quizás no le competen, dado que depende de lo que el usuario decida. Un modo de registrar el error dejando en manos del usuario guardar los cambios o no tras un error, consiste en programar un procedimiento que realice la inserción del error en una transacción autónoma e independiente de la transacción que ha provocado el error. Esto se consigue con ayuda de la instrucción “PRAGMA AUTONOMOUS_TRANSACTION”. Véase el siguiente procedimiento:
create procedure guardar_error(p_error in varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into errores(fecha, error, usuario)
values (sysdate, p_error, user);
commit;
end;
Este procedimiento insertará el error en la tabla errores y guardará los cambios en una transacción independiente a la que invoca el procedimiento. Por lo que no corre el riesgo de confirmar o descartar cambios cuando le compete al usuario hacerlo y no al programador.
Un ejemplo de uso:
begin
buscar_mejor_precio(3434, 23323);
mostrar_mejor_precio;
exception
when others then
guardar_error(SQLERRM||' -> traza: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
raise_application_error(-20000, SQLERRM||' -> traza: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end;
Generación de excepciones
Se ha visto cómo con el procedimiento “raise_application_error” es posible forzar que un programa aborte personalizando el mensaje de error. Este recurso es muy útil para provocar una excepción y propagarla sin necesidad de poner condicionantes en el código. Veamos primero cómo trabajar sin aprovechar este recurso:
create or replace function calcular_precio(p_id_articulo in number, p_error out number) return number is
v_error number;
v_precio number;
begin
if p_id_articulo is null then
v_error := 1;
elsif articulo_descatalogado(p_id_articulo) then
v_error := 2;
else
v_precio := calcular_precio_art(p_id_articulo);
v_error := 0;
end if;
return v_precio;
end;
Fíjese que ahora no nos encontramos ante un error inesperado, sino que se implementa una lógica para validar el parámetro de entrada de la función calcular_precio.
Y lo invocamos del siguiente modo:
declare
ARTICULO_NO_VALIDO exception;
v_error number;
v_precio number;
begin
v_precio := calcular_precio(45, v_error);
if v_error != 0 then
raise ARTICULO_NO_VALIDO;
else
dbms_output.put_line(v_precio);
end if;
exception
when ARTICULO_NO_VALIDO then
raise_application_error(-20000,'El código de artículo indicado no es válido');
end;
Estamos en una situación muy parecida a la vista con anterioridad, debemos evaluar el parámetro de salida para saber si el artículo es válido, en caso contrario se genera una excepción y el proceso aborta mostrando el mensaje de error personalizado.
En este caso la profundidad de llamadas a funciones es de un solo nivel, pero imagine usted que deba realizar varios saltos o llamadas y a la vuelta de cada una de ellas, evaluar si se ha superado la validación de parámetros de entrada para seguir o no con el proceso y en caso contrario ir propagando este control hasta el bloque principal de código donde finalmente se aborta la ejecución. No es práctico. El procedimiento “raise_application_error” nos ahorra todo el tratamiento post llamada al margen de la profundidad de los saltos en llamadas a funciones que precise el algoritmo. Modifiquemos la anterior lógica con este propósito:
create or replace function calcular_precio(p_id_articulo in number) return number is
v_precio number;
begin
if p_id_articulo is null then
raise_application_error(-20000,'El código de artículo indicado en el parámetro de entrada es nulo');
elsif articulo_descatalogado(p_id_articulo) then
raise_application_error(-20000,'El código de artículo indicado en el parámetro de entrada está descatalogado');
else
v_precio := calcular_precio_art(p_id_articulo);
end if;
return v_precio;
end;
Y lo invocamos del siguiente modo:
begin
dbms_output.put_line(calcular_precio(45));
exception
when others then
raise_application_error(-20000, SQLERRM||' -> traza: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end;
Si el artículo 45 está descatalogado el proceso abortará mostrando el siguiente mensaje:
ORA-20000: ORA-20000: El código de artículo indicado en el parámetro de entrada está descatalogado -> traza: ORA-06512: at "DB_TEST.CALCULAR_PRECIO", line 8
ORA-06512: at line 3
ORA-06512: at line 7
Con ello conseguimos liberarnos de condicionantes post llamada y de su propagación hasta la raíz o procedimiento principal. La verdad, resulta muy cómodo.
Y bueno hasta aquí el artículo. Espero que os resulte útil y confío en no encontrar más controles de errores mal diseñados en los futuros clientes a los que acuda. Ahora ya tenéis la información para montároslo bien. 🙂