Exception PL/SQL parte I - tratamiento de errores a fondo

Oracle, pionero en BD relacionales.

Exception PL/SQL parte I - tratamiento de errores a fondo

Notapor Pere » Jue Ene 16, 2014 5:45 pm

Introducción

Oracle permite gestionar las excepciones de un modo muy simple y práctico. En esta primara parte del artículo se va a explicar los recursos que Oracle dispone para el tratamiento de excepciones. En la segunda parte os 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 que línea de código se ha producido el error y que tipo de error se ha producido.

Creamos la tabla con la que trabajaremos:

Código: Seleccionar todo
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:

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   descripcion_metal
(2);
end

PL/SQL procedure successfully completed.

En la ventana de salida DBMS Output vemos que muestras la palabra:

Plomo


Provocamos un error

Ahora pasémosle un identificador que no existe para provocar una excepción

Código: Seleccionar todo
begin
   descripcion_metal
(8);
end

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 viene toda la traza, para ello repasamos el log de abajo a arriba, para ir obteniendo los saltos desde donde se han hechos 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 esta indicando que el error se ha producido en la linea 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 linea 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 controlar el error y que no aborte la ejecución del proceso. Para ello añaidimos el apartado de excepciones al final del procedimiento del siguiente modo.

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   descripcion_metal
(8);
end

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_descripción del procedimiento de manera que no tenga suficiente espacio para almacenar el nombre del metal de identificador 2:

Código: Seleccionar todo
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 esta dimensionada a tan solo tres caracteres, veamos que ocurre al invocar al procedimiento:

Código: Seleccionar todo
begin
   descripcion_metal
(2);
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 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 linea 4 del procedimiento, es decir, donde esta 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.

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   descripcion_metal
(2);
end

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é que 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 por que 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 porque 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 encuentra 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 ilustra 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:

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   descripcion_metal
(2);
end

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 como 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:

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   descripcion_metal
(2);
end

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


Pasemosle ahora al procedimiento un identificador de metal que no exista:

Código: Seleccionar todo
begin
   descripcion_metal
(8);
end


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 como 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 como después del tratamiento a la excepción colocamos la instrucción RAISE para propagar el error:

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   descripcion_metal
(8);
end

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 linea 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 linea 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 esta 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 boque 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 critico y seguir su ejecución. Para ilustrar esto vamos a quitar el tratamiento de excepciones del procedimiento:

Código: Seleccionar todo
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:

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   
-- lineas de código previas al encapsulado

   begin

      descripcion_metal
(8);
   
   exception
      when no_data_found then
         null
;
   end; 
   
   
-- lineas 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 descripción_metal de modo se si ocurre un error de tipo no_dara_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:

Código: Seleccionar todo
begin
   
-- lineas de código previas al encapsulado
   begin

      descripcion_metal
(2);
   
   exception
      when no_data_found then
         null
;
   end;
      
   
-- lineas 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:

Código: Seleccionar todo
begin
   
-- lineas de código previas al encapsulado

   begin

      descripcion_metal
(2);
   
   exception
      when no_data_found then
         null
;
   end;
   
   
   
-- lineas 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 como 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.

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin   
   dbms_output
.put_line('Resultado: '||raiz_cuadrada(9));
end

En la ventana de salida DBMS Output se muestra:

Resultado: 3

Ahora pasémosle un número negativo:

Código: Seleccionar todo
begin   
   dbms_output
.put_line('Resultado: '||raiz_cuadrada(-9));
end


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 numero 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:

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin
   dbms_output
.put_line(raiz_cuadrada(-9));
end


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:

Código: Seleccionar todo
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:

Código: Seleccionar todo
begin   
   dbms_output
.put_line('Resultado: '||raiz_cuadrada(-9));
end

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 que valores devuelven en este caso las funciones nativas SQLCODE y SQLERRM:

Código: Seleccionar todo
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 como 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 esta 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 esta 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 que línea del código PL/SQL se ha dado la excepción,

El TOAD, entre otros programas, muestran 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á como 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.
Pere
 
Mensajes: 74
Registrado: Mar Feb 02, 2010 9:44 pm

Volver a SGBD Oracle

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 10 invitados