Handle Exceptions in Oracle Workflow

While working with Oracle workflow, one frequent requirement is to handle errors in Functions that are used by workflow Activity. There are two ways to handle this requirement.

Approach 1 – Do Nothing

Yes, you read it correctly, in the function just write your code without doing anything special to capture the exception i.e. do not put any exception block. Workflow will automatically capture the exception and display appropriate error code and error message against the activity where the error was encountered in Activity History.

Approach 2 – Use WF_CORE API’s

Add an exception block in your code and call WF_CORE.CONTEXT API, the syntax for which is as follows. The first two parameters for this functions are the package name and the procedure name, from third parameter onwards you can pass any argument. This can be used to set any values which are specific to the procedure and which can help in debugging.

procedure CONTEXT (pkg_name  IN VARCHAR2,

                   proc_name IN VARCHAR2,

                   arg1      IN VARCHAR2 DEFAULT '*none*',

                   arg2      IN VARCHAR2 DEFAULT '*none*',

                   arg3      IN VARCHAR2 DEFAULT '*none*',

                   arg4      IN VARCHAR2 DEFAULT '*none*',

                   arg5      IN VARCHAR2 DEFAULT '*none*');

For example the call can be as follows.

BEGIN

 ...

EXCEPTION

  WHEN OTHERS THEN

             wf_core.CONTEXT (pkg_name    => 'XXARM_ADDRESS',

                              proc_name   => 'ADDRESS_AR_OUTGOING_PROCESS',

                              arg1        => pv_itemtype,

                              arg2        => pv_itemkey,

                              arg3        => pn_actid,

                              arg4        => pv_funcmode,

                              arg5        => ‘ERROR :'|| SQLCODE || ‘ ‘ || SUBSTR(SQLERRM,1,300));

    RAISE;

END;

In the above call an interesting point to note is the arguments passed in fields from arg1 to arg4. If you note these are the values which the workflow passes to the function, so now you have the same values and it is very easy to debug the function. In arg5 we can capture the SQL error that was encountered.

If the control goes in the exception block as defined, an entry is added to the error stack to provide context information that helps locate the source of an error. In the workflow monitor you can view the details of the above error  by navigating to ‘Activity History’ and clicking on the ‘Error’ link in the Status column on the activity which has got error.