Creacion de una cola

Para crear una cola, se deben seguir los siguientes pasos:

1. Crear el tipo de datos que se precise para el cuerpo de la cola.
2. Crear la queue table que es la base de la persistencia y transaccionalidad de la cola
3. Crear la cola en sí

Vamos a ver cada uno de estos pasos

Creacion del tipo de datos

Una cola se utiliza para encolar y guardar items. Antes de crear la cola, debemos indicar que información y estructura deben tener estos items.

En la BBDD Oracle, esto implica crear un TYPE en la BBDD. TYPE es una estructura SQL (no confundir con las estructuras de PL/SQL). Podemos ver un documento más amplio al respecto aqui.

En resumen, podemos decir que se puede crear un objeto tan complejo como se desee comno composición de otros tipos.

Para que un usuario pueda crear tipos, debe tener el GRANT de CREATE TYPE. Este type se crea en su esquema.

Como ejemplo, vamos a ver como se crea un objeto "Cabecera y lineas" para contener un item factura, albaran o similar:

-- primero se crea una linea:
CREATE TYPE linea AS OBJECT (
    cantidad       NUMBER(6),
    descripcion    VARCHAR2(100),
    precio         NUMBER)
/
show errors
-- Ahora creamos un array de lineas
CREATE TYPE lineas AS VARRAY(20) OF linea
/
show errors
-- Finalmente la estructura completa
CREATE TYPE factura AS OBJECT (
    num_factura    NUMBER,
    fecha          DATE,
    observaciones  VARCHAR2(100),
    cliente        VARCHAR2(20),
    productos      lineas)
/
show errors
-- y para ver los tipos creados
desc linea
desc lineas
desc factura

Creación de la Queue Table

Antes de crear una cola, debemos crear una Queue Table. Dependiendo del tipo de cola, se van a crear diversas tablas y vistas: estas tablas y vistas solo permiten acceso en lectura (SELECT) y no se pueden modificar directamente con sentencia SQL. Se modifican a través de la API de las colas.

Para poder crear una queue table, el usuario debe tener permiso de ejecución en el package DBMS_AQADM. La queue table se crea en su esquema.

La creación de una Queue Tables es tan simple como ejecutar:

BEGIN 
dbms_aqadm.create_queue_table ( 
      queue_table=> 't_factura',          -- nombre de la Queue table
      queue_payload_type=> 'factura',     -- Tipo de datos que soporta entre comillas
      multiple_consumers=> FALSE,         -- si es multiple consumers
      message_grouping=> DBMS_AQADM.NONE  -- si agrupa los mensajes por transacciones
  );
COMMIT;        --> no olvidar el commit
END;
/

La opción multiple_consumers se refiere a si un mensaje puede ir destinado a más de un destinatario. En particular, no se refiere a si una cola puede tener varios procesos desencolando de la misma.

Empezamos con una cola No Multiple_consumers porque son las más habituales y es más sencillo trabajar con ellas.

La opción message_grouping indica si los mensajes se deben agrupar por transacciones de la BBDD o bien cada mensaje es independiente.

Creación de una cola

Para poder crear una cola, el usuario debe tener permiso de ejecución en el package DBMS_AQADM. La cola se crea en su esquema.

La creación de una cola es tan simple como ejecutar:

BEGIN 
  dbms_aqadm.create_queue(
        queue_name=> 'c_factura',              -- Nombre de la cola
        queue_table=> 't_factura',             -- Queue table en la que se crea
        queue_type=> DBMS_AQADM.NORMAL_QUEUE   -- Hay colas normales y de error
       ); 
COMMIT;
END;
/
BEGIN 
   dbms_aqadm.start_queue('c_factura', TRUE, TRUE); -- permitir encolar y desencolar
COMMIT;
END;
/

Por defecto, al crear una cola, no se permite encolar y desencolar. El procedimiento start_queue se ejecuta para que se puedan encolar y desencolar mensajes de la cola.

Con estos procesos ya tenemos creada una cola.

Las colas se identifican por su schema.nombre

En versión 11 se deben dar permisos para encolar y desencolar a los usuarios o roles que se desee.

BEGIN
  DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
    'ALL',          -- privilege        IN    VARCHAR2,
   'c_factura',     -- queue_name       IN    VARCHAR2,
   'usuario_o_rol', -- grantee          IN    VARCHAR2,
   FALSE);          -- grant_option     IN    BOOLEAN := FALSE);
