Why Funcmode is important

I recently had a question where the developer had built a process which included a loop.  Within that loop, there was a custom function which determined which level of approver was required before applying processing logic, something similar to this:

The problem was that when the “Do Something” step was being executed, it appeared that the “Find Approval Level” step was always jumping two levels – the first and third approvers were being found, but never the second or fourth.  So, there must have been something wrong in the “Find Approval Level” step if it was always jumping two levels, right?

Wrong!  The problem was that the loop activities in the process hadn’t been defined properly, and the code hadn’t been written correctly in a very basic manner.

The Workflow activities should have had the “On Revisit” flag set correctly for the activities that were in the loop.  The standard is to have it set to “Reset” which will re-run the activities in CANCEL mode before re-running the loop in RUN mode.  Instead, the activities should have had the flag set to “Loop”:

If the flag is set to “Ignore”, then the activity would only be executed once – which means that you would end up in a perpetual loop in this example, since the “More Required?” step would always return “Yes” unless there was only one approval level.

The basic change to the code is to check what the FUNCMODE is before doing any further processing.  If the code was written so that the search for the approver only happened during RUN mode, then the process wouldn’t appear to skip a level.  Unless there is specific code to undo an execution, then I always include a check at the start of my activities which says that if FUNCMODE does not equal WF_ENGINE.ENG_RUN (the externalized constant for RUN mode), then simply RETURN to the Workflow Engine.  This saves processing time and avoids problems like the one we see here.

Posted in Oracle, Technical | No Comments »

Why do I have to wait for my WAIT?

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?

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

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 »

Aborting Workflows – Adding A Date Restriction

Back in April, there was a post on OTN relating to whether you could specify the date when aborting a Workflow process using the standard WF_ENGINE.AbortProcess API. The reason seemed to be that the user only wanted to purge data according to a specific date (e.g. up to the end of 2010).

As I said in my response, it doesn’t make sense to try to convince the system that you did something on a different date. If you set it to BEGIN_DATE+10, what happens to the child activities which occurred on BEGIN_DATE + 11? You would have an end date that was before the begin date, which doesn’t do much for any auditing and reporting that you want to do.

If the requirement is actually to abort and purge processes which started before a certain date, then the code below should help:

BEGIN
  FOR i IN ( SELECT item_type
             ,      item_key
             FROM   wf_items
             WHERE  1 = 1 ) LOOP
    WF_ENGINE.AbortProcess ( i.item_type, i.item_key );
  END LOOP;

  FOR j IN ( SELECT item_type
             ,      item_key
             FROM   wf_items
             WHERE  TRUNC(begin_date) <= TRUNC(TO_DATE('31-DEC-2010'
                                         ,'DD-MON-YYYY')) ) LOOP
    WF_PURGE.items ( j.item_type, j.item_key );
  END LOOP
END;

Modify the implicit cursor in the i loop to find the records that you want to abort, modify the cursor in j loop to include any other criteria.

The reason that you have to do them in loops is to ensure that the processes have all been aborted before trying to purge them - the process (and any child processes it spawns) must be complete before it can be purged. Likewise, when purging child flows, the parent must have completed before it can be purged (something I suggested as a feature many years back )

Posted in Oracle, Technical | No Comments »

How to populate the “From” field on a Workflow notification

Another quick one, but it pops up in fora and my email fairly regularly – when a notification is sent to a user / role, there is nothing in the “From” field to indicate who it actually came from. Sometimes, it’s there (in a seeded notification) and sometimes it’s missing, so how do we put something in there?

Firstly, the “from” needs to be defined in the Workflow Directory Service as a role – if you just want to put in “Bob Smith” for example, then you need to make sure that Bob exists in the directory. If not, then you need to create an ad-hoc role to use instead.

Once you have identified the role (and it may already be in your process) then you really should store that in an item attribute. Then you need to define an attribute on your message called #FROM_ROLE. You can set this to a constant, but I would recommend setting it to synchronize with an item attribute so you can easily change it later if necessary. Set the value of the attribute to the role that you want as the from, and save it.

Now, when the notification is sent out, the value will display the display name for the role. As a warning, though – messages aren’t versioned in the database, so you need to ensure that your changes (if you are using a new item attribute to synchronize with, for example) remain valid for any processes which are already running.

Posted in Oracle, Technical | No Comments »

Icons missing in Workflow Builder

I recently had an email asking me what was wrong with their Workflow Builder installation – whatever they did no icons were appearing in the Builder.

Also, there was no Standard item type being opened – when the user tried to create a new Item Type using the quickstart wizard, they got an error message – and it wasn’t even a nice one, as it looked like the messages file hadn’t been installed correctly and all they got was an error name and code.

Some years back, I had exactly the same problem when I had installed Builder on a machine on client site. The installation process had “worked” correctly (i.e. Oracle hadn’t reported any errors), but obviously there was something wrong.

The problem was that the user I was logged in as did not have FULL admin rights on the machine – it had enough to make most of the install work and write some information into the Windows registry, but not enough to actually install EVERYTHING required.

