Mailing Map Errors

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;

create or replace function GET_ACTIVITY_ERRORS(
cursor cAllErrors is
  select to_char(p.created_on, 'HH:MM:SS DD-MON-YY') || ' '||
  p.execution_audit_id || ' '||
  p.message_text  AuditText
  from all_rt_audit_exec_messages p
  where execution_audit_id in (
   select max(execution_audit_id)
    from all_rt_audit_executions a
    where a.parent_execution_audit_id=FLOW_AUDIT_ID
    and a.execution_name=PROCESS_FLOW_NAME||':'||ACTIVITY_NAME);

errorText VARCHAR2(4000) := 'Error in activity '|| ACTIVITY_NAME || ' : ' || chr(10) || chr(10) ;

    if (html_result) then
      errorText := '<br><b><u>Error in activity '|| ACTIVITY_NAME || ' : </u></b><br>' || chr(10) || chr(10) ;
    end if;
    FOR aerr in cAllErrors LOOP
      if (html_result) then
        errorText := errorText || '<br>  ' || aerr.AuditText || '</br>' || chr(10);
        errorText := errorText || '  ' || aerr.AuditText || chr(10);
      end if;
    RETURN errorText;
            return errorText;  -- return whatever is in the buffer

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.

Mail Flow:

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.

Mail Flow Audit Id:

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;

Mail Flow Body:

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

Mail OWB 1:

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.

Mail Flow Xform:

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

Mail OWB 2:

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.....


I'm having trouble trying to implement this-- when I try to pass PARENT_AUDIT_ID to the transformation param FLOW_AUDIT_ID, I get the error "identifier 'PARENT_AUDIT_ID' must be declared." I made sure to set literal=false when defining the param value. I am also referencing PARENT_AUDIT_ID in the subject line of my email (SUBJECT = 'Process Flow Error (Audit_ID '||PARENT_AUDIT_ID||')' ) and it works fine there.

Posted by Dave Katz on November 16, 2007 at 03:42 AM PST #

I heve some problems with passing the global variable. I get followin errors in RAB: RPE-01038: Failed to evaluate expression declare l_expression NUMBER := PARENT_AUDIT_ID;begin :result := to_char(l_expression);end;. Please modify the expression, redeploy and retry again. ORA-06550: line 1, column 34: PLS-00201: identifier 'PARENT_AUDIT_ID' must be declared ORA-06550: line 1, column 24: PL/SQL: Item ignored ORA-06550: line 1, column 77: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 1, column 58: PL/SQL: Statement ignored

Posted by Jurgen on December 04, 2007 at 08:59 PM PST #

Do you have the property 'literal' set to false and the workflow user in OWB defined as a target user?

Posted by David Allan on December 05, 2007 at 06:07 AM PST #

"yes" to both questions. I checked what Dave did: use it in an email subject and it works ok there for me I'm in the same situation as Dave.

Posted by Jurgen on December 12, 2007 at 11:28 PM PST #

What would be the recommended way to report on any error that occurred earlier, i.e. for a flow with sub-processes and numerous mappings? I am thinking about just including get_activity_errors at the end of the flow.

Posted by julian on December 17, 2007 at 06:13 AM PST #

How I cant create the variable EMAIL_BODY

Posted by Joan on January 25, 2011 at 11:05 PM PST #

Hi Joan You have to click on the Variables node in the process flow editor's Structure (11gR2) or Object Details panel. There is a blog post below illustrating for 10gR2/11gR2 how you add a parameter which is similar, for variable, select the Variables node in the tree, then the + sign in the panel goes green at which point you can click and add a variable. Cheers David

Posted by David Allan on January 25, 2011 at 11:47 PM PST #

I have a mapping with unique constraint violated but I am getting an empty email from the body! Can you help me out? Do I need to set up the ACTIVITY_NAME, HTML_RESULT AND PROCESS_FLOW NAME ? this is what I have on my execution of the workflow ACTIVITY_NAME(in):null FLOW_AUDIT_ID(in):14245 HTML_RESULT(in):FALSE PROCESS_FLOW_NAME(in): null GET_ACTIVITY_ERRORS(out):Error in activity Thank you in advance!

Posted by Jeff on May 25, 2011 at 08:05 AM PDT #

I am having the same problem as Jeff in regards to a blank email. This is all I get in the body of the email Error in activity : . I added the Parent_Audit_Id to my subject to make sure it was pulling something and part works. Any suggestions or solutions?

Posted by guest on October 18, 2011 at 01:38 AM PDT #

Are you using the OWB mail activity in the process flow? Did you set the MESSAGE_BODY parameter for the mail activity?


Posted by David on October 18, 2011 at 04:05 AM PDT #

My error email step has the binding set to EMAIL_BODY variable which is linked to the GET_ACTIVITY_ERRORS output.

Posted by Larry on October 18, 2011 at 05:19 AM PDT #

How did you get it ? Is it a global variable ?? like PARENT_AUDIT_ID ??

Posted by guest on June 13, 2012 at 06:37 AM PDT #


I think I supplied those parameters as literals in my process flow design. The list of global variables you can reference from expressions is defined below;

There are variables like AUDIT_ID and PARENT_AUDIT_ID that you could use to make it more generic.


Posted by guest on June 13, 2012 at 07:58 AM PDT #

I tried to switch the function return datatype to clob. It works with varchar2, but I need more space to output the text.

When I try to execute the workflow I am getting: "ORA-22922: nonexistent LOB value ORA-06512: at line 1".

I am wondering if it is perhaps not possible to pass a clob value between a function activity and an email activity. The only datatype for setting up a binding variable is string, and maybe this will not work for a clob??

Any suggestions for passing along html text to an e-mail that exceeds 4000 characters?

Posted by guest on October 17, 2013 at 07:11 AM PDT #

can you tell me where I have to change the email-adress for a new recipient. Sorry, Iam a beginner for OWB.

Thanks Heike

Posted by Heike on March 21, 2014 at 02:28 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.


« June 2016