Funcionalidad errorlogging del SQL*Plus

Hasta la versión de la base de datos Oracle 10g Release 2 no era posible capturar en SQL*Plus los errores que se generan cuando escribíamos incorrectamente una sentencia SQL. Es decir, los errores conocidos como SP2 no podían ser gestionados por las funcionalidades estándar del SQL*Plus OSERROR o SQLERROR. Esto era algo normal ya que los errores SP2 no son errores del tipo OS, como el típico error "unable to open spool file", ni tampoco son errores de tipo SQL o PLSQL, ya que la sentencia que hemos escrito incorrectamente en realidad no se trata de ningún comando SQL y nunca llegó a alcanzar la capa SQL o PL/SQL de la base de datos Oracle.

Para los que todavía estén un poco confundidos y aún no hayan identificado cuáles son los errores SQL de tipo SP2 os dejo el siguiente ejemplo:

SQL y PL/SQL - La sentencia INSERT multitabla

La versión 9i de las bases de datos Oracle introdujo la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT cambió ligeramente su sintaxis, de manera que ahora permite la inserción de datos en más de una tabla de la base de datos de forma paralela. Existen dos formas de utilizar el comando INSERT multitabla: no condicional y condicional. En la forma no condicional, una cláusula compuesta INTO se ejecuta cada vez que la consulta SELECT devuelve un registro. En la forma condicional, las cláusulas compuestas INTO figuran dentro de cláusulas WHEN a partir de las que se determina si la correspondiente cláusula compuesta INTO se ejecuta o no.

Una claúsula compuesta INTO consiste de una o más cláusulas INTO. Una cláusula INTO debe especificar la tabla de la base de datos sobre la que se van a insertar los datos. Esta cláusula no admite alias. La cláusula INTO tambien proporciona el valor del los campos a ser insertados mediante la cláusula VALUES. La expresiones usadas en la cláusula VALUE pueden tratarse de cualquier expresión permitida, pero siempre debe hacer referencia a columnas devueltas por la consulta SELECT incluida en el INSERT.

Ejecución de cursores PLSQL y sentencias DML utilizando SQL dinámico nativo (Native Dynamic SQL)

Una de las preguntas más frecuentes que me suele hacer la gente es acerca de la posibilidad de definir un procedimiento PL/SQL en el que se declaren múltiples cursores en base al valor de los parámetros de entrada de dicho procedimiento. Las preguntas suelen incluir condiciones muy variadas, pero lo normal es que los implicados sólo necesiten hacer variable la cláusula WHERE y que el resto del cuerpo del cursor PLSQL se mantenga fijo. Cuando esto ocurre yo siempre contesto remitiendo a los que preguntan al artículo que escribí sobre el paquete estándar PL/SQL DBMS_SQL, un paquete que permite crear sentencias SQL dinámicas.

Si he decidido escribir un artículo nuevo al respecto es por el hecho de que resulta mucho más legible un código que utilice directamente SQL dinámico nativo, es decir, un código en el que no se utiliza el paquete estándar DBMS_SQL. A continuación os dejo un sencillo ejemplo de cómo se podría construir, utilizando SQL dinámico nativo, un procedimiento PLSQL en el que se define un cursor cuya cláusula WHERE varíe en función de un parámetro.

Cláusula DEFAULT para definición de columnas - Base de datos Oracle 12c

Las mejoras fundamentales que aporta la cláusula DEFAULT de la versión 12c de las bases de datos Oracle para definición de las columnas: una significativa mejora del rendimiento y una mayor facilidad para inicializar los datos de los registros de una tabla, lo que al final implica menos líneas de código.

Supongamos que para una tabla concreta necesitamos que, cuando se inserta un nuevo registro, un campo determinado tome el valor de una secuencia. La forma en que implementaríamos este requerimiento en versiones anteriores a la 12c sería mediante un trigger PLSQL.

Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

Yo siempre he dicho que cuando para hacer algo se pueden utilizar sentencias SQL sencillas, no resulta conveniente emplear complicados procedimientos PL/SQL que implementen la misma solución. Sin embargo, hay situaciones en que para mejorar el rendimiento de determinados bucles FOR en los que se realizan actualizaciones masivas sobre una determinada tabla de la base de datos Oracle, resulta conveniente utilizar técnicas PLSQL de procesamiento masivo (lo que en inglés se denomina BULK COLLECT).

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL:

La cláusula PIPELINED en las funciones PL/SQL y la excepción NO_DATA_NEEDED

