In addition to the granting of reports they also require a few changes to the IR charting feature. (this also applies to 4.0)

• Adding a title to the chart
• Adding the point values to the chart
• Changing the label rotation for the vertical bar chart
• Changing colours
• Adding in drill own links to the data

So to devise a workaround, back in February (this is when the customer first requested the change, approval to start came in November, they move quickly I wrote a theoretical approach to try and override the XML chart output.

Putting this theory into practice this week I came up with the following code prototype (Unfortunately I’m not able to provide the final solution for commercial reasons). It should give you a good idea of what needs to occur, I’d recommend making a few changes to the XML_CHART_API package as it’s only written to give you an idea of what to do….

create or replace
PROCEDURE FLASH3
( p                     IN VARCHAR2
, preloaderLoadingText  IN VARCHAR2 DEFAULT NULL
, swffile               IN VARCHAR2 DEFAULT NULL
, preloaderInitText     IN VARCHAR2 DEFAULT NULL
, XMLCallDate           IN VARCHAR2 DEFAULT NULL
)
--
-- URL rewriting See - http://www.yourhtmlsource.com/sitemanagement/urlrewriting.html
--
-- RewriteEngine on
-- RewriteRule ^pls/apex/apex_util.flash2(.*) /pls/apex/flash3$1
--
-- grant execute on #OWNER#.flash3 to apex_public_user;
-- create public synonym flash3 for #OWNER#.flash3;
--
-- update wwv_flow_epg_include_mod_local
--
AS
  v_out_buf         htp.htbuf_arr;
  v_out_row_count   NUMBER := 99999999;
  v_clob            CLOB := empty_clob();
  v_xml_start_found BOOLEAN DEFAULT FALSE;

  PROCEDURE htp_buf_prn
  ( p_text              IN CLOB
  ) AS
    l_buflen  INTEGER := 256;
  BEGIN
    FOR i IN 0 .. FLOOR(LENGTH(p_text)/l_buflen) LOOP
      sys.htp.prn(substr(p_text, i * l_buflen + 1, l_buflen));
    END LOOP;
  END htp_buf_prn;

BEGIN
  dbms_lob.createtemporary(lob_loc => v_clob, cache => true, dur => dbms_lob.call);

  apex_util.flash2( p                     => p
                  , preloaderLoadingText  => preloaderLoadingText
                  , swffile               => swffile
                  , preloaderInitText     => preloaderInitText
                  , XMLCallDate           => XMLCallDate
                  );
  owa.get_page(v_out_buf, v_out_row_count);
  htp.init;

  FOR i IN 1..v_out_row_count LOOP
    --
    -- Lets look for the start of our XML document and ignore any of the mime header
    -- X-ORACLE-IGNORE etc.
    --
    IF instr(v_out_buf(i), '<?xml') > 0 OR v_xml_start_found THEN
      dbms_lob.writeappend(lob_loc => v_clob, amount => length(v_out_buf(i)), buffer => v_out_buf(i));
      v_xml_start_found := TRUE;
    END IF;
  END LOOP;
  xml_chart_api.chart_mods
  ( p_in_out_chart_xml => v_clob
  );
  htp_buf_prn(v_clob);

END FLASH3;
/
create or replace
PACKAGE XML_CHART_API AS 

  PROCEDURE chart_mods
  ( p_in_out_chart_xml IN OUT NOCOPY CLOB
  );

END XML_CHART_API;
/
create or replace
PACKAGE BODY XML_CHART_API AS

  PROCEDURE chart_mods
  ( p_in_out_chart_xml IN OUT NOCOPY CLOB
  )
  AS
    l_xml     XMLType;
    doc       dbms_xmldom.DOMDocument;
    nl        DBMS_XMLDOM.DOMNodeList;
    n         DBMS_XMLDOM.DOMNode;
    n2        DBMS_XMLDOM.DOMNode;
    l_node    DBMS_XMLDOM.DOMNode;
    l_attr    DBMS_XMLDOM.DOMAttr;
    e         DBMS_XMLDOM.DOMElement;
    e2        DBMS_XMLDOM.DOMElement;

    len       NUMBER;
    l_chart   VARCHAR2(100);

  BEGIN
    -------------------------------
    -- Turn our clob into XML doc
    -------------------------------

    l_xml := XMLType.createXML(p_in_out_chart_xml);
    doc   := dbms_xmldom.newDOMDocument(l_xml);

    -------------------------------
    -- Get the report type
    -------------------------------
    nl   := DBMS_XMLDOM.getElementsByTagName(doc, 'chart');
    len  := DBMS_XMLDOM.getLength(nl);

    -- loop through elements (should only be 1
    FOR i IN 1 .. len LOOP
        n := DBMS_XMLDOM.item(nl, i-1);
        e := DBMS_XMLDOM.makeelement(n);
        --
        -- We need to know what type of chart, as label rotation is only
        -- required for vertical chart
        --
        l_chart := DBMS_XMLDOM.getAttribute(e, 'plot_type');
    END LOOP;

    -------------------------------
    -- Add a report drill down link
    -------------------------------
    nl   := DBMS_XMLDOM.getElementsByTagName(doc, 'point');
    len  := DBMS_XMLDOM.getLength(nl);

    -- loop through elements
    FOR i IN 1 .. len LOOP
        n := DBMS_XMLDOM.item(nl, i-1);
        e2 := DBMS_XMLDOM.makeelement(n);
        e:= DBMS_XMLDOM.createElement( doc, 'actions' );
        --sys.htp.p('Debug: adding label');
        n2 := DBMS_XMLDOM.makeNode(e);
        e:= DBMS_XMLDOM.createElement( doc, 'action' );
        DBMS_XMLDOM.setAttribute(e, 'type', 'navigateToURL');
        --
        -- Basic example of how to build up the URL for report drill down
        -- this is where I would hand off to another function with the page number
        -- and value to work out what items need setting in the URL
        --
        DBMS_XMLDOM.setAttribute(e, 'url', 'f?p='||v('APP_ID')||':1:'||v('APP_SESSION')||':'||DBMS_XMLDOM.getAttribute(e2,'name')||'_'||DBMS_XMLDOM.getAttribute(e2,'y'));
        --
        -- Lets open the drill down in a new window
        --
        DBMS_XMLDOM.setAttribute(e, 'target', '_blank');
        l_node := DBMS_XMLDOM.appendChild(n2,dbms_xmldom.makeNode(e));
        l_node := DBMS_XMLDOM.appendChild(n,n2);

    END LOOP;
    -------------------------------
    -- Label Rotation & alignment
    -------------------------------
    nl   := DBMS_XMLDOM.getElementsByTagName(doc, 'labels');
    len  := DBMS_XMLDOM.getLength(nl);

    -- loop through elements
    FOR i IN 1 .. len LOOP
        n := DBMS_XMLDOM.item(nl, i-1);
        -- cast our node into an element which we can manipulate
        e := DBMS_XMLDOM.makeelement(n);
        --DBMS_XMLDOM.setAttribute(e, 'display_mode', 'rotated');
        CASE l_chart
          WHEN 'CategorizedHorizontal' THEN
            DBMS_XMLDOM.setAttribute(e, 'rotation', '0');
          ELSE
            DBMS_XMLDOM.setAttribute(e, 'rotation', '270');
        END CASE;
        DBMS_XMLDOM.setAttribute(e, 'align', 'inside');

    END LOOP;

    -------------------------------
    -- Add point values
    -------------------------------
    nl   := DBMS_XMLDOM.getElementsByTagName(doc, 'series');
    len  := DBMS_XMLDOM.getLength(nl);

    -- loop through elements
    FOR i IN 1 .. len LOOP
        n := DBMS_XMLDOM.item(nl, i-1);
        e:= dbms_xmldom.createElement( doc, 'label' );
        DBMS_XMLDOM.setAttribute (e, 'enabled', 'true');

        l_node := dbms_xmldom.appendChild(n,dbms_xmldom.makeNode(e));
    END LOOP;
    DBMS_XMLDOM.writeToClob(doc,p_in_out_chart_xml);
    DBMS_XMLDOM.freeDocument(doc);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_XMLDOM.freeDocument(doc);
      sys.htp.p(SQLERRM);
  END chart_mods;

END XML_CHART_API;
/

Additional Setup

1. Apache mod_rewrite rule:
   #
   # change the PLS handler to the one defined for your DB
   #
   RewriteEngine on
   RewriteRule ^pls/apex/apex_util.flash2(.*) /pls/apex/flash3$1
2. grant execute on #OWNER#.flash3 to apex_public_user
3. create public synonym flash3 for #OWNER#.flash3;
4. update #APEX_SCHEMA#.wwv_flow_epg_include_mod_local function to include FLASH3
   e.g.
   if upper(procedure_name) in (
   ‘#OWNER#.FLASH3′,’FLASH3′) then
   return TRUE;

http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/

So before I can really get started I needed a function to do this. I did see an example in the comments but I was looking for something that could be reused for other purposes.

I looked at dbms_utility.comma_to_table but it had too many limitations and then at regex but unfortunately Oracle doesn’t support look ahead assertion, so after 30mins of frustration I jumped back into PLSQL and came up with the following after getting some inspiration from the following http://www.oratechinfo.co.uk/delimited_lists_to_collections.html:

create or replace
FUNCTION f_csv_convert_to_table
( p_in_string       IN VARCHAR2
, p_in_encapsulator IN VARCHAR2 DEFAULT '"'
)
RETURN wwv_flow_global.vc_arr2
  AS
    l_string              VARCHAR2(32767) := p_in_string || ',';
    l_quote_start_index   PLS_INTEGER := 0;
    l_quote_end_index     PLS_INTEGER := 0;
    l_comma_index         PLS_INTEGER;
    l_index               PLS_INTEGER := 1;
    l_tab                 wwv_flow_global.vc_arr2;
    i                     PLS_INTEGER := 1;
  BEGIN
    LOOP
      l_comma_index := REGEXP_INSTR(l_string, '[,'||p_in_encapsulator||']', l_index);
      EXIT WHEN l_comma_index = 0;

      CASE
        WHEN SUBSTR(l_string,l_comma_index,1) = p_in_encapsulator AND l_quote_start_index = 0 THEN
          l_quote_start_index := l_comma_index;
        WHEN SUBSTR(l_string,l_comma_index,1) = p_in_encapsulator AND l_quote_start_index <> 0 THEN
          l_quote_end_index   := l_comma_index;
        ELSE
          NULL;
      END CASE;

      IF l_quote_start_index = 0 THEN

        l_tab(i) := SUBSTR(l_string, l_index, l_comma_index - l_index);
        i := i+1;

      ELSIF l_quote_start_index <> 0 AND l_quote_end_index <> 0 THEN

        l_tab(i) := SUBSTR(l_string, l_quote_start_index+1, (l_quote_end_index - l_quote_start_index)-1);
        i := i+1;
        --
        -- Lets reset our quote check
        --
        l_quote_start_index := 0;
        l_quote_end_index   := 0;
        --
        -- We need to discard our end comma
        --
        l_comma_index := INSTR(l_string, ',', l_index);
      END IF;
      l_index := l_comma_index + 1;
    END LOOP;
    RETURN l_tab;
END f_csv_convert_to_table;

Now the above only works correctly if the encapsulator is matched i.e. start and end around fields. It will also continue to work if there are line breaks between your encapsulated fields. I’m sure someone can come up with a better solution, but this is all I need at the moment.

In Advait’s post I plan to swap the line

v_data_array := wwv_flow_utilities.string_to_table (v_line);

with

v_data_array := f_csv_convert_to_table (v_line);

Just to keep things simple.

Now back to the task at hand and changing it be completely generic using collections. I’ll follow up with another post with the entire solution in the coming week or so, when time permits.

I thought about popups, using a modal dialog etc. this is in APEX 3.2 btw. but none of this was simple and straight forward with minimal effort. So whilst I was thinking of possibilities I remembered previously using the “BRANCH_TO_PAGE_ACCEPT” request value which gave me the idea for the following simple solution.

On submit of the page I would have a branch that executes “On Submit: Before Processing: (After Computation, Validation and Before Processing)” with a SQL exists condition which checks for the duplicate email address and if so redirects the user to this newly created report page listing all the users with duplicate email addresses. On this page I have button labelled “Create User” which allows them to continue creating the user but first redirects to some Javascript similar to confirmDelete which prompts the user to confirm that they do indeed wish to create the user. On click of Ok they are then redirected back to the create user page with the following REQUEST value in the branch “BRANCH_TO_PAGE_ACCEPT” which automatically tells APEX to ignore the rendering phase and simply process the page. This means that I was able to reuse the entire functionality of the create users page to perform all the processing as all the values were still recorded in session state from the initial page submit when the user clicked “CREATE”.

However setting this request value is simply not enough since the Automatic DML row processing (and my PLSQL processes) requires a valid REQUEST value like “INSERT, CREATE, CREATE_AGAIN, CREATEAGAIN”. So to work around this issue I used the following PLSQL process (which runs before every other process) to switch the request value.

wwv_flow.g_request := 'CREATE';

I simply set a condition of “Request = Expression 1″ with a value of “BRANCH_TO_PAGE_ACCEPT” and my page is successfully processed and the user added. The thing I like about this sort of approach is that we can conditionally change the REQUEST value on the fly which means that we can attribute some logic to multiple independent PSLQL processes. We can update the REQUEST value in flight to either execute the next process or skip it. Please Note: this is not best practice, and will open up debugging issues, but it does have its uses here and there. It’s all about solving problems, and this one today was solved rather painlessly with a neat little trick.

Now I’m no expert in any of these roles, well I’d like to think I’m a good DBA and developer…. the others though, I do the best I can. I landed the graphic design role simply because I know how to spell Photoshop (I recently had to create some images for our company business cards believe it or not… I can’t really myself, considering that I was doing just DBA duties 2 years ago).

I’m required to beautify and custom theme the applications we build. Now I really don’t have much artistic flair, my talent is copying and modifying. My first port of call was to either purchase a template from a site like template monster, if the app is a website style, or consider purchasing something from APEX Themes. The new charlie/delta theme series allows you to custom theme them with jQuery UI. Very smart on their side to put the onus back on the developer to skin the theme to their own requirements using Theme Roller.

However these days I don’t really have that requirement since we use Ext JS. There’s no real requirement to theme the applications as most customers are happy with just using their company logo and sticking with either the blue or grey theme…. well except for login pages. Hence the title of this post…

Now the cheating part about designing login pages is that: if you don’t require anything but a username/password with a couple of additional links, then I’d recommend the following: Create a background image that gives you the cool look that you’re striving for and simply center your form on the background image. Here’s an example for the login page for our Sales team’s licensing application, Note: the positioning of the background and form always stay in place on page resizing.

Here’s the CSS to position your login form and background image (I normally set “No Template” on my page 101 login form).

html, body {
	font:normal 12px verdana;
	padding:0;
	border:0 none;
	overflow:hidden;
	height:100%;
}
body {
	padding: 0px;
	background-image:url(&LOGIN_BACKGROUND_IMAGE.);
	background-repeat:repeat;
	background-position:center;
	background-attachment: fixed;
	text-align:center;
	margin:0 auto;
	vertical-align:middle;
}
#Messages {
        width:345px;
        margin-left:-180px;
        position:absolute;
        top:50%;
        left:50%;
        margin-top:-35px;
}
#BoxBody {
        width:345px;
        margin-left:-170px;
        position:absolute;
        top:50%;
        left:50%;
        margin-top:30px;
}

And here’s the page template

<div id="Messages">#GLOBAL_NOTIFICATION##SUCCESS_MESSAGE##NOTIFICATION_MESSAGE#</div>
<div id="BoxBody">
  <div align="center">#REGION_POSITION_01##BOX_BODY##REGION_POSITION_02##REGION_POSITION_04#</div>
</div>

You can download the .psd file here. For non-photoshop people you can download the .png here.

Currently I have a customer who requires a security change to their application, which affects most of the LOV definitions defined on most of the pages. The task of editing these through the APEX IDE does not excite me at all. Being the lazy developer that I am, it’s much much simpler making these changes at the backend. (As long as you have access to update data in the APEX schema). Now I’m sure most experienced APEX developers make changes through the backend rather than the frontend, especially if you need to make a consistent change across a number of pages. It’s less error prone to do this in SQL than it is to click click click paste and click.

A point I’d like to highlight with making backend updates is that using consistent naming convention can allow you to quickly update more items in a single UPDATE statement. I’m not saying that you couldn’t update an irregular naming convention with the use of regex in your update statement, it’s just going to take you longer and would require a few more tests to get it right. That is unless you’re a regex master…. me? no!

Note: before you manipulate your apps using SQL, all the obvious apply e.g. backup your applications, db, enable flashback, issue a rollback before you exit SQL*Plus after an unsuccessful change etc. etc. Just in case you screw something up, we’ve all been there before…. myself included!

So one of the tasks at hand is to update a district LOV definition (it has a foreign key which references a company table). On each of the pages the district appears three times for a “submitter”, “assignee” and “location”. the standard naming convention used was to have DISTRICT_ID in each of these three different page items (the same goes for COMPANY_ID). Therefore it made it quick and easy to write the following UPDATE statement to change the LOV definition.

e.g.

UPDATE apex_030200.wwv_flow_step_items
SET    lov = 'SELECT district_name, district_id
FROM   district
WHERE  company_id = :'||replace(name,'DISTRICT','COMPANY')||'
AND    (soft_deleted = ''N'' OR (:P'||flow_step_id||'_REQUEST = ''EDIT'' AND :'||name||' = district_id))
ORDER BY 1'
,      lov_display_null = 'YES'
,      lov_null_text = 'Please Select ...'
WHERE  name like '%_DISTRICT_ID%'
AND    flow_id =702 and flow_step_id IN (2,26,29,39,47,55,61,137)

Now this is just a simple test case for one type of LOV, I have roughly about 200+ items to change in the application as they have page specific binds defined in them. So rather than using the APEX IDE I can complete this task in minutes as opposed to hours (which I don’t think my brain could take)….

Now if only I could find a quick way to test these mass changes as I’m yet to find a similar productivity boost? Any advice would be most welcome…..

e.g. original code:

  procedure add_to_clob2(buf_lob in out nocopy clob, buf_str in out nocopy varchar2, str CLOB) as
  begin
    dbms_lob.append(buf_lob, buf_str);
    dbms_lob.writeappend(buf_lob,length(str),str);
    buf_str := '';
  end add_to_clob2;

and here’s the correct version, which I just ammended today (including the original blog post)….

  procedure add_to_clob2(buf_lob in out nocopy clob, buf_str in out nocopy varchar2, str CLOB) as
  begin
    dbms_lob.append(buf_lob, buf_str);
    dbms_lob.copy(buf_lob,str,dbms_lob.getlength(str),dbms_lob.getlength(buf_lob)+1,1);
    buf_str := '';
  end add_to_clob2;

The procedure dbms_lob.writeappend only supports appending VARCHARS, so it was implicitly converting the CLOB to VARCHAR2. Rookie mistake, I should check the documentation more closely. It did lift the restriction in PLJSON from 4K to 32K which is why it took me some time to stumble across the mistake….

The other thing I came across today were a few quirks with APEX 4.0 which I’ve found slightly annoying (this probably affects me more so than others as I access my APEX dev environments over SSH in another country) is that when you have a little time lag and the page is rendering, you can start interacting on the page before it fully loads. This becomes a problem when you’re editing a page and using the hide show toolbar. You may start editing a field on the page only to find that when the page finally loads it jumps to your show only region. Sometimes I just forget that I have that hide/show toolbar enabled (usually when I jump between runtime and development modes), hence a little annoyance.

Another little problem is that you can also get checksum errors if you submit a page too early before it ‘s fully loaded (happens when I’m clicking through the next and previous items e.g. editing columns in a report). A page loading mask would probably resolve these issues whilst all the items are rendered and the jQuery page load operations are performed, but then again that may annoy more people than it pleases.

There’s also some tighter controls in page templates. There’s a new validation for the “Page Template Header” section which ensures you use the #HEAD# substitution tag and also include the “HEAD” tags.

This was not the case in the previous version(s). We have created quite a few non-APEX pages in our applications which have various uses, including some that are simply content wrapped in a DIV rather than using the html, head, and body tags. Some of these are for AJAX operations, others are for static content etc. We’ve upgraded the applications with these templates to APEX 4.0 and they still work fine, the validation problem only arises when we need to edit them. The workaround is to hack the backend table and make the required changes using sqlplus/sqldeveloper. I can understand why the dev team included this but a nicer option might have been to prompt the developer to confirm that they know they have omitted the HEAD tag and allow them to continue to submit/save the changes.

Anyway just a few minor suggestions for the APEX team (if they ever stumble across this post). I’m bombarded day after day by our own customers with minor changes and design suggestions, so this is my only outlet to get some back… well at the expense of the APEX team

We transform our APEX forms into Ext equivalent ones, either on the fly after the page has loaded or pre-built in the page header and slotted into viewport code. Now for the transformed elements on the page I was hoping we could still use APEX’s builtin dynamic actions (not dynamic action plugins) and mixing in jQuery. In theory I thought this may work given that we reuse the same DOM ID for our transformed Ext input items. I was sadly mistaken.

Did I want to miss out on what the inbuilt dynamic actions provides me from a development productivity point of view? No way!! So what to do……. Well there were two options that came to mind (taking into consideration that I don’t want to write a dynamic action plugin for a dynamic action that is available in the APEX IDE. I have the long term vision of transforming existing APEX pages into Ext equivalent ones with minimal changes)

1. I could override the existing APEX dynamic actions javascript with an Ext equivalent version (this would take some time)
2. Or I could simply generate my dynamic action code on the fly by using the data dictionary.

I chose the second option for now as it was the easier of the two, as I’m really time constrained at the moment, however the first option is the more strategic path which I intend to go down in the future. That aside, in the space of about 90 minutes I came up with the following solution for hide and show support on our Ext select lists/combos. I’ve only started with this one as it was my immediate requirement and it’s not exactly elegant, but it does work.

Here’s the working prototype code which you put into a PLSQL process that runs before “Page Footer”…

CREATE OR REPLACE PROCEDURE ext_hide_show_dynamic_action
AS

  l_app_id         VARCHAR2(100) := v('APP_ID');
  l_page_id        VARCHAR2(100) := v('APP_PAGE_ID');
  l_function       VARCHAR2(10);
  l_opacity        VARCHAR2(10);
  l_duration       VARCHAR2(10);

  --
  -- Internal procedure to print out our hide/show function, rather than copy and pasting
  -- this into two almost identical calls in the main section
  --
  FUNCTION print_js_function
  ( p_in_affected_elements IN VARCHAR2
  , p_in_function          IN VARCHAR2
  , p_in_duration          IN VARCHAR2
  , p_in_opacity           IN VARCHAR2
  ) RETURN VARCHAR2
  AS

  BEGIN
    --
    -- We need to check for any icons e.g. date picker, spinner etc.
    -- We need to also hide the label and any resizeable text elements
    --
    RETURN 'Ext.select("label[for='||p_in_affected_elements||'],fieldset[id*='||p_in_affected_elements||'],'||
                          '[id='||p_in_affected_elements||'],[id='||p_in_affected_elements||'] + img[class*=x-form-trigger]").'||p_in_function||
              '({'||
                'duration: '||p_in_duration||','||
                'endOpacity: '||p_in_opacity||','||
                'remove: false,'||
                'useDisplay: true'||
              '});';

  END print_js_function;    

BEGIN
  sys.htp.p(wwv_flow_utilities.open_javascript);
  sys.htp.p('Ext.onReady(function() {');
  --
  -- Lets loop through each of our dynamic actions (we are looking for hide operations)
  --
  FOR c IN
  ( SELECT da.when_element                 when_element
    ,      CASE da.when_condition
             WHEN 'equal to' THEN '=='
             WHEN 'is null'  THEN
               --
               -- We need to check for LOV null value settings
               --
               CASE pi.display_as
                 WHEN 'Select List' THEN '== "'||pi.lov_null_value||'"'
                 ELSE '== "undefined"'
               END
             ELSE NULL
           END                             when_condition
    ,      da.when_expression              when_expression
    ,      lower(act.action_name)          action_name
    ,      act.affected_elements           affected_elements
    ,      act.affected_elements_type      affected_elements_type
           --
           -- We have to work out the hide/show action to perform as we may
           -- want to show an item based on a value, or do the opposite and hide
           -- the item. The event result dictates what to do,
           -- i.e. the one with "True" set
           --
    ,      CASE act.dynamic_action_event_result
             WHEN 'False' THEN
               CASE action_code
                 WHEN 'NATIVE_HIDE' THEN 'NATIVE_SHOW'
                 ELSE 'NATIVE_HIDE'
               END
             ELSE act.action_code
           END                             action_code
           --
           -- Ext has a different event for a combo, select is for list item
           -- selection, whereas change is the field changes onblur
           --
    ,      CASE da.when_event_internal_name
             WHEN 'change' THEN
               CASE pi.display_as
                 WHEN 'Select List' THEN 'select'
                 ELSE 'change'
               END
             ElSE lower(act.action_name)
           END                          when_event_internal_name
    ,      rownum                       rn
    FROM   apex_application_page_da      da
    ,      apex_application_page_da_acts act
    ,      apex_application_page_items   pi
    WHERE  da.dynamic_action_id     = act.dynamic_action_id
    AND    da.application_id        = l_app_id
    AND    da.page_id               = l_page_id
    AND    pi.item_name             = da.when_element
    AND    pi.application_id        = da.application_id
    AND    pi.page_id               = da.page_id
    AND    act.execute_on_page_init = 'Yes'
    AND    lower(act.action_name)   = 'hide'
    /*
    --
    -- Lets make sure that the dynamic action condition and authorisations are met
    --
    AND    extjs_utils.auth_condition_check
           ( da.condition_type
           , da.condition_expression1
           , da.condition_expression2
           , da.authorization_scheme_id
           ) = 0

    */
  ) LOOP

    --
    -- We are going to build a standalone function so we can also call it on page load
    --
    sys.htp.p('Ext.app.dynAct'||c.when_element||c.rn|| ' = function() {'||
                 'if (Ext.getCmp("'||c.when_element||'").getValue()'||c.when_condition||c.when_expression||') {'||
                     --
                     -- Our dynamic action tells us whetehr to show or hide
                     -- if our condition is met
                     --
                     print_js_function
                     ( p_in_affected_elements => c.affected_elements
                     , p_in_function          => CASE c.action_code WHEN 'NATIVE_HIDE' THEN 'fadeOut' ELSE 'fadeIn' END
                     , p_in_duration          => '.75'
                     , p_in_opacity           => CASE c.action_code WHEN 'NATIVE_HIDE' THEN '0' ELSE '1' END
                     ) ||
                  '} else {'||
                     --
                     -- We do the opposite action of the above Hide or Show
                     --
                     print_js_function
                     ( p_in_affected_elements => c.affected_elements
                     , p_in_function          => CASE c.action_code WHEN 'NATIVE_HIDE' THEN 'fadeIn' ELSE 'fadeOut' END
                     , p_in_duration          => '.75'
                     , p_in_opacity           => CASE c.action_code WHEN 'NATIVE_HIDE' THEN '1' ELSE '0' END
                     ) ||

                  '}'||
              '}'
    );
    --
    -- Lets add our event handler
    --
    sys.htp.p('Ext.getCmp("'||c.when_element||'").on("'||c.when_event_internal_name||'", Ext.app.dynAct'||c.when_element||c.rn||');');
    --
    -- Lets run our function on Page Load, to worrk out what the display should be
    --
    sys.htp.p('Ext.app.dynAct'||c.when_element||c.rn||'();');

  END LOOP;
  sys.htp.p('});');
  sys.htp.p(wwv_flow_utilities.close_javascript);
--
-- Exception Handling Routine
--
EXCEPTION
  WHEN OTHERS
  THEN
    -- Put your excpetion handling code here
    RAISE;
END ext_hide_show_dynamic_action;
/

Backing out the Ext JS functionality is easy, we simply change our region template (which is responsible for the Ext JS page item transforms) back to a standard apex template and disbale the above process and we still have the exact same functionality of a hide show item, just using the standard APEX features.

If you’re not happy with adding custom PLSQL to either page zero or your pages then maybe creating a new dynamic action plugin is for you. It’s great that we have several choices, it’s just up to you “the developer” to decide which method suits your style/direction.

The one thing I’ve failed to mention about building JSON meta data objects and printing out dynamic javascript, is that we use additional resources by querying the data dictionary all the time (over and above building traditional APEX apps). This of course has performance impact when you start to scale your application(s). The simple solution (if you’re headed down this path) is to cache the outputted objects into custom tables and store these either, session, user, or application based depending upon your application requirement with expiration/invalidation policies. It will be then as simple as doing a check for a cached item and reusing it if it exists rather than querying the data dictionary for every page load.

Finally if you’re looking at developing your first region plugin, then download the following plugin written by Doug Gualt from Sumneva and install it. As there’s not much documentation or anything really useful that out there that walks you through how to do it, so it’s easiest just to edit Doug’s plugin to see how it hangs together. I coded my first region plugin last night (sad I know) and used this as my way to learn. I had my first region plugin (based on the Ext.manual documentation) completed in under 2 hours, it saved me hours! Thanks Doug!

P.S. this is a great way to generate a logo in ascii text, which sits nicely in your plugin header code.

If successful, an APEX process sends an email is sent to the address with a password reset link.

Otherwise a dialog displays and advises that the email address is not stored within the database.

The email reset link takes you back to the login page and sets an APPLCIATION item via the URL with a password reset token. The token is generated by SYS_GUID(), so it’s just one big random number. If this application  item has been set I have a conditional PLSQL process which will popup an Ext Window with the form for you to submit your new password. Here’s an example of the URL:

http://portal.e-dba.com/apex/f?p=EDBA_PORTAL:LOGIN:2434321017503577::::PWD_RESET_ID:190113209148692319491537939948722019703

To enhance security I’ve coded in a couple of checks.

1. The username must be supplied with the password and a check is performed to see if the username matches the user record attached to the email address that was supplied.
   
2. The token has an expiry time on it and must be used within X many hours, which is configurable by an application substitution string. Otherwise it defaults to 24
3. The token can only be used once, i.e. once a successful reset operation has been performed the token becomes invalid.
   
4. The IP address captured from the person submitting the password reset request must match the IP address from the person entering the password details into form. A number of IP ranges can be excluded, or only allow IP ranges from within the UK, it depends upon the user base and where the employees are located, working from home etc.
   
5. Only X many attempts can be used to try and reset the password, i.e. if you have a valid reset token but don’t know which username you use you can only try to reset the password X many times before the token becomes invalidated. This is also configurable with an application substitution string, otherwise it defaults to 3 if its not set.
   
6. On a successful attempt the following dialog is displayed
   

What I like about Ext forms is that for the failure message notification all I need to do is return a JSON object from my “On Demand” process with the ID of the form item and message in order to display the validation failure. Very simple e.g.

{
    success: false,
    errors: [{
        id: "username",
        "msg": "Server Error: ORA-20003: The supplied reset token has expired, you have 24 hours in which to use it. Please submit a new password retrieval request."
    }]
}

Here’s most of the supporting code from our initial prototype to give you an idea of how it hangs together (Note: it’s not fully complete, it’s missing an email routine as ours is quite comprehensive and a little overkill for this post. It also uses the LOGGER package from Tyler Muth for exception handling, so simply remove the logger.log_error references if you don’t wish to use LOGGER):

Forgotten Password Anchor link

Note: it’s a Display Only Item, Settings: “Output of PLSQL Code”

htp.p('<a href="#" class="ext-label" onclick="Ext.app.promptDialog({title: ''Forgotten Password'', msg:''Please enter your email address to send the password reset link to:'', width: 300 }, Ext.app.passwordReset);">Forgotten Password?</a>');

Email Prompt Dialog

Ext.app.promptDialog = function (config, fn) {
   Ext.MessageBox.show({
      title    : config.title,
      msg      : config.msg,
      width    : config.width,
      buttons  : Ext.MessageBox.OKCANCEL,
      multiline: true,
      fn       : fn
   });
}

Conditional HTML region on page 101, only displays when our APPLICATION ITEM is set with a value.

Note: it’s value is cleared on page submission.

<style>
.app-msg {
   text-align:center;
   font-size: 1.6em;
   height: 90px;
   line-height: 90px;
   vertical-align:middle;
}
.app-img {
   float:left;
   margin-right:10px;
}

</style>
<script>
Ext.onReady(function () {
   pwdResetWin = new Ext.Window({
      layout: 'form',
      width: 375,
      modal: true,
      autoHeight: true,
      closeAction: 'close',
      items: [passwordResetForm]
   });
   pwdResetWin.show();
});
</script>

Password Reset Form

(I pretty much just used an exact copy from the following URL)

var passwordResetForm = {
   xtype: 'form',
   id: 'password-reset-form',
   labelWidth: 150,
   bodyStyle: 'padding:15px;background:transparent',
   border: false,
   url: widgetURL,
   items: [{
      xtype: 'box',
      autoEl: {
         tag: 'div',
         html: '<div class="app-msg"><img src="'+gcExtBase+'/../../img/icons/icon_password_reset.png" class="app-img" />Password Reset Form</div>'
      }
   },
   {
      xtype: 'textfield',
      id: 'username',
      fieldLabel: 'Username',
      name: 'x01',
      allowBlank: false,
      minLength: 3,
      maxLength: 64,
	  msgTarget: 'under',
      anchor: '90%'
   },
   {
      xtype: 'textfield',
      id: 'pwd',
      name: 'x02',
      fieldLabel: 'New Password',
      inputType: 'password',
      allowBlank: false,
      minLength: 6,
      maxLength: 32,
	  msgTarget: 'under',
      anchor: '90%',
      minLengthText: 'Password must be at least 6 characters long.'
   },
   {
      xtype: 'textfield',
      id: 'pwd-confirm',
      name: 'x03',
      fieldLabel: 'Confirm New Password',
      inputType: 'password',
      allowBlank: false,
      minLength: 6,
      maxLength: 32,
      anchor: '90%',
      minLengthText: 'Password must be at least 6 characters long.',
	  msgTarget: 'under',
      vtype: 'password2',
      initialPassField: 'pwd'
   },{
      xtype: 'hidden',
      name: 'p_widget_name',
      value: 'Ext.app.passwordResetSubmit'
   }],
   buttons: [{
      text: 'Submit',
      handler: function () {
         Ext.getCmp('password-reset-form').getForm().submit({
            success: function () {
               pwdResetWin.hide();
               alert('Your password has been successfully reset!');
            }
         });
      }
   },
   {
      text: 'Cancel',
      handler: function () {
         pwdResetWin.hide();
      }
   }]
}

AJAX Javascript

var u = (window.location.href.indexOf("?") > 0) ? window.location.href.substring(0, window.location.href.indexOf("?")) : window.location.href;
var baseURL = u.substring(0, u.lastIndexOf("/"));
var widgetURL = baseURL + '/wwv_flow.show?p_flow_id=' + Ext.getDom('pFlowId').value + '&p_flow_step_id=' + Ext.getDom('pFlowStepId').value + '&p_instance=' + Ext.getDom('pInstance').value + '&p_request=APPLICATION_PROCESS=Ext.app.widget';
Ext.app.passwordReset = function (buttonId, text, opt) {

   (buttonId == 'ok') ? Ext.Ajax.request({
      url: widgetURL,
      disableCaching: false,
      method: "GET",
      params: {
         p_widget_name: "Ext.app.passwordReset",
         x01: text
      },
      success: function (response, opts) {
         var obj = Ext.decode(response.responseText);
         (obj.success) ? alert("A password reset link has been emailed to the address provided: " + obj.email) : alert("The supplied email address was not found in the system: " + obj.email);
      },
      failure: function (response, opts) {
         (console) ? console.log('server-side failure with status code ' + response.status) : alert('server-side failure with status code ' + response.status);
      }
   }) : null;
}

Application Level Process named “Ext.app.widget”

portal_widgets.widget
( p_widget_name         => wwv_flow.g_widget_name
, p_widget_mod          => wwv_flow.g_widget_mod
, p_widget_action       => wwv_flow.g_widget_action
, p_widget_action_mod   => wwv_flow.g_widget_action_mod
, p_widget_num_return   => NULL
, x01                   => wwv_flow.g_x01
, x02                   => wwv_flow.g_x02
, x03                   => wwv_flow.g_x03
, x04                   => wwv_flow.g_x04
, x05                   => wwv_flow.g_x05
, x06                   => wwv_flow.g_x06
, x07                   => wwv_flow.g_x07
, x08                   => wwv_flow.g_x08
, x09                   => wwv_flow.g_x09
, x10                   => wwv_flow.g_x10
);
EXCEPTION
  WHEN OTHERS
  THEN
    logger.log_error('APPLICATION_PROCESS=Ext.app.widget',SQLERRM);
    RAISE;

Password Reset PLSQL

  PROCEDURE passwordResetSubmit
  ( 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_reset_id           portal_lost_passwords.reset_id%TYPE;
    l_user_id            portal_lost_passwords.user_id%TYPE;
    l_request_ip_address portal_lost_passwords.request_ip_address%TYPE;
    l_browser_agent      portal_lost_passwords.request_browser_agent%TYPE;
    l_username           portal_users.username%TYPE := lower(trim(regexp_replace(x01,CHR(10)||'|'||CHR(13),''))); --lower(extjs_utils.striphtml(x01));
    l_new_password       portal_users.password%TYPE := x02;
    l_password_rec       portal_lost_passwords_vw%ROWTYPE;
    l_error_msg          VARCHAR(32767);

  BEGIN

    l_request_ip_address := owa_util.get_cgi_env('REMOTE_ADDR');
    l_browser_agent      := owa_util.get_cgi_env('HTTP_USER_AGENT');
    l_reset_id           := v('PWD_RESET_ID');
    BEGIN

      --
      -- Lets check our reset id is valid
      --
      SELECT *
      INTO   l_password_rec
      FROM   portal_lost_passwords_vw
      WHERE  reset_id = l_reset_id;

      --
      -- We have some extra security checks
      -- 1. We make sure you use the same IP address between the request and reset
      -- 2. You have 24 hours which you can use the token for
      IF l_password_rec.request_ip_address <> l_request_ip_address THEN
        raise_application_error(-20001,'You are trying to reset the password from a different IP address than the one used when completing the request form!!');
      ELSIF l_password_rec.password_reset = 'Y' THEN
        raise_application_error(-20002,'The supplied reset token has been previously used. Please submit a new password retrieval request.');
      ELSIF l_password_rec.created_on < sysdate-(to_number(nvl(v('PASSWORD_TOKEN_LIFETIME'),portal_constants.gc_default_pwd_token_lifetime))/24) THEN
        raise_application_error(-20003,'The supplied reset token has expired, you have 24 hours in which to use it. Please submit a new password retrieval request.');
      ELSIF l_password_rec.failure_count >= to_number(nvl(v('PASSWORD_TOKEN_MAX_FAILURES'),portal_constants.gc_default_pwd_token_failures)) THEN
        raise_application_error(-20004,'You have exceeded the maximum allowed number of failures for this reset token. Please submit a new password retrieval request.');
      END IF;

      l_reset_id := l_password_rec.reset_id;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(-20005,'The supplied reset token is not valid!');
    END;

    SELECT user_id
    INTO   l_user_id
    FROM   portal_users
    WHERE  username = l_username;

    UPDATE portal_lost_passwords
    SET reset_ip_address = l_request_ip_address
    ,   reset_browser_agent = l_browser_agent
    ,   password_reset = 'Y'
    ,   password_reset_on = sysdate
    WHERE reset_id = l_reset_id;

    --
    -- table has a trigger to encrypt the password
    --
    portal_security.change_password
    ( p_user_id   => l_user_id
    , p_username  => l_username
    , p_password  => l_new_password
    );

    htp.p('{ "success": true, "message": "The password has been successfully reset!"}');

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO portal_lost_password_failures
      ( ip_address
      , browser_agent
      , username
      , error_message
      , reset_id
      ) VALUES
      ( l_request_ip_address
      , l_browser_agent
      , l_username
      , 'The username provided was not recognized'
      , l_reset_id
      );
      htp.p('{ "success": false, errors: [{id:"username", "msg": "The username provided was not recognized"}] }');
    WHEN OTHERS
    THEN
      l_error_msg := SQLERRM;
      INSERT INTO portal_lost_password_failures
      ( ip_address
      , browser_agent
      , username
      , error_message
      , reset_id
      ) VALUES
      ( l_request_ip_address
      , l_browser_agent
      , l_username
      , l_error_msg
      , l_reset_id
      );

      logger.log_error(SQLERRM, 'portal_widgets.passwordResetSubmit');

      htp.p('{ "success": false, errors: [{id:"username", "msg": "Server Error: '||replace(regexp_replace(SQLERRM,CHR(10)||'|'||CHR(13),''),'"','\"')||'"}] }');
  END passwordResetSubmit;

  PROCEDURE passwordReset
  ( 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_reset_id           portal_lost_passwords.reset_id%TYPE;
    l_user_id            portal_lost_passwords.user_id%TYPE;
    l_request_ip_address portal_lost_passwords.request_ip_address%TYPE;
    l_browser_agent      portal_lost_passwords.request_browser_agent%TYPE;
    l_email_address      portal_lost_passwords.email_address%TYPE := lower(trim(regexp_replace(x01,CHR(10)||'|'||CHR(13),'')));
  BEGIN

    l_request_ip_address := owa_util.get_cgi_env('REMOTE_ADDR');
    l_browser_agent      := owa_util.get_cgi_env('HTTP_USER_AGENT');

    SELECT user_id
    INTO   l_user_id
    FROM   portal_users_vw
    WHERE  work_email = l_email_address
    AND    rownum = 1;

    INSERT INTO portal_lost_passwords
    ( user_id
    , request_ip_address
    , request_browser_agent
    , email_address
    ) VALUES
    ( l_user_id
    , l_request_ip_address
    , l_browser_agent
    , l_email_address
    ) RETURNING reset_id INTO l_reset_id;

    portal_notify.send_forgot_password_email
    ( p_user_id  => l_user_id
    , p_reset_id => l_reset_id
    );

    htp.p('{ "success": true, "message": "Password retrieval email sent!", "email": "'||l_email_address||'"}');

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO portal_lost_password_failures
      ( ip_address
      , browser_agent
      , email_address
      , error_message
      ) VALUES
      ( l_request_ip_address
      , l_browser_agent
      , l_email_address
      , 'The email address provided was not recognized'
      );
      htp.p('{ "success": false, "message": "The email address provided was not recognized", "email": "'||l_email_address||'"}');
    WHEN OTHERS
    THEN
      logger.log_error(SQLERRM, 'portal_widgets.passwordReset');
      htp.p('{ "success": false, "message": "Error: '||replace(regexp_replace(SQLERRM,CHR(10)||'|'||CHR(13),''),'"','\"')||'" }');
  END passwordReset;

  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.app.passwordReset' THEN
        portal_widgets.passwordReset
        ( 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
        );
      WHEN 'Ext.app.passwordResetSubmit' THEN
        portal_widgets.passwordResetSubmit
        ( 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
        );

     ELSE
        htp.p('{ "success": false, "message": "Widget '||p_widget_name||' not defined in portal_widgets!"}');
    END CASE;

  EXCEPTION
    WHEN OTHERS
    THEN
      logger.log_error(SQLERRM, 'portal_widgets.widget');
      RAISE;

  END widget;

Finally the supporting DDL

DROP TABLE "PORTAL_LOST_PASSWORDS"
/
CREATE TABLE "PORTAL_LOST_PASSWORDS"
("RESET_ID" NUMBER NOT NULL ENABLE,
"USER_ID" VARCHAR2(100) NOT NULL ENABLE,
"REQUEST_IP_ADDRESS" VARCHAR2(100 BYTE),
"REQUEST_BROWSER_AGENT" VARCHAR2(4000 BYTE),
"EMAIL_ADDRESS" VARCHAR2(500 BYTE) NOT NULL ENABLE,
"RESET_IP_ADDRESS" VARCHAR2(100 BYTE),
"RESET_BROWSER_AGENT" VARCHAR2(4000 BYTE),
"PASSWORD_RESET" VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
"PASSWORD_RESET_ON" DATE,
"CREATED_BY" VARCHAR2(500 BYTE) NOT NULL ENABLE,
"CREATED_ON" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" VARCHAR2(500 BYTE),
"LAST_UPDATED_ON" DATE,
"FLEX_01" VARCHAR2(4000 BYTE),
"FLEX_02" VARCHAR2(4000 BYTE),
"FLEX_03" VARCHAR2(4000 BYTE),
"FLEX_04" VARCHAR2(4000 BYTE),
"FLEX_05" VARCHAR2(4000 BYTE),
 CONSTRAINT "PORTAL_LOST_PASSWORDS_PK" PRIMARY KEY ("RESET_ID")
);
DROP TABLE PORTAL_LOST_PASSWORD_FAILURES
/
CREATE TABLE "PORTAL_LOST_PASSWORD_FAILURES"
("ATTEMPT_ID" NUMBER NOT NULL ENABLE,
"IP_ADDRESS" VARCHAR2(100 BYTE),
"BROWSER_AGENT" VARCHAR2(4000 BYTE),
"EMAIL_ADDRESS" VARCHAR2(500 BYTE),
"USERNAME" VARCHAR2(500 BYTE),
"RESET_ID" NUMBER,
"ERROR_MESSAGE" VARCHAR2(4000 BYTE) NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(500 BYTE) NOT NULL ENABLE,
"CREATED_ON" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" VARCHAR2(500 BYTE),
"LAST_UPDATED_ON" DATE,
"FLEX_01" VARCHAR2(4000 BYTE),
"FLEX_02" VARCHAR2(4000 BYTE),
"FLEX_03" VARCHAR2(4000 BYTE),
"FLEX_04" VARCHAR2(4000 BYTE),
"FLEX_05" VARCHAR2(4000 BYTE),
 CONSTRAINT "PORTAL_LOST_PWD_FAILURES_PK" PRIMARY KEY ("ATTEMPT_ID")
);

CREATE OR REPLACE TRIGGER "PORTAL_LOST_PASSWORDS_BIU"
BEFORE INSERT OR UPDATE ON PORTAL_LOST_PASSWORDS
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        IF :NEW.reset_id IS NULL THEN
           SELECT TO_NUMBER(SYS_GUID(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
            INTO :NEW.reset_id
            FROM DUAL;
        END IF;
        :NEW.created_on := SYSDATE;
        :NEW.created_by := NVL(v('APP_USER'),USER);
    END IF;

    :NEW.last_updated_on := SYSDATE;
    :NEW.last_updated_by := NVL(v('APP_USER'),USER);

END;
/

CREATE OR REPLACE TRIGGER "PORTAL_LOST_PWD_FAILURES_BIU"
BEFORE INSERT OR UPDATE ON PORTAL_LOST_PASSWORD_FAILURES
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        IF :NEW.attempt_id IS NULL THEN
           SELECT TO_NUMBER(SYS_GUID(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
            INTO :NEW.attempt_id
            FROM DUAL;
        END IF;
        :NEW.created_on := SYSDATE;
        :NEW.created_by := NVL(v('APP_USER'),USER);
    END IF;

    :NEW.last_updated_on := SYSDATE;
    :NEW.last_updated_by := NVL(v('APP_USER'),USER);

END;
/

CREATE OR REPLACE VIEW portal_lost_passwords_vw
AS
SELECT ( SELECT count(*)
         FROM   portal_lost_password_failures
         WHERE  reset_id = o.reset_id
       ) failure_count
,      o.*
FROM   portal_lost_passwords o
/

Now this is normally fine if we’re not opening the same APEX page, but sometimes we do. e.g. we have a single APEX page which lists all our customer information and we simply change an APEX ITEM with a new customer ID to change the page to a new customer. Now this works fine until we open up several customers at the same time into multiple iframes/tabs. Once we’ve done this our page items in session state are updated with the details of the last customer which we’ve opened. If we tab switch back to the first customer and perform, say a grid refresh, we now see the contents of the grid change to the last customer we opened. This is simply because we perform a PPR operation on the report and session state is used for the binds in our report which happen to hold the values for our last customer.

In order to deal with the issue I came up with the following approach to add a “beforeload” event on our Ext Stores or an interceptor function to be called before an APEX PPR call. We don’t always use Ext grids for report data, sometimes we still use APEX reports, usually with the one column unordered list template wrapped in an Ext Panel. We then use an Ext button in the Panel toolbar to control the refresh of the report reusing APEX’s $a_report/PPR function to perform the AJAX refresh.

For the APEX PPR side of things I’ve written an override (based on something similar Stephen Blair has done with jQuery to fade in and out the report contents on refresh) of the $a_report function to call an interceptor function which will be responsible for updating session state just before the actual AJAX report refresh call. There’s one slight issue with the override which I’m hoping someone will resolve for me is that for every refresh an extra DIV wraps around the report contents, this is not visible to the end user, but it is if you look at the DOM. Unfortunately I just don’t have the time to perfect it, and it works just fine for me but things like this really bug me!

// Ext $a_report override
$a_report = function (pId, pMin, pMax, pFetched, pSort) {
    var url = 'f?p=' + Ext.get('pFlowId').getValue() + ':' + Ext.get('pFlowStepId').getValue() + ':' + Ext.get('pInstance').getValue() + ':FLOW_PPR_OUTPUT_R' + pId + '_';
    if ( !! pSort) {
        url += pSort + '::RP::' + Math.random() + '&fsp_region_id=' + pId;
    } else {
        url += 'pg_R_' + pId + ':NO:::' + Math.random() + '&pg_max_rows=' + pMax + '&pg_min_row=' + pMin + '&pg_rows_fetched=' + pFetched;
    }
    Ext.get('report_' + pId + '_catch').fadeOut({
        duration: .75,
        endOpacity: .3
    });
   // Page could have an interceptor call defined by the Developer to execute before the refresh
   if (typeof(Ext.app.$a_report_interceptor) !== "undefined") {
      Ext.app.$a_report_interceptor.call();
   }
   Ext.Ajax.request({
      url: url,
      disableCaching: false,
      method: "GET",
      success: function (response, opts) {
         var div = Ext.get('report_' + pId + '_catch');
         var parent = div.parent();
         div.remove();
         Ext.get(Ext.DomHelper.append(parent.fadeIn({
            duration: 2,
            endOpacity: 1
         }), {
            html: response.responseText
         }));
      },
      failure: function (response, opts) {
         console.log('server-side failure with status code ' + response.status);
      }
   });
}

Then in our page footer we simply add our custom interceptor function, we get the benefit of APEX doing the substitution at page generation time, so we don’t need to perform any javascript function to read the value from a hidden form item on the page. This could also be handled by a PLSQL process, it’s up to you to decide which place works best.

Ext.app.$a_report_interceptor = function () {
   Ext.app.apexHttpPost('Do Nothing', ['P13_CUST_ID'], ['&P13_CUST_ID.']);
}

See the following earlier post for the Ext.app.apexHttpPost function code.

For our Ext grids, here’s a snippet of how we add a “beforeload” event on our store to call a function defined in our report meta data JSON object defined in our page header

var pReportMetaData = {
   ...........
   "beforeStoreLoadFn": function (store, options) {
      Ext.app.apexHttpPost('Do Nothing', ['P13_CUST_ID'], ['&P13_CUST_ID.']);
   }
}

// Lets define a before load function on our store if one is defined in our meta object
// usually used to update session state
if (pReportMetaData.beforeStoreLoadFn) {
   var fn = pReportMetaData.beforeStoreLoadFn;
   pStore.on("beforeload", function (store, options) {
      fn.call(store, options);
   });
}

Hopefully this gives you one idea of how you can potentially workaround session state issues when using the same page, multiple iframes, and AJAX.

Perhaps the following two images will explain what I’m failing to do with words…

In the above two images; the first shows the display of two separate menu’s with different items (it’s been photoshopped to show you two tabs of the tabpanel as the menu’s are on separate tabs). The second shows the APEX IDE and the menu plugins which are used to define the query source for the menu items.

The difference in approach to the “A button is not always a button” post is that instead of building a json object in the page header with our shortcuts and using a function to clean some malformed JSON outputted by our templates. We changed the design to generate the entire region toolbar JSON object in the page header, as it allowed a more cleaner and maintainable approach and opened up the ability to query the APEX data dictionary and process sub regions as we looped through each parent region at a time.

The following code example from our initial prototype (which only works for one menu button per region) should give you an idea of how we query the APEX data dictionary to build the toolbar object

--
-- Lets loop through all our page regions to create our region toolbar for each
--
FOR c IN
( SELECT *
  FROM   apex_application_page_regions
  WHERE  application_id = v_app_id
  AND    page_id        = v_page_id
  AND    source_type    NOT IN ('EXT_BUTTON_MENU') -- ignore our stripped pseudo plugins
  AND    extjs_utils.auth_condition_check(condition_type,condition_expression1,condition_expression2,authorization_scheme) = 0
)  LOOP
  v_toolbarObj := JSON_LIST();
  --
  -- Lets loop through all our page buttons to create our region toolbar
  --
  FOR c1 IN
  ( SELECT pb.region_id
    ,      label
    ,      nvl(redirect_url,'apex.submit('''||button_name||''');') link
    ,      button_attributes
    ,      template
    FROM   apex_application_page_buttons pb
    ,      apex_application_temp_button  tmp
    ,      apex_applications             app
    WHERE  pb.application_id  = v_app_id
    AND    pb.page_id         = v_page_id
    AND    pb.region_id       = c.region_id
    AND    tmp.application_id = pb.application_id
    AND    app.application_id = pb.application_id
    AND    tmp.template_name  = pb.button_template
    AND    tmp.theme_number   = app.theme_number
    AND    extjs_utils.auth_condition_check(pb.condition_type,pb.condition_expression1,pb.condition_expression2,pb.authorization_scheme) = 0
    ORDER by pb.button_sequence
  ) LOOP
    --
    -- Lets check our button attributes for shortcuts
    --
    v_shortcut_name := regexp_replace(c1.button_attributes, '.*"(EXT_\w+)".*','\1');
    v_shortcut_name := CASE v_shortcut_name
                         WHEN c1.button_attributes THEN NULL
                         ELSE v_shortcut_name
                       END;
    IF v_shortcut_name IS NOT NULL THEN
 
      FOR c2 IN
      ( SELECT shortcut_name
        ,      shortcut
        FROM   apex_application_shortcuts
        WHERE  application_id = v_app_id
        AND    shortcut_name  = v_shortcut_name
      )  LOOP
        v_shortcut := c2.shortcut;
        --
        -- Lets loop through our shortcut and replace our menu references with a JSON object
        --
        WHILE instr(v_shortcut,'#MENU:') > 0 LOOP
          FOR c3 IN
          ( SELECT source_type
            ,      region_source menu_sql
            FROM   apex_application_page_regions
            WHERE  application_id   = v_app_id
            AND    page_id          = v_page_id
            AND    parent_region_id = c.region_id
            AND    source_type      = 'EXT_BUTTON_MENU'
          ) LOOP
            --
            -- Lets execute our query and return the result encoded in a JSON object
            --
            v_json := sql_to_json( p_sql => c3.menu_sql );
            v_shortcut := regexp_replace(v_shortcut,'(\$MENU:\w+\$)',v_json,1,1)..........

In the above we query all the regions on the page and ignore any of our pseudo plugin regions. We then build up the toolbar JSON object with the assistance of PLJSON and then loop through each of the buttons defined for the region and use the defined button template and make the necessary string replacements e.g. #BUTTON_ATTRIBUTES#. But before we do, we check the button attributes to see if a custom MENU shortcut has been defined and if so we extract the shortcut name and query the shortcut source. We then extract any MENU definition in the shortcut source and then in the c3 cursor loop we query “apex_application_page_regions” and check the source type matches our plugin name and we use this SQL definition and execute it to get the results for the named menu.

So what it allows us to do from a development point of view is define a single button template e.g. radio menu, and use our pseudo plugins SQL to define the menu items. Essentially our toolbar menu button is made up of a button template, shortcut, and plugin region. All three components can be subscribed and published across all applications in the workspace so we get the benfit of code centralization and any additional custom config for the button menu can be defined by either using multiple shortcuts or some additional config in #BUTTON_ATTRIBUTES#.

In order to make sure the plugin region never displays, we use a template named either “Ext.Exclude” which wraps the content in a DIV with style=”display:none;”. Optionally we can use another template named “Ext.Destroy” which uses the Ext.onReady function and removes the region/DIV from the DOM on page load. Both come in very handy in different situations. We simply ignore regions which have these templates assigned to them within our viewport generation code.

Note: you will see this error in the region source when you use a stripped plugin like we have

ORA-20100: No render function has been defined for plug-in PLUGIN_EXT_BUTTON_MENU

however this is never visible because the template we define either destroys the region or hides it from display.