Oracle 23c - Mejoras Varias #2

En este artículo vamos a ver una segunda serie de mejoras de Oracle 23c que no evaluamos en otros artículos.

DEFAULT ON NULL para sentencias UPDATE

Oracle históricamente permite definir valores por defecto cuando una sentencia INSERT no incluye a una columna en la lista de valores a insertar, y desde Oracle 12c también se podía definir ese valor por defecto si la columna estaba incluida en la lista pero el valor insertado era NULL.

Oracle 23c amplia esta funcionalidad a las sentencias UPDATE, tal cual veremos en este artículo.

DEFAULT original

La sintaxis de la cláusula DEFAULT original es como se detalla a continuación:

CREATE TABLE tDEFAULT (ID NUMBER, Nombre VARCHAR2(50) DEFAULT 'Ingrese Nombre');

Nota: esta definición asigna el valor 'Ingrese Nombre' sólo si la sentencia INSERT no incluye a la columna Nombre. Por otro lado, la columna acepta nulos, por lo que se puede ejecutar una sentencia UPDATE que asigne el valor NULO a la columna Nombre.

DEFAULT ON NULL [FOR INSERT ONLY]

Oracle 12c introdujo la opción de definir la cláusula DEFAULT ON NULL (opcionalmente seguida de FOR INSERT ONLY) para indicar que el valor por defecto debe ser asignado ya sea si la columna no es incluida en la sentencia INSERT, o si el valor a ser insertado es NULL.

La sintaxis es la siguiente (en ambos casos el comportamiento es el mismo):

CREATE TABLE tDEFAULT_NULL (
    ID NUMBER, 
    Nombre VARCHAR2(50) DEFAULT ON NULL 'Ingrese Nombre',
    Comentario VARCHAR2(50) DEFAULT ON NULL FOR INSERT ONLY 'Ingrese Comentario');

Nota: al definir un DEFAULT con la opción ON NULL la columna se define en forma automática como NOT NULL (como "efecto secundario") por lo que no podemos actualizar la misma a NULL.

DEFAULT ON NULL FOR INSERT AND UPDATE

Esta es la sintaxis que incorpora Oracle 23c y permite que se le asigne el valor definido tanto al insertar un valor nulo como al momento de actualizar el mismo a NULL.

La sintaxis es la siguiente:

CREATE TABLE tDEFAULT_NULL_UPDATE (
    ID NUMBER, 
    Nombre VARCHAR2(50) DEFAULT ON NULL 'Ingrese Nombre',
    Comentario VARCHAR2(50) DEFAULT ON NULL FOR INSERT AND UPDATE 'Ingrese Comentario');

Nota: Definimos sólo la columna Comentario como DEFAULT ON NULL FOR INSERT AND UPDATE.

Ahora vamos a probar esta nueva funcionalidad con un par de ejemplos donde insertamos filas en nuestra tabla de diversas maneras:

INSERT INTO tDEFAULT_NULL_UPDATE (ID) VALUES (1);
INSERT INTO tDEFAULT_NULL_UPDATE VALUES (2, NULL, NULL);
INSERT INTO tDEFAULT_NULL_UPDATE VALUES (3, 'Nombre', 'Comentario');
--
SELECT * FROM tDEFAULT_NULL_UPDATE;

La sentencia SELECT del final del script nos devuelve la siguiente información:

Y ahora vamos a actualizar una de las columnas con NULL, para ver qué sucede:

UPDATE tDEFAULT_NULL_UPDATE SET Nombre = NULL WHERE ID = 2;

Error que empieza en la línea: 1 del comando -
UPDATE tDEFAULT_NULL_UPDATE SET Nombre = NULL WHERE ID = 2
Error en la línea de comandos : 1 Columna : 33
Informe de error -
Error SQL: ORA-01407: no se puede actualizar ("DEMO23C"."TDEFAULT_NULL_UPDATE"."NOMBRE") a un valor NULL
01407. 00000 -  "cannot update (%s) to NULL"

Como era de esperar, el UPDATE da error ya que la columna Nombre no acepta nulos. 

Pero si probamos de actualizar con NULL la columna Comentarios de la fila 3, veremos lo siguiente:

UPDATE tDEFAULT_NULL_UPDATE SET Comentario = NULL WHERE ID = 3;

1 fila actualizadas.

Y al consultar el contenido de la tabla, vemos que el UPDATE se ejecutó sin problemas pero en vez de asignar el valor NULL se asignó el valor especificado como valor por defecto:

Renombrado de Segmentos LOB

Cuando creamos una tabla con segmentos de tipo LOB (columnas CLOB o BLOB) y no especificamos un nombre de segmento, el mismo es generado por Oracle en forma automática, como vemos a continuación:

CREATE TABLE tSegmento (ID NUMBER(10), Datos CLOB);
--
SELECT table_name, column_name, segment_name FROM user_lobs;

Hasta Oracle 23c, la única forma de darle un nombre propio a un segmento de LOB ya creado era "moviendo" el mismo usando la siguiente sintaxis:

ALTER TABLE tSegmento MOVE LOB(Datos) STORE AS tSegmento_Datos ONLINE;

El problema es que esta operación efectivamente mueve los datos (en este ejemplo, al no especificar nada lo hace al mismo tablespace), con lo cual es altamente costosa.

En Oracle 23c se introduce una nueva opción RENAME en la sentencia ALTER TABLE que permite renombrar el segmento de LOB sin necesidad de mover el mismo, lo cual podemos probar a continuación:

ALTER TABLE tSegmento RENAME LOB(Datos) SYS_LOB0000109061C00002$$ TO LOB_tSegmento_Datos;

Al ser una operación que afecta solo al diccionario de datos, es altamente eficiente y no tiene ningún costo significativo. Podemos ver el resultado de la misma si volvemos a consultar la vista user_lobs:

SELECT table_name, column_name, segment_name FROM user_lobs;

Conclusión

En este artículo discutimos otras dos mejoras disponibles en Oracle 23c que facilitan la gestión de segmentos LOB y que también permiten definir valores por defectos en caso de actualizar datos, no solo al insertar.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.