SOA Composite Sensors and the ORA-01461: can bind a LONG value only for insert into a LONG column exception

Last year I wrote about SOA Composite Sensors and how they can be a good alternative for the BPEL Indexes in 10g. This week I was confronted with the "ORA-01461: can bind a LONG value only for insert into a LONG column" exception in one of our composites. It was about a process that is triggered to do some message archiving.

A bit about BPEL Sensors

Funny thing is that this archiving process is triggered by BPEL sensor. To recap: you can create a BPEL Sensor by clicking the monitor icon in your BPEL process:

It's the heart-beat-monitor icon in the button-area top right of the panel. Then it shows the BPEL process in a Layered mode, you can't edit the process any more, but you can add, remove and edit sensors. Sensors are indicated with little antenna icons with an activity. You can put them on any kind of activity. Even Empty activities, what adds extra potential reason to use to an empty activity.

If you click an antena icon you can define a series of sensors, but editing them will bring up the following dialog:

It allows you to add variables and possible expressions to elements within those variables to a sensor. And also add one of more sensor actions that can be triggered on the trigger moment (Evaluation Time) that can be set as well.

A Sensor action can be set as:

In 11g we used the JMS Adapter, but apparently that didn't work anymore the way it was in 12c. So, we changed it to JMS Queues. As with compsite sensors, in the BPEL folder, together with the BPEL process you get two files: YourBPELProcess_sensor.xml containing the Sensor definitions and YourBPELProcess_sensorAction.xml containing the sensor action definitions.

When the sensor is activated, a JMS message is produced on the queue, with an xml following a  predefined xsd. In that XML you will find info about the triggering BPEL instance, like name and instance ID, and a list of variable data. Each of the variables defined in the Sensor is in the list, in the order as defined in the sensor.

By the way, BPEL sensors are part of the product since before 10g...

The actual error case

In our case this message archiving process was triggered from another bpel using a Sensor. The archiving process was listening to the queue as defined in the Sensor Action. Picking up messages that are from certain sensors, using a message selector  based on the sensor name.

On the JMS Interface (Exposed Service) of the message archiving process, I defined a set of Composite Sensors, to be able to search for them. This would help in finding the  archiving instance that belongs to the triggering process. Since sensors work asynchronously, they're not tight together in a Flow Trace.

In some cases, we got the following exception in the Diagnostic log:

[2020-03-11T09:19:50.855+01:00] [DWN_SOA_01] [WARNING] [] [oracle.soa.adapter.jms.inbound] [tid: DaemonWorkThread: '639' of WorkManager: 'default_Adapters'] [userId: myadmin] [ecid: c8e2b75e-7aed-4305-84c5-9ef5cf928c7b-0bb833b1,0:11:9] [APP: soa-infra] [partition-name: DOMAIN] [tenant-name: GLOBAL] [oracle.soa.tracking.FlowId: 463993] [oracle.soa.tracking.InstanceId: 762213] [oracle.soa.tracking.SCAEntityId: 381353] [oracle.soa.tracking.FaultId: 400440] [FlowId: 0000N38eGGo5aaC5rFK6yY1UNay100012j]  [composite_name: MyComposite] [composite_version: 1.0] [endpoint_name: DWN_MyCompositeInterface_WS] JmsConsumer_runInbound: [destination = jms/DWN_OUTGOING, subscriber = null] : weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=org.eclipse.persistence.transaction.JTASynchronizationListener@2d7a86a9[[

Internal Exception: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

Error Code: 1461 javax.resource.ResourceException: weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=org.eclipse.persistence.transaction.JTASynchronizationListener@2d7a86a9

Internal Exception: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

Error Code: 1461
        at oracle.tip.adapter.jms.inbound.JmsConsumer.afterDelivery(JmsConsumer.java:321)
        at oracle.tip.adapter.jms.inbound.JmsConsumer.runInbound(JmsConsumer.java:982)
        at oracle.tip.adapter.jms.inbound.JmsConsumer.run(JmsConsumer.java:893)
        at oracle.integration.platform.blocks.executor.WorkManagerExecutor$1.run(WorkManagerExecutor.java:184)
        at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:209)
        at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
        at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
        at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
        at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
        at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:644)
        at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:622)
        at weblogic.work.DaemonWorkThread.run(DaemonWorkThread.java:39)
Caused by: javax.resource.ResourceException: weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=org.eclipse.persistence.transaction.JTASynchronizationListener@2d7a86a9

Internal Exception: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

Error Code: 1461
        at oracle.tip.adapter.fw.jca.messageinflow.MessageEndpointImpl.afterDelivery(MessageEndpointImpl.java:379)
        at oracle.tip.adapter.jms.inbound.JmsConsumer.afterDelivery(JmsConsumer.java:306)
        ... 11 more
Caused by: weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=org.eclipse.persistence.transaction.JTASynchronizationListener@2d7a86a9

Internal Exception: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column
...

Of course the process instance failed. It took me some time to figure out what went wrong. It was suggested that it was due to the composite sensors, but I waved that away initially, since I introduced them earlier (although a colleague had removed them for no apparent reason and I re-introduced them). I couln't see that these were the problem, because it ran through the unit-tests and in most cases they weren't a problem.

But the error indicates an triggered interface: [endpoint_name: DWN_MyCompositeInterface_WS], and in this case a destination: [destination = jms/DWN_OUTGOING, subscriber = null].

Since the process is triggered from the queue with messages from BPEL Sensors these Composite Sensors were defined on variableData from the BPEL Sensor XML. And as said above, the variables appear in the XML in the order they're defined in the BPEL Sensor.

One of the Composite Sensors were defined as:

<sensor sensorName="UitgaandBerichtNummer" kind="service" target="undefined" filter="" xmlns:imp1="http://xmlns.oracle.com/bpel/sensor">
    <serviceConfig service="DWN_MessageArchivingBeginExchange_WS" expression="$in.actionData/imp1:actionData/imp1:payload/imp1:variableData/imp1:data" operation="ArchiverenBeginUitwisseling" outputDataType="string" outputNamespace="http://www.w3.org/2001/XMLSchema"/>
</sensor>

With the expression: $in.actionData/imp1:actionData/imp1:payload/imp1:variableData/imp1:data.
Because it is a list, there can be more than one variableData occurences. And without an index, it will select all of them. If, for instance one them contains the actual message to archive, and that message is quite large, then the resulting value becomes too large. And that results in the error above.

All I had to do is to select the proper occurence of the message id as shown in the Sensor Dialog above. The expression had to be: $in.actionData/imp1:actionData/imp1:payload/imp1:variableData[2]/imp1:data

Conclusion

This solved the error. I wanted to log this for future reference. But, also to show how to find out this seemingly more obscure error.