APEX_COLLECTION – Parte 2

Escrito por: Eddie Molina

Oracle ACE Pro. Oracle APEX Cloud Developer Certified Professional. Presidente de ORAUGCRC (Grupo de Usuarios Oracle de Costa Rica). Miembro de líderes de LAOUC. Conferencista, consultor, desarrollador y capacitador en tecnologías Oracle, especialmente Oracle Application Express con más de 30 años de experiencia.



Apex



13/04/2022

En la entrada anterior, iniciamos con una introducción a la API APEX_COLLECTION, en donde se enumeran las diferentes operaciones que se pueden ejecutar con este paquete.

En esta entrada y las siguientes, veremos las diferentes funciones y procedimientos que conforman APEX_COLLECTION.

Procedimiento ADD_MEMBER

Utilice este procedimiento para agregar un nuevo miembro a una colección existente. Se genera un error si la colección especificada no existe para el usuario actual en la misma sesión para el ID de la aplicación actual. Los espacios no se utilizan al agregar un nuevo miembro, por lo que una colección existente con miembros de ID de secuencia (1,2,5,8) agrega el nuevo miembro con una ID de secuencia de 9.

La sintaxis del procedimiento es:

APEX_COLLECTION.ADD_MEMBER (
p_collection_name IN VARCHAR2,
p_c001 IN VARCHAR2 DEFAULT NULL,
…
p_c050 IN VARCHAR2 DEFAULT NULL,
p_n001 IN NUMBER DEFAULT NULL,
p_n002 IN NUMBER DEFAULT NULL,
p_n003 IN NUMBER DEFAULT NULL,
p_n004 IN NUMBER DEFAULT NULL,
p_n005 IN NUMBER DEFAULT NULL,
p_d001 IN DATE DEFAULT NULL,
p_d002 IN DATE DEFAULT NULL,
p_d003 IN DATE DEFAULT NULL,
p_d004 IN DATE DEFAULT NULL,
p_d005 IN DATE DEFAULT NULL,
p_clob001 IN CLOB DEFAULT EMPTY_CLOB(),
p_blob001 IN BLOB DEFAULT EMPTY_BLOB(),
p_xmltype001 IN XMLTYPE DEFAULT NULL,
p_generate_md5 IN VARCHAR2 DEFAULT ‘NO’);

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de una colección existente. El largo máximo es de 255 bytes. Los nombres de colección se convierten a mayúsculas.
`p_c001 through p_c050`	Valor de atributo alfanumérico a ser agregado. El tamaño máximo es de 4000 bytes, si se sobrepasa este tamaño, el contenido se trunca a 4000 caracteres.
`p_n001 through p_n005`	Valor de atributo numérico a ser agregagdo.
`p_d001` through `p_d005`	Valor de atributo tipo fecha a ser agregado.
`p_clob001`	Utilice p_clob001 para miembros de colección que excedan los 4000 caracteres.
`p_blob001`	Utilice p_blob001 para agregar valores de atributo binarios.
`p_xmltype001`	Use p_xmltype001 para almacenar XML bien conformado.
`p_generate_md5`	Los valores válidos incluyen YES y NO. YES para especificar si se debe calcular el resumen del mensaje de los datos del miembro de la colección. Utilice este parámetro para comparar el MD5 del miembro de la colección con otro miembro o para ver si ese miembro ha cambiado.

Un ejemplo del uso de este procedimiento es:

BEGIN

     APEX_COLLECTION.ADD_MEMBER(
          p_collection_name => ‘GROCERIES’
          p_c001 => ‘Grapes’,
          p_c002 => ‘Imported’,
          p_n001 => 125,
          p_d001 => sysdate );
END;

Función ADD_MEMBER

Utilice esta función para agregar un nuevo miembro a una colección existente. Llamar a esta función devuelve el ID de secuencia del miembro recién agregado. Se genera un error si la colección especificada no existe para el usuario actual en la misma sesión para el ID de la aplicación actual. Los espacios no se utilizan al agregar un nuevo miembro, por lo que una colección existente con miembros de ID de secuencia (1,2,5,8) agrega el nuevo miembro con una ID de secuencia de 9.

La sintaxis de la función es:

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de una colección existente. El largo máximo es de 255 bytes. Los nombres de colección se convierten a mayúsculas.
`p_c001 through p_c050`	Valor de atributo alfanumérico a ser agregado. El tamaño máximo es de 4000 bytes, si se sobrepasa este tamaño, el contenido se trunca a 4000 caracteres.
`p_n001 through p_n005`	Valor de atributo numérico a ser agregagdo.
`p_d001` through `p_d005`	Valor de atributo tipo fecha a ser agregado.
`p_clob001`	Utilice p_clob001 para miembros de colección que excedan los 4000 caracteres.
`p_blob001`	Utilice p_blob001 para agregar valores de atributo binarios.
`p_xmltype001`	Use p_xmltype001 para almacenar XML bien conformado.
`p_generate_md5`	Los valores válidos incluyen YES y NO. YES para especificar si se debe calcular el resumen del mensaje de los datos del miembro de la colección. Utilice este parámetro para comparar el MD5 del miembro de la colección con otro miembro o para ver si ese miembro ha cambiado.

Un ejemplo del uso de esta función es:

DECLARE
     l_seq number;
BEGIN
     l_seq := APEX_COLLECTION.ADD_MEMBER(
          p_collection_name => ‘GROCERIES’
          p_c001 => ‘Grapes’,
          p_c002 => ‘Imported’,
          p_n001 => 125,
          p_d001 => sysdate );
END;

Procedimiento ADD_MEMBERS

Utilice este procedimiento para agregar una matriz de miembros a una colección. Se genera un error si la colección especificada no existe para el usuario actual en la misma sesión para el ID de la aplicación actual. Los espacios no se utilizan al agregar un nuevo miembro, por lo que una colección existente con miembros de ID de secuencia (1,2,5,8) agrega el nuevo miembro con una ID de secuencia de 9. El recuento de elementos en la tabla p_c001 PL/SQL se utiliza como el número total de elementos en todas las tablas PL/SQL. Por ejemplo, si p_c001.count es 2 y p_c002.count es 10, solo se agregan 2 miembros. Si p_c001 es nulo, se genera un error de aplicación.

La sintaxis del procedimiento es:

APEX_COLLECTION.ADD_MEMBERS (
p_collection_name IN VARCHAR2,
p_c001 IN APEX_APPLICATION_GLOBAL.VC_ARR2,
p_c002 IN APEX_APPLICATION_GLOBAL.VC_ARR2 default empty_vc_arr,
p_c003 IN APEX_APPLICATION_GLOBAL.VC_ARR2 default empty_vc_arr,
…
p_c050 IN APEX_APPLICATION_GLOBAL.VC_ARR2 default empty_vc_arr,
p_n001 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
p_n002 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
p_n003 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
p_n004 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
p_n005 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
p_d001 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
p_d002 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
p_d003 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
p_d004 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
p_d005 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
p_generate_md5 IN VARCHAR2 default ‘NO’);

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de una colección existente. El largo máximo es de 255 bytes. Los nombres de colección se convierten a mayúsculas.
`p_c001 through p_c050`	Valor de atributo alfanumérico a ser agregado. El tamaño máximo es de 4000 bytes, si se sobrepasa este tamaño, el contenido se trunca a 4000 caracteres.
`p_n001 through p_n005`	Valor de atributo numérico a ser agregagdo.
`p_d001` through `p_d005`	Valor de atributo tipo fecha a ser agregado.
`p_generate_md5`	Los valores válidos incluyen YES y NO. YES para especificar si se debe calcular el resumen del mensaje de los datos del miembro de la colección. Utilice este parámetro para comparar el MD5 del miembro de la colección con otro miembro o para ver si ese miembro ha cambiado.

Un ejemplo del uso de este procedimiento es:

Begin
     APEX_COLLECTION.ADD_MEMBERS(
          p_collection_name => ‘EMPLOYEE’,
          p_c001 => l_arr1,
          p_c002 => 1_arr2);
End;

Función COLLECTION_EXISTS

Utilice esta función para determinar si existe una colección. Se devuelve VERDADERO si la colección especificada existe para el usuario actual en la sesión actual para el Id. de aplicación actual; de lo contrario, se devuelve FALSO.

La sintaxis de la función es:

APEX_COLLECTION.COLLECTION_EXISTS (
p_collection_name IN VARCHAR2)
RETURN BOOLEAN;

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de una colección existente. El largo máximo es de 255 bytes. Los nombres de colección se convierten a mayúsculas.

Un ejemplo del uso de esta función es:

Begin
l_exists := APEX_COLLECTION.COLLECTION_EXISTS (
p_collection_name => ‘EMPLOYEES’);
End;

Función COLLECTION_HAS_CHANGED

Utilice esta función para determinar si una colección ha cambiado desde que se creó o si se restableció el indicador de cambio de colección.

La sintaxis de la función es:

APEX_COLLECTION.COLLECTION_HAS_CHANGED (
p_collection_name IN VARCHAR2)
RETURN BOOLEAN;

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de la colección. Un error es returnado si la colección no existe con el nombre del usuario actual y en la misma sesión.

Un ejemplo del uso de esta función es:

Begin
l_exists := APEX_COLLECTION.COLLECTION_HAS_CHANGED (
p_collection_name => ‘EMPLOYEES’);
End;

Función COLLECTION_MEMBER_COUNT

Utilice esta función para obtener el número total de miembros de la colección nombrada. Si existen lagunas, el recuento total de miembros devuelto no es igual al ID de secuencia más alto de la colección. Si la colección nombrada no existe para el usuario actual en la sesión actual, se genera un error.

La sintaxis de la función es:

APEX_COLLECTION.COLLECTION_MEMBER_COUNT (
p_collection_name IN VARCHAR2)
RETURN NUMBER;

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de la colección.

Un ejemplo del uso de esta función es:

Begin
l_count := APEX_COLLECTION.COLLECTION_MEMBER_COUNT( p_collection_name => ‘DEPARTMENTS’);
End;

Procedimiento CREATE_COLLECTION

Utilice este procedimiento para crear una colección vacía que aún no existe. Si existe una colección con el mismo nombre para el usuario actual en la misma sesión para el ID de aplicación actual, se genera un error de aplicación.

La sintaxis del procedimiento es:

APEX_COLLECTION.CREATE_COLLECTION(
p_collection_name IN VARCHAR2,
p_truncate_if_exists IN VARCHAR2 default ‘NO’);

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de la colección. La longitud máxima es de 255 caracteres. Se devuelve un error si esta colección existe con el nombre especificado del usuario actual y en la misma sesión.
`p_truncate_if_exists`	Si es YES, los miembros de la colección primero se truncarán si la colección existe y no se generará ningún error. Si NO (o no YES), y la colección existe, se generará un error.

Un ejemplo del uso de este procedimiento es:

Begin
APEX_COLLECTION.CREATE_COLLECTION(
p_collection_name => ‘EMPLOYEES’);
End;

Procedimiento CREATE_OR_TRUNCATE_COLLECTION

Utilice este procedimiento para crear una colección. Si existe una colección con el mismo nombre para el usuario actual en la misma sesión para el Id. de aplicación actual, se eliminan todos los miembros de la colección. En otras palabras, la colección nombrada se trunca.

La sintaxis del procedimiento es:

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
p_collection_name IN VARCHAR2);

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de la colección. La longitud máxima es de 255 caracteres. Todos los miembros de la colección nombrada se eliminan si la colección nombrada existe para el usuario actual en la sesión actual.

Un ejemplo del uso de este procedimiento es:

Begin
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
p_collection_name => ‘EMPLOYEES’);
End;

Procedimiento CREATE_COLLECTION_FROM_QUERY

Utilice este procedimiento para crear una colección a partir de una consulta proporcionada. La consulta se analiza como el propietario de la aplicación. Este método se puede utilizar con una consulta con hasta 50 columnas en la cláusula SELECT. Estas columnas en la cláusula SELECT completan los atributos de 50 caracteres de la colección (C001 a C050). Si existe una colección con el mismo nombre para el usuario actual en la misma sesión para el ID de aplicación actual, se genera un error de aplicación.

La sintaxis del procedimiento es:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
     p_collection_name IN VARCHAR2,
     p_query IN VARCHAR2,
     p_generate_md5 IN VARCHAR2 default ‘NO’,
     p_truncate_if_exists IN VARCHAR2 default ‘NO’);

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de la colección. La longitud máxima es de 255 caracteres. Se devuelve un error si esta colección existe con el nombre especificado del usuario actual y en la misma sesión.
`p_query`	Consulta a ejecutar para rellenar los miembros de la colección.
`p_generate_md5`	Los valores válidos incluyen YES y NO. YES para especificar si se debe calcular el resumen del mensaje de los datos del miembro de la colección. Utilice este parámetro para comparar el MD5 del miembro de la colección con otro miembro o para ver si ese miembro ha cambiado.
`p_truncate_if_exists`	Si es `YES`, luego, los miembros de la colección se truncarán primero si la colección existe y no se generará ningún error. Si NO (o no YES), y la colección existe, se generará un error.

Un ejemplo del uso de este procedimiento es:

Begin
     l_query := ‘select make, model, year from AUTOS’;
     APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
          p_collection_name => ‘AUTO’,
          p_query => l_query,
          p_generate_md5 => ‘YES’);
End;

Procedimiento CREATE_COLLECTION_FROM_QUERY2

Utilice este procedimiento para crear una colección a partir de una consulta proporcionada. Este método es idéntico a CREATE_COLLECTION_FROM_QUERY, sin embargo, las primeras 5 columnas de la cláusula SELECT deben ser numéricas y las siguientes 5 deben ser de fecha. Después de las columnas numéricas y de fecha, puede haber hasta 50 columnas de caracteres en la cláusula SELECT. La consulta se analiza como el propietario de la aplicación. Si existe una colección con el mismo nombre para el usuario actual en la misma sesión para el ID de aplicación actual, se genera un error de aplicación.

