Why do I have to wait for my WAIT?

July 15th, 2011 Matt

One of the common questions I receive relates to why a process does not wait for the right amount of time when using a standard Wait activity – some times it will wait for the right amount of time, some times a little bit longer, and some times a lot longer (maybe up to an hour!).  Today, I’ll look at how the Wait activity works and why you get the different times for how long you need to wait before the process continues.

The way that the standard Wait activity works (WF_STANDARD.wait) is that it firstly determines whether this is the first or second time that the activity has been visited.  If it’s the first time that the activity has been visited by the process, then the different activity attributes are checked and the “wake up” time is calculated (i.e. when should the process next do something).  Once the time has been calculated, the procedure sets the result to deferred until that time and ends.  If it’s the second time that the activity has been visited, then the process sets the result to complete and ends.

So, rather than a “Wait” activity, it is actually a “Defer Until” activity.  That may not sound like too much of a difference, but there is a fundamental one.  If I suggested that the process will “wait”, then the inference is that as soon as the process has waited for a given period of time, then it will continue – if I was telling a PL/SQL piece of code to wait for a minute then I would call DBMS_LOCK.sleep(60); and as soon as that minute was up, the process will automatically continue.

However, the standard Wait activity doesn’t actually wait and then continue, it defers processing and the Workflow Engine moves on and processes something else instead.  That’s because rather than waiting, the thread has been deferred until a certain point in time which corresponds to the wake up time.

Unfortunately, this means that you need to run something to process those deferred activities – the Background Engine (WF_ENGINE.Background).  One or more Background Engines will need to be scheduled to run at the right frequency to process your deferred activities – which is why sometimes you wait for the right period of time (your Workflow got to the Wait activity just before the Background Engine ran), a short amount of time (the process got there and waited a little while until the next scheduled run of the Background Engine started), or you had to wait a long time (the Background Engine isn’t scheduled at the right frequency to meet the process needs).

Determining the number of Background Engines required, and their frequency of scheduling, is a bit of a dark art – each enterprise will need to monitor that the Background Engines are run at a suitable period but one that isn’t going to thrash the database into submission.  If, for example, you need the process to wait for 10 seconds before continuing, then you need to schedule a background engine to run every 9 seconds or less, so that the wait cannot get to more than 10 seconds.

Having something wait for a short period of time sounds wonderful in theory (“Oh, we’ll just have the process pause for a few seconds while XYZ happens”) but can have a significant impact on the overall performance of the system.  As soon as I hear someone suggest such a thing (which is fairly often!) then the question I always ask is “Why?”  What is so important to the process that you need to wait for 10 seconds, but can’t wait for 5 minutes?  This is particularly appropriate if you are then sending notifications which require a response – you want to pause the process for a minute, to get all the data, so you can send it in an email??

If you really (and I do mean really) need a short wait, then you need to consider what the triggering mechanism is – exactly what are you waiting for?  If that’s something that can be determined by a change of state somewhere then you are better off including a pause in the process via a Business Event and then have your triggering condition raise the event to show that it has completed the work and the process can restart.  Some developers (more than I care to remember!) have a tendency to include a short wait, then a check to see whether the job has completed, and if not, then loop back and wait again.  If you change the development approach to an event-driven one, then this becomes redundant – and it means that if something happens to prevent the action taking place, your Workflow isn’t going to loop and loop and loop, which adds lots more audit and history to the process than you really want.

As a final thought – I have NEVER used a standard Wait activity, since I started with Workflow development.  There are better ways to do it, and always have been (even before Business Event System was invented).

Posted in Functional, Oracle, Technical | No Comments »

How do I restrict the list of users to reassign to?

July 8th, 2011 Matt

Standard Workflow functionality provides the users with the ability to reassign a notification to another user or role (unless you specifically tell the process not to).  However, if you aren’t careful, then everyone in the Workflow directory service is included in that list, which can produce some problems.

Firstly, the end user may not know which of the users they should be using – if there are multiple John Smith’s in the organization for example.  Or if there are two users called “Faisal Mohammad” and “Mohammad Faisal” which one is the right person to be receiving the notification?

Secondly, the business may want to restrict who the notification can be reassigned or transferred to.  For example, if the notification is one requiring an approval, then there may need to be some logic included to ensure that only people in a specific department can be used as the new recipient.

Thankfully, there is a mechanism included in the product which allows you to restrict the reassignment list of values using an attribute on the message.  Here’s how:

Firstly, you need to determine your list of new users and put them all in a Workflow Role.  This could be a new ad-hoc role that you have defined specifically for this one instance of the process, or you may already have a suitable role defined as standard (e.g. if you only want to be able to delegate to users who have been assigned the “UK Super HRMS Manager” responsibility.

Once you know the name of the role which have all the users assigned to it, then you need to store that name in the Workflow process in an item attribute.

The final step is to define an attribute on the message that you want to restrict the list, called #WF_REASSIGN_LOV and synchronise that with the item attribute holding the value of the role.

Once the new definition of the process has been deployed to the database, the messages will now check the value of the attribute and the list of users who can now be set as the new owner will be restricted accordingly.

Posted in Oracle, Technical | No Comments »

Encoding BLOBs into Base64

July 1st, 2011 Matt

I was asked recently whether there was any way to convert a BLOB into Base64 in PL/SQL easily – it’s possible in Java, but the resulting string may well be more than 32k which seems to be a limit in what can be returned.

Digging out the trusty Oracle Built Ins guide, there is a UTL_ENCODE package which can do this, but only up to 32k it seems, because it only handles a RAW datatype.  So, the way round the limitation would be to strip out the first <n> characters in the BLOB, convert them to a raw, then encode them, then put them into a new CLOB, which can handle the greater than 32k limit.

Putting together a few calls to UTL_ENCODE, DBMS_LOB and UTL_RAW (since we need to convert the RAW back into a VARCHAR2 to be able to append it to a CLOB), I came up with the following:

CREATE OR REPLACE PROCEDURE encode_to_base64 ( p_blob IN  BLOB
                                             , p_clob OUT CLOB ) AS
  l_raw          RAW(32767);
  l_length       PLS_INTEGER;
  l_loops        PLS_INTEGER;
  l_pos          PLS_INTEGER;
  l_encoded_raw  RAW(32767);
  l_encoded_vc2  VARCHAR2(32767);
  l_size         PLS_INTEGER;

BEGIN

  l_size := 12288;

  l_length := DBMS_LOB.getLength(p_blob);

  IF l_length > l_size THEN
    l_loops := ROUND((l_length/l_size)+0.5);
  ELSE
    l_loops := 1;
  END IF;

  l_pos := 1;

  FOR i IN 1..l_loops LOOP
    DBMS_LOB.READ(p_blob, l_size, l_pos, l_raw);
    l_encoded_raw := UTL_ENCODE.base64_encode(l_raw);
    l_encoded_vc2 := UTL_RAW.cast_to_varchar2(l_encoded_raw);
    DBMS_LOB.writeAppend ( p_clob, LENGTH(l_encoded_vc2), l_encoded_vc2);
    l_pos := l_pos + l_size;
  END LOOP;

  DBMS_LOB.writeAppend(p_clob,1,CHR(10));

END encode_to_base64;
/

That should do it – hope it’s useful!

Posted in Oracle, Technical | No Comments »