Oracle 21c - Mejoras a Expresiones Calificadas

A partir de Oracle 21c se agregan tres nuevos tipos opciones de iterador para su uso en expresiones calificadas. El uso de expresiones calificadas mejora la claridad de los programas y la productividad del programador. Las mismas se encuentran disponibles desde Oracle 18c, incorporando nuevas funcionalidades en Oracle 21c.

Algunas de estas mejoras están asociadas a otras mejoras PL/SQL, como ser las del iterador FOR .. LOOP que vimos en los dos artículos anteriores (parte #1 y parte #2)

Iteradores

A continuación veremos los tres nuevos iteradores que pueden ser usados en expresiones calificadas.

Iterador por Indice

Este iterador permite popular una colección proporcionando expresiones para el cálculo tanto del índice como del valor a almacenar.

Ejemplo:

DECLARE 
  TYPE TablaT IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;;
  vTablaT    TablaT;
BEGIN
  -- 
  vTablaT := TablaT (FOR i in 2 .. 4 index i * 2 => i * 3); 
  --
END;

Resultado:

Indice=4    Valor=6
Indice=6    Valor=9
Indice=8    Valor=12

Lo que hace el iterador es recorrer la lista de valores declaradas en la cláusula FOR (en nuestro caso, del 2 al 4) y asignarle al indice el resultado de "i * 2", y almacenar el valor "i * 3" en la colección. Como vemos, ambas expresiones pueden usar el valor del iterando "i".

En nuestro ejemplo usamos un array asociativo con valores numéricos, pero los mismos podrían ser de cualquier tipo de datos (varchar, date, etc.). Este iterador acepta la mayoría de las mejoras que se incorporaron en Oracle 21c al iterador FOR .. LOOP, como ser saltear valores, detenerse al llegar a una condición dada, o iterar en saltos distintos de 1, como mostramos a continuación:

DECLARE 
  TYPE TablaT IS TABLE OF DATE INDEX BY PLS_INTEGER;;
  vTablaT    TablaT;
BEGIN
  -- 
  vTablaT := TablaT (FOR i IN 1..10 BY 2 WHEN i > 5 INDEX i => SYSDATE + i); 
  --
END;

En este caso, va a iterar del 1 al 10 de a dos en dos pero solo cuando i sea mayor a 5, por lo que los valores índices serían 6, 8 y 10 y los valores almacenados serían la fecha actual mas 6, 8 y 10 días.

Iterador por Secuencia

Este iterador permite popular una colección proporcionando expresiones para el calculo del valor a almacenar, mientras que el indice se popula en forma automática con valores secuenciales comenzando en 1.

Ejemplo:

DECLARE 
  TYPE TablaT IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;;
  vTablaT    TablaT;
BEGIN
  -- 
  vTablaT := TablaT (FOR i in 2 .. 4 SEQUENCE => i * 3); 
  --
END;

Resultado:

Indice=1    Valor=6
Indice=2    Valor=9
Indice=3    Valor=12

La diferencia con el ejemplo anterior es que los índices en este caso se calcularon en forma automática con valores secuenciales comenzando por el 1, mientras que usamos el valor del iterador en la expresión que calcula el valor a almacenar en la colección.

Al igual que el iterador por indice, el iterador por secuencia permite el uso de la mayoría de las mejoras de Oracle 21c realizadas a FOR .. LOOP.

Iterador Básico

Este iterador permite popular una colección proporcionando expresiones para el calculo del valor a almacenar, mientras que el indice se popula en forma automática con el valor del iterador..

Ejemplo:

DECLARE 
  TYPE TablaT IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;;
  vTablaT    TablaT;
BEGIN
  -- 
  vTablaT := TablaT (FOR i in 2 .. 4 => i * 3); 
  --
END;

Resultado:

Indice=2    Valor=6
Indice=3    Valor=9
Indice=4    Valor=12

La diferencia con el ejemplo anterior es que los índices en este caso se calcularon en forma automática con el valor del iterador, el cual también puede ser utilizado en la expresión que calcula el valor a almacenar en la colección.

Notación Posicional para Array Asociativo

Adicionalmente a estos nuevos iteradores, Oracle 21c simplifica aun mas la asignación de valores a un array asociativo cuando no usamos iterador. A continuación veremos la evolución en las últimas versiones de Oracle.

Previo a Oracle 18c

DECLARE 
  TYPE TablaT IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  vTablaT    TablaT;
BEGIN 
  --
  vTablaT(1) := 'Diez';
  vTablaT(2) := 'Veinte';
  vTablaT(3) := 'Treinta';

END;
/

Oracle 18c

DECLARE 
  TYPE TablaT IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  vTablaT    TablaT;
BEGIN 
  --
  vTablaT := TablaT(1 => 'Diez', 2 => 'Veinte', 3 => 'Treinta');

END;
/

Oracle 21c

DECLARE 
  TYPE TablaT IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  vTablaT    TablaT;
BEGIN 
  --
  vTablaT := TablaT('Diez', 'Veinte', 'Treinta');
END;
/

Como vemos, en Oracle 21c no necesitamos indicar el valor del indice usando la notación posicional, el mismo es generado en forma automática comenzando con 1.

Links

Recomendamos leer la documentación oficial de Oracle para tener un mejor conocimiento de estas nuevas características.

Oracle 21c - Mejoras al iterador FOR LOOP - Parte #2

En el articula anterior, "Oracle 21c - Mejoras al iterador FOR LOOP - Parte #1" vimos las mejoras que trae la version 21c de Oracle Database al iterador FOR .. LOOP. En este articulo veremos otras mejoras que permiten utilizar el mismo con colecciones de PL/SQL.

Control de Iteraciones de Colecciones

Existen otros tres nuevos modificadores del iterador FOR .. LOOP que permiten obtener valores en base a colecciones de PL/SQL en vez de rangos. Su funcionamiento es similar al uso de "VALUES OF" y de "INDICES OF" en las operaciones "FORALL" de PL/SQL.

Uso de VALUES OF 

Esta opción nos permite "recorrer" los distintos valores almacenados en un array asociativo de PL/SQL (conocidos como index-by-table) que posee tres elementos, pero cuyos valores de indices se encuentran dispersos (15, 40 y 68):

DECLARE 
  TYPE  TablaT IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  vTabla TablaT;
BEGIN
  vTabla(15) := 10;
  vTabla(40) := 20;
  vTabla(68) := 30;

  FOR NumVar IN VALUES OF vTabla LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 10
Soy el numero 20
Soy el numero 30

PL/SQL procedure successfully completed.

SQL>

Uso de INDICES OF

Esta opción es similar a la opción que vimos anteriormente, pero en vez de obtener los valor asociados a un indice, obtenemos directamente el valor del indice. Usando los mismos datos del ejemplo anterior, podemos ver que obtenemos los valores 15, 40 y 68 que representan los indices del array asociativo que creamos:

DECLARE 
  TYPE  TablaT IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  vTabla TablaT;
BEGIN
  vTabla(15) := 10;
  vTabla(40) := 20;
  vTabla(68) := 30;

  FOR NumVar IN INDICES OF vTabla LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 15
Soy el numero 40
Soy el numero 68

PL/SQL procedure successfully completed.

SQL>

Uso de PAIRS OF

Esta opción nos permite acceder tanto a los valores almacenados en un array asociativo como a los indices asociados a los mismo en un mismo paso. Podemos pensar a la misma como una combinación de las dos opciones que vimos anteriormente. Para comprender su uso, veamos un ejemplo donde almacenamos el valor alfanumérico de cada numero utilizado como índice, y accedemos a ambos en el iterador FOR .. LOOP:

DECLARE 
  TYPE  TablaT IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  vTabla TablaT;
BEGIN
  vTabla(15) := 'Quince';
  vTabla(40) := 'Cuarenta';
  vTabla(68) := 'Sesenta y Ocho';

  FOR NumVar, TextVar IN PAIRS OF vTabla LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar) || ' que sería ' || TextVar);
  END LOOP;
