Oracle Apex - Forcing Password Change in Custom Authentication

Introduction

If you've created you own authentication scheme in APEX, you may be aware that there's no mechanism to hook into that enforces a password change. Typically you might want your users to change password after 90 days, or if it's just been reset by an admin.

The example in this blog uses a redirect on a global page region to switch to the change password page. It's not a full authentication solution and assumes the following:-

• You have a table called system_users
• The table has a column called "expired" which is either '1' or '0'.
• You have already created a change password page (in my example this is page 106).
• Page 106 has a process to change your password and reset the "expired" flag.
• You already have a working authentication and password change function.

Instructions

1 - Create a function to check if your user account is expired.

(My example below assumes you have a "system_users" table with a column called "expired". But it's only a simple example. Write something better!)

Function check_expired(p_app_user varchar2)
 RETURN NUMBER AS
 l_return NUMBER;
BEGIN
  BEGIN
    SELECT 1
    INTO   l_return
    FROM   system_users
    WHERE  expired = 1
    AND    upper(username) = upper(p_app_user));
  EXCEPTION
    WHEN no_data_found THEN
      l_return := 0;
  END;
  RETURN l_return;
END check_expired;


2 - Create an application item called "G_PASSWORD_EXPIRED" and ensure its scope is 'Global'.

3 - Create an application computation for this item, set the computation point to After Authetication and the type to 'PL/SQL Expression'.

The compution will be:-

  check_expired(:APP_USER);

Create an Application Compution for G_PASSWORD_EXPIRED

4 -  Create a global page and add a region called "Redirect".

5 - Set the region type to 'PL/SQL Dynamic Content' and add the following code:-

IF :G_PASSWORD_EXPIRED = 1 THEN
   APEX_UTIL.REDIRECT_URL('F?P=&APP_ID.:106:&APP_SESSION.::&DEBUG.');
END IF;

(NB. Page 106 is the change password page)

6 - Set the Server-side Condition to 'Current Page is NOT in comma delimited list', and list pages you don't want the redirect to be affected by.

(NB. This should be the login page, and the change password page.)

7 - On your change password page, ensure your ChangePassword process sets the G_PASSWORD_EXPIRED item to zero, or you will be stuck in a loop.

Change Password Page Should also Reset G_PASSWORD_EXPIRED

That's all you need... the global page adds a page redirect and no attempt to hack an alternative page allowed me to get around it.

Oracle APEX - Updating data in a report using AJAX

Overview

I have a simple report showing hundreds of rows of config type information which I wanted to be able to alter quickly. I don't want to open each row in a form, or have to submit a tabular form to update any changes,.. it's all too slow.

What I want instead is a checkbox type column in the report which when clicked causes an AJAX request will be fired off to the server, which in turn runs an update query.

Sounds simple enough!

Setting up the Report

Let's start by looking at the report..
 

I've added a column called 'Ignore' which looks like a radio field/item. When clicked this will run an update to toggle the value in the database table.

Here's SQL that creates this in the report (notice I'm using a graphic of a radio button instead or a HTML form elements).

CASE WHEN sm.method = '3' THEN
         '<img name='||sd.personkey||' src="#IMAGE_PREFIX#pkt_rb_chk.gif" title="Ignored, click to remove" onclick="clicked(this)">'
       ELSE
         '<img name='||sd.personkey||' src="#IMAGE_PREFIX#pkt_rb.gif" title="Click to ignore" onclick="clicked(this)">'
       END ignore,

My database table stores an ignore as a '3' in the 'method' column. And, the reason why I'm using images is because I wanted to store the record key against the images using its name attribute. (It simplifies the javascript.)

Adding the Javascript

Next I added the following code to the Function and Global Variable Declaration on the report page. It uses some handy JQuery functions to make the code easier.

