Bloqueo de tablas hijo por causa de ejecutar sentencias PL/SQL sobre tablas padre

En versiones de la base de datos Oracle anteriores a la 9i, cuando la clave primaria de una tabla padre (parent table) no se encuentra indexada en la tabla hijo (child table), es muy probable que tengamos problemas con los bloqueos de la tabla hijo que se producen, bien cuando se actualiza (con la sentencia PLSQL UPDATE) la clave primaria de la tabla padre (lo cual ocurre con relativa frecuencia ya que existen determinados trabajos que actualizan todas las columnas de una tabla incluso cuando el valor de la misma no ha cambiado), o bien cuando se realizaba el borrado (con la sentencia PL/SQL DELETE) de algún registro de la tabla padre.

El caso es que en las circunstancias anteriores y para evitar que se produzca un bloqueo completo de la tabla hijo (full table lock), lo más recomendable es indexar también en la tabla hijo la clave primaria de la tabla padre. No obstante, esta norma de bloqueo cambió con la versión 9i de la base de datos Oracle.

Cambios en los parámetros de la base de datos Oracle, cuándo tienen lugar

Existe la creencia un tanto generalizada de que cuando realizamos un cambio en los parámetros de la base de datos Oracle (utilizando el comando SQL ALTER SYSTEM SET ...) mientras la instancia de la base de datos está operativa y si los cambios los realizamos utilizando el fichero de parámetros almacenado (SPFILE), estos cambios no tienen lugar hasta que la base de datos se reinicia. Esta idea es falsa y equivocada, ya que el comando SQL ALTER SYSTEM dispone del parámetro SCOPE para especificar en que momento deben tener efecto los cambios.

Trabajando con fechas en PL/SQL: los tipos DATE, TIMESTAMP e INTERVAL

Las fechas son un tipo de datos del PL/SQL considerablemente más complejo que un tipo carácter o un tipo numérico. Una fecha o momento de tiempo está compuesto de múltiples campos (año, mes, día, hora, minutos, etcétera) y, además, existen un buen número de normas para determinar si una fecha es válida o no (los años bisiestos, los cambios de hora, etcétera). Como consecuencia de todo esto, en PLSQL resulta habitual tener que:

• Declarar constantes y variables de tipo fecha o tiempo.
• Utilizar funciones para modificar dichas variables y mostrarlas en el formato deseado por el usuario.
• Manipular fechas y tiempos para realizar cálculos variados.

Este artículo será el primero de una serie en los que explicaré todo lo que un programador PL/SQL necesita conocer para trabajar con los diferentes tipos de datos asociados con fechas y momentos de tiempo (DATE, TIMESTAMP e INTERVAL).

Cláusula BEQUEATH para las vistas (Oracle 12c)

Anteriormente a la versión 12c de las bases de datos Oracle, si desde una vista había que ejecutar una función PL/SQL siempre se invocaba con permisos del propietario de la vista, no los privilegios del propietario de la función. Esto implicaba que si la función había sido definida por el invocador, la conducta de la misma podía ser bastante diferente a lo esperado cuando se ejecutaba desde una vista.

Para solucionar este problema, la versión 12c de la base de datos Oracle incorpora la cláusula BEQUEATH que permite definir una vista para que si esta incluye funciones, los permisos de ejecución de las mismas se acomoden con los permisos del usuario que invoca la vista.

La función SQL PRODUCT o PROD, como simularla

Todo el mundo que programe en SQL y PL/SQL ha tenido alguna vez la necesidad de utilizar la función SUM(nombre_campo) dentro de una sentencia SELECT, función que suma el valor de todos los registros nombre_campo que devuelve la función SELECT al ser ejecutada. No obstante, si en vez de la suma queremos obtener el producto, ¿existe una función PRODUCT(nombre_campo) equivalente a SUM en SQL?

Teóricamente debería existir, como existe por ejemplo en Excel o en Matlab (en este caso con el nombre de PROD), pero lo cierto es que en SQL no existen las funciones PRODUCT ni PROD. Entonces, ¿qué hacer si tenemos que calcular el producto de un determinado campo para diferentes registros de una tabla?

Cláusula CONSTRAINT para mejorar el rendimiento de las consultas PL/SQL

Resulta bastante interesante mencionar la importancia de que, al crear las tablas de nuestra base de datos Oracle, utilicemos, cuando sea posible, la cláusula CONSTRAINT para mejorar el rendimiento de las consultas PLSQL que utilicen dicha tabla. Muchos desarrolladores de PL/SQL piensan que la utilización de la cláusula CONSTRAINT sólo sirve para garantizar la integridad de los datos, lo cual es cierto, pero esta cláusula también la utiliza el optimizador Oracle para determinar el plan de ejecución óptimo.

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

Cómo 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).

Cómo utilizar un cursor PL/SQL como parámetro de salida en un procedimiento

En este artículo o voy a proponer un ejercicio práctico bastante sencillo que puede resultar de bastante útilidad práctica. Supongamos que queremos crear un procedimiento PLSQL que utilice un par de parámetros de entrada, que podrán tomar el valor NULL, y con un parámetro de salida que será el cursor PL/SQL correspondiente a la siguiente sentencia SELECT:

SELECT * FROM empleados
WHERE nombre = parametro_1
AND apellidos = parametro_2;

Cómo obtener información sobre los objetos almacenados en las bases de datos Oracle

Ya hemos hablado en un artículo anterior sobre el diccionario de datos de las bases de datos Oracle, la vista USER_OBJECTS de dicho diccionario contiene un registro por cada objeto de la base de datos del que somos propietarios (o mejor dicho, del que es propietario el esquema con el que estamos conectados a dicha base de datos.

USER_OBJECTS (hacer clic sobre la imagen para ampliarla)

Las columnas que se utilizan de forma más común dentro de esta vista son:

• OBJECT_NAME: nombre del objeto.
• OBJECT_TYPE: tipo de objeto (toma valores como PACKAGE (paquete PLSQL), PROCEDURE (procedimiento), FUNCTION (función) o TRIGGER.
• STATUS: muestra el estado del objeto que puede ser VALID (válido) o INVALID (inválido).
• LAST_DDL_TIME: indica la última vez que el objeto fue cambiado.