El paquete PL/SQL DBMS_SCHEDULER para programación de trabajos

DBMS_SCHEDULER es el paquete PLSQL que reemplazó en la versión de la base de datos Oracle 10g al paquete DBMS_JOB. Aunque el paquete DBMS_JOB sigue existiendo por razones de compatibilidad, no debe utilizarse ya que es muy probable que deje de existir en futuras versiones de la base de datos Oracle. El paquete DBMS_SCHEDULER permite programar la ejecución, en los instantes que deseemos, de bloques PLSQL, así como de procedimientos y funciones PL/SQL. Por otro lado, también permite programar la ejecución de binarios y shell-scripts.

Permisos necesarios

Con permisos de DBA se tiene acceso a todas las funciones del paquete DBMS_SCHEDULER. Para administrar la programación de procesos se necesita tener la rol (role) de SCHEDULER_ADMIN. Y finalmente, para crear y ejecutar procesos bajo tu propia identidad, se necesita tener el privilegio CREATE JOB. Por otro lado, aprovecho para mencionar que el paquete DBMS_JOB requería la inicialización de un parámetro del sistema, tras lo cual se lanzaba un proceso en background encargado de coordinar la ejecución de los distintos procesos programados, pero esto ya no es necesario si utilizamos Oracle 10g y el paquete DBMS_SCHEDULER.

Programación rápida de ejecución de un bloque PL/SQL

Para programar la ejecución de un bloque PLSQL podemos utilizar directamente el siguiente código. Sin duda es la forma más rápida y sencilla de programar la ejecución de un trabajo.

BEGIN
  DBMS_SCHEDULER.create_job(
     job_name        => 'DEMO_BLOQUE_PROGRAMADO'
    ,job_type        => 'PLSQL_BLOCK'
    ,job_action      => 
      'begin 
       nom_paq.nom_proced(''valor_param'');
       end;'
    ,start_date      => '06/11/2009 02:00 AM'
    ,repeat_interval => 'FREQ=DAILY'
    ,enabled         => TRUE
    ,comments        => 'Bloque PLSQL programado');
  COMMIT;
END;
/

En el código de arriba lo que hemos hecho es programar un bloque PL/SQL para que se ejecute diariamente a las dos de la mañana a partir del día 11 de junio de 2009.

Ejemplo completo de programación de un trabajo

Lo primero que debemos hacer el definir el programa que se va a ejecutar.

BEGIN
  DBMS_SCHEDULER.create_program (
     program_name        => 'DEMO_PROC_PROGRAMADO'
    ,program_type        => 'STORED_PROCEDURE'
    ,program_action      => 'nom_paq.nom_proced'
    ,number_of_arguments => 1
    ,enabled             => FALSE
    ,comments            => 'Proc. PLSQL programado');
  DBMS_SCHEDULER.define_program_argument (
     program_name      => 'DEMO_PROC_PROGRAMADO'
    ,argument_position => 1
    ,argument_name     => 'valor1'
    ,argument_type     => 'VARCHAR2'
    ,default_value     => 'valor por defecto');
  DBMS_SCHEDULER.enable (
     name => 'DEMO_PROC_PROGRAMADO');
  COMMIT;
END;
/

El parámetro program_type puede tomar los siguiente valores: PLSQL_BLOCK, STORED_PROCEDURE o EXECUTABLE.

A continuación debemos definir cuando y con que frecuencia queremos ejecutar nuestro programa, es decir, debemos definir la schedule propiamente dicha.

BEGIN
  DBMS_SCHEDULER.create_schedule(  
     schedule_name   => 'DEMO_SCHEDULE'
    ,start_date      =>  '01/01/2009 01:00:00'
    ,repeat_interval => 'FREQ=WEEKLY'
    ,comments        => 'Semanalmente a las 01:00');
  COMMIT;
END;
/

Si queremos deshabilitar la schedule anteriormente definida utilizaremos el siguiente código:

BEGIN
  DBMS_SCHEDULER.drop_schedule(
     schedule_name => 'DEMO_SCHEDULE'
    ,force         => TRUE );
  COMMIT;
END;
/

Para establecer la frecuencia (repeat_interval) podemos utilizar las siguientes expresiones: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY y SECONDELY.

A continuación debemos definir el proceso o trabajo que queremos programar. Esto se realiza mediante la asignación de un programa a una o más schedules.

BEGIN
  DBMS_SCHEDULER.create_job(
     job_name      => 'DEMO_TRABAJO_PLSQL'
    ,program_name  =>'DEMO_PROC_PROGRAMADO'
    ,schedule_name =>'DEMO_SCHEDULE'
    ,enabled       => FALSE
    ,comments      => 'Semanal a las 01:00');
  DBMS_SCHEDULER.set_job_argument_value(
     job_name          => 'DEMO_TRABAJO_PLSQL'
    ,argument_position => 1
    ,argument_value    => 'valorparametro1');
  DBMS_SCHEDULER.enable('DEMO_TRABAJO_PLSQL');
  COMMIT;
END;
/

Por otro lado y según ya he comentado, con el paquete DBMS_SCHEDULER también podemos hacer correr directamente un ejecutable o una shell script:

BEGIN
   DBMS_SCHEDULER.create_job
   (
      job_name      => 'RUN_SHELL_SCRIPT_DEMO',
      schedule_name => 'DEMO_SCHEDULE',
      job_type      => 'EXECUTABLE',
      job_action    => '/home/shell/demo_script.sh',
      enabled       => TRUE,
      comments      => 'Ejecución de un shell script'
   );
  COMMIT;
END;
/

Otros paquetes estándares del PLSQL: DBMS_LOCK, DBMS_SQL y DBMS_OUTPUT.

Haz clic aquí para leer todo el artículo.

Como evitar el uso de constantes fijas (hard-coded) en PL/SQL

Esta claro que la mayoría de los programas y aplicaciones PLSQL tienen su propio conjunto de constantes que determinan las características de dicha aplicación. Por lo general, estos valores constantes tienen que ser utilizados en distintos lugares del código. En muchas ocasiones estos valores permanecerán invariables durante todo el ciclo de vida de la aplicación pero, en muchos otros casos, cambiarán de forma periódica (por ejemplo, una vez al año).

Pongamos un ejemplo, supongamos que en un programa PL/SQL establecemos que el salario anual de un empleado no puede superar los 50.000 euros. Podemos codificar esta norma utilizando la siguiente subrutina:

IF l_salario_anual > 50000
THEN
RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior
  a 50000 euros');
END IF;

La mayoría de los programadores sabemos que referenciar un valor constante de esta manera no es muy recomendable. En inglés se dice que la constante está "hard-coded" directamente en la rutina. Resulta obvio señalar que este tipo de prácticas suele derivar en problemas, ya que el valor de 50.000 euros cambiará muy probablemente de año en año y, cuando esto ocurra, deberemos revisar el código PLSQL y cambiar el antiguo valor por el nuevo en cada uno de los sitios donde sea utilizado.

En PL/SQL disponemos de tres formas bastante evidentes de evitar tener que utilizar constantes directamente en el código:

• Crear un paquete PLSQL que asigne el valor a esa constante
• Crear un función PLSQL que devuelva dicho valor
• Almacenar y gestionar el valor de dicha constante en una tabla de la base de datos Oracle

A continuación pondré un ejemplo de cada una de las opciones mencionadas.

Paquete PL/SQL de constantes

CREATE PACKAGE c_constantes IS
 c_salario_maximo CONSTANT NUMBER := 50000;
END c_constantes;

Una vez definido el paquete ya sólo tendríamos que referirnos al mismo de la siguiente manera:

IF l_salario_anual > 
   c_constantes.c_salario_maximo
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || c_constantes.c_salario_maximo
  || ' euros');
END IF;

Función PL/SQL que devuelve el valor de la constante

CREATE PACKAGE f_constantes IS
FUNCTION f_salario_maximo RETURN NUMBER;
END f_constantes;

CREATE PACKAGE BODY f_constantes IS
FUNCTION f_salario_maximo RETURN NUMBER IS
BEGIN
  RETURN 50000;
END;
END f_constantes;

Y ahora para referirnos a la constante utilizaríamos el siguiente código:

IF l_salario_anual > 
   f_constantes.f_salario_maximo()
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || f_constantes.f_salario_maximo()
  || ' euros');
END IF;

Almacenar la constante en una tabla de la base de datos

Si en nuestra aplicación debemos gestionar un buen número de constantes, la opción más recomendable sería crear una tabla con los campos, por ejemplo, "nombre", "descripción" y "valor", y sacar de ella los valores constantes correspondientes. En nuestro caso, y suponiendo que el campo "valor" es un VARCHAR2, podríamos escribir la siguiente función PLSQL:

CREATE PACKAGE t_constantes IS
FUNCTION t_numero 
  (t_nombre IN tabla_constantes.valor%TYPE) 
  RETURN NUMBER;
END t_constantes;

CREATE PACKAGE BODY t_constantes IS
FUNCTION t_numero 
  (p_nombre IN tabla_constantes.valor%TYPE) 
  RETURN NUMBER IS
BEGIN
  v_valor tabla_constantes.valor%TYPE;
  SELECT valor
  INTO v_valor
  FROM tabla_constantes
  WHERE nombre = p_nombre;
  RETURN TO_NUMBER(v_valor);
END;
END t_constantes;

Y para referirnos a la constante utilizaríamos algo semejante a:

IF l_salario_anual >
   t_constantes.t_numero('salario maximo')
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || t_constantes.t_numero('salario maximo')
  || ' euros');
END IF;

En este ejemplo he definido una función para devolver valores de tipo numérico (NUMBER), pero igualmente podríamos definir funciones semejantes que devuelvan valores de tipo VARCHAR2, DATE, etcétera.

Artículos relacionados:
Tipos de datos en PLSQL.
Procedimientos y funciones en PLSQL.

Haz clic aquí para leer todo el artículo.

La funcionalidad de muestreo dinámico o Dynamic Sampling

La funcionalidad de muestreo dinámico (Dynamic Sampling) estuvo por primera vez disponible para la release 2 de la bases de datos Oracle 9i. Esta funcionalidad posibilita que el optimizador SQL y PL/SQL basado en costes (CBO) muestree las tablas que utiliza una consulta (query) durante la fase de parsing duro, para mejorar los valores estadísticos que utiliza el optimizador al incluir en dichas estadísticas los segmentos no analizados con anterioridad. Como ya he indicado, este muestro sólo ocurre durante la fase de parsing duro y se utiliza para mejorar las estadísticas utilizadas por el optimizador PLSQL, de ahí el nombre de muestro dinámico.

