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:

Excepciones PL/SQL pre-definidas
NombreORA ERRORSe produce cuando
ACCESS_INTO_NULLORA-06530se intenta asignar valores a atributos de un objeto no inicializado.
CASE_NOT_FOUNDORA-06592ninguna de las secciones WHEN de una sentencia CASE es seleccionada además de no haber una cláusula ELSE.
COLLECTION_IS_NULLORA-06531se intentan acceder a los métodos de una nested table no inicializada o varray, o se intenta asignar valores a los atributos de una nested table sin inicializar o varray.
CURSOR_ALREADY_OPENORA-06511se intenta abrir (OPEN) un cursor que ya está abierto. Para poder reabrir un cursor se debe cerrar(CLOSE) primero.
DUP_VAL_ON_INDEXORA-00001se ha intentado registrar un valor por segunda vez en una columna con la restricción UNIQUE.
INVALID_CURSORORA-01001se intenta realizar una operación inválida en un cursor, como por ejemplo, cerrar un cursor que no ha sido abierto previamente.
INVALID_NUMBERORA-01722en una sentencia SQL, ha fallado la conversión de cadena a número dividido a que la cadena no representa un número válido.
LOGIN_DENIEDORA-01017se intenta acceder a una base de datos con usuario o contraseña inválido.
NO_DATA_FOUNDORA-01403una sentencia SELECT INTO no devuelve ninguna fila, se está haciendo referencia un elemento eliminado de una nested table o a un elemento sin inicializar.
NOT_LOGGED_ONORA-01012se intenta realizar una llamada a la base de datos cuando no se ha realizado una conexión.
PROGRAM_ERRORORA-06501hay algún error interno en PL/SQL
ROWTYPE_MISMATCHORA-06504el cursor principal y un PL/SQL cursor invocados en una llamada tienen tipos de retorno incompatibles.
SELF_IS_NULLORA-30625se intenta invocar un método miembro, pero la instancia del objeto no ha sido inicializado.
STORAGE_ERRORORA-06500PL/SQL corrió fuera de memoria o simplemente la memoria está corrupta.
SUBSCRIPT_BEYOND_COUNTORA-06533se referencia a una nested table  o un elemento de algún varray usando un índice más grande que el tamaño de la colección.
SUBSCRIPT_OUTSIDE_LIMITORA-06532se referencia a una nested table o varray usando un índice (por ejemplo -1) que está fuera del rango legal.
SYS_INVALID_ROWIDORA-01410la conversión de una cadena a un ROWID universal falla debido a que la cadena no representa un un rowid válido.
TIMEOUT_ON_RESOURCEORA-00051se cumple el tiempo de espera cuando se solicita un recurso de la base de datos.
TOO_MANY_ROWSORA-01422una sentencia SELECT INTO devuelve más de una fila.
VALUE_ERRORORA-06502ha ocurrido un error de conversión, truncamiento o límite de tamaño aritmético. Por ejemplo, se intenta convertir una cadena a número , pero el tamaño del número de la cadena supera el tamaño límite del tipo de número al que se desea convertir.
ZERO_DIVIDEORA-01476se intenta realizar una división por cero.

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.

Funciones para rastreo de errores
FunciónDescripción
FORMAT_ERROR_BACKTRACEMuestra la traza inversa hasta el punto dónde ocurrió la excepción. Adicionalmente muestra las líneas de código de cada llamada.
FORMAT_CALL_STACKMuestra la descripción del error, similar a SQLERRM

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

Sistemas Operativos: 
Lenguaje de programación: