Oracle APEX Collection Details

[admin]

50 character attributes (VARCHAR2(4000))
5 number attributes
5 date attributes
1 XML Type attribute
1 large binary attribute (BLOB)
1 large character attribute (CLOB)
1 MD5

CREATE_COLLECTION
CREATE_OR_TRUNCATE_COLLECTION
CREATE_COLLECTION_FROM_QUERY
CREATE_COLLECTION_FOM_QUERY2
CREATE_COLLECTION_FROM_QUERY_B
CREATE_COLLECTION_FROM_QUERYB2

Collection creation

Code: Select all

declare
v_collection_name varchar2(255) := 'ERPSTUFF';
begin

IF NOT
    apex_collection.collection_exists(p_collection_name => v_collection_name) THEN

    apex_collection.create_collection(v_collection_name);

  END IF;

 apex_collection.add_member(p_collection_name => v_collection_name,
                               p_c001            => :P1_CODE,
                               p_c002            => :P1_PRODUCT_NAME,
                               p_n001            => :P1_QUANTITY,
                               p_d001            => :P1_NEED_BY_DATE
                               ); 

 exception when others then 
  raise_application_error(-20001,DBMS_UTILITY.format_error_backtrace);
  
end;

View Collections

Code: Select all

SELECT c001, c002, n001, d001 
FROM APEX_collections 
WHERE collection_name = ‘ERPSTUFF’

OR

SELECT c001 as CODE, c002 as PRODUCT_NAME, n001 as QUANTITY, d001 NEED_BY_DATE
FROM APEX_collections 
WHERE collection_name = ‘ERPSTUFF'

APEX_COLLECTION.TRUNCATE_COLLECTION (
p_collection_name => 'ERPSTUFF');

APEX_COLLECTION.DELETE_MEMBER (
p_collection_name => 'ERPSTUFF',
p_seq => 2);

APEX_COLLECTION.RESEQUENCE_COLLECTION (
p_collection_name => 'ERPSTUFF');

APEX_COLLECTION.GET_MEMBER_MD5 (
p_collection_name => 'ERPSTUFF',
p_seq => 2) -- RETURN VARCHAR2;

APEX_COLLECTION.MOVE_MEMBER_DOWN (
p_collection_name => 'ERPSTUFF',
p_seq => 2);

APEX_COLLECTION.MOVE_MEMBER_UP (
p_collection_name => 'ERPSTUFF',
p_seq => 2);

BEGIN
APEX_COLLECTION.UPDATE_MEMBER (
p_collection_name => ‘ERPSTUFF',
p_seq => ‘4',
p_c001 => ‘LAPTOP',
p_c002 => ‘MOBILE');
END;