El optimizador de las bases de datos Oracle usa una gran variedad de datos de entradas para generar los planes de ejecución. Utiliza todos los índices y restricciones (constraints) definidos en las tablas, las estadísticas del sistema (velocidad de entrada/salida de los servidores, velocidad de CPU), y las estadísticas recogidas de los segmentos involucrados en la ejecución de la consulta. El optimizador utiliza estas estadísticas para estimar el número de registros involucrados en cada uno de los pasos de los que consta un plan de ejecución específico, siendo este volumen de registros la variable principal a la hora de calcular el coste de ejecución de una consulta. Cuando el optimizador calcula incorrectamente el volumen de registros, probablemente escogerá un plan de ejecución ineficiente causando problemas de rendimiento en la base de datos. De hecho, la principal razón por la que el optimizador Oracle puede decidirse por un plan de ejecución incorrecto, es precisamente por esta causa, es decir, una estimación incorrecta del volumen de registros a procesar en cada paso del plan de ejecución.

En conclusión, el principal motivo que existe detrás de utilizar la funcionalidad del muestreo dinámico, es ayudar al optimizador a estimar correctamente el volumen de registros a procesar, alimentándole con mayor cantidad de datos y una información más exacta relativa a la ejecución de la consulta que se necesita ejecutar en ese mismo momento.

Formas de trabajar del muestreo dinámico o Dynamic Sampling

El tipo de muestreo dinámico puede establecerse de dos formas diferentes:

• Con el parámetro OPTIMIZER_DYNAMIC_SAMPLING puede establecerse el tipo de muestreo dinámico, bien a nivel de instancia de la base de datos, bien a nivel de sesión mediante la utilización del comando ALTER SESSION.
  
• También se puede utilizar el hint de SQL o PL/SQL denominado DYNAMIC_SAMPLING para establecer es tipo de muestreo a nivel de consulta

A continuación os mostraré un ejemplo en el que podréis ver algunas diferencias entre un plan de ejecución generado por el optimizador Oracle sin utilizar muestreo dinámico, y otro plan en el que si que se utiliza dicha funcionalidad. En el ejemplo debemos considerar que la tabla mitabla consta de 60.000 registros:

SQL> set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(mit 0) */ * 
SQL> from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |15034| 52     (0)|00:00:01|
| 1|TABLE ACCESS FULL|T   |15034| 52     (0)|00:00:01|
------------------------------------------------------

SQL> select * from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |64222| 54     (2)|00:00:01|
| 1|TABLE ACCESS FULL|T   |64222| 54     (2)|00:00:01|
------------------------------------------------------

En el ejemplo vemos un primer caso de plan de ejecución en el que hemos deshabilitado el muestreo dinámico utilizando el hint DYNAMIC_SAMPLING, mientras que el segundo plan de ejecución, puesto que por defecto dicha funcionalidad está habilitada y no hemos utilizado ningún hint en la consulta, se ha generado utilizando el muestro dinámico. Como podemos ver, los planes de ejecución arrojan datos bastante dispares. Al deshabilitar el muestreo dinámico, el optimizador utiliza los valores estadísticos por defecto que típicamente no son muy fiables, ya que el número de registros se estima en base a número de bloques de datos en la tabla y al tamaño medio de los registros de dicha tabla. Así, al deshabilitar el muestreo dinámico, el optimizador deduce que la tabla cuenta con tan sólo 15.034 registros frente a los 60.000 que tiene en realidad. Por otro lado, al activar el muestreo dinámico, el optimizador da un valor mucho más cercano a la realidad, 64.222 registros.

La inexactitud se hace todavía más patente si borramos totalmente el contenido de la tabla mitabla. En este caso, el plan de ejecución si deshabilitamos el muestreo dinámico seguirá devolviéndonos el valor de 15.034 registros, mientras que el plan de ejecución con el dynamic sampling habilitado no dirá que el número de registros a procesar es tan sólo 1.

El muestreo dinámico es más eficaz cuando en una consulta estamos utilizando una tabla que ha sido creada y poblada con datos, pero que todavía no ha sido analizada mediante el paquete estándar PLSQL DBMS_STATS, paquete que se utiliza para generar los datos estadísticos de las tablas. Hay que considerar que desde la primera release de la base de datos Oracle 10g, ya sólo esta soportado el optimizador basado en costes, y que dicho optimizador necesita datos estadísticos exactos para funcionar correctamente. Por lo tanto, si una tabla existe y sus estadísticas no han sido recogidas todavía, el muestreo dinámico ayuda sobremanera a que el optimizador genere planes de ejecución adecuados.

Por último terminaré indicando que la funcionalidad de muestreo dinámico ofrece once niveles de dynamic sampling, de 0 (deshabilitado) a 10. En la release 2 de Oracle 9i el valor por defecto es 1, mientras que en la release 2 de Oracle 10g y superiores, el valor por defecto es 2. Si queréis informaros acerca del significado e implicaciones que supone utilizar un nivel de muestreo dinámico u otro, os emplazo a que visitéis este enlace: Dynamic Sampling Levels.

Artículos relacionados:
Hints PLSQL para el modo de optimización.
Planes de ejecución Oracle.

Haz clic aquí para leer todo el artículo.

El paquete estándar DBMS_LOCK para sincronizar procesos

Hace unas semanas alguien me preguntó como se podía determinar si un procedimiento PL/SQL (procedure PLSQL) estaba siendo ejecutado para evitar tener dos instancias del mismo proceso corriendo simultáneamente. Para manejar este tipo de situaciones, así como situaciones en las que queramos ejecutar procedimientos y funciones de forma secuencial y sincronizar diferentes procesos, Oracle dispone de el paquete de funciones y procedimientos DBMS_LOCK.

Con el paquete DBMS_LOCK podemos establecer bloqueos de usuario (PL/SQL User Locks (UL)) con los que podremos parar la ejecución de un procedure si al ejecutarlo nos encontramos con que un determinado bloqueo o lock está en proceso.

