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.

Cómo ejecutar sentencias DDL dentro de un trigger PL/SQL

Supongo que muchos conoceréis el hecho de que no es posible incluir sentencias DDL (es decir, sentencias de definición de objetos como CREATE, REVOKE, GRANT, ALTER, etcétera) dentro de un trigger PL/SQL. Esto es un hecho que tiene una explicación muy sencilla, ¿qué ocurriría si dentro de un trigger ejecutamos una sentencia no transaccional o DDL y necesitamos deshacer la transacción (rollback)? Sencillamente no podríamos deshacer la ejecución de dicha sentencia. En esta situación nuestra transacción dentro del trigger PLSQL no habría tenido lugar, pero la sentencia DDL se habría ejecutado dejando nuestra base de datos Oracle en una situación claramente no deseable.

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.

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.

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

La vista ALL_IDENTIFIERS, usos y funcionalidades (2)

Ya escribí con anterioridad un articulo introductorio sobre la vista ALL_IDENTIFIERS. ALL_IDENTIFIERS muestra información relevante sobre todos los identificadores de los objetos PL/SQL o unidades de programa almacenados en la base de datos Oracle a los que tiene accesos un determinado usuario.

Las columnas clave de la vista ALL_IDENTIFIERS son:

• OWNER: El propietario de objeto PL/SQL o unidad de programa que contiene el identificador.
• NAME: Nombre del identificador.
• TYPE: Tipo de identificador (por ejemplo, FORALL OUT, CONSTANT, PACKAGE o RECORD).
• SIGNATURE: Cadena única asignada a cada identificador, útil para poder distinguir entre los diferentes identificadores que tienen el mismo nombre.
• OBJECT_NAME: Nombre del objeto o unidad de programa que contiene el identificador.
• OBJECT_TYPE: Tipo del objeto o unidad de programa que contiene el identificador (por ejemplo, PACKAGE, TRIGGER o PROCEDURE).
• USAGE: Tipo de uso del dentificador.
• USAGE_ID: Identificador único del uso del identificador.
• USAGE_CONTEXT_ID: Clave foránea que identifica el USAGE_ID de su registro padre (por ejemplo, el registro padre de un parámetro de una función o procedimiento dentro de un paquete PLSQL sería la propia función o procedimiento).
• LINE: Número de la línea de programa donde aparece el identificador.
• COL: Número de columna en la línea de programa donde aparece el identificador.

PL/Scope para analizar el código PL/SQL, la vista USER_IDENTIFIERS (1)

Ya hablamos en un artículo anterior sobre la posibilidad de ejecutar consultas sobre la vista USER_SOURCE para comprobar la presencia o ausencia de determinadas cadenas de caracteres en nuestro código PL/SQL, algo que, entre otras posibilidades, permite realizar comprobaciones simples para comprobar si nuestro código cumple con unos mínimos requisitos de calidad.

Pero la base de datos Oracle, desde la aparición de la versión 11g, ofrece medios mucho más potentes para analizar nuestro código PL/SQL utilizando PL/Sope. En este artículo hablaremos de como utilizar esta nueva funcionalidad para ampliar y mejorar lo que ya ofrecía la vista USER_SOURCE mediante la utilización de la vista USER_IDENTIFIERS. Este es el primer artículos de una serie en la que hablaremos de todas las posibilidades que ofrece esta nueva vista.

Vistas USER_ARGUMENTS y DBA_ARGUMENTS para analizar los argumentos de funciones y procedimientos

USER_ARGUMENTS y DBA_ARGUMENTS son dos vistas realmente útiles para los programadores de PL/SQL. Contienen información sobre los argumentos, también llamados parámetros, de cada uno de los programas almacenados en nuestro esquema (USER_ARGUMENTS) o en toda la base de datos Oracle (DBA_ARGUMENTS, el acceso a esta vista suele estar restringido a los administradores de la base de datos). Al mismo tiempo, ofrece una gran cantidad de información muy bien diseccionada y estructurada.

Las columnas clave de esta vista son:

• OWNER (solo en DBA_ARGUMENTS): Nombre del esquema propietario del procedimiento o función PL/SQL.
• OBJECT_NAME: Nombre del procedimiento o función.
• PACKAGE_NAME: Nombre del paquete PL/SQL dentro del cual se ha definido el procedimiento o función.
• ARGUMENT_NAME: Nombre del argumento o parámetro.
• POSITION: Posición del argumento dentro de la lista de parámetros. Si toma el valor 0, hace referencia a la cláusula RETURN dentro de una función.
• IN_OUT: Indica si el argumento es de entrada o salida o permite ambos modos (IN - entrada, OUT - salida, IN OUT - entrada salida).
• DATA_TYPE: El tipo de dato del argumento.
• DATA_LEVEL: El nivel de anidamiento del argumento para tipos compuestos. Por ejemplo, si uno de los parámetros es de tipo registro, la vista USER_ARGUMENTS incluirá una entrada para este argumento con un DATA_LEVEL de 0 y diversas entradas con un DATA_LEVEL de 1 para cada campo de dicho registro.

Funciones de grupo en SQL (cláusulas GROUP BY y HAVING)

En los ejemplos del artículo anterior se utilizaban las funciones de grupo aplicadas sobre todos los registros seleccionados en un SELECT determinado. Lo cierto es que en muchas ocasiones podemos necesitar categorizar las agrupaciones dentro de un conjunto de datos.

Cláusula GROUP BY

La cláusula GROUP BY permite recolectar los datos dentro de un SELECT y agrupar los resultados por una o más columnas. Es decir, las funciones de grupo y la cláusula GROUP BY se utilizan conjuntamente para calcular los valores que arrojan dichas funciones para cada uno de los grupos.