Utilidad del paquete estándar PL/SQL DBMS_ROWID

Hace unas semanas me llegó una consulta sobre PL/SQL en la que se me preguntaba si era posible conocer, utilizando una consulta SQL, el nombre de la partición en la que se encontraba almacenado un determinado registro de una tabla. El paquete estándar PLSQL DBMS_ROWID nos puede ayudar a obtener esta información mediante la extracción del ROWID_OBJECT que identifica de manera única el segmento donde se encuentran los datos. Después bastará que asociemos este identificador con una de las vistas XXX_OBJECTS (donde XXX puede ser DBA, ALL o USER) para obtener el nombre de la partición (ver ejemplo a continuación).

SELECT sh.order_number
     , do.subobject_name
     , do.data_object_id
  FROM oe.so_headers_all sh
     , dba_objects do
 WHERE do.data_object_id = 
       DBMS_ROWID.ROWID_OBJECT(sh.rowid)
   AND sh.order_number = '123456'

ORDER_NUMBER SUBOBJECT_NAME DATA_OBJECT_ID
------------ -------------- --------------
123456       PART1          15107         

Transacciones parciales en PLSQL para lidiar con tablas muy grandes o voluminosas

Hace unas semanas alguien mencionó en un comentario que en su base de datos Oracle tenía una tabla con millones de registros y que, utilizando un bucle PLSQL, pensaba ejecutar un UPDATE y un COMMIT por cada, digamos, 500 registros procesados en dicha tabla, evitando así posibles problemas con los segmentos de rollback. El caso es que dicho lector me preguntaba si yo tenía alguna sugerencia al respecto. Este tipo de problema es, ciertamente, algo más complejo de lo que a simple vista parece, y sobre el que conviene escribir con cierta calma.

Si dividimos una transacción muy larga en muchas otras más pequeñas, existen bastantes probabilidades de que a mitad de la transacción global se produzca un error ORA-01555 (snapshop too old), problema generalmente causado por la alta frecuencia en la ejecución de sentencias COMMIT, o que simplemente se produzca un fallo del sistema. Entonces nos encontraremos con una transacción realizada parcialmente. Por lo tanto, antes de implementar este tipo de solución, deberemos asegurarnos de la que la transacción global puede re-ejecutarse. La cuestión es que en muchos casos esto no será posible y, por tanto, tendremos que escribir bastante código PL/SQL para hacer posible esta re-ejecución.

PLSQL dinámico con las funciones DBM_SESSION.SET_CONTEXT y SYS_CONTEXT (¿Por qué?)

En programación PL/SQL siempre tenemos lectores que nos hacen preguntas interesantes, en esta ocasión se nos ha preguntado acerca del motivo por el cual al utilizar PLSQL dinámico hay mucha gente que utiliza las funciones estándar de Oracle SYS_CONTEXT y DBM_SESSION.SET_CONTEXT, de manera que en la cláusula WHERE de cualquier consulta SQL, en lugar de utilizar simplemente literales se utiliza la función SYS_CONTEXT.

Es decir, por qué utilizar "WHERE valor = SYS_CONTEXT('mi_contexto','valor')", en vez de, por ejemplo, la simple y más corta sentencia "WHERE valor = 15".

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.

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.

Almacenamiento de subconsultas (subqueries PL/SQL) en la caché de las bases de datos Oracle

El almacenamiento caché de subconsultas o subqueries PL/SQL se trata de una funcionalidad de las bases de datos Oracle, denominada en inglés scalar subquery caching, que se encarga de optimizar internamente la ejecución de aquellas consultas que incorporan subconsultas. El funcionamiento es bastante intuitivo, si durante la ejecución de una consulta PLSQL compleja, dicha consulta incluye alguna subquery, la base de datos Oracle intentará almacenar en la caché la salida de dicha subconsulta con el objetivo de poder reutilizar dichos datos, una y otra vez, durante la ejecución de la consulta PL/SQL principal. Obviamente esto será mucho mejor para el rendimiento de la base de datos que el tener que re-ejecutar la subconsulta múltiples veces.

Los resultados de la subconsulta quedan almacenados en una estructura de datos interna o hash table que, mientras dura la ejecución de la consulta PLSQL, queda residente en la memoria caché de la sesión Oracle correspondiente. Dicha estructura de datos desaparece de la caché en el momento que la consulta PL/SQL termina.

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.

Como mostrar mensajes de texto en PL/SQL

Para mostrar mensajes en PL/SQL existe un paquete denominado DBMS_OUTPUT que incluye un conjunto de procedimientos y funciones que permiten almacenar información en un "buffer", información que puede recuperarse más tarde. Estas funciones pueden utilizarse también para mostrar la información almacenada en dicho "buffer" a los usuarios.

Dentro de estas funciones encontramos, por ejemplo, la función PUT_LINE que pone en el "buffer" parte de la información seguida por un "end-of-line". Esta función puede usarse también para mostrar texto a los usuarios. La función utiliza un único parámetro del tipo carácter y si la función se usa para mostrar mensajes a los usuarios, entonces el contenido del parámetro se corresponde con el contenido del mensaje. Ejemplo:

Sacar por pantalla los resultados de una consulta con DBMS_SQL.return_result

Desde la salida de la versión 12c de las bases de datos Oracle, es posible sacar por pantalla conjuntos de resultados de forma implícita. Esto es algo bastante útil si nos vemos en la tesitura de tener que migrar código escrito en SQL transaccional a código PL/SQL.

Para versiones anteriores de la base de datos Oracle, el PLSQL no soportaba la posibilidad de crear un procedimiento que simplemente volcase el contenido de una consulta SQL a la pantalla. Para conseguirlo, los desarrolladores de PL/SQL teníamos que escribir la consulta, recorrer con un bucle el conjunto de resultados devuelto por dicha consulta, y llamar en cada iteración al procedimiento estándar DBMS_OUTPUT.PUT_ LINE encargado de mostrar los datos en la pantalla.

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);