END;
/
Soy el numero 15 que sería Quince
Soy el numero 40 que sería Cuarenta
Soy el numero 68 que sería Sesenta y Ocho

PL/SQL procedure successfully completed.

SQL>

Oracle 21c - Mejoras al iterador FOR LOOP - Parte #1

El lenguaje PL/SQL de Oracle posee desde sus comienzos una funcionalidad muy utilizada que es el iterador FOR..LOOP. El mismo permite en forma sencilla recorrer una serie de valores consecutivos dentro de un rango dado. Veamos un ejemplo de su funcionalidad:

BEGIN
  FOR NumVar IN 10 .. 12 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 10
Soy el numero 11
Soy el numero 12


PL/SQL procedure successfully completed.

SQL>

No es necesario declarar previamente la variable "NumVar" ni aclarar el incremento deseado (siempre es de a uno). Es posible incluir la palabra clave "REVERSE" antes del rango para que el mismo lo haga en orden reverso, pero hasta Oracle 21c no era posible hacer mucho mas:

BEGIN
  FOR NumVar IN REVERSE 10 .. 12 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 12
Soy el numero 11
Soy el numero 10


PL/SQL procedure successfully completed.

SQL>

Lo nuevo en Oracle 21c

Oracle 21c ofrece algunas opciones adicionales, las cuales vamos a analizar a continuación y en un segundo artículo.

