The previous post looked at how to launch a workflow from a database trigger, using the CreateProcess and StartProcess (or the LaunchProcess) APIs. However, I’m really against invoking workflows using the APIs any more – this relies on hard-coding in a number of places that can be potentially difficult to change. If you ever wanted your code to communicate with a different workflow, or with more than one workflow, or to run some additional PL/SQL, or just to be able to switch it off for a while, then you need to make changes to either the workflow or to the code that launches it. This logic could be embedded in a number of different places, some of which you might not be aware of – you should raise a business event instead, and then you can easily change the processing logic through event configuration.

But, I digress – this post is meant to be talking about raising a business event from a trigger. As we saw yesterday, there is a restriction which stops you calling Workflow code from a trigger, since a number of savepoints are issued by the workflow engine. If you try to raise a business event directly from a trigger, you end up in the same situation – there are savepoints that are issued which are not allowed. Even if you try to force the event manager to process the subscriptions in a deferred session, you still hit the same problem. So, how can you do this?

The savepoint issue is only an issue if you are trying to raise an event to the manager locally, i.e. via PL/SQL. If you raise the event externally via an AQ, then there is no savepoint issued, so you can do it from the trigger.

There are a few points to be careful of when doing this though:

1. In order to raise the event from a trigger, you will need to manually build up the WF_EVENT_T ADT to enqueue for processing. If you use the RAISE API to raise the event via PL/SQL, this is all done automatically for you (or you can do it manually if need be), but for raising the event externally this needs to be done by your code.

2. In order to process the event, you need to schedule an inbound agent listener to pick it up – if this is not scheduled to run periodically, then the event will never get raised to the event manager for processing. This means that you will have a delay in processing the business event between the trigger firing and the agent listener processing the queue. If you need the event to be fired immediately, then you cannot do it via raising an event, due to this latency.

3. The message is not enqueued onto the queue until the code that fired the trigger does the commit. This ensures transactional integrity throughout – if the controlling session rollsback, then the message is not visible on the queue. [This only applies if you are using the standard seeded queues, or custom queues where the message is only visible on commit. It is not advisable to use queues where the message can be processed before comitting]

So let’s look at exactly how to do it. Here’s a code sample:

10   BEGIN
20      v_event_name        := 'xx.matt.searle.test.event';
30      v_parameter_t.name  := 'ORG_ID';
40      v_parameter_t.value := v_org_id;
50      v_parameter_list.extend;
60      v_parameter_list(1) := v_parameter_t;
70      v_parameter_t.name  := 'ITEM_NO';
80      v_parameter_t.value := v_segment1;
90      v_parameter_list.extend;
100     v_parameter_list(2) := v_parameter_t;
110     v_event_key := :NEW.LAYER_ID||'-'||TO_CHAR(ln_inv_layer_id)||'-'||TO_CHAR(GetNextEventKey);
120     enqueue_event ( p_event_name       => v_event_name
130                  , p_event_key        => v_event_key
140                  , p_event_parameters => v_parameter_list
150                  , p_msg_id           => v_message_id );
160  END;
Lines 30 and 40 sets the name and value of a parameter to ORG_ID and a value in v_org_id variable. This is then stored in a variable of type WF_PARAMETER_T.
Line 50 adds a new record to the table of parameters.
Line 60 then adds the parameter v_parameter_t to the table of parameters v_parameter_list which is defined as a variable of type WF_PARAMETER_LIST_T as record 1 in the table. When the workflow is initiated, the ORG_ID item attribute will be populated with the value from the parameter. If there is no ORG_ID attribute, then one is created on the fly for you.
Lines 70 to 100 repeat this for the second parameter to pass into the workflow.
Line 110 generates a unique event key, using a function called getNextEventKey to pick a number off a sequence
Lines 120 to 150 will call a procedure to enqueue the message onto an Oracle AQ and return the message ID of the new message on the queue.

The full code (including the enqueue procedure) can be found here.

If you want to make the procedure more generic / resuable, you could include passing a list of the parameters into the procedure and then add these to the event payload. The key thing to note when using this sample is that you need to extend the table for each new parameter, and then make sure that you are setting the right record in the table. You could include something like

v_parameter_count := 0;
v_parameter_t.name  := 'ORG_ID';
v_parameter_t.value := v_org_id;
v_parameter_list.extend;
v_parameter_count := v_parameter_count + 1;
v_parameter_list(v_parameter_count) := v_parameter_t;
to ensure that you don’t get the count of the records in the table wrong, and that you increment the count whenever you extend the table. I’ve not done it that way, but what you choose to do with the code from here is up to you!

Matt

This entry was posted on Thursday, August 21st, 2008 at 9:49 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.