A continuación os describo los tres procedimientos y funciones del paquete DBMS_LOCK más importantes:

DBMS_LOCK.allocate_unique (nombre_lock, id_lock, expira_seg): Este procedimiento lo que hace es asignar un identificador único (id_lock) para el nombre de bloqueo (nombre_lock) que hemos utilizado como parámetro de entrada. Por lo tanto, id_lock se trata de un parámetro de salida, y los valores que puede tomar están comprendidos en el rango de 1073741824 a 1999999999. Por otro lado, el parámetro expira_seg es opcional e indica el número de segundos que se mantendrá el bloqueo una vez que haya sido solicitado, si no se especifica, el valor por defecto es de 864000 segundos (10 días).

DBMS_LOCK.request (id_lock, tipo_lock, timeout, unlock_on): Con esta función lo que hacemos es solicitar el establecimiento de un bloqueo de usuario.

Veamos el uso de los distintos parámetros:

• id_lock: Valor del identificador único generado anteriormente mediante la ejecución del procedimiento DBMS_LOCK.allocate_unique.


• tipo_lock: Permite definir el tipo de bloqueo que queremos establecer. Puede tomar los siguientes valores: 2 - Row Share (SS), 3 - Row Exclusive (SX), 4 - Share (S), 5 - Share Row Exclusive (SSX) y 6 - Exclusive (X). En la mayoría de los casos utilizaremos el valor de 6 para establecer un bloqueo exclusivo.


• timeout: Número de segundos que queremos permanecer esperando a que se libere el bloqueo si nos encontramos con que otro idéntico ha sido solicitado anteriormente y está todavía en marcha. Si no especificamos ningún valor, la función permanecerá en espera hasta que el bloqueo se libere (el valor por defecto que toma la variable timeout es muy alto).


• unlock_on: Indica si el bloqueo se debe liberar al ejecutar los comandos COMMIT o ROLLBACK (valor TRUE) o si debe mantenerse aunque ejecutemos alguno de los comandos mencionados (valor FALSE). El valor por defecto es FALSE.

Los valores que puede devolver esta función son los siguientes:

• 0: Bloqueo establecido satisfactoriamente.


• 1: El bloqueo no se pudo establecer por superarse el tiempo de espera (timeout).


• 2: Bloqueo multiple (deadlock). Aparece cuando se ha excedido el límite de peticiones de bloqueo permitidas sobre el mismo identificador.


• 3: Parámetro erróneo o inválido.


• 4: Aparece cuando en una misma sesión se solicita el mismo bloqueo por segunda vez sin haber liberado la primera petición.


• 5: Identificador de lock ilegal.

DBMS_LOCK.release (id_lock): Esta función se utiliza para liberar un bloqueo previamente establecido con la función DBMS_LOCK.request. La función puede devolver los valores 0, 3, 4 ó 5. El significado de dichos valores es exactamente el mismo que en la función DBMS_LOCK.request salvo el valor 4, que en este caso indica que no somos los dueños del lock y no podemos liberarlo.

Ejemplo práctico del uso del paquete DBMS_LOCK

En una primera sesión ejecutamos:

DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
     DBMS_LOCK.request(v_lockhandle, 6));
END;

La salida por pantalla tras la ejecución del processo sería:

id_lock = 1074484814107448481486
request status = 0

A continuación en la tabla V$LOCK podemos identificar el bloqueo en curso de la siguiente manera (ojo, sólo hay que utilizar los diez primeros caracteres del id_lock):

SELECT sid, type, id1, lmode, 
       request, ctime, block
FROM   v$lock 
WHERE  id1 = '1074484814'

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   201     0

Si ejecutamos el mismo proceso sobre una segunda sesión, observaremos que dicho proceso se queda esperando a que el bloqueo establecido en la primera sesión se libere. En la tabla V$LOCK veremos que aparece una nueva línea en espera de establecer un bloqueo exclusivo (lmode = 0, request = 6):

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   790     1
 97 UL   1074484814     0       6     9     0

Ahora si abrimos una tercera sesión y ejecutamos:

DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
    DBMS_LOCK.request(v_lockhandle, 6, 20));
END;

El proceso se quedará en espera durante 20 segundos (observad que he utilizado el parámetro de timeout con el valor de 20). Transcurrido este tiempo el proceso terminará y obtendremos la siguiente salida:

id_lock = 1074484814107448481486
request status = 1

Es decir, el proceso DBMS_LOCK.request devuelve el valor "1" indicando que el establecimiento del bloqueo falló por timeout. Por otro lado, durante esos 20 segundos de espera, aparecerá una tercera línea en la tabla V$LOCK (lmode = 0, request = 6), que no considero necesario mostrar.

Ahora, si en la primera sesión liberamos el bloqueo ejecutando:

DECLARE
  v_lockhandle VARCHAR2(200) := '1074484814';
BEGIN
  DBMS_LOCK.release(v_lockhandle);
END;

Entonces observaremos que, al liberarse el bloqueo, la ejecución del proceso que estaba en espera en la segunda sesión termina. En la tabla V$LOCK nos encontraremos con lo siguiente:

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
 97 UL   1074484814     6       0     3     0

Es decir, ahora el bloqueo exclusivo (lmode = 6) está establecido por la segunda sesión.

Artículos relacionados:
El paquete estándar PLSQL DBMS_SQL.
El paquete estándar PLSQL DBMS_OUTPUT.

Haz clic aquí para leer todo el artículo.

Tablas Oracle: Claves naturales o claves sustitutivas

