Scripts PL/SQL Utilitarios para ORACLE

Introducción

En el día a día es muy común encontrarse con diferentes escenarios que ponen a prueba a uno, además de que nos llegan a consumir bastante tiempo, cuando realmente son cosas pequeñas y básicas.

Éste pequeño artículo pretende ser un conglomerado de scripts que suelo utilizar en el desarrollo con PL/SQL además de un conjunto de herramientas que pueden serle útiles a cualquiera.

Scripts PL/SQL

Control de errores

Podemos hacer uso del bloque EXCEPTION para poder capturar los errores no controlados y así evitar que nuestro script PL/SQL ocasiones errores en ejecución:

DECLARE
  PROCEDURE CONTROL_EXCEPCIONES
  IS
  CO_ERROR  NUMBER; 
  DE_ERROR  VARCHAR2(3000);
  BEGIN
    DBMS_OUTPUT.PUT_LINE('División por 0: ' || (10/0)); /*Div por 0*/
  EXCEPTION 
    WHEN OTHERS THEN
      /*Manejo de excepciones no controladas*/
      CO_ERROR := SQLCODE; 
      DE_ERROR := SQLERRM(CO_ERROR);
      DBMS_OUTPUT.PUT_LINE('Error: '|| CO_ERROR || ', descripción: ' ||DE_ERROR);
  END;
BEGIN
 CONTROL_EXCEPCIONES();
END;

Salida del script anterior:

Error: -1476, descripción: ORA-01476: divisor is equal to zero
  

Como se puede ver, el código de error para la división por 0 es ORA-01476, el cual lo podemos controlar usando la constante ZERO_DIVIDE para mostrar un mensaje personalizado:

DECLARE
  PROCEDURE CONTROL_EXCEPCIONES
  IS
  CO_ERROR  NUMBER; 
  DE_ERROR  VARCHAR2(3000);
  BEGIN
    DBMS_OUTPUT.PUT_LINE('División por 0: ' || (10/0)); /*Div por 0*/
  EXCEPTION 
    WHEN ZERO_DIVIDE THEN
      /*Controlamos la excepción ORA-01476*/
      DBMS_OUTPUT.PUT_LINE('Intento de división por 0');
    WHEN OTHERS THEN
      /*Manejo de excepciones no controladas*/
      CO_ERROR := SQLCODE; 
      DE_ERROR := SQLERRM(CO_ERROR);
      DBMS_OUTPUT.PUT_LINE('Error: '|| CO_ERROR || ', descripción: ' ||DE_ERROR);
  END;
BEGIN
 CONTROL_EXCEPCIONES();
END;

Salida del script anterior

Intento de división por 0

De ésta manera podemos imprimir un mensaje más propicio según la excepción ocurrida.

Par saber el nombre de una excepción puedes utilizar la siguiente tabla de constantes para excepciones pre-definidas en Oracle:

Rastreo de errores no controlados

En ambientes de pruebas, es muy común el uso de SQLCODE y SQLERRM para poder mostrar algún error no controlado y de ésta manera ubicarlo y darle solución. Pero ésta práctica no es muy fiable ya que SQLERRM sólo te da una descripción del error, más no indica dónde ocurrió y esto suele ser un gran problema por que hay que volver a ejecutar la prueba para localizar dónde ocurrió.

Para poder proveer mejor información de depuración ante la ubicación de errores no controlados se puede usar las funciones FORMAT_ERROR_BACKTRACE y FORMAT_CALL_STACK ambas del paquete DBMS_UTILITY.

Veámos un ejemplo:

DECLARE
  PROCEDURE PROCEDIMIENTO_ERROR
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('División por 0: ' || (10/0)); /*Div por 0*/
  EXCEPTION 
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
      DBMS_OUTPUT.PUT_LINE('Traza inversa:');
      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
  END;
BEGIN
 PROCEDIMIENTO_ERROR();
END;

Salida del script:

Error:ORA-01476: divisor is equal to zero

Traza inversa:
ORA-06512: at line 5

Como se puede observar, la función FORMAT_ERROR_BACKTRACE devuelve la línea de código en la que ocurrió la excepción, ésto nos ayuda a ubicar rápidamente el código que ocasiona el problema.

Finalmente, recomiendo almacenar éstas descripciones en una tabla de incidencias así podremos tener una mejor información cuando haya ocurrido un error en un entorno de producción