Iteración de valor único

En Oracle 21c Podemos hacer iteraciones de un un solo valor, de la siguiente manera:

BEGIN
  FOR NumVar IN 20 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 20

PL/SQL procedure successfully completed.

SQL>

En si, esta no parece una opción muy interesante, pero lo que le suma utilidad es la posibilidad de usar la clausula REPEAT con la opción WHILE para definir una lógica adicional, como mostramos a continuación:

BEGIN
  FOR NumVar IN 2, REPEAT NumVar * 3 WHILE NumVar < 100 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 2
Soy el numero 6
Soy el numero 18
Soy el numero 54

PL/SQL procedure successfully completed.

Iteraciones Múltiples

Esta opción nos permite definir varios rangos para iterar, de forma tal que la lógica que vamos a ejecutar la definimos en un solo lugar (en nuestro ejemplo, es un simple DBMS_OUTPUT) sin necesidad de crear multiples bloques de iteración:

BEGIN
  FOR NumVar IN 2 .. 4, REVERSE 6 .. 8, 15 .. 16 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 2
Soy el numero 3
Soy el numero 4
Soy el numero 8
Soy el numero 7
Soy el numero 6
Soy el numero 15
Soy el numero 16

PL/SQL procedure successfully completed.

Iteraciones por rango con salto definido por el usuario

Esta opción nos permite controlar el incremento entre cada iteración (antes de Oracle 21c era si o si el valor 1), como vemos a continuación:

BEGIN
  FOR NumVar IN 2 .. 6 BY 2, REVERSE 2 .. 6 BY 2 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 2
Soy el numero 4
Soy el numero 6
Soy el numero 6
Soy el numero 4
Soy el numero 2

PL/SQL procedure successfully completed.

Iteraciones por rango con salto decimal definido por el usuario

Si bien la sintaxis permitía el uso de valores decimales para los valores de inicio y fin del rango, estos eran redondeados a un valor enter en forma automática. Oracle 21c permite definir tanto rangos decimales (manteniendo los incrementos en 1) como también incrementos decimales, como podemos ver en los siguientes ejemplos. 

En este ejemplo, el primer rango tiene comienzo y fin con decimales pero incremento entero:

BEGIN
  FOR NumVar NUMBER(4,1) IN 2.3 .. 4.3 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 2.3
Soy el numero 3.3
Soy el numero 4.3

PL/SQL procedure successfully completed.

Este segundo ejemplo, tanto los valores de inicio / fin del rango como el incremento se define como valor decimal:

