I love the flexibility of Javascript and how it’s easy to overwrite existing code either by reusing the same function name or using framework wrappers like Ext.override. We use custom APEX templates and centralized generic PLSQL packages to integrate Ext widgets into APEX, the downside is that these packages can be too generic, i.e. for a Tree widget we need to handle specific actions like drag and drop re-ordering of the tree nodes and saving these changes to the database. Now in some instances we may base the tree data on a non-updateable view as we may want to disable the ability to reorder certain parts of the tree, so we use a UNION style query with some additional config to pass through when creating the Ext JS tree
e.g.
CREATE OR REPLACE FORCE VIEW PORTAL_LINKS_NAV_VW
("ID", "PID", "NAME", "LINK", "A1", "A2") AS
SELECT f.form_id id
, '12345' pid
, f.form_name name
, 'f?p=&APP_ID.:SSO:&APP_SESSION.::::P30_FORM_ID:'||f.form_id link
, extjs_utils.asciiEscape(flex_01) a1
, '{ "allowDrag": false, "allowDrop": false }' a2
FROM portal_sso_forms f
WHERE f.enabled = 'Y'
UNION
SELECT '12345'
, NULL
, 'Single Sign On'
, NULL
, NULL
, '{ "allowDrag": false, "allowDrop": false }'
FROM dual
UNION
SELECT to_char(link_id) id
, to_char(nvl(parent_link_id,54321)) pid
, link_name name
, link_url link
, NULL a1
, NULL a2
FROM portal_nav_links
WHERE user_id = portal_security.get_user_id(v('APP_USER'))
OR user_id IS NULL
UNION
SELECT '54321'
, NULL
, 'General Links'
, NULL
, NULL
, NULL
FROM dual
ORDER BY 3;
Normaly we query the APEX data dictionary and use DBMS_SQL to describe the tree query which gives us all the information we need to update the base table data for a reordering event. However we can see from the above query definition there is no way we could update the view (well technically we could use an instead of trigger but that defeats the purpose of this post). So we need some custom functionality to handle a tree re-order event. Instead of writing a complete separate custom widget simply for this purpose what we did was when the widget re-order event came through our widget handler we performed a lookup using the database data dictionary to check for a PLSQL override
e.g.
PROCEDURE widget
( p_widget_name IN VARCHAR2 DEFAULT NULL
, p_widget_mod IN VARCHAR2 DEFAULT NULL
, p_widget_action IN VARCHAR2 DEFAULT NULL
, p_widget_action_mod IN VARCHAR2 DEFAULT NULL
, p_widget_num_return IN VARCHAR2 DEFAULT NULL
, x01 IN VARCHAR2 DEFAULT NULL
, x02 IN VARCHAR2 DEFAULT NULL
, x03 IN VARCHAR2 DEFAULT NULL
, x04 IN VARCHAR2 DEFAULT NULL
, x05 IN VARCHAR2 DEFAULT NULL
, x06 IN VARCHAR2 DEFAULT NULL
, x07 IN VARCHAR2 DEFAULT NULL
, x08 IN VARCHAR2 DEFAULT NULL
, x09 IN VARCHAR2 DEFAULT NULL
, x10 IN VARCHAR2 DEFAULT NULL
) AS
l_exists BOOLEAN DEFAULT FALSE;
l_proc VARCHAR2(200);
BEGIN
CASE p_widget_name
WHEN 'Ext.tree.ajax.reorder' THEN
--
-- We will check to see if there is an override procedure to execute
--
FOR c IN
( SELECT *
FROM user_procedures
WHERE object_name = 'EXT_PLSQL_OVERRIDES'
AND procedure_name = 'TREE_REORDER_'||p_widget_mod
) LOOP
l_exists := TRUE;
l_proc := c.object_name ||'.'|| c.procedure_name;
END LOOP;
IF l_exists THEN
EXECUTE IMMEDIATE 'BEGIN '||l_proc||'(:b1,:b2); END;' USING x01, x02;
htp.p('{ "message": "The node was updated successfully!", "success": true}');
ELSE
extjs_widgets.tree_reorder
( p_widget_name => p_widget_name
, p_widget_mod => p_widget_mod
, p_widget_action => p_widget_action
, p_widget_action_mod => p_widget_action_mod
, p_widget_num_return => p_widget_num_return
, x01 => x01
, x02 => x02
, x03 => x03
, x04 => x04
, x05 => x05
, x06 => x06
, x07 => x07
, x08 => x08
, x09 => x09
, x10 => x10
);
END IF;
ELSE
.....
END CASE;
END widget;
create or replace
PACKAGE BODY EXT_PLSQL_OVERRIDES AS
PROCEDURE tree_reorder_18897225474833114
( p_node_id IN VARCHAR2
, p_new_parent_id IN VARCHAR2)
AS
BEGIN
UPDATE portal_nav_links
SET parent_link_id = p_new_parent_id
WHERE link_id = p_node_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Custom exception logging code goes here
-- e.g. logger.log(SQLERRM,'EXT_PLSQL_OVERRIDES.tree_reorder_18897225474833114');
htp.p('{"success":false, "message": "'||extjs_utils.asciiEscape(sqlerrm)||'"');
END tree_reorder_18897225474833114;
END EXT_PLSQL_OVERRIDES;
So what this allows us to do is cater for specific functionality for generic widgets. We basically code the ability for the developer to override any generic functionality we declare in PLSQL (Note: our PLSQL packages use “AUTHID CURRENT_USER” so when we check for the presence of the overrides package it will be in the APEX application parsing schema). The only downside from the above approach is that we hard code region id’s. This means that we have to retain the APPLICATION_ID for the lifetime of the application. To workaround this issue we use a high range of application id’s which we don’t expect to be reused by anyone (we hope).
The concept above is not to really aimed to illustrate our integration of Ext and APEX but more so to show you the benefit you can get by using a combination of the Database Data Dictionary and APEX Data Dictionary. Combining both will allow you to create some pretty cool dynamic functionality.
