Listas de selección en Cascada en Oracle APEX

Uno de los ítems más usado en las aplicaciones web son las listas de selección, estas facilitan a los usuarios llenar formularios a la vez que evita errores al ingresar mal texto. en este artículo les explicaré como crear listas de selección en APEX y las diferentes características que posee APEX para facilitarnos las modificaciones.

• Creación de Listas de selección
• Listas de selección en cascada
• Uso de componentes compartidos en listas de selección
• Facilitar mantenimiento de listas de selección en cascada

Ambiente de ejemplo

Para el ambiente de ejemplo tendremos dos tablas, Dirección y proyecto, cada proyecto pertenece a una sola dirección



Este será nuestro objetivo, tener dos listas de selección de modo que cuando el usuario seleccione una dirección, el sistema muestre solo los proyectos que pertenecen a la dirección seleccionada

Creación de Listas de selección

Para crear las listas de selección primero creamos una región y luego un ítem




Nuestro ítem se llamará "P2_DIRECCIONES" y será de tipo "Lista de Selección".



En las propiedades del elemento se habilitará la sección de lista de valores, allí debemos indicar el tipo de lista de valores que usaremos.

• Componente Compartido: Hacer uso de los componentes compartidos de APEX para obtener la lista de valores, lo veremos con más detenimiento más adelante. Es el recomendado para listas de selección que se repiten más de una vez en la aplicación.
• Consulta SQL: Nos permite ingresar una consulta SQL con dos valores de retorno, uno para mostrar y otro que se retornará al sistema para ser procesado, es el que usaremos en este momento. Es el recomendado para las listas que aparecen una sola vez en la aplicación y toma valores de una tabla de base de datos.
• Valores Estáticos: Nos permite definir valores estáticos en la lista, estos no serán tomados de tablas de Base de datos. Al igual que con la Consulta SQL, es recomendado para listas que aparecen una sola vez en la aplicación.
• Cuerpo de la Función PL/SQL que devuelve una consulta SQL: No la recomiendo debido a que en esta función armaremos la consulta SQL y retornaremos un texto con esta, si ocurre algún cambio en la base de datos que afecte esta consulta, no lo notaremos si no hasta que ingresemos a esta página.

Seleccionamos el tipo "Consulta SQL" e ingresamos la consulta correspondiente para obtener las direcciones.

select NOMBRE as NOMBRE,
       ID_DIRECCION as ID_DIRECCION 
from DIRECCION

Podemos ver otras opciones en las propiedades de la Lista de Valores

• Mostrar Valores Adicionales: Cuando asignamos un valor a esta lista de valores y este no está en los datos retornados por la consulta, activar esta opción permitirá que se muestre ese valor, si está desactivada se ignorará y se mostrará el primer valor
• Mostrar Valor Nulo: Esto permite mostrar un valor al inicio de la lista, si el usuario selecciona este valor, será tomado como nulo y en caso de tener una validación para evitar que este ítem sea nulo, el sistema la activará. Es muy útil para evitar que el usuario envíe un formulario con el valor por defecto de la lista cuando olvidó seleccionar uno.
• Valor de visualización Nulo: Al estar activada la opción de "Mostrar Valor Nulo", podremos indicar cual es el valor que verá el usuario en esta opción. 
• Valor de Retorno Nulo: Al estar activada la opción de "Mostrar Valor Nulo", podremos indicar cual es el valor que se retornará si el usuario selecciona esta opción. En caso de dejarlo vacío, se retornará null.
• Elementos Principales de Lista de Valores en Cascada: Al ingresar el nombre de otro ítem de lista de valores, se indicará a APEX que cuando cambie el valor de ese ítem, se guarde en la sesión el valor y se vuelva a ejecutar la consulta del ítem que estamos modificando y muestre los valores correspondientes. Lo usaremos para las listas en cascada.

Al guardar la página ya tendremos la primera lista de selección.

Listas de selección en cascada

Para la lista de selección en cascada creamos un nuevo ítem llamado P2_PROYECTOS e indicamos que es de tipo "Lista de selección".

En esta ocasión la consulta tendrá un where, para filtrar los proyectos que pertenecen a la dirección seleccionada.

select NOMBRE as NOMBRE,
       ID_PROYECTO as ID_PROYECTO 
from PROYECTO 
where ID_DIRECCION = :P2_DIRECCIONES

Noten que también colocamos P2_DIRECCIONES en el campo "Elementos Principales de Lista de Valores en Cascada" para indicar a APEX que cada vez que cambie el valor de P2_DIRECCIONES, se guarde el nuevo valor en sesión y luego se vuelva a ejecutar la consulta de Proyectos para cargar el ítem de proyectos. Este campo es muy importante, si no se indica, no se actualizarán los proyectos al cambiar las direcciones.

Al guardar ya tenemos nuestro objetivo, dos listas de selección en cascada.

Uso de componentes compartidos en listas de selección

APEX nos permite realizar copia de los elementos y regiones en otras páginas o en la misma página,  de este modo podemos reutilizar ítems que ya hemos creado, supongamos que en nuestra aplicación tenemos 100 veces este par de ítems y tenemos 1.000 páginas. Nos solicitan un cambio sencillo, mostrar las direcciones en orden alfabético. Solucionarlo es realmente sencillo, sólo debemos agregar un Order By a nuestra consulta, pero... tenemos 100 ítems, aunque sea algo sencillo debemos hacerlo muchas veces y eso nos tomará mucho tiempo. Para facilitar esto, APEX dispone de componentes compartidos. Es uno de los tipos de listas de valores que se pueden escoger y es el recomendado cuando la lista de valores se repite más de una vez. (De hecho debimos crearlo inicialmente así, pero quería mostrarles la manera sencilla de crearlo y el problema que podría generar).

