OIC - Microsoft SQL Server Connector Error when used Merge Operation

Question

I tried to use the merge operation with Microsoft SQL Server connector. When I send the PK to database sql server for update the record, I get this error:

Error Summary:

ICS runtime execution encountered {http://schemas.oracle.com/bpel/extension}remoteFault while executing operation: merge Error Reason: oracle.tip.adapter.sa.api.JCABindingException: DBWriteInteractionSpec Execute Failed Exception. merge failed. Descriptor name: [testUpsert.Articolo]. Caused by Exception [EclipseLink-3002] (Eclipse Persistence Services - 2.6.5.v20170607-b3d05bd): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [[B@9faeb15], of class [class [B], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[SysTimestamp-->Articolo.SysTimestamp]] with descriptor [ObjectRelationalDataTypeDescriptor(testUpsert.Articolo --> [DatabaseTable(Articolo)])], could not be converted to [class java.lang.Integer].. Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered not retriable, likely due to a modelling mistake.

Exception Description:

The object [[B@9faeb15], of class [class [B], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[SysTimestamp-->Articolo.SysTimestamp]] with descriptor [ObjectRelationalDataTypeDescriptor(testUpsert.Articolo --> [DatabaseTable(Articolo)])], could not be converted to [class java.lang.Integer].Fault Details : <err:serviceInvocationError xmlns:err="http://xmlns.oracle.com/cloud/generic/service/fault"> err:typeDBWriteInteractionSpec Execute Failed Exception</err:type> err:titlemerge failed. Descriptor name: [testUpsert.Articolo].</err:title> err:detailCaused by Exception [EclipseLink-3002] (Eclipse Persistence Services - 2.6.5.v20170607-b3d05bd): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [[B@9faeb15], of class [class [B], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[SysTimestamp-->Articolo.SysTimestamp]] with descriptor [ObjectRelationalDataTypeDescriptor(testUpsert.Articolo --> [DatabaseTable(Articolo)])], could not be converted to [class java.lang.Integer]..</err:detail> err:errorCodeserviceInvocationError</err:errorCode> err:remedialActionPlease see the logs for the full DBAdapter logging output prior to this exception.</err:remedialAction> </err:serviceInvocationError>

Error Code:

merge {http://schemas.oracle.com/bpel/extension}runtimeFault

Somebody know if is supported by Microsoft SQL Server like operation?

Answer 1

Symptoms

ICS DB Adapter mapping issue

Customer identified that some integrations were failing due to missing endpoints within DB Adapters in ICS. After re-entering Endpoints back to flow, they started running successfully again. Issue looked to be occurring randomly.

Error

Error which we are able to see in ICS Run -

com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.oracle.com/bpel/extension}remoteFault} messageType: {{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage} parts: {{ summary=ICS runtime execution encountered {http://schemas.oracle.com/bpel/extension}remoteFault while executing operation: InsertTrailerRecordInTable_REQUEST ,detail=InsertTrailerRecordInTable_REQUEST {http://schemas.oracle.com/bpel/extension}runtimeFaultError Occurred ,code={http://schemas.oracle.com/bpel/extension}remoteFault} cause: {null}

Changes

Identified - DB endpoint definitions had been changed.

Cause

DB endpoint definitions were changed or modified. If any modifications happen to the stored procs customer as to perform following action, as OIC Database Adapter will generate the Wrapper Packages/Objects for the stored procedures used in the integration

Bug 29396860 - ICS DB Adapter mapping issue (NO Activation/Deactivation) Scheduled Integration

Solution

1. Deactivate the integration
2. Drop wrapper package + generated types. i.e. In the IAR for respective endpoint there should be 2 SQL scripts file. Use the one with name "*_drop.sql" to drop the ICS generated wrapper and types and then reselect the Stored Procedure. This needs to be done for the endpoint whose ever stored procedure is altered.
3. Restart the Agent
4. Re-edit the DB Adapter wizard by selecting the original store package/procedure. This should create new wrapper packages and types
5. Activate integration again
6. Test your flow.