En algunas ocasiones me he encontrado con bases de datos en las que se aplica la norma de, a la hora de diseñar una tabla nueva, utilizar siempre una clave sustitutiva (surrogate key) , incluso existiendo una clave natural perfectamente aplicable. Cuando he preguntado por el motivo de crear tales claves sustitutivas, la razón ha sido casi siempre la de aumentar la eficiencia de la base de datos eliminando la posibilidad de tener que enlazar dos tablas utilizando más de una columna.

Primero aclararé, para aquellos que no lo tengan claro, el concepto de clave sustitutiva o surrogate key. Una clave sustitutiva no es más que una clave interna, un identificador único, que no tiene significado para el negocio, y que identifica de forma única un registro de una tabla de la base de datos.

En mi opinión, si al definir una tabla disponemos de una clave natural, que obviamente debe ser única e inmutable, debemos utilizarla en detrimento de la clave sustitutiva. El argumento de que la utilización de claves sustitutivas va a mejorar el rendimiento de la base de datos es erróneo, y las claves naturales hacen más sencilla la escritura de nuestras consultas SQL o del código PL/SQL.

Por ejemplo, si disponemos de un par de tablas, la tabla PRODUCTOS y la tabla VERSION_PRODUCTOS, lo lógico sería utilizar para la primera tabla un campo producto_id como clave primaria (primary key) , campo que podría tratarse de una clave sustitutiva poblada por una secuencia, y usar como clave primaria de la segunda tabla una combinación de producto_id y de numero_version. Las tablas podrían definirse como sigue:

CREATE TABLE productos
( producto_id NUMBER PRIMARY KEY,
  otros_datos... );

CREATE TABLE version_productos
( producto_id REFERENCES productos,
  numero_version NUMBER,
  otros_datos...
  CONSTRAINT ver_pro_pk PRIMARY KEY 
  (producto_id, numero_version));

En nuestro ejemplo, el uso de una clave sustitutiva añadiría un trabajo extra a cualquier operación de INSERT que debería generar la clave sustitutiva, además de hacer lo propio para la clave natural. Mi opinión es que si la clave natural es inmutable y razonable, entonces debemos utilizarla. En este caso concreto, con razonable quiero significar que la clave no necesite utilizar quince campos para ser generada, sino que sólo hagan falta de dos a cinco columnas.

Haz clic aquí para leer todo el artículo.

Problemas con los triggers SQL

Mucha gente piensa que los triggers PL/SQL son una de las más potentes herramientas de las bases de datos Oracle. De hecho lo son, pero existen dos razones fundamentales por las que, personalmente, trato de evitar la utilización de triggers a la hora de implementar mis proyectos en PL/SQL.

Problemas de mantenimiento

A largo plazo, la utilización de triggers suele causar grandes dolores de cabeza a la hora de pensar en el mantenimiento. Al ser piezas del código que sólo ocurren como consecuencia de que se ha realizado otra operación, es muy frecuente que la gente se olvide de que los triggers están allí, y revisar el código pensando en todas las piezas de la base de datos que pueden afectarle, se hace poco menos que imposible.

Este problema es muy sencillo de comprender. Una persona experta en PL/SQL que no conoce una aplicación realizada en código PL/SQL y SQL, puede comprender fácilmente lo que realiza un procedimiento almacenado, pero si nuestro sistema utiliza triggers de manera rutinaria, entonces ese procedimiento puede desencadenar centenares de otro tipo de procesos y cambios en la base de datos que no son detectables a simple vista.

En conclusión, los triggers hacen que la comprensión de un sistema sea complicada, causan que su mantenimiento sea más difícil de lo normal y crean confusión al permanecer ocultos en el esquema la base de datos. Mucha gente considera que los triggers son como sentencias DDL y que, al igual que no hay necesidad de revisar una sentencia CREATE TABLE, tampoco hay necesidad de revisar el código de un trigger PL/SQL. En realidad, los triggers son subrutinas que son llamadas una y otra vez, cuyo código debe revisarse de igual manera que se revisan paquetes y procedimientos almacenados.

Uso incorrecto de los triggers

En muchos casos de los que yo he analizado, los triggers PL/SQL se han implementado de forma incorrecta. El código de los triggers suele incluir enormes errores que el programador no fue capaz de prever o anticipar. Veamos algunos ejemplos de este tipo de implementaciones incorrectas.

En alguna ocasión me he encontrado con triggers que realizan operaciones como esta:

:new.nombre_completo := 
:new.nombre||' '||:new.apellidos;

Lo primero que se me viene a la cabeza al ver una línea de código como la de arriba, es que la columna nombre_completo debería ser una columna de una vista (view) o, en Oracle 11g, una columna virtual. La columna nombre_completo es el resultado de una función trivial, con casi ningún coste de procesamiento y que, de ser necesario, podría hasta ser indexada. No existe ninguna razón para almacenar el resultado de la función en la tabla física.

Además, imaginemos que por alguna razón queremos cambiar el valor de la columna nombre_completo para algunos registros, simplemente el trigger no nos dejará hacerlo y, si no conocemos la existencia del mismo, nos preguntaremos una y mil veces por qué el campo no se actualiza al ejecutar el comando UPDATE. El trigger creará, cuando menos, confusión, y, con suerte, nos daremos cuenta de su existencia y de que debemos deshabilitarlo para poder realizar la deseada actualización del campo.

Otro de los usos incorrectos de los triggers es utilizarlos para enviar emails a los usuarios cuando se inserta o se actualiza un registro de una tabla. Veamos el siguiente trigger:

