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.

Continuous Integration - For Oracle Apps?

What is Continuous Integration? 

Continuous Integration is a software development practice where members of a team integrate their work frequently, usually each person integrates at least daily - leading to multiple integrations per day. Each integration is verified by an automated build (including test) to detect integration errors as quickly as possible.

Why should you use Continuous Integration in your project?

When embarking on a change, a developer takes a copy of the current code base on which to work. As other developers submit changed code to the source code repository, this copy gradually ceases to reflect the repository code. The longer a branch of code remains checked out, the greater the risk of multiple integration conflicts and failures becomes when it is reintegrated into the main line. When developers submit code to the repository they must first update their code to reflect the changes in the repository since they took their copy. The more changes the repository contains, the more work developers must do before submitting their own changes.

Eventually, the repository may become so different from the developers' baselines that they enter what is sometimes called "integration hell", where the time it takes to integrate exceeds the time it took to make their original changes. In a worst-case scenario, developers may have to discard their changes and completely redo the work.

Continuous integration involves integrating early and often, so as to avoid the pitfalls of "integration hell". The practice aims to reduce rework and thus reduce cost and time.

Principles of Continuous Integration or Activities that make effective Continuous Integration

Maintain a code repository (i.e. a version control system) – This is the most basic requirement without this it would be impossible to manage multiple versions of code.
Automate the build – Getting code sources into running systems can be a tedious and error prone process without automation. Tools such as ‘make’, ‘ant’ help you with this.
Make your build self testing – This is the most important step to evaluate whether your build was successful. Traditionally a build means compiling, linking, and all the additional stuff required to get a program to execute. A program may run, but that doesn't mean it does the right thing. A good way to catch bugs more quickly and efficiently is to include automated tests in the build process. Tools such as ‘XUnit’ can be used for this activity. For end-to-end kind of testing tools such as ‘Fitnesse’ can be used.
Everyone commits to the baseline every day and Every commit (to baseline) should be built - By committing regularly, every committer can reduce the number of conflicting changes. Checking in a week's worth of work runs the risk of conflicting with other features and can be very difficult to resolve. Early, small conflicts in an area of the system cause team members to communicate about the change they are making. More importantly this makes it easier to identify the area in the code which might have caused the bug to be introduced.
Keep the build fast - The build needs to complete rapidly, so that if there is a problem with integration, it is quickly identified.
Test in a clone of the Production Machine – This is so that you identify bugs in the kind of environment in which the code is finally supposed to run.

Where can I find more information?

Continuous Integration concept was proposed by Martin Fowler and his website has an excellent essay on this topic. http://www.martinfowler.com/articles/continuousIntegration.html

Can this be used in Oracle Apps projects?

As described above, the need for Continuous Integration arises when multiple developers work have to work on the same code. In case of Oracle Apps, most of the source files are such that at a time only one person can work on it for e.g. Forms (FMB), Reports (RPT), Workflow (WFT) etc.  (These are binary source files and it is not easy for the someone to identify the changes between two different versions, hence I say that at a time only one person can work on it. In contrast source files written in text format, for e.g. Java program lend themselves easily to be worked on by multiple people at the same time). Although there are files such as PL/SQL packages which are written in text format, but they are rarely required to be worked on by multiple people and there aren’t frequent updates required to them.

Also looking at the big picture, any development work that happens in Oracle Apps does not involve developing something from scratch, where many people are working to build the same system, it is always a bolt on to the basic technology framework of Oracle Apps. This reduces the issues faced while integrating new developments, which are normally encountered in projects using technologies such J2EE, .NET etc.

I therefore believe, Continuous Integration would be more appropriate to use in a product development project using technologies such as J2EE or .NET. Complex product development projects normally require frequent updates to functionality, leading to new releases, where the using Continuous Integration would really help.