APEX Development

If you’re a regular follower of this blog I do apologize for some of the code snippets as they are coming out of development and are not always 100%. We’re basically blogging as we develop whilst it’s still fresh in our minds and actually have the time. So with that disclaimer I’m posting today’s great revelation on using custom Ext validation types on our APEX Ext flavoured form. I just can’t believe how easy it is….

Initially I wanted to have a dynamic client side validation for ensuring that a password and confirm password field matched. So my first step (as I’m still learning the Ext library and a newbie as I’ve only been using it for a couple of months) I did some searching on the net to see if anyone had blogged or posted something about it. I came across the following great post Adam->Blog(); which got me started (the comments were the actual gold).

I’ve used the details in the post along with an additional email check

/*
 * Ext JS Library 2.2.1
 * Copyright(c) 2006-2009, Ext JS, LLC.
 * licensing@extjs.com
 *
 * http://extjs.com/license
 *
 * APEX and Ext JS Integration Kit - 2.2.1
 * Copyright(c) 2009-2010, e-DBA Ltd
 * apex-sig@e-dba.com
 *
 * http://www.e-dba.com/uk/f?p=WEBSITE:APEXTJS_LICENSE
 */
/*

   Validations

*/
Ext.apply(Ext.form.VTypes, {
   // Password Check
   passwordText: 'The passwords entered do not match.',
   password: function(value, field) {
      var valid = false;
      if (field.matches) {
         var otherField = Ext.getCmp(field.matches);
         if (value == otherField.getValue()) {
            otherField.clearInvalid();
            valid = true;
         }
      }
      return valid;
   },
   // Phone Number check
   phoneText: "Not a valid phone number.  Must be in the following format: 123-4567 or 123-456-7890.",
   phoneMask: /[d-]/,
   phoneRe: /^(d{3}[-]?){1,2}(d{4})$/,
   phone: function(v) {
      return this.phoneRe.test(v);
   },
   // Email address check
   emailText: "Not a valid email address. Must be in the following format: yourname@company.domain",
	emailRe: /^(\s*[a-zA-Z0-9\._%-]+@[a-zA-Z0-9\.-]+\.[a-zA-Z]{2,4})\s*$/,
	email: function(v) {
		return this.emailRe.test(v);
	},
   // Numeric check
   numericText: "Only numbers are allowed.",
   numericMask: /[0-9]/,
   numericRe: /(^-?dd*.d*$)|(^-?dd*$)|(^-?.dd*$)/,
   numeric: function(v) {
      return this.numericRe.test(v);
   },
   // Decimal Number check
   decNumText: "Only decimal numbers are allowed.",
   decNumMask: /(d|.)/,
   decNumRe: /d+.d+|d+/,
   decNum: function(v) {
      return this.decNumRe.test(v);
   }
});

When we do our item transform within the form region (see our earlier blog post) we simply set a “vtype:email” attribute which will execute our email address regular expression check

Ext.app.textFieldTransform = function(el,pAllowBlank,pvType,pID,pFormValidate,pFormCollection) {
	if (el.dom.className.indexOf("aext-form-textfield") == -1) {
		el.dom.className += " "+"aext-form-textfield";
		var tf = new Ext.form.TextField({
			 "id"        : el.dom.id,
			 "vtype"     : pvType,
			 "allowBlank": pAllowBlank,
			 "applyTo"   : el.dom.id
		});
		tf.render();
		if (pFormValidate) {
			tf.on({
				"invalid": function(field, msg) {
					Ext.app.fieldInvalid(pID,pFormCollection,this,msg);
				},
				"valid": function(field, msg) {
					Ext.app.fieldValid(pID,pFormCollection,this,msg);
				}
			});
		}
	}
}

So thats the javascript side covered but our form is generated by APEX so how do we enable it? We define the vtype in the comments section for our form item within APEX (similar to ApexLib), and when building our JSON form metadata object in our page header via an application level PLSQL process, we simply add this vtype setting by checking the comments section of the item to see if a vtype has been enabled, i.e. we query “apex_application_page_items”. The metadata object is passed as a parameter to our create Ext form function which is defined within our region template.

