Oracle objetos descompilados

DISCLAIMER: Tenía esta entrada en mi antiguo Evernote, pero no tenía ninguna referencia de donde la he sacado, así que, si alguien detecta que he copiado lo que muestro en esta entrada, que me lo haga saber en los comentarios.

¿Es un problema pasar una SQL que descompile un paquete?, sobre el papel, la respuesta es NO, no es un problema ya que Oracle antes de ejecutarlo si se encuentra el objeto descompilado lo intenta compilar y si lo logra lo ejecuta de forma transparente (si es que puede, ya veremos más adelante cuando no puede hacer que sea totalmente transparente).

Vamos a verlo con un ejemplo. Creamos una tabla, para que al añadirle un campo descompile la especificación y el body de un paquete:

CREATE TABLE dummy_test (
  campo VARCHAR2(30));

CREATE OR REPLACE PACKAGE pk_dummy_test IS
  PROCEDURE prueba(p_reg dummy_test%ROWTYPE);
  FUNCTION get_valor RETURN NUMBER;
END pk_dummy_test;
/

CREATE OR REPLACE PACKAGE BODY pk_dummy_test IS
  PROCEDURE prueba(p_reg dummy_test%ROWTYPE) IS
  BEGIN
    NULL;
  END prueba;

  FUNCTION get_valor RETURN NUMBER IS
  BEGIN
    RETURN(1);
  END get_valor;
END pk_dummy_test;
/

Ejecutamos la función get_valor:

SELECT pk_dummy_test.get_valor() FROM dual;
--RESULTADO: 1

Ahora, desde otra sesión de sqlplus añadimos un campo nuevo a la tabla DUMMY_TEST que hará que se descompile el paquete PK_DUMMY_TEST ya que usamos un parámetro de tipo ROWTYPE.

ALTER TABLE dummy_test ADD campo2 VARCHAR2(30);

SELECT object_type, status   FROM user_objects  WHERE object_name = 'PK_DUMMY_TEST';

Ahora el paquete está descompilado

Volvemos a ejecutar la función desde la sesión que la ejecutó antes:

SELECT pk_dummy_test.get_valor() FROM dual;

Ya no da error, compiló el paquete

¿Entonces, dónde está el problema?

El problema puede venir de dos lados, vamos a ver el más simple que es cuando no es capaz de recompilar el paquete ya que realmente está inválido. Por ejemplo falta un campo en una tabla que se usa en el paquete. En ese caso al intentar ejecutar algo del paquete obtendremos el error: «ORA-04063: package body «LIBRA540.PK_DUMMY_TEST» tiene errores».

El otro problema, viene por el diseño del paquete cuando se utilizan variables de sesión. Vamos a ver el mismo ejemplo que antes pero añadiendo una variable de sesión a la ecuación.

DROP TABLE dummy_test;

CREATE TABLE dummy_test (
  campo VARCHAR2(30));
 
CREATE OR REPLACE PACKAGE pk_dummy_test IS
  PROCEDURE prueba(p_reg dummy_test%ROWTYPE);
  FUNCTION get_valor RETURN NUMBER;
END pk_dummy_test;
/

CREATE OR REPLACE PACKAGE BODY pk_dummy_test IS
  v_variable NUMBER := 0;

  PROCEDURE prueba(p_reg dummy_test%ROWTYPE) IS
  BEGIN
    NULL;
  END prueba;

  FUNCTION get_valor RETURN NUMBER IS
  BEGIN
    v_variable := v_variable + 1;
    RETURN(v_variable);
  END get_valor;
END pk_dummy_test;
/

Ejecutamos la función get_valor:

SELECT pk_dummy_test.get_valor() FROM dual;

Repetimos, desde otra sesión añadimos un campo a la tabla DUMMY_TEST

ALTER TABLE dummy_test ADD campo2 VARCHAR2(30);

SELECT object_type, status  FROM user_objects  WHERE object_name = 'PK_DUMMY_TEST';

