Oracle 23c - Instalación más Sencilla de Esquemas de Ejemplo

Oracle provee desde hace décadas un conjunto de esquemas con datos para poder utilizarlos de ejemplo, tal como se detalla a continuación:

• HR: Human Resources.
• CO: Customer Orders.
• SH: Sales History.
• OE: Order Entry (Obsoleto - No se actualiza más).
• PM: Product Media (Obsoleto - No se actualiza más).

La instalación de los mismos solía ser una tarea "tediosa" ya que los scripts para instalarlos debían ser ejecutados por un usuario con permisos de DBA y no permitia elegir un esquema, siendo obligatorio instalarlo en el esquema provisto.

Instalación en Oracle 23c (en realidad Oracle 19c en adelante)

Oracle Database 23c incluye un nuevo conjunto de scripts para instalar los esquemas de ejemplo, que pueden ser instalados en cualquier base de datos Oracle 19c o posterior, y que no requieren de permisos de DBA ni la utilización de SQL*Loader.

Los mismos se encuentran disponibles en el repositorio de Oracle Samples en GitHub.

Descargando el archivo

Podemos descargar el archivo que contiene los esquemas usando el siguiente comando en Linux:

wget https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.1.zip

Luego de descargado, debemos descompactar el mismo usando unzip:

unzip v23.1.zip

Este paso va a crear una carpeta "db-samples-schemas-23.1" que contiene los scripts necesarios para instalar los cinco esquemas mencionados anteriormente.

Instalando el esquema HR

En este artículo vamos a usar el esquema HR como ejemplo, pero lo vamos a instalar en otro usuario ya existente de la base de datos, en este caso llamado RRHH, a diferencia de los scripts anteriores que no permitian usar otro usuario y debían ser ejecutados por un usuario DBA.

Debemos asegurarnos que el usuario deseado (en este caso RRHH) tenga permisos para crear objetos (en Oracle 23c se puede hacer fácilmente, como contamos en el artículo "Oracle 23c - Nuevo Rol DB_DEVELOPER_ROLE") y que tenga cuota sobre algun tablespace para poder crear los objetos.

En el directorio "human_resources" ubicado dentro de "db-samples-schemas-23.1" vamos a encontrar los siguientes archivos:

• hr_create.sql
• hr_populate.sql
• hr_code.sql
• hr_install.sql
• hr_uninstall.sql
• README.md
• README.txt

Lo que debemos hacer a continuación es conectarnos a la base de datos con el usuario RRHH y ejecutar el siguiente script:

@hr_create

Este script crea los objetos de datos (tablas, vistas, índices) del esquema HR en el usuario en el que nos encontramos conectado.

A continuación, vamos a llenar las tablas creadas en el paso anterior mediante el siguiente script:

@hr_populate

El paso final es crear los triggers y procedimientos del esquema, en este caso con el siguiente script:

@hr_code

Y podemos usar el siguiente script para confirmar que todos los objetos del esquema HR han sido creados en el esquema destino:

col user format a20
col object_name format a30
col object_type format a20
SELECT user, object_name, object_type FROM user_objects;

Obteniendo el siguiente resultado:

USER                 OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ --------------------
RRHH                 DEPARTMENTS_SEQ                SEQUENCE
RRHH                 JOBS                           TABLE
RRHH                 JOB_ID_PK                      INDEX
RRHH                 EMPLOYEES                      TABLE
RRHH                 EMP_EMAIL_UK                   INDEX
RRHH                 EMP_EMP_ID_PK                  INDEX
RRHH                 EMPLOYEES_SEQ                  SEQUENCE
RRHH                 JOB_HISTORY                    TABLE
RRHH                 JHIST_EMP_ID_ST_DATE_PK        INDEX
RRHH                 EMP_DETAILS_VIEW               VIEW
RRHH                 EMP_DEPARTMENT_IX              INDEX
RRHH                 EMP_JOB_IX                     INDEX
RRHH                 EMP_MANAGER_IX                 INDEX
RRHH                 EMP_NAME_IX                    INDEX
RRHH                 DEPT_LOCATION_IX               INDEX
RRHH                 JHIST_JOB_IX                   INDEX
RRHH                 JHIST_EMPLOYEE_IX              INDEX
RRHH                 JHIST_DEPARTMENT_IX            INDEX
RRHH                 LOC_CITY_IX                    INDEX
RRHH                 LOC_STATE_PROVINCE_IX          INDEX
RRHH                 LOC_COUNTRY_IX                 INDEX
RRHH                 SECURE_DML                     PROCEDURE
RRHH                 SECURE_EMPLOYEES               TRIGGER
RRHH                 ADD_JOB_HISTORY                PROCEDURE
RRHH                 UPDATE_JOB_HISTORY             TRIGGER
RRHH                 REG_ID_PK                      INDEX
RRHH                 REGIONS                        TABLE
RRHH                 COUNTRIES                      TABLE
RRHH                 COUNTRY_C_ID_PK                INDEX
RRHH                 LOCATIONS                      TABLE
RRHH                 LOC_ID_PK                      INDEX
RRHH                 LOCATIONS_SEQ                  SEQUENCE
RRHH                 DEPARTMENTS                    TABLE
RRHH                 DEPT_ID_PK                     INDEX

34 rows selected

Podemos consultar alguna de las tablas:

Scripts Adicionales

El script "hr_install.sql" puede utilizarse, conectado con un usuario con permisos de DBA, para hacer la instalación completa del esquema HR, incluyendo la creación de dicho usuario.