SQL> CREATE TRIGGER enviar_correo
 2   AFTER INSERT ON pedidos
 3   FOR EACH ROW
 4   BEGIN
 5   UTL_MAIL.send
 6   (sender=>'app@miempresa.com',
 7   recipients=>'pedidos@miempresa.com',
 8   subject=>'Nuevo pedido '||:new.num_pedido,
 9   message=>'Nuevo pedido recibido');
 10  END;
 11  /

Trigger created.

El trigger es sintácticamente correcto, compilará sin problemas y, si se inserta un nuevo pedido en la tabla "pedidos", nuestros usuarios recibirán un email indicándoselo. Sin embargo, el trigger tiene un grave problema. Alguien se ha preguntado qué ocurrirá si insertamos cien registros en la mencionada tabla y, por cualquier razón, decidimos deshacer las inserciones ejecutando el comando ROLLBACK. Pues simplemente lo que ocurrirá es que habremos enviado cien emails a los usuarios indicándoles que existen cien nuevos pedidos que en realidad no existen.

En este sentido conviene señalar que, para que este tipo de situaciones ocurran, no es necesario que uno mismo invoque el ROLLBACK, sino que continuamente la base de datos Oracle deshace operaciones INSERT, UPDATE, MERGE o DELETE, sin necesidad de que uno se lo indique personalmente.

Podemos decir que prácticamente cualquier trigger PL/SQL que llame a funciones UTL_ (como UTL_FILE, UTL_HTTP, UTL_MAIL, UTL_SMTP, UTL_TCP, etcétera) estará incorrectamente implementado. En conclusión, con los triggers no se debe realizar ninguna operación que no pueda ser deshecha con posterioridad.

Los triggers también pueden causar problemas cuando los programadores no entienden debidamente una de las características más interesantes de las bases de datos Oracle, el hecho de que las lecturas no bloquean las escrituras, y de que las escrituras no bloquean las lecturas. Esto se torna especialmente crítico cuando los triggers se utilizan para forzar que se cumpla alguna regla determinada. Todo esto será tema de otro artículo posterior.

Haz clic aquí para leer todo el artículo.

Acceso remoto mediante DBLINK a una base de datos Oracle

La manera más sencilla de acceder desde una base de datos Oracle a tablas y vistas (views) de otra base de datos Oracle es mediante el uso de un DBLink (enlace a base de datos). No obstante, en muchos casos puede no ser recomendable la utilización de DBLinks, ya que el acceso a tablas y vistas remotas puede provocar importantes problemas de rendimiento en ambas bases de datos, tanto la remota como la local. En la mayoría de los casos estos problemas de rendimiento se deben a la imposibilidad de utilizar los índices de las tablas remotas.

Los DBLinks se crean en la base de datos local utilizando el comando PL/SQL o SQL CREATE DATABASE LINK. Obviamente, el usuario que ejecute dicho comando debe tener los permisos necesarios para poder hacerlo. La sintaxis del comando SQL mencionado es como sigue:

CREATE [PUBLIC] DATABASE LINK Nombre_dblink
  CONNECT TO Nombre_usuario INDENTIFIED BY Contraseña
  USING 'Cadena_conexion';

En donde:

Nombre_dblink es el nombre del DBLink.

Nombre_usuario y Contraseña son los identificadores que utilizará el DBLink para conectarse a la base de datos remota. Por lo tanto, el acceso a las tablas y vistas de la base de datos remota se hará con los permisos que dicho usuario tenga sobre ellas en dicha base de datos. Si se omiten estos valores, entonces el acceso a la base de datos remota se hará con el mismo nombre de usuario y contraseña con el que el usuario en cuestión esté conectado a la base de datos local.

Cadena_conexion identifica a la base de datos remota. Puede ser el nombre de la instancia, si ésta está definida en el archivo tnsnames.ora de la base de datos origen, o también, para la versión 8 de la base de datos Oracle o posteriores, podemos utilizar directamente la definición TNS (esto posibilita que no tengamos que modificar el fichero tnsnames.ora).

Por otro lado, si definimos el DBLink como PUBLIC, todos los usuarios tendrán acceso al mismo.

A continuación os pongo algunos ejemplos del comando CREATE DATABASE LINK:

CREATE DATABASE LINK dbl_bd001
  CONNECT TO repadmin IDENTIFIED BY reppass1
  USING 'bd001';

CREATE PUBLIC DATABASE LINK dbl_bd001
  CONNECT TO repadmin IDENTIFIED BY reppass1
  USING '(description = 
           (address = 
             (protocol = tcp)
             (host = bd.miempresa.com)
             (Port = 1521) )
           (connect_data = 
             (sid = bd001) )
         )';

Una vez creado el DBLink, para referenciar una tabla o vista de la base de datos remota se ha de indicar el nombre de la tabla o vista, concatenado con el carácter "@" y el nombre que se le ha dado al DBLink. Las tablas y vistas remotas podrán usarse en consultas (sentencias SELECT) y, si la base de datos Oracle utiliza el sistema de bases de datos distribuidas, entonces se podrán ejecutar también sentencias SQL o PLSQL del tipo DELETE, INSERT, UPDATE o LOCK TABLE. Asimismo, se pueden crear sinónimos para abreviar la forma de referenciar a las tablas y vistas remotas. A continuación os pongo algunos ejemplos de sentencias:

SELECT * FROM Nombre_tabla_remota@Nombre_dblink;

INSERT INTO Nombre_tabla_remota@Nombre_dblink (campo1)
  VALUES ('prueba');

UPDATE Nombre_tabla_remota@Nombre_dblink;
  SET campo1 = 'actualizado'
  WHERE campo1 = 'prueba';