function clicked(obj){
  var personkey = obj.name;
  var img = obj.src.replace(/http:+\/.+\/+/,'');
  var checked;
  if (img == 'pkt_rb.gif') {
    checked = 1;
    var match = $(obj).closest('td').prev('td').text();
    if (match == 'Automatic' || match == 'Manual'){
      // Prompt for confirmation..
      var msg=confirm("This action will remove the current match, continue?");
      if (msg!=true){
        return;
      }
    }
  }
  else { checked = 0; }
  var ajax = new htmldb_Get(null,$v('pFlowId'),
            'APPLICATION_PROCESS=ToggleIgnore',0);
  ajax.addParam('x01',personkey);
  ajax.addParam('x02',checked);
  ajax.get();
  ajax = null;
  $(obj).closest('td').next('td').text('');
  if (checked == 1){
    $(obj).closest('td').prev('td').text('Ignore');
    obj.src = obj.src.replace(/.gif$/,'') + '_chk.gif';
  }
  else {
    $(obj).closest('td').prev('td').text('-');
    obj.src = obj.src.replace(/_chk.gif$/,'') + '.gif';
  }
}

The function takes the image object and uses a regular expression to find out the image name, and thus whether it's selected or not. (This gives us the initial 'ignore' status) We throw up a confirmation box if ignore is being set and then we start the AJAX call.

The AJAX call uses an APEX standard function called html_Get. This handles the request, the parameters you need to pass, and any browser variations. In our example we don't have any values being returned to the browser, but it would be easy to handle this by altering the 'get' command.

var gReturn = ajax.get();

// then do something with this value 

The final stage is to alter the radio image, and its hover-over text. Again we use a regular expression to alter the image name.

Next we need the back-end code that updates the table.

Adding an Application Process

You possibly noticed the AJAX function above is calling an Application Process called 'ToggleIgnore'.

So I created this Application Process using the name 'ToggleIgnore' and used the following anonymous block of PL/SQL code.

DECLARE
  l_person_key varchar2(20);
  l_checkbox varchar2(1);
BEGIN
  wwv_flow_api.set_security_group_id;
  l_person_key := apex_application.g_x01;
  l_checkbox := apex_application.g_x02;

  // Update the table
  switchboard_pkg.set_ignore(
    p_personkey => l_person_key,
    p_checked   => l_checkbox
  );
END;

The parameters are passed into p_personkey and p_checked, and in my example I've stored the update code in a packaged procedure. (It simply runs an update, but you shouldn't need me to go through that part).

Hopefully that's enough to help you get started building your own custom AJAX update methods.

Oracle APEX - Soft Validation using Dynamic Actions

Introduction

It's often a good idea to have validation on your forms in APEX, especially when you have business rules that govern how data can be entered. On a recent system enhancement we wanted to add a 'target bonus' flag to a page, but only allow it to be set to 'Yes' if the 'rebate level' was not set to 'None'.

This is simple enough to do..

Create a Page Validation, with a validation type of 'PL/SQL', 'Function Returning Boolean', then enter code as follows:-

BEGIN
  IF :P4_TARGET_BONUS = 'Yes' AND :P4_REBATE_LEVEL = 'None' THEN
     RETURN false;
  ELSE
    RETURN true;
   END IF;
END;

But it turns out that it's not quite what the customer wanted. There may be instances were it's OK to break these rules, so in that case can we just warn the user by showing a pop-up confirmation box instead.

Confirmation boxes are a UI thing which needs to happen before the page is submitted, so this sounds like a job for a dynamic action.

Using Dynamic Actions For Validation

I deleted the page validation (above) and set about using jQuery and a Dynamic Action to perform the same field comparison, but this time at the browser level.

Adding a Confirm action for soft validation.

1. Create a dynamic action against your submit button and set the following details in the When section.

Event: Click
Selection Type: Button
Condition: Javascript expression
Value: ($v('P4_TARGET_BONUS') == 'Yes' && $v('P4_REBATE_LEVEL') == 'None');

2. Next add a True action for when validation fails..

Action: Confirm
Fire When Event Result is: True
Text: Target Bonus is not allowed while Rebate Level is 'None', continue?

3. Add another True action which will be used if OK is clicked on the confirm action..

Action: Submit Page
Fire When Event Result is: True
Request/Button Name: UPDATE

