Placeholder Image

Background

Oracle Integration exposes various types of metadata in integrations that can dynamically be assigned at runtime. You can access the integration name, identifier, version, and runtime and environment data applicable to each integration.

As part of the runtime attributes, the instance ID provides a unique association with each integration flow for identification purposes. Customers have been persisting this instance or flow ID for a variety of reasons, such as cross-referencing data or sequencing requests. In Oracle Integration Generation 2, the instance ID is a numeric value and is typically stored as an integer in database tables. In Oracle Integration 3 (OIC 3), the instance ID is alphanumeric. Parsing this value as an integer will throw an error. This blog covers two options for adapting to the instance ID change when upgrading to OIC 3.

 

Oracle Integration 3 Upgrade Process

As part of the Oracle Integration Gen2 to Integration 3 upgrade, there is a two-step process which must be completed before an instance can be scheduled for upgrade:

  1. Complete the Upgrade Prerequisites that apply to the instance.
  2. Verify if the instance has passed the upgrade eligibility check on the Upgrade page.

Some of the Upgrade Prerequisites don’t have associated checks on the Upgrade page. One of these prerequisites is the Instance ID change from integer to string.

 

Instance ID Change

The system-generated instance ID (or flow ID) that is displayed on the Instances page and in the activity stream for an integration instance has changed from numerical in Oracle Integration Generation 2 to alphanumeric in Oracle Integration 3. This change will affect integration flows that are based on the instance ID being an integer (for example, when the instance ID is parsed in a database column with a numerical data type). Running these integrations unchanged after upgrading to Oracle Integration 3 will throw a conversion exception error when persisting in a database.

Conversion Exception Description: The object […], of class [class java.lang.String], could not be converted to [class java.math.BigDecimal].

 

Adapting to Instance ID Change

To continue running integrations in Oracle Integration 3 that rely on the flow (or instance) ID, one of the following changes must be considered:

  1. Integration design: map a different numerical value to replace the instance ID.
  2. Database design: change the data type of the column holding the instance ID to string (CHAR/VARCHAR).

 

Let’s review how each of the above changes can be implemented.

Assumptions

The recommended design changes covered in this document are based on Oracle Database services (such as Oracle Autonomous Database). Additional steps could be required for other database types.

Option 1: Change the Mapper to use UNIX Time

This approach involves a design change in the mapper, without any database updates. Instead of the instance ID, we map the UNIX time, which can be parsed as an integer. The UNIX time used in this example will increment every second.

Note: The UNIX time is the number of seconds that have elapsed since the UNIX Epoch, starting with 1st January 1970 at 00:00:00 UTC.

 

Map this expression to the target attribute that previously contained the instance ID value:

floor (((fn:current-dateTime() – xsd:dateTime (“1970-01-01T00:00:00” )) div xsd:dayTimeDuration (“PT1S” )) )

 

OIC Blog - Flow ID changes - mapper unix time

 

Option 2: Alter the Database Column Data Type to a Character String

This approach involves a change in the database column data type, without any integration design changes.

Note: The solution is limited to database actions created using the operation “Run a SQL Statement.”

Run SQL Statement operation

Here’s the syntax of the SQL script to alter an Oracle Database column type, given the column has no data:

ALTER TABLE <table-name>
MODIFY <column-name> VARCHAR(50);

 

If the column is already populated, you receive the following error (or similar) when running the script to modify the column data type:

ORA-01439: column to be modified must be empty to change datatype

 

We use the following approach to change the datatype of the column holding the Instance ID values. Before you start, take note of the Instance ID column name.

1.    Add a temporary column (for example, “TEMP”) of type VARCHAR.
2.    Copy values from the source column (containing Instance IDs) to the temporary column.
3.    Set the source column values to NULL.
4.    Update the source column type from NUMBER to VARCHAR.
5.    Copy Instance ID values from the temporary column back to the source column.
6.    Remove the temporary column.

Run the following steps in SQL Developer.

Step 1: Add temporary column of type VARCHAR2(50)

SQL syntax:

ALTER TABLE table-name
ADD temporary-column VARCHAR2(50);

Here’s an example statement:

ALTER TABLE FLOW_MANAGEMENT
ADD TEMP VARCHAR2(50);
 

Step 2: Copy values from source to temporary column

SQL syntax:

UPDATE table-name
SET temporary-column-name = source-column-name

Here’s an example statement:

UPDATE FLOW_MANAGEMENT
SET TEMP = FLOWID;
 

Step 3: Set source column values to NULL

SQL syntax:

UPDATE table-name
SET source-column-name = NULL;

Here’s an example statement:

UPDATE FLOW_MANAGEMENT
SET FLOWID = NULL;

 

Step 4: Update the source column type from NUMBER to VARCHAR2(50)

SQL syntax:

ALTER TABLE table-name
MODIFY source-column-name VARCHAR2(50);

Here’s an example statement:

ALTER TABLE FLOW_MANAGEMENT
MODIFY FLOWID VARCHAR2(50);

 

Step 5: Copy Instance ID values from temporary column back to source column

SQL syntax:

UPDATE TABLE table-name
SET source-column-name = temporary-column-name

Here’s an example statement:

UPDATE FLOW_MANAGEMENT
SET FLOWID = TEMP;

 

Step 6: Remove temporary column

SQL syntax:

ALTER TABLE table-name
DROP COLUMN temporary-column-name;

Here’s an example statement:

ALTER TABLE FLOW_MANAGEMENT
DROP COLUMN TEMP;

After completing the table statements, you should be able to run the integration without any runtime errors.

Issues after Upgrading to Oracle Integration 3

As a rule, file an SR with Oracle Support as soon as you encounter any unexpected issues.