CREATE SYNONYM tremota
  FOR Nombre_tabla_remota@Nombre_dblink;

SELECT * FROM tremota;

Nota: Existe otro concepto que no he explicado aquí y es el de los DBLinks compartidos (comando CREATE SHARED DATABASE LINK), el que necesite más información sobre este concepto puede mirar en el manual de Oracle sobre sistemas de base de datos distribuidos (Oracle Distributed Database Systems). Por otro lado, también es posible acceder mediante DBLink a bases de datos remotas que no sean Oracle. Para crear un DBLink de este tipo, y antes de que nadie me pregunte, podéis realizar una búsqueda en Internet de las palabras: "Oracle Heterogeneous Services". Quizá más adelante escriba algún artículo con referencia a estos dos temas.

Haz clic aquí para leer todo el artículo.

Generador de números aleatorios en PL/SQL

Es posible que en alguna ocasión necesitéis utilizar un generador de números aleatorios en un programa PL/SQL. Oracle proporciona el paquete estándar DBMS_RANDOM para este propósito. Obviamente podemos escribir nuestra propia rutina PLSQL que genere números aleatorios, pero paquete estándar de Oracle DBMS_RANDOM es más rápido ya que llama al generador de números aleatorios interno de la base de datos Oracle.

Los procedimientos y funciones que incluye este paquete son:

INITIALIZE: Inicializa el valor de la semilla del generador de números aleatorios.

EXEC dbms_random.initialize(12345678);

NORMAL: Devuelve números aleatorios siguiendo un distribución normal.

SELECT dbms_random.normal
FROM dual;
-- Sólo valores positivos
SELECT ABS(dbms_random.normal)
FROM   dual;

RANDOM: Genera números aleatorios.

SET SERVEROUTPUT ON
DECLARE
 aleator PLS_INTEGER;
 semilla NUMBER(20); 
BEGIN
 SELECT hsecs
 INTO   semilla
 FROM   gv$timer;
 dbms_random.initialize(semilla);
 FOR i IN 1..10
 LOOP
   aleator := dbms_random.random;
   dbms_output.put_line(aleator);
   semilla := aleator; 
 END LOOP;
 dbms_random.terminate;
END;
-- Genera números positivos entre 1 y 100
SELECT (1+ABS(MOD(dbms_random.random,100)))
FROM   dual;

SEED: Reinicializa el valor de la semilla

EXEC dbms_random.seed(87654321);

STRING: Crea una cadena de caracteres aleatoria. La definición de la función es como sigue, dbms_random.string(opcion IN CHAR, longitud IN NUMBER), donde opción puede tomar los valores:

'a' o 'A': Genera una cadena aleatoria con sólo letras íncluyendo mayúsculas y minúsculas.
'l' o 'L': Genera una cadena aleatoria con sólo letras minúsculas.
'p' o 'P': Genera una cadena aleatoria con cualquier tipo de carácter imprimible.
'u' o 'U': Genera una cadena aleatoria con sólo letras mayúsculas.
'x' o 'X': Genera una cadena aleatoria con caracteres alfa numéricos en mayúsculas.

-- Genera una cadena de 10 caracteres formada
-- por letras mayúsculas y minúsculas
SELECT dbms_random.string('A', 10)
FROM   dual;

TERMINATE: Indica que hemos terminado de usar el paquete DBMS_RANDOM.

dbms_random.terminate;

VALUE: Sin parámetros devuelve un valor aleatorio entre 0 y 1 con un número fijo de decimales, pero también puede usarse con parametros para que devuelva números aleatorios entre un rango de numeros.

SELECT dbms_random.value
FROM   dual;
-- Develve números aleatorios entre 10 y 20
SELECT dbms_random.value(10, 20)
FROM dual;

A continuación podéis ver un ejemplo de programa PL/SQL que genera números aleatorios entre 0 y 1 y que no utiliza el paquete DBMS_RANDOM

DECLARE
 semilla NUMBER;
BEGIN
 semilla := EXP(TO_NUMBER(TO_CHAR(SYSDATE,'ss'))/59);
 FOR i IN 1..10
 LOOP
   semilla := 1/(semilla - TRUNC(semilla));
   semilla := semilla - TRUNC(semilla);
   dbms_output.put_line(semilla);
 END LOOP;
END;

Haz clic aquí para leer todo el artículo.

Paquete DBMS_SQL para utilizar SQL dinámico (Dynamic SQL)

El grupo de paquetes DBMS se trata de un conjunto de funciones y procedimientos que el PL/SQL de Oracle incorpora de forma estándar. Estos paquetes DBMS pueden ser de mucha utilidad cuando estamos programando en PLSQL. Hoy voy a hablar sobre el paquete DBMS_SQL que permite utilizar SQL dinámico en procedimientos almacenados y bloques PL/SQL.

Las sentencias de SQL dinámico tienen la característica de que no forman parte del código fuente PL/SQL, sino que están almacenadas dentro de cadenas de caracteres que, bien forman parte de los parámetros de entrada, o bien son construidas durante la ejecución del programa PLSQL. Esto posibilita la creación de programas de propósito mucho más general. Por ejemplo, se pueden crear procedimientos que operen sobre una tabla cuyo nombre no se conoce hasta el momento de la ejecución, ya que se trata de un parámetro de entrada de dicho procedimiento.