END;
/

Se pueden crear varias colas sobre la misma queue table sin que ello implique pérdida de rendimiento en los accesos. Por supuesto, estas colas tienen el mismo tipo de datos (ya que el tipo de datos se define al crear la queue table). Si deseamos cols con distinto tipo de datos, se deben crear sobre distintas queue tables

Encolar y Desencolar

Vamos a ver 2 procedimientos sencillos para encolar y desencolar mensajes de una cola.

Cada vez que se llama al procedimiento encolar, se añade un mensaje a la cola.

Cada vez que se llama al procedimeinto desencolar, se obtiene el primer mensaje de la cola (y se desencola). Si no hay mensajes en la cola, el procedimiento se espera hasta que haya alguno.

Encolar

El procedimiento de encolar es tan sencillo como:

CREATE OR REPLACE PROCEDURE ENCOLA (datos in factura, cola in VARCHAR2) as
    enq_opt dbms_aq.enqueue_options_t;
    msg_opt dbms_aq.message_properties_t;
    msg_id raw(16);
begin
DBMS_AQ.ENQUEUE (cola,        --queue_name IN VARCHAR2
                 enq_opt,     --enqueue_options IN ,
                 msg_opt,     --message_properties IN message_properties_t,
                 datos,       --payload
                 msg_id);     --msgid OUT RAW);;
end;
/
show errors

Para crear este procedimeinto se debe tener el GRANT de CREATE PROCEDURE y permiso de ejecución sobre DBMS_AQ

Obsérvese que las variables del procedimeinto (enq_opt, msg_opt y msg_id) son obligatorias, pero tomamos su valores por defecto. msg_id devuelve el identificador interno del mensaje. Existe el campo correlation_id que se puede utilizar como identificador del mensaje definido por el usuario (veremos ejemplos más adelante).

Los parámetros del procedimiento son la cola donde queremos poner los datos  y el dato en sí.

Este procedimiento no hace COMMIT: el mensaje encolado estará disponible cuando la transacción haga COMMIT. Si la transacción hace ROLLBACK, el mensaje desaparece de la cola: se hace rollback del enqueue.

Por supuesto, una misma transacción puede encolar varios mensajes en la misma cola o en distintas colas y distintas sesiones pueden encolar en la misma cola.

No se permite encolar en colas remotas: la cola debe ser siempre local (estar en la propia Base de Datos)

Desencolar

Para desencolar el primer mensaje de la cola, podemos crear un procedimeinto del tipo:

CREATE OR REPLACE PROCEDURE DESENCOLA (datos out factura, cola in VARCHAR2) as
    deq_opt dbms_aq.dequeue_options_t;
    msg_opt dbms_aq.message_properties_t;
    msg_id raw(16);
begin
    DBMS_AQ.DEQUEUE (cola,     --queue_name IN VARCHAR2,
                     deq_opt,  --dequeue_options IN ,
                     msg_opt,  --message_properties IN message_properties_t,
                     datos,    --OUT payload
                     msg_id ); --msgid OUT RAW);;
end;
/
show errors

Para crear este procedimeinto se debe tener el GRANT de CREATE PROCEDURE y permiso de ejecución sobre DBMS_AQ

Obsérvese que las variables del procedimeinto (deq_opt, msg_opt y msg_id) son obligatorias, pero tomamos su valores por defecto. msg_id devuelve el identificador interno del mensaje.

Los parámetros del procedimiento son la cola de donde queremos obtener los datos  y el dato en sí.

Este procedimiento no hace COMMIT: el mensaje desencolado se eliminará de la cola cuando la transacción haga COMMIT. Si la transacción hace ROLLBACK, el mensaje vuelve a la cola: se hace rollback del dequeue.

Por supuesto, una misma transacción puede encolar y desencolar varios mensajes en la misma cola o en distintas colas y distintas sesiones pueden desencolar en la misma cola al mismo tiempo: en este caso, cada sesión obtendrá mensajes distintos.

No se permite desencolar de colas remotas: la cola debe ser siempre local (estar en la propia Base de Datos).

Ver el contenido de una cola

Al crear la queue table y las colas, se crean una seri de tablas y vistas que podemos consultar (pero no modificar su contenido). Todas ellas se forman a prtir del nombre de la queue table.