Here’s an example of our metatadata object

var extR3396723937030638 = {
   "title": "Manage Users",
   "autoWidth": true,
   "autoHeight": true,
   "width": 700,
   "collapsible": true,
   "frame": false,
   "height": 700,
   "draggable": true,
   "iconCls": "icon-form",
   "shadow": false,
   "floating": false,
   "P5_DESCRIPTION": {
      "dataType": "textarea",
      "nullable": true
   },
   "P5_EMAIL_ADDRESS": {
      "dataType": "string",
      "nullable": false,
      "vType": "email"
   },
   "P5_ENABLED": {
      "dataType": "string",
      "nullable": false
   },
   "P5_FIRST_NAME": {
      "dataType": "string",
      "nullable": false
   },
   "P5_KNOWN_AS": {
      "dataType": "string",
      "nullable": true
   },
   "P5_LAST_NAME": {
      "dataType": "string",
      "nullable": false
   },
   "P5_PASSWORD": {
      "dataType": "password",
       "nullable": false,
      "vType": "password",
      "matches": "P5_PASSWORD_CONFIRM"
   },
   "P5_USERNAME": {
      "dataType": "string",
      "nullable": false
   },
   "P5_USER_ID": {
      "dataType": "int",
       "decimalPrecision": 0,
      "nullable": false
   },
   "P5_COMPANY": {
      "dataType": "string",
      "nullable": false
   },
   "P5_PASSWORD_CONFIRM": {
      "dataType": "password",
      "nullable": false,
      "vType": "password",
      "matches": "P5_PASSWORD"
   }
};

And here’s our region form template javascript call, which calls our transform functions