BEGIN
  FOR NumVar NUMBER(4,1) IN 2.3 .. 4.3 BY 0.4 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 2.3
Soy el numero 2.7
Soy el numero 3.1
Soy el numero 3.5
Soy el numero 3.9
Soy el numero 4.3

PL/SQL procedure successfully completed.

Lo único que tenemos que tener en cuenta, es definir el tipo de datos de la variable (NumVar) que va a mantener los valores, con un tipo de datos apropiado, como se resalta en los ejemplos.

Salteando valores y deteniendo las iteraciones

La opción WHEN nos permite saltear valores no deseados, por ejemplo si queremos "procesar" solo los números pares en un rango en particular, podemos usar la siguiente sintaxis:

BEGIN
  FOR NumVar IN 3 .. 10 WHEN MOD(NumVar,2) = 0 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 4
Soy el numero 6
Soy el numero 8
Soy el numero 10

PL/SQL procedure successfully completed.

Por otro lado, la opción WHILE (la cual vimos dentro del uso de REPEAT) nos permite definir una condición de salida que detendrá la ejecución del bloque, como podemos ver a continuación:

BEGIN
  FOR NumVar IN 4 .. 20 WHILE NumVar * NumVar < 100 LOOP
    dbms_output.put_line('Soy el numero ' || TO_CHAR(NumVar));
  END LOOP;
END;
/
Soy el numero 4
Soy el numero 5
Soy el numero 6
Soy el numero 7
Soy el numero 8
Soy el numero 9

PL/SQL procedure successfully completed.

Como podemos ver, al evaluar NumVar = 10, la operación NumVar * NumVar da como resultado 100, dejando de cumplir la condición definida en la cláusula WHILE, por lo que el valor 10 no se imprime y el bucle termina.

Lo que sigue

En el próximo artículo veremos en detalles otras mejorar al iterador FOR .. LOOP incluidas en Oracle 21c, que permiten usar el mismo con colecciones PL/SQL.

Actualización - Características de Oracle 21c incluidas en Oracle 19c

En los últimos días se conocieron algunas de las características de Oracle 21c que fueron incluidas (backported) en Oracle 19c. Esto se debe a que Oracle 19c es la version de Long Term Support actual (para conocer mas en detalle que implica cada ser Long Term Support, pueden ver mi articulo "Innovation Release vs Long Term Support Release"), por lo que gran parte de la base instalada se mantendrá en esa versión por varios años.

A continuación revisaremos aquellas que ya se encuentran disponibles.

SQL Macros

Parte de esta característica de Oracle 21c (SQL Macros para expresiones de tipo tabla) fue incluida en Oracle 19c, en su version 19.6 y posteriores.

Fuente: SQL Macros in Oracle Database 21c

Estadísticas de Optimizador en Tiempo Real

Esta característica de Oracle 21c fue incluida en Oracle 19c, en su version 19.10 y posteriores.

Fuente: Optimizer Real-time Statistics Parameter in RU 19.10 Onwards / New Parameters in Oracle 19.10.0 – and a default change

Tablas Blockchain

Esta característica de Oracle 21c fue incluida en Oracle 19c, en su version 19.10 y posteriores. Por el momento requiere un parche adicional, #32431413.

Fuente: Why Oracle Implement Blockchain in the Database / Blockchain Tables in Oracle Database 21c

Tablas Inmutables

Esta característica de Oracle 21c fue incluida en Oracle 19c, en su version 19.11 y posteriores.

Fuente: Why Oracle Implement Blockchain in the Database

Otras Características que Pueden ser Incluidas en Oracle 19c

La siguiente es una lista de otras características de Oracle 21c que pueden ser incluidas en Oracle 19c en el corto plazo:

• Memoria Persistente.
• Mejoras a InMemory.
• Mejoras a JSON (internamente el tipo de dato OSON ya existe, por lo que no seria complicado inplementarlo).

Oracle 21c - Usando Expresiones en Parámetros de Inicialización