Primero debemos crear el componente compartido, para ello ingresamos a Creador de Aplicaciones -> [Nuestra aplicación] -> Componentes compartidos -> Lista de Valores y seleccionamos la opción Crear.
Allí seleccionamos crear una lista de valores de tipo Dinámico, (También podemos crear listas estáticas en caso de que tengamos valores fijos).



En APEX 19.2 tenemos varias opciones, como obtener la lista de un Origen WEB, o sólo indicar la tabla y las columnas de las cuales queremos la lista, pero en versiones anteriores, APEX nos muestra inmediatamente un campo para ingresar la consulta SQL, por eso realizaré el ejercicio con esta opción. Y colocaremos la misma consulta que colocamos en la lista de selección de DIRECCIONES.



Luego vamos al ítem de Direcciones  y cambiamos el tipo de lista de valores a Componente compartido, posteriormente seleccionamos el componente compartido que acabamos de crear.




Al guardar vemos que la lista no cambió y todo está normal, esto es para comprobar que no "dañamos" nada a realizar este cambio. Luego volvemos al componente compartido y modificamos la consulta agregando el Order By.



Al modificar la consulta y guardar el componente, volvemos a recargar la página de la aplicación y vemos que sin realizar modificaciones a esta, se ve reflejado el cambio y ahora las direcciones están ordenadas alfabéticamente. Si varias listas de selección usaran el mismo componente compartido, verían reflejado el cambio, de este modo podremos realizar modificaciones a la manera en la que mostramos la información de una manera fácil y rápida.

Facilitar mantenimiento de listas de selección en cascada

Los componentes compartidos nos facilitan el mantenimiento de las listas de selección, pero en el caso de listas en cascada no podemos usarlos ya que no permiten pasar parámetros por cada lista de selección que las use y por eso no podríamos aplicar un filtro en cada lista diferente. Para facilitar el mantenimiento de estas listas podríamos tener una función que reciba parámetros y nos retorne una colección de registros que podamos usar en una consulta SQL.

Para ello usaremos Pipelined Table Functions, que nos retornará, por decirlo así, una tabla virtual que podremos usar en una consulta SQL.

Podemos crear un paquete para almacenar estos tipos de funciones o podemos crearlas en un paquete relacionado de la tabla, en este caso crearé un paquete para almacenar estas funciones y lo llamaré LOV.  Este es el código del Paquete y lo analizaremos poco a poco.

create or replace PACKAGE "LOV"
AS

TYPE records IS RECORD(
       display     VARCHAR2(3200),
       id_r     number
       );

TYPE records_table IS TABLE OF records;

function PROYECTO_POR_DIRECCION(idDireccion DIRECCION.ID_DIRECCION%TYPE) return records_table PIPELINED;

end;

Linea 4: Crearemos un tipo de dato que será un Record de dos datos, el que mostraremos (display) y el que retornaremos (id_r).
Linea 9: Crearemos un tipo que será una tabla del tipo que creamos anteriormente, este será el tipo que retornarán nuestras funciones.
Linea 11: Acá declaramos las funciones y podemos leer los parámetros que sean necesarios para nuestra consulta.

Ahorta veremos el código del cuerpo del paquete.

create or replace PACKAGE BODY "LOV"
AS

---------------------------------------------------------------------------------------------------
-- Retorna los proyectos de acuerdo a la dirección seleccionada
---------------------------------------------------------------------------------------------------
function PROYECTO_POR_DIRECCION(idDireccion DIRECCION.ID_DIRECCION%TYPE) return records_table PIPELINED
IS
v_retorno records;
BEGIN
    for r in (select PROYECTO.NOMBRE as NOMBRE,
    PROYECTO.ID_PROYECTO as ID_PROYECTO 
    from PROYECTO PROYECTO
    where ID_DIRECCION = idDireccion)
    loop
        v_retorno.display     := r.NOMBRE;
        v_retorno.id_r         := r.ID_PROYECTO;
        PIPE ROW (v_retorno);
    end loop;

    RETURN;

END PROYECTO_POR_DIRECCION;

end;

Linea 9: Declaramos la variable de tipo records para almacenar los valores de nuestra consulta
Linea 11: Iniciamos un ciclo FOR sobre los resultados de nuestra consulta en la cual incluimos los parámetros que recibe la función.
Linea 16 y 17: Agregamos los valores a la variable v_retorno.
Linea 18: Al usar la función PIPE ROW, estamos guardando este registro en la "tabla virtual" que retornaremos.

Con esta función podremos pasar parámetros y obtener los registros filtrados de acuerdo a los parámetros, pero ahora ¿Cómo realizo la consulta usando esta función?

Para convertir lo que nos retorna esta función en una tabla, usaremos el operador TABLE()  de la siguiente manera:

select display, id_r
from table(LOV.PROYECTO_POR_DIRECCION(:P2_DIRECCION));

En este caso estamos pasando como parámetro la lista de selección que ocacionaría que la lista de proyectos se refresque. Al usar el operados TABLE(), podemos consultar las columnas como si fuese una tabla normal. De este modo, si surge algún cambio en la manera en la que debemos mostrar los datos, simplemente modificaremos la consulta en la función y todas las listas de selección que están obteniendo los datos de esta manera, verán reflejados los cambios.



Fuentes:

• Create an Oracle function that returns a table - stackoverflow