El script "hr_uninstall.sql" puede utilizarse, conectado con un usuario con permisos de DBA, para hacer la desinstalación completa del esquema HR, incluyendo la eliminación de dicho usuario.

Nota: Los pasos de instalación, desinstalación o creación manual son similares para cualquiera de los esquemas de ejemplo provistos, sólo se deben usar los scripts similares disponibles en los otros directorios.

Conclusión

La instalación de los esquemas de ejemplo de Oracle 23c ha sido optimizada para que se puedan utilizar por cualquier usuario sin necesidad de permisos de DBA.

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.

Oracle 23c - Mejoras a cláusula RETURNING

En esta versión de Oracle Database se introduce una pequeña pero interesante mejora a la clausula RETURNING que si bien es válida para cualquier sentencia DML (INSERT, UPDATE o DELETE) es realmente útil para UPDATE.

Como funcionaba RETURNING hasta Oracle 23c

La clausula RETURNING permitía obtener los valores involucrados en una sentencia DML, devolviendo el valor insertado en el caso de INSERT (ya que no hay un valor anterior), el valor existente o "anterior" en el caso de DELETE (ya que una vez borrado el registro no hay mas valores disponibles) y en el caso de UPDATE el nuevo valor asignado a la columna.

Ejemplo

Para ver el comportamiento, vamos a usar el código siguiente para crear una tabla de pruebas, con un registro en la misma:

CREATE TABLE PruebaReturning (
  ID NUMBER CONSTRAINT PK_PruebaReturning PRIMARY KEY,
  Valor VARCHAR2(50)
);
--
INSERT INTO PruebaReturning VALUES (1, 'Valor Inicial');
--
COMMIT;
--

Luego de crear y popular la tabla, ejecutaremos el siguiente código que usa RETURNING en un UPDATE para obtener el valor actualizado. Noten que el comportamiento por defecto de la clausula solo requiere identificar la columna para la cual se desea obtener el valor, y donde lo vamos a "almacenar":

DECLARE 
    vValorFinal   VARCHAR2(50) := '-';
BEGIN
    --
    UPDATE PruebaReturning
    SET Valor = REPLACE(Valor,'Inicial','Actualizado')
    WHERE ID = 1
    RETURNING Valor INTO vValorFinal;
    --
    dbms_output.put_line('vValorFinal = ' || vValorFinal);
END;
/
ROLLBACK;

Al ejecutar este código obtenemos el siguiente resultado:

El problema es que no podemos obtener el valor anterior que poseía la columna, lo cual puede ser algo interesante de tener en el caso de UPDATE.

Como funciona RETURNING desde Oracle 23c

Desde Oracle 23c la clausula RETURNING permite especificar la opción OLD o  NEW antes del nombre de la columna, lo cual permite "elegir" uno de ellos, o proporcionar ambos de ser requerido.

Sintaxis

La nueva sintaxis de la cláusula es la siguiente:

RETURNING [OLD | NEW] <Expresion> INTO <Variable>

Nota: Si bien es posible usar esta nueva opción en sentencias INSERT o DELETE, en el caso de estar insertando un nuevo registro el valor anterior (OLD) sera siempre NULL,  y en el caso de estar borrando el valor nuevo (NEW) será también siempre NULL, por lo que la misma no es útil aunque es permitida.

Ejemplo

A continuación, vamos a probar con un bloque PL/SQL similar al anterior, pero esta vez usaremos OLD y NEW obteniendo tanto el valor anterior como el nuevo:

DECLARE 
    vValorInicial VARCHAR2(50) := '-';
    vValorFinal   VARCHAR2(50) := '-';
BEGIN
    --
    UPDATE PruebaReturning
    SET Valor = REPLACE(Valor,'Inicial','Actualizado')
    WHERE ID = 1
    RETURNING OLD Valor, NEW Valor INTO vValorInicial, vValorFinal;
    --
    dbms_output.put_line('vValorInicial = ' || vValorInicial);
    dbms_output.put_line('vValorFinal = ' || vValorFinal);
    --
END;
/

Al consultar la salida de DBMS_OUTPUT podemos ver que pudimos capturar tanto el valor anterior como el nuevo valor al hacer la actualización de la fila:

 

Conclusión

Este cambio en Oracle Database 23c permite una mayor flexibilidad al momento de capturar las versiones anteriores o actuales de los datos que son alcanzados por una sentencia UPDATE.

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.

Oracle 23c - Más Columnas en Tablas con el parámetro MAX_COLUMNS

Si bien el límite de 1000 columnas existente hasta Oracle 23c al momento de crear tablas (o vistas) parece ser algo inalcanzable, existen diversos escenarios que pueden requerir una cantidad de columnas mayor a 1000 para una tabla. Oracle Database 23c permite extender el número de columnas por tabla hasta 4096 configurando el parámetro MAX_COLUMNS.

Parámetro MAX_COLUMNS

Este parámetro introducido en Oracle 23c tiene un valor por defecto de "STANDARD", lo cual significa que el límite sigue siendo 1000 columnas:

Al configurar el mismo con el valor EXTENDED en la PDB que lo requiera, le indicamos a la base de datos que puede crear objetos (tablas y vistas) con hasta 4096 columnas:

ALTER SYSTEM SET max_columns=EXTENDED SCOPE=spfile;

Nota: Este parámetro requiere que la base de datos sea reiniciada para tomar efecto, no puede modificarse en forma dinámica.