4. Finally add a False Action to perform a simple submit if validation passes..

Action: Submit Page
Fire When Event Result is: True
Request/Button Name: UPDATE

That's it!... you can make your own validation tests more complex in the dynamic actions javascript box using the standard comparison and logic operators.

Oracle APEX - Advanced Tabular Form Validation

Introduction

In my previous APEX post I took you through a method of adding click-able column help to your tabular forms. I also needed to add validation between my columns, and initially it wasn't clear how I might do this. You can validate against static values, normal item values, or just check for nulls, but nowhere can you validate against a value from another column on the same row.

Tabular Form Requirement

Just to recap from before, I have a table of measures with three columns to hold the weighting, cap and uplift values for each. (shown below)

My Tabular Form

The validation I wanted to add was to ensure that if a Cap value is added then it must be greater than the Weighting value.

One way of performing this type of validation is to use some jQuery code which runs on the browser, but generally its not recommended for security reasons. Instead we'll use a method that replies on the inherent use of collections for tabular tables and write our validation using PL/SQL.

Creating the Validation

1. Run the page containing your tabular form and inspect the input fields from the columns your need to validate to get their id's.

The HTML for the first Weighting field is:-

<input id="f03_0001" type="text" value="0" maxlength="2000" size="2" name="f03"

autocomplete="off">

The HTML for the first Cap field is:-

<input id="f04_0001" type="text" value="0" maxlength="2000" size="2" name="f04"

autocomplete="off">

So the two pieces of information we need here is f03 and f04.