En esta entrada explicaré para que sirve la cláusula PIPELINED en las funciones PL/SQL, y como se debe utilizar la excepción NO_DATA_NEEDED, que nada tiene que ver con la excepción NO_DATA_FOUND, para controlar las funciones PLSQL que incluyen dicha cláusula y que en inglés se denominan pipelined functions. Primero quiero remarcar que la funcionalidad pipelined fue introducida por primera vez en la versión 9i de las bases de datos Oracle. Básicamente, el uso de la cláusula PIPELINED resulta de gran utilidad y es prácticamente imprescindible cuando necesitamos que en lugar de una tabla sea una rutina PL/SQL la que nos sirva como fuente de datos.

La mejor forma de explicar el funcionamiento de esta cláusula es con un ejemplo.

¿Qué es mejor para el rendimiento, utilizar consultas PLSQL con subqueries o con joins?

Alguna vez me han llegado cuestiones en la que se me preguntaba qué es mejor, en términos de rendimiento de las bases de datos Oracle, si utilizar en las consultas PLSQL subqueries (subconsultas) o utilizar joins (es decir, listar todas las tablas en la clausula FROM y unirlas en el WHERE). Lo primero que hay que tener claro es que escribir una consulta PL/SQL utilizando subqueries o utilizando joins es semánticamente diferente; además, utilizar una u otra forma puede derivar en que ambas consultas devuelvan resultados diferentes y que no sean directamente intercambiables.

Lo que yo recomiendo para elegir entre un tipo de consulta u otro es, en general, hacer lo siguiente:

• Utilizar una subquery o subconsulta cuando no se necesita ninguna columna de la tabla que es referenciada en la subquery.
• Utilizar un join en caso de necesitar alguna de las columnas.

SQL y PL/SQL - La sentencia MERGE

La sentencia MERGE, a la que muchos denominan UPSERT debido a su funcionalidad, está disponible desde la aparición de la versión 9i de la base de datos Oracle. Se trata de una de las funcionalidades del kernel de Oracle más utiles a la hora de permitir el uso de la tecnología ETL (Extract, Transform and Load - Extraer, Transformar y Cargar) con las bases de datos Oracle. Este tipo de tecnología puede utilizarse y está especialmente enfocada para ser usada en aplicaciones de data warehousing (almacen de datos). Básicamente, lo que permite la sentencia SQL MERGE es, dependiendo de una condición lógica, actualizar registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple, de ahí surge la denominación de UPSERT.

Anteriormente a la versión 9i de Oracle, la alternativa en SQL era ejecutar dos sentencias DML, un UPDATE y un INSERT, cada una utilizando condiciones lógicas opuestas. En cuanto a las alternativas en PL/SQL eran: bien intentar insertar un registro y si la sentencia INSERT fallaba debido a una excepción PLSQL del tipo DUP_VAL_ON_INDEX, entonces realizar un UPDATE del registro en cuestión; bien intentar actualizar un registro y si la sentencia UPDATE devolvía SQL%NOTFOUND, entonces ejecutar la correspondiente sentencia INSERT.

Hints avanzados en PL/SQL para forzar la forma de acceder a una tabla

Ya he hablado anteriormente acerca de los hints PLSQL más comunes (FULL, ROWID, INDEX, NO_INDEX) para forzar el método de accesso a una tabla Oracle. En este artículo voy a presentar algunos hints más de este tipo, que se utilizan menos frecuentemente, pero no por ello menos útiles.

CLUSTER (nombre_de_tabla): Fuerza el accesso a la tabla indicada utilizando un índice de tipo cluster. Los índices de este tipo se utilizan para localizar registros que comparten valores comunes de una forma rápida. La clave del índice cluster puede estar constituida por una o más columnas de la tabla. Los registros de la tabla son agrupados según la clave y almacenados físicamente juntos en el disco duro.

Identificación de tablas y columnas en una base de datos Oracle mediante sentencias SQL

Una vez que una tabla Oracle es creada utilizando el comando CREATE TABLE, la estructura de dicha tabla se puede visualizar utilizando las siguientes vistas (views) del sistema de la base de datos Oracle:

DBA_TABLES: Muestra la información de la tabla a nivel de cabecera.

DBA_TAB_COLUMNS: Muestra la información de la tabla a nivel de columna.

DBA_TAB_PRIVS: Muestra los privilegios de acceso a las tablas de los usuarios de la base de datos.

DBA_COL_PRIVS: Muestra los privilegios de acceso a nivel de columna. Es bastante raro tener la necesidad de definir permisos a nivel de columna, de hecho, yo nunca me he encontrado con una bases de datos Oracle que utilice esta funcionalidad.