Salah satu kelemahan Interactive Report (IR) adalah hanya menyediakan type source SQL tidak ada pilihan untuk PL/SQL. Sehingga kita tidak bisa membuat dynamic query seperti pada classic report.
Namun hal tersebut masih kita bisa cari solusinya dengan cara :
- Membuat function return table pipelined atau.
- Memakai bantuan query terhadap APEX collection.
Pada posting saya ini akan menjelaskan pada teknik kedua yaitu
Memakai bantuan query terhadap APEX collection.
Skenario :
- Kita akan membuat IR thd semua tabel yang ada di skema HR untuk contoh demo (EMP & OEHR_EMPLOYEES).
- Kita hanya membatasi max kolom sejumlah 10.
- Skema akses apex memiliki system privilege : SELECT ANY DICTIONARY.
Logic & Process :
- Membuat sebuah fungsi yg secara dinamis bisa me-return 10 nama kolom tabel input.
create or replace
function get_report_headers
(
p_table in varchar2 default 'EMP'
, p_separator in varchar2 default ','
, p_type in varchar2 default 'HEADER'
, p_num in number default 1
) return varchar2 as
v_temp_arr apex_application_global.vc_arr2;
v_list varchar2(32767);
begin
if p_type = 'HEADER' then
SELECT column_name bulk collect
into v_temp_arr
FROM USER_TAB_COLUMNS
WHERE owner = 'HR'
and table_name = p_table
and owner = 'HR'
and data_type <> 'BLOB'
order by column_id;
v_list := apex_util.table_to_string(v_temp_arr,p_separator);
return v_list;
else
if p_num > 10 then
v_list := null;
else
begin
SELECT initcap(REPLACE(column_name,'_',' '))
into v_list
FROM USER_TAB_COLUMNS
WHERE owner = 'HR'
and table_name = p_table
and owner = 'HR'
and column_id = p_num;
exception when others then v_list := null;
end;
end if;
return v_list;
end if;
end get_report_headers;
Buatlah sebuah page dengan 10 item dan sebuah IR.
Pada before header buatlah sebuah process untuk membuat sebuah collection unik.
declare
v_query VARCHAR2(32767):=null;
v_collection varchar2(100) := nvl(:P20_COLL_NAME,'DYNAMIC_'||:SESSION_ID);
begin
v_query := 'select '||
get_report_headers(p_table => :P20_TABLE_NAME) ||
' from HR.' || nvl(:P20_TABLE_NAME,'EMP');
IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => v_collection) THEN
APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => v_collection);
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
,p_query => v_query );
else
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
,p_query => v_query );
end if;
end;
Pada before header buatlah sebuah process mengisi 10 item yang telah dipersiapkan untuk nama header kolom.
:P20_C01 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 1);
:P20_C02 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 2);
:P20_C03 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 3);
:P20_C04 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 4);
:P20_C05 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 5);
:P20_C06 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 6);
:P20_C07 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 7);
:P20_C08 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 8);
:P20_C09 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 9);
:P20_C10 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 10);
Buatlah IR :
select *
from APEX_collections
where collection_name = :P20_COLL_NAME;
Buatlah heading column secara dinamis me-refer ke item serta kondisi thd nilai item reference (C001...C010).
Buatlah semua kolom condition : never (selain C001...C010).
Demo :
Dynamic IR with collection