Revirtiendo el valor a STANDARD

Para poder volver a configurar el valor en STANDARD, debemos asegurarnos que ningún objeto posee mas de 1000 columnas, o recibiremos un error como el siguiente:

ALTER SYSTEM SET max_columns=STANDARD SCOPE=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-60471: max_columns can not be set to STANDARD as there are one or more objects with more than 1000 columns

Conclusión

Modelos de datos complejos que pueden requerir tablas con mas de 1000 columnas pueden ser creados mediante la configuración del parámetro MAX_COLUMNS al valor EXTENDED.

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.

Oracle 23c - Información Detallada de Tablas y Columnas con Annotations

ANNOTATIONS es una funcionalidad de Oracle Database 23c que complementa la opción COMMENTS disponible para muchos objetos (como ser tablas y columnas), permitiendo hace runa descripción mas detallada de los mismos.

A diferencia de COMMENTS que permite ingresar un único texto libre, ANNOTATIONS permite asociar valores o pares de valores (clave + valor) definidos libremente, con el objetivo de poder describir y clasificar los objetos.

Otra diferencia clave con COMMENTS es que ANNOTATIONS funciona en forma aditiva (se pueden agregar nuevas claves o pares clave valor) mientras que no es posible tener mas de un comentario por cada objeto o columna.

Sintaxis

La sintaxis es muy sencilla:

ANNOTATIONS ( {ADD|DROP} Nombre_Anotacion {'Valor_Anotacion'} )

La misma esta compuesta de un nombre de anotación y opcionalmente un valor (el cual es un literal de tipo texto). La palabra clave ADD no es requerida cuando se usa la sintaxis para crear los objetos o la anotación.

Ejemplos

Al crear una tabla podemos incluir valores que nos ayudan a comprender el uso de cada una de las columnas, como mostramos a continuación:

CREATE TABLE tComprobantes (
    ID_Comprobante NUMBER(10) NOT NULL
        ANNOTATIONS ( ClavePrimaria, NombreVisible 'Identificador Comprobante', TipoColumna 'Interno'),
    ID_TipoComprobante NUMBER(5) NOT NULL
        ANNOTATIONS ( Referencia, NombreVisible 'Identificador Tipo Comprobante', TipoColumna 'Interno'),
    Nro_Comprobante NUMBER(8) NOT NULL
        ANNOTATIONS ( NombreVisible 'Número de Comprobante', TipoColumna 'Visible'),
    Fecha_Comprobante DATE NOT NULL
        ANNOTATIONS ( NombreVisible 'Fecha de Comprobante', TipoColumna 'Visible'),
    Importe_Comprobante NUMBER (12,2) NOT NULL
        ANNOTATIONS ( NombreVisible 'Importe Comprobante', TipoColumna 'Visible'),
    Fecha_Alta DATE NOT NULL
        ANNOTATIONS ( NombreVisible 'Fecha Alta', TipoColumna 'Auditoria'),
    Fecha_Modificacion DATE 
        ANNOTATIONS ( NombreVisible 'Fecha Modificación', TipoColumna 'Auditoria'),
    Usuario_Alta VARCHAR2(50) NOT NULL
        ANNOTATIONS ( NombreVisible 'Usuario Alta', TipoColumna 'Auditoria'),
    Usuario_Modificacion VARCHAR2(50) 
        ANNOTATIONS ( NombreVisible 'Usuario Modificación', TipoColumna 'Auditoria')
    ) ANNOTATIONS (NombreVisible 'Comprobantes', TipoTabla 'Transaccional');

En el ejemplo podemos ver lo siguiente:

• La columna "ID_Comprobante" tiene una anotación ClavePrimaria que indica que la misma es la clave primaria de la tabla. Esta anotación no posee asociado un valor.
• La columna "ID_TipoComprobante" tiene una anotación  Referencia que indica que es una referencia o clave foránea a otra tabla. Esta anotación no posee asociado un valor.
• Tanto la tabla como todas las columnas tienen una anotación NombreVisible el cual tiene asociado en cada caso un valor que representa como debería etiquetarse la columna o tabla al ser mostrada por las aplicaciones que la usen.
• Todas las columnas tienen una anotación TipoColumna el cual tiene asociado un valor que indica si la columna es de tipo Interno (ID que no son mostrados), de tipo Visible (datos que usualmente se incluyen en la UI) o de tipo Auditoria (información de auditoria para controlar los cambios realizados a cada registro).
• La tabla posee también una anotación TipoTabla que identifica a la tabla como una tabla que contiene transacciones (podríamos tener otros tipos como ser "Datos Maestros", datos de "Auditoria", etc.).

Si queremos eliminar una anotación, solo debemos usar la palabra DROP en la clausula de Annotations, como vemos a continuación:

Objetos que Soportan ANNOTATIONS

Actualmente, los siguientes objetos de una base de datos Oracle soportan ANNOTATIONS:

• Tablas y sus columnas.
• Vistas y sus columnas.
• Vistas Materializadas y sus columnas.
• Indices.
• Dominios y columnas de Dominios multi columna.

¿Cómo Usar la Información de ANNOTATIONS?

Una vez que creamos nuestros objetos con anotaciones, podemos consultar las mismas usando dos vistas del diccionario de datos llamadas USER_ANNOTATIONS y USER_ANNOTATIONS_USAGE .