Ext.app.apExtForm("panel#REGION_ID#", tbar#REGION_ID#, title#REGION_ID#, ext#REGION_ID#, formValidate#REGION_ID#, "#REGION_ID#");
......

      Ext.app.inputFieldTransform(pPanel, pMetaObj, true, pFormCollection, pRegionId);
      Ext.app.selectTransformById(pPanel, pMetaObj, true, pFormCollection);
      Ext.app.textareaResizable();

My colleague mentioned that APEX 4.0 is going to provide us with client side validations and plugin capability, which is great, but I’d be surprised if the validations have been implemented as well as Ext (highlighting enabled, mouseover validation messages etc.), and event based which you can easily fire off functions to perform actions when validation failures occur. As for the plugin capability I can’t wait, I’m currently thinking that we could potentially create custom Ext email item types, phone number types, sliders, etc. Anyway it’s not out yet so I’ll reserve judgement until it is. Here’s an example screenshot from our development environment for our Ext enabled validations…

Following on from the last post, given that we were automatically creating hidden items to support the grid filters we still needed to add the conditions to the SQL query. I toyed with the idea of putting the required additional query structure in a table, out to a file, or to the screen, but I thought: Why don’t I just update the query source myself dynamically. So next comes the problem, is there an API call to do this or will I have to update an APEX table…..

Initially I though I found my answer, wwv_flow_api.set_plug_source, but after implementing the code to do the update I was getting ORA errors relating to LOB(s) given that the API call requires a varchar2 for plug_source but the underlying table column used a CLOB I came to the conclusion that either I had the wrong API call or it was simply deprecated/not in use.

So the solution I adopted was to update the APEX table directly (yeah I know bit of hack and not supported but come on it’s not like I’m going to break anything, well unless my code is wrong which happened during development on multiple occasions. Remember always ensure you have a good backup policy in place! Oh and don’t forget to ammend this when you upgrade APEX in the future!): “apex_030200.WWV_FLOW_PAGE_PLUGS”

Here’s the dependency SQL…

grant select,update on apex_030200.WWV_FLOW_PAGE_PLUGS to USERNAME
/
create synonym USERNAME.WWV_FLOW_PAGE_PLUGS for apex_030200.WWV_FLOW_PAGE_PLUGS
/

Before I get to the underlying PLSQL code, as this is a bit of a hack and only required in development I decided that I wanted to code it in such a way that the grant will only be provided in the development database however the PLSQL package it resides in, will be migrated to production, therefore I’d use “EXECUTE IMMEDIATE” to perform the update so that the package would still be valid even if he grant wasn’t applied.

Here’s the PLSQL source extract…

.............
          --
          -- For dates we will apply the defined format otherwise we will rely on
          -- the application format default
          --
          IF c1.data_type = 'DATE' AND c.format_mask IS NOT NULL THEN
            v_qry_filter := v_qry_filter ||' AND (('||c.column_alias||' <= '
                                         ||'to_date(:'||v_item_name||','''||c.format_mask||''')) OR (:'|| v_item_name
                                         ||' IS NULL))'||CHR(10)||CHR(13);
          ELSE
            v_qry_filter := v_qry_filter ||' AND (('||c.column_alias||' <= :'
                                         ||v_item_name||') OR (:'|| v_item_name
                                         ||' IS NULL))'||CHR(10)||CHR(13);
          END IF;
..........
    IF v_items_added THEN

      --
      -- Allocate memory (temp table space) for the clob.
      --
      dbms_lob.createtemporary
      ( lob_loc  => v_qry_source
      , cache    => TRUE
      ) ;

      --
      -- Lets make sure we have the grant to update the table
      --
      FOR c_has_tab_priv IN (
        SELECT table_name
        FROM   user_tab_privs
        WHERE  table_name = 'WWV_FLOW_PAGE_PLUGS'
        AND    owner      = wwv_flow.g_flow_schema_owner
        AND    privilege  = 'UPDATE'
      ) LOOP

        --
        -- We need to update the query definition with our new filters
        --
        FOR c IN (
          SELECT region_id
          ,      region_source
          FROM   apex_application_page_regions v
          WHERE  application_id  = v('APP_ID')
          AND    page_id         = v('APP_PAGE_ID')
          AND    template IN ('Ext.grid','Ext.grid.edit')
        ) LOOP
          --
          -- To simplify adding our filters to an exisiting query we will simply
          -- wrap the query in an inlinve view and perform the filters on the inline view
          --
          dbms_lob.write
          ( lob_loc     => v_qry_source
          , amount      => length('SELECT * FROM (')
          , offset      => 1
          , buffer      => 'SELECT * FROM ('
          );       

          dbms_lob.append
          ( dest_lob     => v_qry_source
          , src_lob      => c.region_source
          );       

          dbms_lob.write
          ( lob_loc     => v_qry_source
          , amount      => length(') WHERE 1=1 '||v_qry_filter)
          , offset      => DBMS_LOB.getlength(v_qry_source)+1
          , buffer      => ') WHERE 1=1 '||v_qry_filter
          );       

          --
          -- Lets parse the SQL query before saving it, and we won't save it if it's not valid
          --
          c_id := dbms_sql.open_cursor;
          dbms_sql.parse(c_id, v_qry_source, dbms_sql.native);
          dbms_sql.close_cursor(c_id);

          --
          -- Lets using EXECUTE IMMEDIATE rather than a explicit update statement
          -- in case people may not want to apply the grant on  WWV_FLOW_PAGE_PLUGS
          --
          EXECUTE IMMEDIATE 'UPDATE WWV_FLOW_PAGE_PLUGS '||
                            'SET    plug_source = :b1 '||
                            'WHERE  id          = :b2 '||
                            'AND    flow_id     = :b3 '||
                            'AND    page_id     = :b4'
          USING IN v_qry_source, c.region_id, v('APP_ID'), v('APP_PAGE_ID');

          --
          -- We need to erase the lob in case there is more than 1 grid on the page
          --
          v_source_length := DBMS_LOB.getlength(v_qry_source);

          dbms_lob.erase
          ( lob_loc     => v_qry_source
          , amount      => v_source_length
          , offset      => 1
          );
        END LOOP;
      END LOOP;

      --
      -- Free the temporary LOB
      --
      dbms_lob.freetemporary(v_qry_source);

    END IF;

The end result is that we can create a normal APEX report via the wizard, set the template to our Ext.grid template, update the column attributes to enable/disable filtering, and we’re done. It takes like 2 minutes to complete the report in full, so we get a big productivity boost by dynamically generating the items and appending the query source with our additional filters.

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') OR :P9_EXT_MODIFICATION_DATE_EQ IS NULL)
AND instr(upper("NAME"),upper(nvl(:P9_EXT_NAME,"NAME"))) > 0
AND (geoname_id = :P9_EXT_GEONAME_ID_EQ OR :P9_EXT_GEONAME_ID_EQ IS NULL)
AND (geoname_id >= :P9_EXT_GEONAME_ID_GT OR :P9_EXT_GEONAME_ID_GT IS NULL)
AND (geoname_id <= :P9_EXT_GEONAME_ID_LT OR :P9_EXT_GEONAME_ID_LT IS NULL)
AND modification_date BETWEEN to_date(nvl(:P9_EXT_MODIFICATION_DATE_GT,'01-JAN-0001'), 'DD-MON-YYYY') AND to_date(nvl(:P9_EXT_MODIFICATION_DATE_LT,'01-JAN-2999'), 'DD-MON-YYYY')
AND (population > :P9_EXT_POPULATION_GT OR :P9_EXT_POPULATION_GT IS NULL)

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!

In this post we are focusing on transforming a standard APEX form into an Ext flavoured form simply by setting a custom region template which we have built. We will be replacing the Form layout with an Ext Panel and we will be changing the input fields to their Ext equivalent. We will be using the Ext basic form level validation which we previously posted about with the APEX Tabular Form.

For the design approach we use the same method as our report, we create a JSON metadata object in our page header based on the APEX Form definition in the APEX data dictionary. Whilst we build our JSON object we use the apex dictionary details to query the database data dictionary to work out what types of fields to convert them to. i.e. for VARCHAR2 we use TextField, for NUMBER we use NumberField, etc.

The advantage of this is that there are some extra restrictions the Ext form fields impose for us. For example in a number field it will only accept numbers even if you type text it will not appear in the input field. Number fields can also have a decimal precision set so you if you enter more than X many decimal places they are removed. Date fields require a specific format and a validation error will be displayed if it does not conform. The great thing about this is that it cleans your data before page submission and negates the server side validation requirement although it’s still worth having it there as a backstop.

The high level overview of how ts done…

1. We create a new Ext panel and set the body to be our APEX form region
2. We query our JSON metadata object using the Region ID to work out what attributes to set e.g. title, width, collapsible etc. (Note: we have enabled a form option button similar to the grid options we blogged about previously for customizing the widget)
3. We grab the particular region and it’s input fields we are interesed in by using an “Ext.get(FormRegion).select(”input,textarea,select”).each” javascript call
4. We then use the element id e.g. P4_NAME to query our JSON metadata object in the page header which is output by our page process. This has a number or attributes which we are interested in such as Data Type, Format, Decimal Precision, Nullable etc.
5. We then perform a switch statement in javascript based on the data type and hand it off to a javascript transform function which we have written which will turn it into the Ext equivalent field with any additional parameters defined in the metadata object. i.e. if nulls are allowed, Date Format etc..
6. Once rendered, if any validation issues occur we simply disable the top and bottom toolbars on the Panel so the form can’t be submitted and simply enable them once the entire form is valid for submitting.

For this particular integration piece we are relying on APEX form submission, so we don’t need to worry about defining the region as an Ext.form.FormPanel as APEX will handle the processing via the “Automatic Row Processing (DML)” process that’s created via the wizard. Our form buttons use the APEX javascript function “doSubmit”. We can still use all available APEX features which we like for this form, e.g. computations, processes, validations etc. We are simply changing the look and feel to Ext but still processing the form itself via APEX. This gives us a flexible solution and has reduced the development time for producing this integration piece. In the near future we will be looking to implement a full AJAX version of the form with processing masks etc. when we have more time (know the feeling?? i.e. to much to do not enough time).

Here’s a good point to note about the integration. As everything occurs in the region template we can simply change the region template for the form back to our APEX theme Form Region and all Ext functionality will be removed, there is nothing that you need to change with your form elements or at the page/application level. Simple and clean reusable design, which is our motto by the way!

Here’s a preview of the end result…

I’m a fan of Apexlib, and it’s geen a great utility for providing some of the missing functionality APEX was lacking. One of those features was dynamic validations for “not null” and “date format” form fields. With the integration kit we are building we are looking to offer an Ext equivalent of everything Apexlib provides “and more” so this is one of the tasks we needed to deliver. The effort required to achieve this? Not much at all… since we transform our (tabular) form elements (one of our earlier posts) into Ext equivalent fields we get the benefit of using config settings such as “allowBlank” and “format” which do the validations for us. All thats required is that we set these config values accordingly.

When a validation failure occurs we simply disable the grid top toolbar so the page can’t be submitted and thus resulting in a reduction of required server side validations (but of course it doesn’t hurt to have them in case of an unexpected page submission, especially when not null ones are auto generated by the APEX engine for you). It’s just a nicer experience for the end user when they don’t have to submit pages all the time, not to mention less server side processing.

Here’s our function to transform our form date fields into an Ext equivalent

// convert inputs with class "ext-form-date-picker" to date fields
function extGridDateFields(pID, pRptMetaObj, pGridValidate, pGridValCollection) {
   var els = Ext.get(pID).select("[class=ext-form-date-picker]");
   els.each(function(el) {
      if (el.dom.className.indexOf("aext-form-date") == -1) {
         try {
            var lFormat = eval("pRptMetaObj." + el.dom.name + ".dateFormat");
            var lAllowBlank = eval("pRptMetaObj." + el.dom.name + ".nullable");
         } catch(e) {
            var lFormat = 'd-M-Y';
            var lAllowBlank = true;
         }
         el.dom.className += " " + "aext-form-date";
         var df = new Ext.form.DateField({
            "allowBlank": lAllowBlank,
            "applyTo": el.dom.id,
            "format": lFormat,
            "altFormats": 'j|j/n|j/n/y|j/n/Y|j-M|j-M-y|j-M-Y'
         });
         df.render();
         if (pGridValidate) {
            df.on({
               "invalid": function(field, msg) {
                  gridFieldInvalid(pID, pGridValCollection, this, msg);
               },
               "valid": function(field, msg) {
                  gridFieldValid(pID, pGridValCollection, this, msg);
               }
            });
         }
      }
   })
}

And here’s the supporting validation code (we use an Ext Mixed collection to keep track of the validation error, when none exist the top toolbar is enabled, when one or more exist the top toolbar is disabled).

function gridFieldInvalid(pGridID, pCollection, pField, pMsg) {
   if (!pCollection.key(pField.id)) {
      pCollection.add(pField.id, pField);
   }
   Ext.getCmp(pGridID).getTopToolbar().disable();
}
function gridFieldValid(pGridID, pCollection, pField, pMsg) {
   if (pCollection.key(pField.id)) {
      pCollection.removeKey(pField.id);
   }
   var isEmpty = true;
   pCollection.each(function() {
      isEmpty = false;
   });
   isEmpty ? Ext.getCmp(pGridID).getTopToolbar().enable() : Ext.getCmp(pGridID).getTopToolbar().disable();
}

How do we know what is nullable and what requires date formats? We query the database data dictionary using the defined values under the column attributes for each column in the report. We then add the details for any columns which require validations to our existing JSON report meta data object which we print out in our page header. We get the benefit of two metadata dictionaries with APEX, the application and the database. I think the emerging trend of “thick databases” will start to take hold when more people realize the main benefits are reduced development time and costs…. two of the most important things in today’s climate!