Oracle Warehouse Builder (OWB) Weblog

Sending mail from map or process flow activities often pops up in questions. The OWB process flow designer has a mail activity that makes sending a mail simple, you may find this inadequate if so, sending mail from the Oracle database is fairly simple, googling around you can find all kinds of stuff (here is one example). When I was trying this out I tried both approaches.

From within a process flow the simplest way I found to get all errors (possible errors initializing activity parameters for map and the map execution itself) is to have a custom function that takes the process flow execution id which you can get from a global OWB variable and pass the name of the flow and the name of the activity in order to query the OWB audit tables (it is not great passing names, but you will get all error types). I have created a simple function which also takes a boolean indicating the format (using HTML or plain text) and will return a string which will be the email body, it can return plain text or HTML formatted text. The OWB mail activity supports a text body, the example I google'd (see SQL here) has both text and HTML.

Here is the code for the function I used in the process flow;

This function is used in the flow using the OWB email activity and in the version which will send HTML formatted emails, I use the boolean to get HTML tokens in the result. With this function I'll get errors such as UK violations when executing the map or issues initializing the parameters for the map such as date format issues (say there was an expression during the evaluation of the mapping activity input parameter representing load date).

The basic process flow has a mapping activity that has a transition for success, all other error paths (activity initiation errors, execution warnings and execution errors) go through the mail path. I tried using the assign activity rather than the transformation activity to get the string representing the email body, I preferred the transformation activity - when an assign activity uses a custom transformation, you will have to qualify the function with the schema since the OWB code does an ALTER SESSION SET CURRENT_SCHEMA to the OWB control center so that the OWB code that the assign implementation references will be resolved. Not exactly good for everyone using it having to hard-code schema names, that's after having setup the evaluation location for the flows. So the transformation activity was used. The output of the function is bound to a variable named EMAIL_BODY. The variable is used in the email activity.



The transformation activity that retrieves the error codes has a parameter which the flow audit id, the OWB global variable PARENT_AUDIT_ID is passed into this parameter, I've set the literal value to false, since this is an expression that needs evaluated.



The output of the function is stored in the process flow variable EMAIL_BODY, you can assign the result of the function to the variable by binding the output parameter GET_ACTIVITY_ERRORS to the variable;



This version of the flow sends a plain simple text email with the audit details;



If we use the PLSQL procedure that sends HTML content we can get richer emails using all of the HTML controls we need, so the new flow uses the send html mail PLSQL procedure and alters the HTML_RESULT parameter to GET_ACTIVITY_ERRORS.



With this approach we can do all kinds of formatting, tables and linking. Here is the output with bold and underlying HTML;



If you need any manual intervention there are 2 mechanisms, using the notification activity which is an elaborate mechanism which relies on the workflow notification, there is also a manual activity that pauses the flow and allows manual intervention which can be performed in the repository browser using the activity expedite, more to come on this.....