Skip to Content
Nov 15, 2010 at 07:34 PM

JDBC insert with XMLTYPE data type



SOAP to JDBC scenario. Oracle 11G as a receiver.

Requirement is to insert whole xml payload message in one of Oracle table fields as a xml string. Target oracle DB table column is defined with XMLTYPE data type, it has capacity to hold xml data more than 4GB. I am using graphical mapping with direct INSERT statement.

When I try to insert xml payload with smaller size transaction goes through. However when the xml payload size increases it is giving following error in JDBC receiver communication channel monitoring.

Could not execute statement for table/stored proc. "TABLE_NAME" (structure "StructName") due to java.sql.SQLException: ORA-01704: string literal too long

Here is insert statement as in communication channel monitoring. (Note: XML payload with bold characters is truncated)

INSERT INTO TABLE_NAME (REQ_ID, OUTAGE_OBJ, TIMESTAMP, PROCESSED_FLAG) VALUES (VAL1, <?xml version="1.0" encoding="UTF-8"?>............</>, TO_DATE(2010-11-15 10:21:52,YYYY-MM-DD HH24:MI:SS), N)

Any suggestions to handle this requirement?

Thank you in advance.

Anand More.