Suponiendo que tenemos que informar al equipo de desarrollo los nombres a utilizar al momento de visualizar las columnas de la tabla creada anteriormente, podemos obtener los mismos con la siguiente consulta:

SELECT object_name, object_type, column_name, annotation_name, annotation_value
FROM user_annotations_usage
WHERE Annotation_name = 'NOMBREVISIBLE';

La cual nos devuelve la siguiente información:

Lo mismo si queremos ver un detalle de alguna columna en particular, podemos probar con:

SELECT object_name, object_type, column_name, annotation_name, annotation_value
FROM user_annotations_usage
WHERE OBJECT_NAME = 'TCOMPROBANTES' 
AND COLUMN_NAME = 'ID_COMPROBANTE';

Lo cual nos mostrará las anotaciones asociadas a esa columna en particular:

Conclusión

Ahora es posible realizar documentar mas fácilmente los objetos de nuestras bases de datos mediante el uso de ANNOTATIONS, soportando multiples valores y combinaciones de Clave + Valor para describir correctamente los mismos.

La sección "Application Usage Annotations" de la documentación oficial contiene una descripción detallada de todo lo mencionado anteriormente, por si desean ver mas detalles.

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.

Oracle 23c - SUM y AVG con tipo de datos INTERVAL

Otra mejora que ofrece Oracle Database 23c es la que nos permite realizar agregaciones de tipo SUM (suma) o AVG (promedio) con datos del tipo INTERVAL.

Esto nos resulta muy útil, por ejemplo, cuando el dato de tipo INTERVAL representa tiempo transcurrido o necesario para diversas tareas, y queremos saber cuanto es el total de tiempo necesario para completar todo el proceso, o cual es la duración promedio de las tareas que lo componen.

Escenario

Vamos a construir una tabla de Tareas, la cual contiene un ID y Nombre de Tarea, un Tipo de Objeto, una fecha de inicio y fin de la tarea, y la duración de la misma expresada con el tipo de datos INTERVAL. Para ello vamos a usar como origen la vista ALL_OBJECTS del diccionario de datos, como mostramos a continuación:

CREATE TABLE tTareas AS
SELECT ao.Object_ID AS ID_Tarea, 'Crear ' || ao.Object_name AS Tarea, ao.object_type AS TipoObjeto,
    ao.Created AS Inicio, ao.Last_DDL_Time AS Fin, 
    CAST(Last_DDL_Time AS TIMESTAMP) - CAST(Created AS TIMESTAMP) AS Duracion
FROM all_objects ao
WHERE ao.Created <> ao.Last_DDL_Time
ORDER BY 1
FETCH FIRST 100 ROWS ONLY;

Si consultamos algunos datos de la misma, veremos que la columna Duracion es de tipo de datos INTERVAL: 

SELECT * FROM tTareas
FETCH FIRST 10 ROWS ONLY;

SUM con INTERVAL

A continuación, vamos a mostrar como se puede utilizar la función SUM para ver el total de tiempo insumido en las tareas, agrupando en este caso las mismas por tipo de objeto:

SELECT TipoObjeto, SUM(Duracion) AS DuracionTotal, COUNT(*) AS NroObjetos
FROM tTareas
GROUP BY TipoObjeto;

Y podemos ver como obtenemos los resultados deseados:

AVG con INTERVAL

Así como obtuvimos el tiempo total insumido por cada tipo de objeto, podemos aplicar la función AVG para obtener el promedio:

SELECT TipoObjeto, AVG(Duracion) AS TiempoPromedio, COUNT(*) AS NroObjetos
FROM tTareas
GROUP BY TipoObjeto;

Podemos ver los resultados a continuación, y en el caso de "EDITION", al ser solo un objeto, el tiempo promedio es igual a la duración total que calculamos en el ejemplo anterior:

Funciones Analíticas

Así como usamos SUM y AVG como funciones de agregación, es posible usarlas como funciones analíticas con datos de tipo INTERVAL, como vemos a continuación:

SELECT Tarea, Duracion, SUM(Duracion) OVER(ORDER BY ID_Tarea) AS Acumulado
FROM tTareas
WHERE TipoObjeto = 'TABLE';

Lo que vemos es la duración acumulada, tarea tras tarea (conocido como RUNNING TOTAL), en vez de el resultado "total" final:

Conclusión

Ahora es posible realizar operaciones de agregación como SUM y AVG en datos de tipo INTERVAL, para poder obtener tanto sumatorias de tiempo transcurrido como su promedio.

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.

 

Oracle 23c - Tipo de Datos BOOLEAN en SQL

Si bien Oracle posee el tipo de datos Boolean en su lenguaje PL/SQL, el mismo no podía ser utilizado para definir columnas en la base de datos, ni en sentencias SQL.

BOOLEAN en Columnas

El nuevo tipo de datos puede utilizarse en columnas de tablas y vistas en Oracle 23c, como podemos ver a continuación:

CREATE TABLE IF NOT EXISTS tClientes (ID_Cliente NUMBER(10), Nombre_Cliente VARCHAR2(100), Activo BOOLEAN);

Nota: En el ejemplo usamos la nueva sintaxis de Oracle 23c "IF NOT EXITS" explicada en el artículo "Oracle 23c - IF [NOT] EXISTS en Sentencias DDL" de este blog.

Este nuevo tipo de datos acepta múltiples valores de entrada que son automáticamente convertidos a TRUE o FALSE, como podemos ver en la siguiente tabla:

