A while back we posted on adding grid filters to our APEX Ext Grid template by simply setting attributes under the “Column Definition” setting in the report definition.
In order to support grid filters on our APEX grid/report we need to create a number of hidden items on the page to filter the query by. e.g. here’s a query example
SELECT geoname_id , name , modification_date , latitude , longitude , population FROM geoname WHERE (modification_date = to_date(:P9_EXT_MODIFICATION_DATE_EQ, 'DD-MON-YYYY') OR9_EXT_MODIFICATION_DATE_EQ IS NULL) AND instr(upper("NAME"),upper(nvl(:P9_EXT_NAME,"NAME"))) > 0 AND (geoname_id =
For string filters we simply do a “like” comparison or “soundex” so we only require 1 hidden item. However for date and numeric filters we have a number of options, “Less Than / Before”, “Equal To / On”, and “Greater Than / After” so we need three hidden items for the one column filter.
We are faced with two options, we can either create these hidden items manually…. boring! or we can use the APEX data dictionary and API to dynamically create them using our own custom PLSQL. This is where APEX really gets really interesting, we can use wwv_flow_api to generate the items as well as a region to hold them. To make it that little bit sweeter, we’ll also add the PLSQL call to an application process that runs before header (this gives us the benefit of being within the APEX runtime engine), which means that for every new grid page we create, when we view the page in runtime mode (first time only) it will auto create the items for us! When it comes time for our production release we will simply set the condition of the application level process to “Never” to avoid any unnecessary processing.
When we create the hidden items, we need work out the data type for the column filter and create the necessary supporting hidden items which will appear in our query filter. We simply use the APEX data dictionary to get the report items with filtering enabled and then query the databae data dictionary to get the data type in order to work out what hidden items we need to generate.
Here’s an extract of our PLSQL…
IF NOT v_region_exists THEN
vId := wwv_flow_id.next_val;
vPlugId := vId;
wwv_flow_api.set_version(wwv_flow_api.g_compatable_from_version);
wwv_flow_api.create_page_plug
( p_id => vId
, p_flow_id => v('APP_ID')
, p_page_id => v('APP_PAGE_ID')
, p_plug_name => 'APExtJS Hidden Items'
, p_region_name => 'APExtJS Hidden Items'
, p_plug_display_sequence => '10000'
, p_plug_template => 0
);
END IF;
--
-- Lets have a look at the current page and see if there is a apex report
-- defined and filtering enabled by setting the reference table fields
-- if so we will create a hidden item if one does not exist
--
FOR c IN (
SELECT v.column_alias
, v.reference_schema
, v.reference_table_name
, v.reference_column_name
FROM apex_application_page_rpt_cols v
WHERE v.application_id = v('APP_ID')
AND v.page_id = v('APP_PAGE_ID')
AND upper(v.column_alias) <> 'TOTALROWS'
AND v.reference_schema IS NOT NULL
AND v.reference_table_name IS NOT NULL
AND v.reference_column_name IS NOT NULL
AND NOT EXISTS (SELECT item_name
FROM apex_application_page_items
WHERE application_id = v.application_id
AND page_id = v.page_id
AND (item_name = 'P'||v('APP_PAGE_ID')||'_EXT_'||v.column_alias
OR item_name = 'P'||v('APP_PAGE_ID')||'_EXT_'||v.column_alias||'_LT'
OR item_name = 'P'||v('APP_PAGE_ID')||'_EXT_'||v.column_alias||'_EQ'
OR item_name = 'P'||v('APP_PAGE_ID')||'_EXT_'||v.column_alias||'_GT'
)
)
ORDER BY v.region_id, v.display_sequence
) LOOP
FOR c1 IN (SELECT data_type
FROM all_tab_columns
WHERE owner = c.reference_schema
AND table_name = c.reference_table_name
AND column_name = c.reference_column_name)
LOOP
IF c1.data_type IN ('VARCHAR2','CLOB','NVARCHAR2','NCLOB','CHAR') THEN
--
-- The following was taken from John Scotts book Pro Application Express
-- page 675
--
vId := wwv_flow_id.next_val;
wwv_flow_api.set_version(wwv_flow_api.g_compatable_from_version);
wwv_flow_api.create_page_item
( p_id => vId
, p_flow_id => v('APP_ID')
, p_flow_step_id => v('APP_PAGE_ID')
, p_display_as => 'HIDDEN'
, p_item_plug_id => vPlugId
, p_name => 'P' || v('APP_PAGE_ID') || '_EXT_' || c.column_alias
);
ELSIF c1.data_type IN ('DATE','NUMBER','FLOAT') THEN
--
-- The following was taken from John Scotts book Pro Application Express
-- page 675
--
vId := wwv_flow_id.next_val;
wwv_flow_api.set_version(wwv_flow_api.g_compatable_from_version);
wwv_flow_api.create_page_item
( p_id => vId
, p_flow_id => v('APP_ID')
, p_flow_step_id => v('APP_PAGE_ID')
, p_display_as => 'HIDDEN'
, p_item_plug_id => vPlugId
, p_name => 'P' || v('APP_PAGE_ID') || '_EXT_' || c.column_alias ||'_LT'
);
If you interested in the dynamic generation of APEX components then you should have a look at the APEXGEN package, though it is a little disappointing it hasn’t been updated since it’s initial release, but the concept is fantastic. It won’t be long before we will be creating APEX applications with an Ext interface just using an ER model generated by SQL Developer’s new modeling tool. Of course there are a lot of code generators out there but none the likes of APEX with it’s excellent customization framework, so watch this space!
