SQL Insertion Error -ORA-01704: string literal too long

[Former Member]

Hi All,

I am facing issue while inserting the XML data having more than 4000 chars

The environment details are as follow

SAP MII 14.0 SP5 Patch 7

Oracle 11G

OJDBC5

I have already referred the similar issue but was not able to achieve the desired result

http://scn.sap.com/thread/3146941

http://scn.sap.com/thread/1972839

http://scn.sap.com/thread/1830341

1. Updated the JDBC driver(OJDBC5 to OJDBC6), still same error -string literal too long

i).is there specific driver for 11g for supporting the string characters longer than 4000 ?

ii) also is it mandatory to restart the server after updating the driver ?

iii)any other settings change required  ?

The sql query runs successfully with PL/SQL having clob data type ,but same query fails within the workbench.

The other approach of Stored Procedure works but problem lies in dynamically creating the number of params(both in SQL query and in SP) after breaking in 4000 chunks

Looking forward for the help

Thanks & Regards

Ravish Pandey

[Former Member · Accepted solution]

Hi all ,

I am able to fix the issue with PL/SQL stmt within the MII Query Template without using SP call seprately

To test the below query , need to do follow

1.Create a table(here mytable1) having one field type as CLOB(CLOBCOL) and other as varchar2(KEY_COLUMN)

2. Create the SQLQuery in MII and use below mentioned code

3. Pass the Xml in the Param.1

The sample code is below.

Here Param.1 is the xml having more than 4k values and the below PL.SQL stmt is define in the MII Sql Query having commnad mode .

DECLARE

xmlData CLOB;

BEGIN
  xmlData := '[Param.1]';

 
  insert into mytable1 ("CLOBCOL", "KEY_COLUMN")  VALUES(xmlData, 'rkp123');

END;

[former_member185280]

When reading big clobs in the past I had bring it over in chunks. Maybe you can do the opposite and append the clob in chunks.

jdbc - How to append text to an oracle clob - Stack Overflow

Regards,
Christian