Valor Boolean	Valores Aceptados de Entrada
TRUE	TRUE, true, 'TRUE', 'true', 'yes', 'YES', 'on', 'ON', 't', 'T', 'y', 'Y', '1', 1
FALSE	FALSE, false, 'FALSE', 'false', 'no', 'NO', 'off', 'OFF', 'f', 'F', 'n', 'N', '0', 0

Podemos confirmarlo mediante este script que nos permitirá insertar datos de prueba en la tabla que creamos:

INSERT INTO tClientes VALUES (1,'Cliente 1', TRUE);
INSERT INTO tClientes VALUES (2,'Cliente 2', FALSE);
INSERT INTO tClientes VALUES (3,'Cliente 3', 1);
INSERT INTO tClientes VALUES (4,'Cliente 5', '0');
INSERT INTO tClientes VALUES (5,'Cliente 5', 45);
INSERT INTO tClientes VALUES (6,'Cliente 6', 'f');
INSERT INTO tClientes VALUES (7,'Cliente 7', 'ON');
INSERT INTO tClientes VALUES (8,'Cliente 8', 'n');

Nota: Al usar valores numéricos, cualquier valor distinto de 0 será considerado como VERDADERO (TRUE) y el valor 0 como FALSO (FALSE).

Restricciones

Podemos crear restricciones de diversos tipos sobre columnas BOOLEAN, como ser:

• Clave Primaria (Primary Key)
• Clave Foránea (Foreign Key)
• Clave Única (Unique Key)
• Check Constraint 
• NOT NULL

Valor por Defecto

Se puede definir un valor por defecto para las columnas de tipo BOOLEAN:

CREATE TABLE tBoolean (ID NUMBER(10), Activo BOOL DEFAULT TRUE);

Nota: Se puede abreviar el nombre de tipo de datos a BOOL en vez de BOOLEAN.

Uso en SQL

El tipo de datos BOOLEAN puede usarse en sentencias SQL como cualquier otro tipo de datos de Oracle. En el siguiente ejemplo, vamos a mostrar los registros de la tabla creada anteriormente que posean la columna Activo con un valor de TRUE usando SQL Developer 23.1:

SELECT * 
FROM tClientes c
WHERE c.Activo = TRUE;

Obteniendo el siguiente resultado:

