IC86527: INCONSISTENT CLOB DATA INSERT INTO ORACLE DATABASE

APAR status

• Closed as program error.

Error description

• Customer is on V4318 and have BP which inserts an XML file into
  a CLOB field. This CLOB field is part of a table in an external
  [not SI DB] Oracle table. This BP was executing successfully in
  4318. Now they have moved to V5.2.2, where it fails with the
  error 17002: IO Error: 2 char of CLOB data cannot be read.
  Simulation Steps
  1) Check-in the map, and create a BP with the BP code attached.
  2) Create a sample table and Procedure in Oracle using the sql
  code attached.
  3) Execute the BP with the input_52rows.csv
  Observations
  1) The Procedure is meant to insert an XML document into a CLOB
  field and also this CLOB data [document] is converted into
  XMLTYPE and inserted into a XMLTYPE field in the same table. We
  tested this internally and we are faced by a "31011: Error
  executing stored procedure/function" error. This is because
  CLOB data that is inserted is not a valid XML and while being
  converted into XMLTYPE 31011 error code is returned which is a
  XML Parsing failure.
  2) The CLOB data that is inserted is supposed to be a valid XML
  [this is produced by a translation service, and the output is a
  valid XML as seen in the process data], however when this same
  data is inserted the document is repearted again at the end
  rendering it invalid XML. Attached invalid XML.
  3) The input document that translated and then inserted into
  the table is of the size 40kb and has Chinese characters, and
  we observed that this file fails this test. Whereas removing a
  few lines and reducing the file size allows the file to be
  translated and inserted successfully.
  4) To avoid the Chinese characters from being converted to
  garbage characters document encoding should be UTF-8 [this
  document insert fails], however erasing the document encoding
  [making it null using GetDocumentInfo service] allows for
  successful document insert, but the Chinese characters are
  converted to garbage characters.
  We believe that this could be an issue with the way CLOB data
  is being sent to the DB procedure from the LWJDBC adapter.
  However it is also important to note the role of the document
  character encoding and the file size variation scenarios.
  Expected Behavior
  The XML file should be successfully inserted into the CLOB and
  XMLTYPE fields from BP executing in V5.2.2
  

Local fix

• STRRTC - 347806
  VS / VS
  Circumvention: None
  

Problem summary

• Users Affected:
  All
  Users work with LightweightJDBCAdapter to update database clob
  column with mutilple-byte characters.
  Problem Description:
  Update to clob fields with LightweightJDBCAdapter may fail when
  using mutilple-byte characters.
  Platforms Affected:
  All platforms.
  

Problem conclusion

• Resolution Summary:
  Changed JDBC API calls to let JDBC driver implementation to
  calculate the number of characters in the CLOB stream based on
  character encoding.
  Delivered In:
  5020500
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  STR B2B INTEGRA

• Fixed component ID

  5725D0600

Applicable component levels

• R524 PSY

     UP