Además, con el paquete DBMS_SQL se pueden ejecutar cualquier tipo de sentencias, tanto DML (Data Manipulation Language - Lenguaje de manipulación de datos), como DDL (Data Definition Language - Lenguaje de definición de datos). Por lo tanto, también permite directamente desde PL/SQL crear, modificar o borrar: tablas, índices, o cualquier objeto DDL (incluso procedimientos).

En el siguiente ejemplo os muestro un procedimiento almacenado en el que, dependiendo de los parámetros de entrada, se actualiza, bien el sueldo (campo salario), bien el departamento (campo dpto), de un determinado empleado.

CREATE OR REPLACE PROCEDURE upd_tabla_empleados
(   p_campo  IN VARCHAR2
  , p_emp_id IN VARCHAR2
  , p_valor  IN VARCHAR2 ) IS

  l_cursor        INTEGER;
  -- Sentencia a ejecutar
  l_sql           VARCHAR2(500);
  -- Número de rows actualizadas
  l_updated_rows  INTEGER;
BEGIN

  l_sql := 
  'UPDATE empleados SET ' || p_campo || ' = ' ||
  p_valor || ' WHERE empleado_id = ' || p_emp_id;

  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse (l_cursor, l_sql, dbms_sql.native);
  l_update_rows := dbms_sql.execute (l_cursor);
  dbms_sql.close_cursor (l_cursor);

END;

Por lo tanto si ejecutamos el comando:

SQL> EXEC upd_tabla_empleados ('dpto', '1234', 'Compras');

Estaremos asignando el empleado con ID 1234 al departamento de compras.

Y si ejecutamos:

SQL> EXEC upd_tabla_empleados ('salario', '1122', '3000');

Estaremos asignando al empleado con ID 1122 un salario de 3.000 euros.

Haz clic aquí para leer todo el artículo.

Integridad virtual con la base de datos Oracle 11g

Supongamos que nuestra base de datos Oracle dispone de una tabla en la que uno de los campos está formado realmente por muchos subcampos concatenados. El campo en cuestión no puede modificarse pero necesitamos validar y forzar la integridad de uno de los subcampos. Por ejemplo, queremos que los valores devueltos al ejecutar la función SUBSTR(column,7,3) sobre dicho campo estén incluidos en una tabla de referencia o lookup table.

Para versiones de la base de datos Oracle anteriores a la 11g no existía una forma sencilla de lidiar con este tipo de requerimientos ya que la validación de valores almacenados en una determinada columna, obligaba a que dichos valores estuvieran almacenados en una columna real que apuntase a la tabla de referencia o lookup table. En nuestro problema no existe una columna real sino que estamos tratando con una función aplicada sobre una columna. En la versión de la base de datos Oracle 10g y anteriores, lo más razonable sería crear una columna adicional en la tabla y, mediante el uso de un trigger PL/SQL, insertar o actualizar el valor retornado por la función SUBSTR en dicha nueva columna. A continuación podéis ver el código correspondiente a la implementación de esta solución:

SQL > CREATE TABLE tabla_referencia
  2   ( validacion VARCHAR2(3) PRIMARY KEY,
  3     descripcion VARCHAR2(20) );
Table created.

SQL > CREATE TABLE tabla_datos
  2   ( concatenados VARCHAR2(20) PRIMARY KEY,
  3     validacion VARCHAR2(3) NOT NULL
  4     REFERENCES tabla_referencia );
Table created.

SQL > CREATE TRIGGER tabla_datos
  2   BEFORE INSERT OR UPDATE 
  3   OF concatenados ON tabla_datos
  4   FOR EACH ROW
  5   BEGIN
  6     :new.validacion := 
  7       SUBSTR(:new.concatenados,7,3);
  8   END;
  9   /
Trigger created.

SQL > INSERT INTO tabla_referencia (validacion)
  2   VALUES ('XYZ');
1 row created.

SQL > INSERT INTO tabla_datos (concatenados)
      VALUES ('123456XYZ012345');
1 row created.

SQL > INSERT INTO tabla_datos (concatenados)
  2   VALUES ('123456ABC012345');
INSERT INTO .....
*
ERROR at line 2:
ORA-02291: Integrity constraint violated - 
parent key not found

La solución propuesta conlleva un par de problemas. El primero se trata de un obvio problema de almacenamiento ya que estamos almacenando el mismo dato dos veces, una vez en el campo concatenados y otra en el campo validacion. El segundo problema viene dado por la necesidad de utilizar un trigger PL/SQL y las implicaciones que este hecho puede conllevar sobre el rendimiento y los tiempos de ejecución de los comandos INSERT y UPDATE.

El caso es que con la versión de la base de datos Oracle 11g podemos forzar la integridad utilizando funciones aplicadas sobre una determinada columna; es decir, no estamos limitados a utilizar columnas reales, podemos utilizar una columna virtual. Así pues, con Oracle 11g podemos prescindir del trigger PL/SQL. Veamos a continuación como debemos crear nuestra tabla de datos para conseguir que esto ocurra:

SQL > CREATE TABLE tabla_datos
  2   ( concatenados VARCHAR2(20) PRIMARY KEY,
  3     validacion AS 
  4       (SUBSTR(concatenados,7,3))
  5     REFERENCES tabla_referencia );
Table created.

En este caso hemos definido una columna virtual con el nombre concatenados, columna que es simplemente un metadato, que no se almacena de manera redundante, y que no requiere de un trigger PL/SQL para su mantenimiento, pero que si que se puede utilizar para forzar la integridad de nuestra base de datos cruzando dicho valor con los valores almacenados en nuestra tabla de referencia o lookup table.

Artículos relacionados: Oracle lanza la versión 11g de su base de datos.

Haz clic aquí para leer todo el artículo.