La sintaxis del procedimiento es:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 (
     p_collection_name IN VARCHAR2,
     p_query IN VARCHAR2,
     p_generate_md5 IN VARCHAR2 default ‘NO’,
     p_truncate_if_exists IN VARCHAR2 default ‘NO’);

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de la colección. La longitud máxima es de 255 caracteres. Se devuelve un error si esta colección existe con el nombre especificado del usuario actual y en la misma sesión.
`p_query`	Consulta a ejecutar para rellenar los miembros de la colección.
`p_generate_md5`	Los valores válidos incluyen YES y NO. SÍ para especificar si se debe calcular el resumen del mensaje de los datos del miembro de la colección. Utilice este parámetro para comparar el MD5 del miembro de la colección con otro miembro o para ver si ese miembro ha cambiado.
`p_truncate_if_exists`	Si es `YES`, luego, los miembros de la colección se truncarán primero si la colección existe y no se generará ningún error. Si NO (o no YES), y la colección existe, se generará un error.

Un ejemplo del uso de este procedimiento es:

BEGIN
     APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 (
          p_collection_name => ‘EMPLOYEE’,
          p_query => ‘select empno, sal, comm, deptno, null, hiredate, null, null, null, null, ename, job, mgr from emp’,
          p_generate_md5 => ‘NO’);
END;

Procedimiento CREATE_COLLECTION_FROM_QUERY_B

Utilice este procedimiento para crear una colección a partir de una consulta proporcionada mediante operaciones masivas. Este método ofrece un rendimiento significativamente más rápido que el método CREATE_COLLECTION_FROM_QUERY. La consulta se analiza como el propietario de la aplicación. Si existe una colección con el mismo nombre para el usuario actual en la misma sesión para el ID de aplicación actual, se genera un error de aplicación.

Este procedimiento utiliza SQL dinámico masivo para realizar las operaciones de extracción e inserción en la colección nombrada. Este procedimiento impone dos limitaciones:

No se calcula la suma de comprobación MD5 para los datos del miembro.
Ningún valor de columna en la consulta p_query puede superar los 2000 bytes. Si se encuentra una fila que tiene un valor de columna de más de 2000 bytes, se genera un error durante la ejecución. En Oracle Database 11g versión 2 (11.2.0.1) o posterior, este límite de columna es de 4000 bytes.

La sintaxis del procedimiento es:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
     p_collection_name IN VARCHAR2,
     p_query IN VARCHAR2,
     p_names IN apex_application_global.vc_arr2,
     p_values IN apex_application_global.vc_arr2,
     p_max_row_count IN NUMBER default null,
     p_truncate_if_exists IN VARCHAR2 default ‘NO’);

Donde:

Parámetro	Descripción
`p_collection_name`	El nombre de la colección. La longitud máxima es de 255 caracteres. Se devuelve un error si esta colección existe con el nombre especificado del usuario actual y en la misma sesión.
`p_query`	Consulta a ejecutar para rellenar los miembros de la colección.
`p_names`	Matriz de nombres de variables de vinculación utilizados en la instrucción de consulta.
`p_values`	Matriz de valores de variables de vinculación utilizados en las variables de vinculación en la instrucción de consulta.
`p_max_row_count`	Número máximo de filas devueltas por la consulta en p_query que deben agregarse a la colección.
`p_truncate_if_exists`	Si es YES, los miembros de la colección primero se truncarán si la colección existe y no se generará ningún error. Si NO (o no YES), y la colección existe, se generará un error.

Un ejemplo del uso de este procedimiento es:

declare
    l_query varchar2(4000);
Begin
    l_query := ‘select empno, ename, job, sal from emp where deptno = :b1’;
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
        p_collection_name => ‘EMPLOYEES’,
        p_query => l_query,
        p_names => apex_util.string_to_table(‘b1’),
        p_values => apex_util.string_to_table(’10’));
End;

En la siguiente entrada, continuaremos con el detalle del segundo grupo de procedimientos y funciones que componen el paquete APEX_COLLECTION.

← Anterior: APEX_COLLECTION Siguient: APEX_COLLECTION - Parte 3 →

APEX_COLLECTION – Parte 2

Escrito por: Eddie Molina

Apex

13/04/2022

Procedimiento ADD_MEMBER

Función ADD_MEMBER

Procedimiento ADD_MEMBERS

Función COLLECTION_EXISTS

Función COLLECTION_HAS_CHANGED

Función COLLECTION_MEMBER_COUNT

Procedimiento CREATE_COLLECTION

Procedimiento CREATE_OR_TRUNCATE_COLLECTION

Procedimiento CREATE_COLLECTION_FROM_QUERY

Procedimiento CREATE_COLLECTION_FROM_QUERY2

Procedimiento CREATE_COLLECTION_FROM_QUERY_B

También te puede gustar…

Uso de casillas de control en un reporte interactivo

IG – Añadir menú personalizado

Generar DDL desde el Taller de SQL de APEX