JDBC insert with XMLTYPE data type

[former_member403472]

Hi,

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.

[Former Member · Accepted solution]

Hi,

I think that the problem is in maximal SQl statement length. The maximal length will be limited by DB settings or it's limited somewhere in JDBC settings (probably limited by JDBC driver). You should check the DB/ORACLE settings and JDBC settings.

[Former Member]

Hi Anand,

The problem here is definitely the length of the SQL query. i.e "INSERT INTO ......... VALUES......."

This is what i got when i searched for this ORACLE error code:

ORA-01704: string literal too long

Cause: The string literal is longer than 4000 characters.

Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

Please ask a ORACLE DB expert on how to handle this Also i am not sure how can we handle Bind Varibales in SAP PI.

I hope this helps.

Regards, Gaurav.

[former_member403472]

Thank you for prompt replies.

Gaurav ~

Yes, i gone through oracle help on this error. However could not figure out where to start my debugging.

Sateesh ~

You are right. I dont have any INSERT in communicaiton channel. Instead I am populating INSERT using graphical mapping.

Well.. If not this solution, do you think of any other way to handle this requirement? I am thinking about stored procedure, but that will be last my resort.

[baskar_gopalakrishnan2]

Bind Variables can be handled normally using Prepared Statement.

[former_member403472]

Hi JAVAGUY,

Do you have any help link/ pointer where I can find detailed information about it

Thanks in advance.

Anand

[baskar_gopalakrishnan2]

http://www.jdbc-tutorial.com/jdbc-prepared-statements.htm

Note: You can use UDF to handle this...

[Former Member]

Hi Anand,

Can you try first to insert the data using SQL editor and check what error itis giving in SQL editor.

check with database owner to check the datatype declared and till wat limit it will accept the data.

can u paste the exact error which is shown in comm channel monitor.

chirag

[former_member403472]

Hi Chirag,

I tried this insert using SQL Developer, however it could not execute such a long query (xml field itself is more than 4000 char long). I understand this will be issue with most of UI to run longer queries in front.

Anand More.

[former_member296836]

Hi Anand,

did you found a solution for this problem?

Facing the same problem. I would like to insert a complete IDOC in a CLOB field, but

for messages > 4000 characters it doesn't work.

Regards

Chris

[Former Member]

Hi Anand,

Which communication channel you wrote that Insert query , I assume In receiver JDBC we can't wrote the Insert query.

Kindly clear the things,

thank you

Sateesh