So, the quick fix is to make sure that you are logging into the machine as someone with full rights (typically log into the machine itself rather than onto a network) and then do the install.

Posted in Oracle, Technical | No Comments »

How do I tell if a role is valid?

Just a quick post in response to a question on the OTN forums today – if I have a role, how do I know whether it’s valid or not?

The quickest way to do it (I think!) would be just to check the end date – if the end date is set, and we’re past that point in time, then the role is invalid.  BUT – what if there is more to it than that?  What if Oracle modifies the product so that the end date field isn’t the only thing that needs to be tested?

The way that I recommend doing the check (and I’ve recently written some code around this very subject!) is to use the standard WF_DIRECTORY API to fetch me the information about the role.  If the role is invalid, then there won’t be any data returned; if the rolw is valid, then you get data back.

And here’s the code:

DECLARE
  l_display_name      WF_ROLES.display_name%TYPE;
  l_email_address     WF_ROLES.email_address%TYPE;
  l_notification_pref WF_ROLES.notification_preference%TYPE;
  l_language          WF_ROLES.language%TYPE;
  l_territory         WF_ROLES.territory%TYPE;
  l_module            VARCHAR2(100);
  l_role              WF_ROLES.name%TYPE := '&role';
BEGIN

  WF_DIRECTORY.getRoleInfo ( role                    => l_role
                           , display_name            => l_display_name
                           , email_address           => l_email_address
                           , notification_preference => l_notification_pref
                           , language                => l_language
                           , territory               => l_territory );

  IF l_display_name IS NULL THEN
    dbms_output.put_line('Role is invalid');
  ELSE
    dbms_output.put_line('Role is valid');
  END IF;

END;
/

It can be downloaded here.

Posted in Oracle, Technical | No Comments »

COUNT(*) or COUNT(1) or COUNT(anything else)…

It should be a fairly simple task – count the number of rows in a table.  I recently had the “pleasure” of discussing this with a colleague who was adamant that the best way to count the number of rows in a table was to use something explicit, e.g. COUNT(1) or “even better” COUNT(ROWID).

The basis for his thinking seemed to be OCP Introduction to Oracle 9i: SQL Exam Guide which apparently says something like

Do not use count(*) to determine the number of rows in a table. Use count(1) or count(ROWID) instead. These options are faster because they bypass some unnecessary operations in Oracle’s SQL pocessing mechanism.

Now, I’ve been told both things in the past – I was always taught to use COUNT(*), but some people that I’ve worked with have always used COUNT(0) or similar.  So, as with all these kinds of questions, I turned to Tom Kyte for more information…

Looking at a number of questions and answers on the AskTom site, this seems to have been done to death, so I’ll just post a link to what appears to be a definitive couple of answers: here and here.

As ever, there are a number of neat points made in the threads, but for those that don’t want to read through them all, here’s my concise summary:

• COUNT(1) is optimised to COUNT(*) in the database.
• COUNT(*) is meaningful (it shows we are counting everything), whereas COUNT(rowid) means nothing.
• COUNT(*) is the right way to go.
• This remains true for any version of the database greater than 8.
• This remains true regardless of the column types in the table
• Matt was right.

Posted in Oracle, Technical | No Comments »

Profile Validation and Timeouts – Revisited

Back in September, I wrote a post which had some SQL to provide a list of Workflow timeout values which can be used in profile option values.

The code sample below was something that Gary Derrick put together which does a similar task, albeit more simplified than the version I produced in the original post:

SQL="SELECT a.meaning,
a.v
INTO :visible_option_value,
:profile_option_value
FROM
(SELECT DECODE(LEVEL - 1,0,'No Timeout'
, DECODE(LEAST(LEVEL - 1,59),LEVEL - 1,TO_CHAR(LEVEL - 1)
    ||' Minute'
    ||DECODE(LEVEL - 1,1,'','s')
, DECODE(LEAST(LEVEL - 1,1439),LEVEL - 1,TO_CHAR((LEVEL - 1)/60)
    ||' Hour'
    ||DECODE(LEVEL-1,60,'','s')
, TO_CHAR((LEVEL-1)/1440)
    ||' Day'
    ||DECODE(LEVEL - 1,1440,'','s')))) meaning
, LEVEL - 1 v
FROM dual
CONNECT BY LEVEL <= 7200
) a
WHERE a.v IN (0, 1,2, 60, 120, 1440, 2880)"
COLUMN="Meaning(15)"
HEADING="Timeout(15)"

The code generates a list of values based on the number of minutes you specify in the WHERE clause.  In this example, the list of values would be no timeout (0), 1 Minute(1), 2 Minutes (2), 1 Hour (60), 2 Hours (120), 1 Day (1440), 2 Days (2880) – if you want different values, then change the number of minutes to include in the query.

The value of the profile option can then be retrieved directly by the Workflow process without any need to manipulate it – it’s ready to use in the code, but is also human-friendly in terms of what the LOV displays.

Hope you find it useful – thanks Gary!

Posted in Functional, Oracle, Technical | No Comments »