En el artículo "¿Qué hay de nuevo en Oracle 21c?" publicado en diciembre del año pasado, mencionamos algunas de las mejoras y nuevas características de Oracle 21. En este y en próximos artículos vamos a ver en detalle lo que Oracle 21c trae de nuevo, comenzando con la posibilidad de usar expresiones en la definición de los parámetros de inicialización (ya sea en initXXX.ora o SPFile).

Usando Variables de Entorno en Parámetros de Inicialización

Esta posibilidad nos permite usar variables definidas a nivel de entorno (sistema operativo) en parámetros. Esto nos permite independizarnos, por ejemplo, de la ubicación de archivos.

En el ejemplo siguiente, vamos a configurar el parámetro "db_recovery_file_dest" para que use el valor de la variable de entorno "RECO" en un entorno Linux

SQL> ALTER SYSTEM SET db_recovery_file_dest='$RECO' SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
db_recovery_file_dest                string      $RECO
db_recovery_file_dest_size           big integer 250G

Luego, vamos a verificar como se guarda efectivamente el valor. Para ello crearemos un archivo pfile a partir del spfile que acabamos de modificar, y luego vamos a buscar en el mismo la linea que contenga el tecto "db_recovery_file_dest"

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL> host fgrep db_recovery_file_dest /tmp/pfile.txt
*.db_recovery_file_dest='$RECO'

SQL>

Es importante asegurarnos que la variable de entorno esté configurada al momento de iniciar la instancia, de lo contrario obtendremos un error "ORA-07217: sltln: environment variable cannot be evaluated". Los cambios que se hagan a la variable de entorno con posterioridad al inicio de la instancia no son tenidos en cuenta por la misma.

Usando otros Parámetros en Parámetros de Inicialización

Esta opción nos permite definir el valor de un parámetro basándonos en el valor de otro parámetro ya definido. Si consultamos el valore de los parámetros que incluyen la palabra PROCESSES, veremos lo siguiente:

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     40
log_archive_max_processes            integer     4
processes                            integer     600
SQL>

A continuación, vamos a definir que queremos que el valor de "log_archive_max_processes" sea el 10% del valor de PROCESSES (sólo a modo de ejemplo). Para ello, vamos a usar la siguiente sintaxis:

SQL> alter system set log_archive_max_processes='processes/10';

System altered.

SQL>

Si consultamos nuevamente el valor del parámetro, veremos que el mismo ahora esta configurado en 60 (10% de 600):

SQL> show parameter log_archive_max_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     60
SQL>

Y podemos validar también como queda "guardado" en el archivo de configuración (veremos que se guarda la "formula" y no el valor resultante de la misma):

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL> host fgrep log_archive_max_processes /tmp/pfile.txt
*.log_archive_max_processes=processes/10

SQL>

Por lo que podemos deducir que cualquier cambio que aplicamos al valor del parámetro PROCESSES automáticamente afectará a todos los parámetros definidos en base al mismo, sin necesidad de tener que modificarlos en forma individual.

Consideraciones generales

Estas son algunas consideraciones adicionales cuando usamos expresiones en parámetros de inicialización:

• Las expresiones deben estar dentro de comillas simples al momento de usarlas en una sentencia ALTER SYSTEM or ALTER SESSION.
• Las expresiones pueden ser usadas tanto en archivos de tipo PFile (parámetros estáticos) como en archivos de parámetros dinámicos de tipo SPFile.
• Se pueden usar dos funciones adicionales, MAX y MIN, que permiten elegir el valor mayor o menor (según el caso) entre dos valores. Esto es útil cuando debemos fijar un valor mínimo. Por ejemplo, la expression MIN(40,PROCESSES) devolverá 40 cuando el parámetro PROCESSES es mayor a 40, o el valor de PROCESSES cuando sea menor a 40.

Para mas ejemplos y practica, podemos consultar el artículo "Practice: Using Expressions in Initialization Parameters" en la documentación de Oracle 21, en la sección "Learning Database New Features".