Mensajes que hay en una cola

Podemos ver los mensajes que hay en una cola consultando la vista AQ$<queuetable>

Ejemplo:

SQL> select queue, msg_id, to_char(enq_time,'yyyy-mm-dd hh24:mi:ss') enq_time,  msg_state, enq_txn_id
     from aq$t_factura;

QUEUE       MSG_ID                            ENQ_TIME             MSG_STATE  ENQ_TXN_ID
----------- --------------------------------- -------------------- ---------- -----------
C_FACTURA   73ECB6703FE60382E040007F01017DFB  2009-09-19 23:10:37  READY      6.42.4624

Se muestra la cola, el MSG_ID o identificador interno del mensaje (es distinto para cada mensaje), la hora de encolar, el estado del mensaje (READY indica que está listo para desencolar) y la transacción que lo ha encolado.

Ver el contenido de un mensaje

Utilizando sqlplus, se puede ver el contenido del mensje. Para ello, basta conmostrar el campo user_date de la tabla <queuetable>.

Vamos a ver un ejemplo:

SQL>  select user_data
      from t_factura
      where msgid = '73ECB6703FE60382E040007F01017DFB';

USER_DATA(NUM_FACTURA, FECHA, OBSERVACIONES, CLIENTE, PRODUCTOS(CANTIDAD, DESCRIPCION, PRECIO))
-----------------------------------------------------------------------------------------------
FACTURA(39, '19-SEP-09', 'observaciones factura', 'cliente 24', LINEAS(LINEA(2, 'desc1', 25), L
INEA(4, 'desc2', NULL), LINEA(1, 'desc3', 1)))

Se nos muestra el contenido en formato de objeto y según sus constructores.

Es de destacar que sqlplus es uno de los pocos programas que permiten visualizar el contenido de los datos de forma directa.

Sesión práctica de encolar y desencolar

Vamos aver una sesión de ejemplo práctico de como se encola y desencola.

Crearemos un procedimiento auxiliar que cree mensajes y los encole. Asi, además, vemos ejemplos de como trabajar con objetos:

CREATE OR REPLACE PROCEDURE ENCOLAR_MENSAJE (num in NUMBER, cola in VARCHAR2 default 'c_factura') as
    v_datos factura;
    v_lineas lineas;
BEGIN
    v_lineas := lineas();   -- constructor: array vacio
      -- antes de llenar el array de lineas, hay que dimensionarlo
    v_lineas.extend(3);     -- el array ahora tiene 3 elementos
      -- llenamos las lineas con valores.
      -- Observese el constructor linea(cantidad, descripcion, precio)
    v_lineas(1) := linea(2, 'desc1', 25);
    v_lineas(2) := linea(4, 'desc2', NULL);
    v_lineas(3) := linea(1, 'desc3', 1);
      -- Ahora la factura:
    v_datos := factura( num, sysdate, 'observaciones factura', 'cliente 24', v_lineas);
      -- y encolamos
    encola(v_datos, cola);
end;
/
show errors

Para este ejemplo suponemos:

• Un usuario de BBDD ha ejecutado las sentencias anteriores para crear las colas.
• Hay dos sesiones de sqlplus con el mismo usuario que ha creado las colas.

SQL> --Encolamos dos mensajes
SQL> exec encolar_mensaje(1);
SQL> exec encolar_mensaje(2);

SQL> set serveroutput on
SQL> -- Los desencolamos:
SQL> declare
     datos factura;
    begin
      desencola(datos, 'c_factura');
      dbms_output.put_line(datos.num_factura);
    end;
/

SQL> COMMIT;

1 
SQL> -- despues del commit, desencola ha terminado
SQL> -- se ha desencolado la factura num 1
SQL> ROLLBACK;

SQL> set serveroutput on
SQL> declare
     datos factura;
    begin
      desencola(datos, 'c_factura');
      dbms_output.put_line(datos.num_factura);
    end;
/
1 
SQL> 

SQL> declare
     datos factura;
    begin
      desencola(datos, 'c_factura');
      dbms_output.put_line(datos.num_factura);
    end;
/
2
SQL> 

SQL> declare
     datos factura;
    begin
      desencola(datos, 'c_factura');
      dbms_output.put_line(datos.num_factura);
    end;
/

SQL> ROLLBACK;

1 
SQL> COMMIT;