2. Create a page level validation and give it a suitable name. (I'm going to call mine "Check Cap Values Exceed Weighting")

3. Select 'PL/SQL' as the validation type.

4. Then select 'Function Returning Boolean'.

5. Here's the code I entered:-

BEGIN
  FOR i in 1..apex_application.g_f03.count
  LOOP
  IF apex_application.g_f04(i) IS NOT NULL
    THEN IF apex_application.g_f03(i) IS NOT NULL 
      THEN IF TO_NUMBER(apex_application.g_f04(i)) <= TO_NUMBER(apex_application.g_f03(i))
        THEN RETURN false;
      END IF; 
    END IF; 
  END IF; 
  END LOOP;
END;

NB. APEX automatically creates a collection for each column in a tabular form ("apex_application.g_f01", "apex_application.g_f02" etc...) This function loops through the values in columns 3 and 4 comparing the values. 

It's reasonably simple, just don't forget to turn these values into numbers before you do your comparison, or you'll find that 8 is greater than 10 and your validation won't work as you intended!

6. Enter the following for Error Message:-

Cap values must be greater than Weighting, or left empty.

7. Finally set the condition to When Button Pressed 'SUBMIT'.

That's it!

Oracle Apex - Adding Tabular Form Help

Introduction

If you use Oracle APEX I'm sure you like the pop-up help text that you can assign each of your region items. It's part an items configuration, just ensure the UI template "Option with Help" is selected, scroll down to Help Text box and type it in. (Simple)

That's all great, but when I recently found I needed to use a Tabular Form (which is a like an editable report) I went to the column definitions, scrolled to the bottom of the config page, but found it didn't have anywhere to enter help.

Tabular Form Column Help

In the past it's not been a big deal, nobody really asked for it, and you could always add extra stuff to the Page Help,.. but it's not ideal. For this tabular form I needed to be able to explain what the columns were used for because the heading title doesn't do a good job of describing it.

I remembered the method I came up with for Region Help, and figured I'd have a go at adapting this for the column titles. I particular liked this idea because it kept things simple. It uses dummy items to hold the help text which means the help also gets added to the page help without any extra work.

Here's my tabular form:-

My APEX Tabular Form

So lets get right into it using our example above which was on page number 30...

1. Create four Display Only items and ensure you have the 'Optional with Help' UI template selected:

• P30_MEASURE_HELP with label "Measure Column",
• P30_WEIGHTING_HELP with label "Weighting Column",
• P30_CAP_HELP with label "Cap Column",
• P30_UPLIFT_HELP with label "Uplift Column".

    (NB. the label defines how it will look in the page help)
 
2. For each set the help text (as you would for a normal region item).

3. Run the page and you will see these extra item labels showing on your screen, don't panic, this is only temporary. If you have Firebug installed, right click the label and select Inspect Element.

(Which ever browser you use find a way to see to the link html.)

4. Copy the html to your clipboard and paste it into a text editor. It will look similar to this..

<a class="optional-w-help" tabindex="999" href="javascript:popupFieldHelp('2859011741903715','641282443145')">Uplift Column</a>

5. There are two numbers here that are passed to the popupFieldHelp() function, the first one is the help unique id. The second number is your session id which we need to replace so it always shows the current session number. Delete the second number and enter '&APP_SESSION', then remove the word 'Column' from the link text.

The line should now read...

<a class="optional-w-help" tabindex="999" href="javascript:popupFieldHelp('2859011741903715','&APP_SESSION.')">Uplift</a>

6. Now edit the page, right click the report and select 'Edit Report Attributes'.

The completed changes to the report headings

7. Paste your html into the correct heading, and then repeat for the other three items.

8. Finally re-edit your help items and set them to 'Hidden'.

That's it, you're done. Now when you hover your mouse over the titles the '?' appear. All that remains is to click them all to make sure it works.

Oracle Apex - Adding Region Help

Oracle APEX Help Text

Occasionally at work I build systems using Oracle's Application Express and I find I have to stop the web developer within me from going overboard and solving problems in a non APEX way. We had just completed a new system for managing customer discounts and had asked the system owner to come up with field and page help for the application. After a few days we received a spreadsheet full of descriptions and help for most of the pages, but to my surprise they'd included region help. The latter being something that isn't currently implemented in vanilla APEX.

Just to explain a little for those not conversant in APEX, systems are built from a number of pages, and each page can be essentially a report or form. You can have a mix of these by using regions, but as everything must be in a region anyway it's really a case of adding extra regions. Additionally it can be beneficial to split forms across multiple regions to aid clarity or group by function. The following example shows percentage fields used to capture discount percentages for a customer.

Standard Form Region in APEX.

Item help has already been added to this region and this can be seen by clicking one of the labels. This runs a JQuery function called 'popupFieldHelp' and generates a fancy popup like this example.

Item Help Example.

So far all perfectly normal, there's nothing new here!

Additionally these items appear on the Page Help, grouped together under a section title that matches their region title.

What About Region Help?

So everything was great until we were asked for a region help, but as the requirement was valid I couldn't just discard the idea. But I didn't want to write custom JQuery code because my non-developer colleague may need to update it, and I also wanted it to appear in the page help. Here's what I came up with...

1. Add a 'Display Only' page item to your region and choose a sensible name.

(In our example this will be 'P19_ADDITIONAL_DISCOUNTS_HELP').

2. Set the Label text to reference the region name, to be shown in the page help.

(I used 'Additional Discounts Region' for my label.)

3. Add the help text to the item in the normal way entering your region help text.

4. Move the item so that it is first in the region and run the page.

5. Test your new help text by clicking on the item label, then right click it and copy the link code.

(nb. In Firefox this is 'Copy Link Location' and in Internet Explorer it is 'Copy Shortcut'.)

6. Paste the link code into Notepad (or similar) and you should have something like this..

javascript:popupFieldHelp('3439324541165905','112943106205802')

(nb. The first number is your item number, the second is the session number)

7. Edit the region and alter the title to..

<a class="uHelpLink" href="javascript:popupFieldHelp ('xxxxxxxxx','&APP_SESSION.')">Additional Discounts</a>

Where xxxxxxxxx is your item number that we found in the previous stage.

8. Save and run the page and your title will now show as a link (see image below).

Region Help Linked To Region Title.

9. Click the title link and check it works.

10. Finally edit the page again and set the item to 'Hidden' so that it's label no longer shows in the region.

(nb. The item help box will now be hidden when editing the item, but it's still there and available to page and item popup help functions.)

---

Update, 20/01/2015 Method adapted for tabular form columns.