Oracle APEX - APEX COLLECTION

"APEX collections" is one of the most important features that come with Oracle APEX, and it can be used in many ways for different purposes, but in general, you can think of Apex collection as a temporary space to store complex data types — nonscalar — for instance, full row of data from a table or a view.

This data can be manipulated in any way and then stored in table(s). Each Apex Collection can can has 50 varchar2 attributes (c001 -> c0050), 5 number attributes (n001->n005), 5 date attributes (d001->d005), 1 CLOB attribute (clob001), and 1 BLOB attribute (blob001). Each collection must have a unique name. 

I will only show some basic steps. For a more detailed explanation please follow the official APEX documentation

Create an APEX_COLLECTION:

In this example I check if my collection exists and if not then I will create a new one and add some rows. 

begin
 if not apex_collection.collection_exists('DATA_COLLECTION') then 
  apex_collection.create_collection('DATA_COLLECTION');
  apex_collection.add_member(
    p_collection_name => 'DATA_COLLECTION',
    p_c001 =>            '1',
    p_c002 =>            'APEX & SQL: THE Reporting Solution',
    p_c003 =>            'Grundsätzlich soll mit der...',
    p_c004 =>            '2015',
    p_c005 =>            'https://apex.oracle.com/pls/apex/f?p=55360:1'
  );
  apex_collection.add_member(
    p_collection_name => 'DATA_COLLECTION',
    p_c001 =>            '2',
    p_c002 =>            'Plug-Ins maßgerecht verwenden',
    p_c003 =>            'Wann macht ein Plugin Sinn...',
    p_c004 =>            '2015',
    p_c005 =>            'https://apex.oracle.com/pls/apex/f?p=80307:1'
  );
 end if;
end;

Next is to select the data from inside an APEX report:

select 
    c001,
    c002,
    c003,
    c004,
    c005
  from apex_collections
  where collection_name = 'DATA_COLLECTION'

And finally I want to check if I get the same results inside my SQL Developer:

-- Set up APEX SESSION

declare
  v_ws_id number;
  v_app_id number := &app_id;
  v_session_id number := &session_id;
begin
  select workspace_id into v_ws_id from apex_workspaces;
  wwv_flow_api.set_security_group_id(v_ws_id);
  wwv_flow.g_flow_id := v_app_id;
  wwv_flow.g_instance := v_session_id;
end;
/
-- Execute SQL
select *
from apex_collections
where collection_name = 'DATA_COLLECTION';


Dynamic Column Attributes FORM using APEX_ITEM

Use the below SQL in Simple Report APEX Region. 

SELECT LEVEL AS item_no,
APEX_ITEM.TEXT(p_idx => 1, p_attributes => NULL) AS Name,
APEX_ITEM.text( p_idx=> 2 , p_value=> null , p_attributes=> 'class="number"') as Age,
APEX_ITEM.SELECT_LIST(  p_idx        =>3,
                        p_value       => 'MALE',
                        p_list_values => 'MALE;MALE,FEMALE;FEMALE'
--                        p_attributes  in varchar2 default null,
--                        p_show_null   in varchar2 default 'NO',
--                        p_null_value  in varchar2 default '%null%',
--                        p_null_text   in varchar2 default '%',
--                        p_item_id     in varchar2 default null,
--                        p_item_label  in varchar2 default null,
--                        p_show_extra  in varchar2 default 'YES'
                        ) Gender,
APEX_ITEM.TEXT(p_idx => 4, p_attributes => NULL) AS School
FROM dual
CONNECT BY LEVEL <= TO_NUMBER (:P9_HOW_MANY);

This will create form elements dynamically based on the SQL Query instead of creating individual items page by page.

Comments

Post a Comment

Popular posts from this blog

Oracle BI Publisher Tags For-each grouping for Matrix report

For-Each Extensions For-each tags can be confusing.  We will be going over the following tags:     For-each@ section :    For-each@ cell   For-each- group :   field1; field2;     For-each-group: current-group() ;field3;   For-each-group@ column       Sample XML:  <CORPORATION>      <NAME>Vision</NAME>     <DEPT>     <DEPT_NAME>IT</DEPT_NAME>      <EMPLOYEE>         <EMPNO>6</EMPNO>        <ENAME>Rich Walker</ENAME>        <JOB>CLERK</JOB>        <SAL>100</SAL>     </<EMPLOYEE>>     <EMPLOYEE>        ...
Read more

SQL Scripts for Custom Table Creation in Oracle Application 12.2.x

Due to introduction of Online Patching, Post 12.2.x version, there are certain steps to be followed while creating Custom Table. 1. For new table creations - after creating the table in custom schema execute below script to generate edition view and synonym for it in APPS schema. exec AD_ZD_TABLE.UPGRADE('xxcust_schema','xxcust_Table'); 2. For table alterations - after running the DDL run below script to regenerate the edition view for synchronizing any table changes. exec AD_ZD_TABLE.PATCH('xxcust_schema','xxcust_table') 3. Table grants must be done through API to avoid invalids. exec AD_ZD.GRANT_PRIVS('SELECT','xxcust_table,'xx_role') 4. To view objects in all editions, add "_ae" to the data dictionary views select * from user_objects_ae; 5. To update seed data tables in Patch edition, execute prepare command. example:  exec ad_zd_seed.prepare('WF_MESSAGES');  The prepare Procedure will creat...
Read more

Oracle Fusion Business Intelligence Roles

Oracle Business Intelligence roles apply to both Oracle Business Intelligence Publisher and Oracle Fusion Transactional Business Intelligence. They grant access to Business Intelligence functionality, such as the ability to run or author reports. These roles are in addition to the roles that grant access to reports, subject areas, Business Intelligence catalog folders, and HCM data. This topic describes the Business Intelligence roles. This table lists the Business Intelligence roles. Business Intelligence Role Description BI Consumer Role Runs Business Intelligence reports. BI Author Role Creates and edits reports. BI Administrator Role Performs administrative tasks such as creating and editing dashboards and modifying security permissions for reports, folders, and so on. BI Publisher Data Model Developer Role Creates and edits Business Intelligence Publisher data models. BI Consumer Role The predefined Transactional Business Intelligence Transacti...
Read more