Podemos ver que la consulta devuelve solo 4 registros (lo cual es correcto ya que el valor 45 utilizado al insertar el cliente #5 es considerado TRUE) pero la columna Activo muestra como valor 1 y no TRUE. Esto se debe a que el cliente JDBC incluido en SQL Developer todavía no realiza la conversión correcta, pero si probamos la misma sentencia en SQL*Plus incluido con Oracle Database 23c, el resultado mostrado es el correcto:

Cambios en Funciones de Conversión

Las funciones TO_NUMBER, TO_CHAR y TO_NCHAR fueron modificadas para aceptar valores booleanos como entrada, como podemos ver a continuación:

SELECT c.ID_Cliente, TO_CHAR(Activo) AS ValChar, TO_NUMBER(Activo) AS ValNum
FROM tClientes c
WHERE c.ID_Cliente < 4;

Obteniendo el siguiente resultado:

Y también se incorpora una nueva función TO_BOOLEAN para convertir valores numéricos o de texto en booleanos:

SELECT TO_BOOLEAN(2), TO_BOOLEAN('t'), TO_BOOLEAN('false');

Podemos ver (en SQL*Plus) que devuelve TRUE o FALSE según el caso (en SQL Developer por el momento devuelve 1 o 0 como mencionamos anteriormente):

Nota: En este ejemplo usamos la nueva funcionalidad de sentencias SELECT sin clausula FROM, la cual ya comentamos en el artículo "Oracle 23c - Sentencias SELECT sin cláusula FROM".

Conclusión

Oracle Database 23c incorpora el tipo de datos BOOLEAN (ya existente en PL/SQL) en su lenguaje SQL permitiendo definir columnas con este tipo de datos y consultando o filtrando las mismas en sentencias SQL, como se documenta en la sección Data Types de la documentación de Oracle Database 23c.

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.

VirtualBox 7.0.8 ya está disponible

VirtualBox 7.0.8 es una versión de mantenimiento (no incorpora nuevas funcionalidades) incluyendo el Critical Patch Update de Abril de 2023 que se puede descargar en forma gratuita desde el sitio web de VirtualBox, y además podemos consultar el historial de cambios y la documentación en los siguientes links:

• Cambios de la version 7.0.8.
• Documentación Oficial.

Las correcciones mas importantes incluidas en esta version son:

• VMM: Mejoras generales en el área de visualización anidada
• GUI: los asistentes relacionados con Oracle Cloud ahora deben proponer perfiles habilitados antes de deshabilitarlos
• Oracle VM VirtualBox Extension Pack: se corrigió el envío del módulo de soporte criptográfico para el cifrado completo de VM
• 3D: se corrigieron varios problemas de gráficos con Guests de Microsoft Windows 7.
• Main/UefiVariableStore: API agregada para agregar firmas a la lista MOK
• Adiciones de Guest de Linux y controladores de host: presente soporte inicial para kernel 6.3
• Adiciones de Guest de Linux: se solucionó el problema de compilación de vboxvideo con los kernels RHEL 8.7, 9.1 y 9.2

Oracle 23c - Joins en DELETE y UPDATE

Otra mejora importante en Oracle Database 23c es la  posibilidad de escribir sentencias DELETE o UPDATE utilicen JOIN entre la tabla a actualizar  o borrar y otras tablas de la base de datos.

Para probar la nueva funcionalidad, vamos a comenzar creando una tabla con 100 filas provenientes de la vista de diccionario de datos "ALL_OBJECTS":

CREATE TABLE MisObjetos AS
SELECT OBJECT_ID, OWNER, OBJECT_NAME, CAST('Desconocido' AS VARCHAR2(128)) AS OBJECT_TYPE
FROM ALL_OBJECTS
ORDER BY 1
FETCH FIRST 100 ROWS ONLY;

Si analizamos el script utilizado, vemos que creamos la columna la columna OBJECT_TYPE llenándola con un valor fijo que no es el existente en la vista original, lo cual podemos comprobar rápidamente con esta sentencia:

SELECT DISTINCT mo.object_type FROM MisObjetos mo;

Como podemos ver, la columna no tiene los valores provenientes de la vista original:

Usando JOIN en UPDATE

Vamos a "corregir" el error cometido al crear la columna OBJECT_TYPE de la tabla, usando una sentencia UPDATE que haga un JOIN contra la vista ALL_OBJECTS:

UPDATE MisObjetos mo
SET mo.object_type = ao.object_type
FROM ALL_OBJECTS ao
WHERE mo.OBJECT_ID = ao.OBJECT_ID;

Podemos ver que la sintaxis implica usar la tabla que debe ser actualizada en la cláusula UPDATE, y se agrega la cláusula FROM con la(s) tabla(s) que necesitamos relacionar con la tabla destino, especificando en la cláusula WHERE la relación entre ellas.

El resultado de ejecutar la sentencia es el siguiente:

100 filas actualizadas.

Y podemos validar que ahora las filas en nuestra tabla tienen la columna actualizada con el valor correcto:

SELECT mo.object_type, COUNT(*)
FROM MisObjetos mo
GROUP BY mo.object_type;

Usando JOIN en DELETE

Ahora vamos a eliminar de nuestra tabla las filas donde el tipo de objeto sea "TABLE", utilizando la siguiente sentencia:

DELETE MisObjetos mo
FROM ALL_OBJECTS ao
WHERE mo.OBJECT_ID = ao.OBJECT_ID
AND ao.object_type = 'TABLE';

la cual devuelve el siguiente mensaje:

8 filas eliminado

Nota: Usamos la columna OBJECT_ID de la vista del diccionario de datos en el WHERE sólo para demostrar la funcionalidad, ya que podríamos haber filtrado por la misma columna en nuestra tabla sin necesidad de establecer el JOIN.

Si validamos de nuevo el contenido de nuestra tabla, podemos ver que ya no tenemos filas con el valor "TABLE"

SELECT mo.object_type, COUNT(*)
FROM MisObjetos mo
GROUP BY mo.object_type;

Sintaxis ANSI

Esta nueva funcionalidad no emplea la sintaxis ANSI para establecer la relación entre la tabla a ser modificada y otra tabla (o vista) de nuestra base de datos, pero sí puede usarse en el resto de la cláusula FROM para relacionar con otras tablas, como se muestra en este ejemplo:

UPDATE MisObjetos mo
SET mo.object_type = ao.object_type
FROM ALL_OBJECTS ao
JOIN ALL_USERS au ON au.USERNAME = ao.OWNER
WHERE mo.OBJECT_ID =ao.OBJECT_ID;

En el ejemplo, usamos el WHERE para relacionar MisObjetos con ALL_OBJECTS, y la sintaxis ANSI para relacionar ALL_USERS con ALL_OBJECTS.

Conclusión

Oracle Database 23c permite escribir sentencias UPDATE y DELETE que utilizan JOIN entre tablas (pero sin usar la sintaxis ANSI) usando la cláusula WHERE para establecer la relación entre la tabla a ser actualizada o borrada y la tabla que deseamos unir.

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.

Que hay de nuevo en APEX 23.1?

Luego del anunció de la disponibilidad de APEX 23.1 en el sitio apex.oracle.com (el cual hicimos en el artículo "Oracle APEX 23.1 (versión de pre-producción) disponible en apex.oracle.com") ahora vamos a analizar las nuevas características y mejoras que ofrece APEX 23.1.

Por el momento, APEX 23.1 está solo disponible en el sitio apex.oracle.com, y anunciaremos cuando este disponible tanto para ser descargado o disponible como APEX Service o componente de ADB en Oracle Cloud.

Nuevas Funcionalidades

Componentes de Plantillas

Este nuevo tipo de complemento permite crear componentes de UI reutilizables, los cuales soportan acciones, menus y atributos personalizados.

• Completamente declarativo: Los componentes de plantilla le permiten crear complementos con atributos que se pueden establecer de forma declarativa en Page Designer. Los atributos definidos en el complemento estarán disponibles en Page Designer para cualquier columna o región que use el tipo de complemento.
• Acciones y botones de menú: Con la introducción de posiciones de acción y plantillas de acción para complementos, puede definir acciones y botones de menú con condiciones de nivel de fila para componentes de plantilla.
• Componentes de temas universales: Se agregaron seis componentes de plantilla listos para usar al tema universal y están disponibles como tipos de región en Page Designer. Estas nuevas regiones recuerdan la paginación, funcionan con búsqueda por facetas y filtros inteligentes, y admiten el elemento Ordenar por externo.

Notificaciones Push de PWA

Esta nueva funcionalidad permite enviar notificaciones en forma sencilla a los usuarios que se suscribieron a las mismas.

Es posible definir el titulo, cuerpo, ícono y enlaces para el mensaje enviado, pudiendo linkear a APEX o a cualquier otra pagina. La suscripción a notificaciones puede hacerse en forma sencilla desde las aplicaciones APEX, y los administradores pueden gestionar una cola de notificaciones.

Mejoras en APEX 23.1

Object Browser Modernizado

Se realizaron importantes mejoras en el navegador, como ser:

• Filtrado: es posible ver y filtrar objetos de base de datos de todo tipo desde un solo árbol.
• Edición: Una experiencia de edición mejorada facilita continuar editando el último objeto que estaba viendo y garantiza que los cambios no guardados nunca se pierdan sin querer con una nueva advertencia en la navegación.
• Performance mejorado: Cree objetos comunes más fácilmente desde una nueva página de destino y cambie rápidamente entre las múltiples pestañas de un objeto, por ejemplo, entre la especificación y el cuerpo de un paquete o las columnas y los datos de una tabla.
• Accesible : El Object Browser está codificado según las pautas de accesibilidad, lo que facilita el trabajo en objetos de la base de datos con tecnologías de asistencia, como lectores de pantalla.

Mejoras en el procesamiento de páginas

APEX 23.1 permite agrupar una secuencia de procesos de página para descargar el procesamiento en segundo plano o simplemente simplificar el mantenimiento de la ejecución condicional.

• Control de ejecución en segundo plano: Más control sobre los procesos que se ejecutan en segundo plano con la adición de monitoreo a la página Sesiones activas. Informe sobre la ejecución de ejecuciones en segundo plano cuando se elimina una página o aplicación, y permita que los desarrolladores continúen abortándolas.
• Cadenas de ejecución: Un nuevo tipo de proceso de página llamado cadenas de ejecución permite ejecutar procesos de página uno tras otro agregándolos como elementos secundarios de una cadena y ejecutar cadenas declarativamente en segundo plano o en primer plano.
• Informes de estado y progreso: Informe sobre el estado y el progreso de los procesos en segundo plano con una nueva API  (APEX_BACKGROUND_PROCESS)  y  una nueva vista (APEX_APPLICATION_PAGE_BG_PROC).

Mejoras a Fuentes de Datos REST

Las siguientes mejoras permiten un mayor control sobre fuentes de datos REST:

• Invocar API para fuentes REST: La API Invoke ahora admite orígenes de datos REST, proporcionando una invocación REST totalmente declarativa para procesos de página y simplifica la asignación de parámetros de origen REST a elementos de página, expresiones SQL, valores estáticos y más.
• Descubra fuentes REST con Swagger: APEX ahora es compatible con el descubrimiento de fuentes REST en función de las respuestas de Swagger/OpenAPI. Cree una fuente de datos REST completa basada en una respuesta, incluido un perfil de datos, así como metadatos adicionales como operaciones y URL u otros parámetros.
• Selectores Raw: Se ha agregado un nuevo elemento de cambio a los perfiles de origen de datos REST para seleccionar las filas tal como están. Cuando está habilitado, todos los selectores se usarán exactamente como se especifica y APEX no desinfectará los selectores de filas y columnas al generar la consulta SQL para el análisis JSON.

Mejoras a App Builder

Las actualizaciones de APEX Builder mejoran la experiencia del desarrollador, incluyendo:

• Copiar páginas desde Crear aplicación: Permite crear rápidamente copias de páginas desde la aplicación actual u otras aplicaciones directamente desde el asistente Crear página. El nuevo botón Crear página como copia disponible en el asistente Crear página lo envía directamente al asistente Copiar página, donde puede seguir los pasos familiares para tener una copia de una página en poco tiempo.
• Guardar y ejecutar desde el Editor de código: Ejecute páginas en Page Designer directamente desde un cuadro de diálogo de editor de código sin cerrar utilizando la nueva función Guardar y ejecutar páginas en los editores de código.
• Ayuda sensible al contexto: Los enlaces a los recursos de documentación en las aplicaciones de APEX Builder se han actualizado para incluir contexto y permitir que la documentación se redirija al libro o capítulo adecuado, lo que aumenta la calidad de todos los enlaces de documentación desde dentro de APEX Builder.
• URL amigables en Builder: APEX Builder se actualizó para usar URL amigables, allanando el camino para la habilitación futura de PWA en Builder.
• Actualizaciones de atributos de complementos: La infraestructura de complementos de elementos se ha ampliado para admitir 25 atributos, en lugar de 15. Tambien es posible definir y asignar grupos para atributos personalizados, lo que facilita agrupar atributos de complementos similares.
• Compatibilidad con gráficos de propiedades: Soporte nativo para gráficos de propiedades en la base de datos 23c. Cree componentes de página APEX directamente en un gráfico de propiedades mediante consultas PQL. Cree listas de valores compartidas, automatizaciones y configuraciones de búsqueda directamente en gráficos de propiedades mediante PQL.
• Integración web del desarrollador de SQL: Abra SQL Developer Web directamente desde el menú Builder. Compatible con ORDS versión 23.1 y debe configurarse a nivel de instancia. Para áreas de trabajo con 1 esquema de base de datos asociado que está habilitado para REST, SQL Developer Web se abrirá en una pestaña separada. De lo contrario, a los desarrolladores se les solicitará un cuadro de diálogo modal con una lista de los esquemas para conectarse o habilitar REST.
• Capturar ECID en el registro del servicio web: Configure servicios web externos pasando el Id. de contexto de ejecución para el seguimiento de un extremo a otro.
• Continuidad de la aplicación: Se agregó un nuevo atributo de seguridad de la aplicación, Confirmación de estado de sesión. Esto controla cómo se manejan los cambios en la sesión y los valores de los elementos, además de las confirmaciones que los acompañan. Estos se posponen hasta el final del procesamiento de solicitudes para aplicaciones nuevas, mientras que las aplicaciones antiguas tienen los cambios escritos y confirmados inmediatamente.

Mejoras a Administración de APEX

• Resumen de administración: Permite supervisar el estado de sus instancias, espacios de trabajo y esquemas con métricas diarias y gráficos de tendencias, disponibles diariamente en el nuevo Resumen del administrador de APEX.
• Restricciones de aprovisionamiento automático: Administrar la creación de espacios de trabajo en una instancia ahora es más fácil con una nueva interfaz de usuario y API que le permite definir y administrar una lista de bloqueo de patrones de correo electrónico. Consulta una lista de bloqueo al aprovisionar automáticamente un espacio de trabajo para restringir las direcciones de correo electrónico que coincidan con el patrón en la lista de bloqueo.
• Nueva API APEX_APPLICATION_ADMIN: Esta nueva API permite a los desarrolladores interactuar mediante programación con trabajos de administración.

Mejoras a Universal Theme y Experiencia de Usuario:

Universal Theme fue modernizado para incluir los Componentes de Plantilla y muchas otras mejoras:

• Nuevos componentes de plantilla: El tema universal ahora incluye componentes de plantilla para avatar, insignia, comentarios, fila de contenido, lista de medios y línea de tiempo. Muchos de estos componentes están destinados a reemplazar las plantillas de informes clásicos que proporcionaban patrones de diseño similares, pero ahora están disponibles como componentes de plantilla totalmente declarativos que son mucho más fáciles de configurar y usar.
• Fidelidad de iconos mejorada: Los íconos de aplicaciones generados para PWA se han actualizado para aplicar el enmascaramiento apropiado para que se vea apropiado en más plataformas y dispositivos.
• Mejoras en el selector de visualización de regiones: Los selectores de visualización de región ahora admiten la visualización de iconos de región junto a los títulos en las pestañas del selector de visualización de región y controlan la selección de pestañas en la carga de la página con nuevas opciones para recordar la última pestaña seleccionada. Los selectores de visualización de región también admiten APEX_REGION.RESET para restablecer la preferencia de usuario o sesión para la selección de pestañas.
• Exportar e Importar en Theme Roller: Descargue y cargue fácilmente temas directamente desde la interfaz de Theme Roller desde el nuevo botón de menú Acciones.
• Rendimiento de representación mejorado: Las regiones colapsables y del selector de visualización de regiones se pintan aún más rápido durante la carga de la página para reducir el parpadeo.
• Actualizaciones de plantillas: Las actualizaciones de las plantillas Hero y Title Bar proporcionan una nueva posición del botón Arriba. Las plantillas de región estándar e informe también se han actualizado para incluir una nueva opción de plantilla de alineación, lo que permite un control adicional sobre la alineación de los elementos Ordenar por.

Mejoras en el componente de aprobaciones

El componente de aprobaciones, que permite definir un flujo de tareas, incluye estsas mejoras:

• Elemento de fecha de vencimiento: Este elemento está disponible como una propiedad para el complemento de proceso Human Task - Create. Cuando se especifica, este valor anulará la configuración de la fecha de vencimiento para la definición de la tarea subyacente.
• API de creación de tareas actualizada: La API APEX_APPROVAL.CREATE_TASK ahora incluye un parámetro de fecha de vencimiento. Cuando se especifica, este valor anulará la configuración de la fecha de vencimiento para la definición de la tarea subyacente.

Actualizaciones y mejoras adicionales

Estos son cambios puntuales que ofrecen mejoras individuales a distintos componentes de APEX;

• Nuevo selector de color: El selector de color JET se reemplazó en APEX Theme Roller, Page Designer y IR/IG Highlights con un nuevo selector de color basado en componentes web. Este selector de color se puede crear dinámicamente es compatible con el color JET.
• Objetos habilitados para REST: La funcionalidad de habilitación de REST para los objetos de la base de datos se eliminó del Explorador de objetos APEX y se colocó en Servicios APEX RESTful. Los usuarios ahora pueden crear y modificar servicios RESTful desde la página Objetos RESTful habilitados.
• Mejoras de accesibilidad: Los atributos de texto sin formato se han actualizado para escapar correctamente de los atributos HTML y ARIA y evitar que el marcado HTML se exponga a los lectores de pantalla.
• TinyMCE adoptado: Se ha agregado la biblioteca de JavaScript TinyMCE y ahora es la base para el editor de texto enriquecido, ya que CKEditor ha quedado obsoleto.
• Actualizaciones de JavaScript: Varias bibliotecas de JavaScript fueron actualziadas a versiones más recientes, incluidas Oracle JET 14.0.0, FullCalendar 5.11.3, PrismJS 1.29.0, MarkedJS 4.2.5, DOMPurify 2.4.3, CKEditor5 36.0.0, Terser 5.16.1, CSSO 5.0. 5, Cropper.js 1.5.13 y MapLibre 2.4.0.