IBM Support

Error inserting data greater than 4,000 bytes into an Oracle BLOB datatype column when using the DBLOOKUP() or DBQUERY() functions

Troubleshooting


Problem

When using an IBM WebSphere Transformation Extender (WTX) map with either DBLOOKUP() or DBQUERY() functions, the Oracle database adapter may throw error ORA-01461 whenever the inserted value for a BLOB datatype column exceeds 4,000 bytes.

Symptom

An adapter failure (and a map failure if the database function is wrapped in a FAIL() function) and error ORA-01461 is written to the adapter trace.

Cause

The DBLOOKUP() or DBQUERY() functions use a text based binding mechanism whenever submitting data into a BLOB column or any other type of column.

This works well for the BLOB column until Oracle processes any text sequence of 4000 bytes or greater, which causes an Oracle limitation to be reached and an error to be reported.

Resolving The Problem

The solution is to use the map output card to insert the data.

IBM WebSphere Transformation Extender (WTX) uses a different update mechanism during output card processing.

The update processes the BLOB column with a separately defined update statement.

Two update calls are made. One update call for the other columns being inserted / updated and another update call for the BLOB column.

This update mechanism is currently only available for map output cards.

[{"Product":{"code":"SSVSD8","label":"IBM Transformation Extender"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"8.4.1.2;8.4.1.1;8.4.1;8.4.0.5;8.4.0.4;8.4.0.3;8.4.0.2;8.4.0.1;8.4;8.3.0.6;8.3.0.5;8.3.0.4;8.3.0.3;8.3.0.2;8.3.0.1;8.3","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
16 June 2018

UID

swg21699388

Page Feedback