Paquete descompilado.

Volvemos a ejecutar la función desde la sesión que la ejecutó antes, pero en vez de devolvernos resultado nos dá el siguiente error: 

ORA-04068: se ha anulado el estado existente de los paquetes 

ORA-04061: el estado existente de package «LIBRA540.PK_DUMMY_TEST» ha sido invalidado

ORA-04065: package «LIBRA540.PK_DUMMY_TEST» no se ha ejecutado porque se ha modificado o borrado

Lo ejecutamos de nuevo y ya funciona: 

SELECT pk_dummy_test.get_valor() FROM dual;

¿Qué ha pasado?

La sesión en la que estábamos tenía almacenado en la variable v_variable el valor 1, en ese momento otra sesión añadió un campo que descompiló el paquete con lo que los valores que tenían otras sesiones almacenados se pierden.

Al intentar ejecutar la función del paquete, Oracle ve que está descompilado y lo compila sin problemas, pero no puede continuar la ejecución sin advertir al usuario con el ORA-04068 de que los valores que va a obtener pueden ser erróneos ya que las variables de sesión han perdido el valor que tenían y el paquete puede comportarse de forma anómala, ya que según la lógica la siguiente ejecución debería devolver 2, pero devuelve 1 ya que se ha reiniciado la variable.

¿Soluciones?

La solución pasa por evitar al máximo variables de sesión en los paquetes. Las variables globales deberían evitarse y pasar los valores entre funciones y procedimientos por parámetros. Las variables que sean necesarias para gestionar algún tipo de caché, pues deberían ser llevadas a otro paquete que se encargue exclusivamente de su almacenamiento y que sea lo más robusto posible ante las descompilaciones. 

Ejemplo:

DROP TABLE dummy_test;

CREATE TABLE dummy_test (
  campo VARCHAR2(30));

CREATE OR REPLACE PACKAGE pk_dummy_test_var IS
  v_variable        NUMBER := 0;
END pk_dummy_test_var;
/

CREATE OR REPLACE PACKAGE pk_dummy_test IS
  PROCEDURE prueba(p_reg dummy_test%ROWTYPE);
  FUNCTION get_valor RETURN NUMBER;
END pk_dummy_test;
/

CREATE OR REPLACE PACKAGE BODY pk_dummy_test IS
  PROCEDURE prueba(p_reg dummy_test%ROWTYPE) IS
  BEGIN
    NULL;
  END prueba;

  FUNCTION get_valor RETURN NUMBER IS
  BEGIN
    pk_dummy_test_var.v_variable := pk_dummy_test_var.v_variable + 1;
    RETURN(pk_dummy_test_var.v_variable);
  END get_valor;
END pk_dummy_test;
/

SELECT pk_dummy_test.get_valor() FROM dual;
--resultado 1

Igual que antes, desde otra sesión añadimos un campo nuevo a la tabla DUMMY_TEST.

ALTER TABLE dummy_test ADD campo2 VARCHAR2(30);

SELECT object_type, object_name, status   FROM user_objects   WHERE object_name IN ('PK_DUMMY_TEST', 'PK_DUMMY_TEST_VAR');

PK_DUMMY_TEST inválido, PK_DUMMY_TEST_VAR válido

Volvemos a ejecutar la función desde la sesión que la ejecutó antes:

SELECT pk_dummy_test.get_valor() FROM dual;
--RESULTADO:2 

IMPORTANTE: Llevando a otro paquete las variables de sesión, estamos contradiciendo las buenas prácticas que dicen que las varibles de sesión sólo deberían ser accesibles por el paquete que las utiliza, en Oracle 12c se puede indicar que el paquete PK_DUMMY_TEST_VAR únicamente sea accesible desde PK_DUMMY_TEST, pero en Oracle 11g eso no es posible, por lo que podría ser que desde PL/SQL de personalización nos modifiquen el comportamiento de los programas modificando esas variables y sin tener control sobre ello.