Handling Oracle Exceptions

There may be requirements wherein certain oracle exceptions need to be treated as Warnings and certain exceptions need to be treated as Fatal.

Normally, a fatal Oracle error may not be registered as a warning or row error and the session may not fail, conversely a non-fatal error may cause a PowerCenter session to fail.This can be changed with few tweaking in

A. Oracle Stored Procedure

B. The Oracle ErrorActionFile and

C. Server Settings

Let us see this with an example.

An Oracle Stored Procedure under certain conditions returns the exception NO_DATA_FOUND. When this exception occurs, the session calling the Stored Procedure does not fail.

Adding an entry for this error in the ora8err.act file and enabling the OracleErrorActionFile option does not change this behavior (Both ora8err.act and OracleErrorActionFile are discussed in later part of this blog).

When this exception (NO_DATA_FOUND) is raised in PL/SQL it is sent to the Oracle client as an informational message not an error message and the Oracle client sends this message to PowerCenter. Since the Oracle client does not return an error to PowerCenter the session continues as normal and will not fail.

A. Modify the Stored Procedure to return a different exception or a custom exception. A custom exception number (only between -20000 and -20999) can be sent using the raise_application_error PL/SQL command as follows:

raise_application_error (-20991,’<stored procedure name> has raised an error’, true);

Additionally add the following entry to the ora8err.act file:

20991, F

B. Editing the Oracle Error Action file can be done as follows:

1. Go to the server/bin directory under the Informatica Services installation directory (8.x) or the Informatica Server installation directory (7.1.x).

E.g.,

For Infa 7.x

C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act

For Infa 8.x

C:\Informatica\PowerCenter8.1.1\server\bin

2. Open the ora8err.act file.

3. Change the value associated with the error.
“F” is fatal and stops the session.
“R” is a row error and writes the row to the reject file and continues to the next row.

Examples:

To fail a session when the ORA-03114 error is encountered change the 03114 line in the file to the following:

03114, F

To return a row error when the ORA-02292 error is encountered change the 02292 line to the following:

02292, R

Note that the Oracle action file only applies to native Oracle connections in the session. If the target is using the SQL*Loader external loader option, the message status will not be modified by the settings in this file.

C. Once the file is modified, following changes need to be done in the server level.

Infa 8.x

Set the OracleErrorActionFile Integration Service Custom Property to the name of the file (ora8err.act by default) as follows:

1. Connect to the Administration Console.

2. Stop the Integration Service.

3. Select the Integration Service.

4. Under the Properties tab, click Edit in the Custom Properties section.

5. Under Name enter OracleErrorActionFile.

6. Enter ora8err.act for the parameter under Value.

7. Click OK.

8. Start the Integration Service.

PowerCenter 7.1.x

In PowerCenter 7.1.x do the following:

UNIX

For the server running on UNIX:

1. Using a text editor open the PowerCenter server configuration file (pmserver.cfg).

2. Add the following entry to the end of the file:

OracleErrorActionFile=ora8err.act

3. Re-start the PowerCenter server (pmserver).

Windows

For the server running on Windows:

1. Click Start, click Run, type regedit, and click OK.
2. Go to the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\
PowerMart\Parameters\Configuration

Select Edit; New; String Value. Enter the “OracleErrorActionFile” for the string value.
Select Edit; Modify.

Enter the directory and the file name of the Oracle error action file:

\ora8err.act

Example:

The default entry for PowerCenter 7.1.3 would be:
C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act

And for PowerCenter8.1.1 it would be
C:\Informatica\PowerCenter8.1.1\server\bin

Click OK

Read More about Oracle