Problem while inserting data with more than 4000 character.

[Former Member]

Hi All,

I am facing some issue while dealing with data having more than 4000 characters.

I am using SAP xMII 11.5.3 b66 and Oracle 10g. JDBC driver is u201Cojdbc14 10.2.0.5.0u201D. The datatype of column in the target table is u201CCLOBu201D (So it should accept data with more than 4000 characters).

Below given is the sample scenario I tried.

I have one table called u201CCLOBTABu201D and a column u201CCLOBCOLu201D with datatype CLOB.

I created a query template for inserting data into this sample table, u201Cinsert into CLOBTAB values (u2018[Param.1]u2019)u201D.

And I tried to pass the big data from front end through this u2018Param.1u2019. But it is throwing u201CERROR - java.sql.SQLException: ORA-01704: string literal too longu201D error.

Is there any work around available for this issue?

I checked the following threads, but not able to find a solution.

Oracle JDBC driver 10g and xMII:

CLOB Data Columns:

Do we need to set u201CSetBigStringTryClob=trueu201D for solving this? If yes, where to set?

Thanks is advance.

Regards,

Subin

[Former Member · Accepted solution]

Hi Subin,

You can try couple of things.

If your data is less than 32767, you can pass it to stored procedure and change it to clob type like

PROCEDURE CLOBQUERY
(
	Param	IN	CLOB,
)

and you can call Procedure

EXECUTE CLOBQUERY '[Param.1]'

If your data is around than 1000000(32767*32), you can break the data in length of 32767 and pass it to param 1 to 32 like

EXECUTE CLOBQUERY '[Param.1][Param.2][Param.3]..[Param.32]'

Finally you can try to update jdbc drive.

check the link Link: [JDBC Limitation|http://confluence.atlassian.com/display/JIRA/UsingOracle10gdriverstosolvethe4000character+limitation]

[Former Member · Accepted solution]

Thanks a lot for your reply Anshul.

I tried these things earlier. I am using a procedure for inserting the large data. When I executed the procedure from SQL Developer IDE, it is inserting the whole data to the target table as expected. So no issues in the procedure.

I am facing the issue when I tried to call this procedure in MII query template.

call largedataproc('[Param.1]')

I tried it by setting value for Param.1 in Query Template Editor itself. When I tested the query from query template editor, u201CERROR - java.sql.SQLException: ORA-01704: string literal too longu201D was the result.

I have read the document about JDBC Limitation, but donu2019t know where to set u2018SetBigStringTryClob=trueu2019 in the case of MII 11.5.

I tried to pass this through server url as a property, but that is also not working.

Thanks & Regards

Subin

[Former Member · Accepted solution]

Hi Subin

As mention in thread

Oracle JDBC driver 10g and xMII: .

it is not possible in 11.5.

but you can break you data in lot of 4000. and set in query parameters from 1 to 32.

this approach will support around 1lakh characters.

Regards

Anshul

[Former Member · Accepted solution]

Thanks Anshul for the suggestion.

I am passing around 25 parameters to the procedure(24 input parameters excluding the big data), so there are not much parameters left for splitting the big data.

If SAP MII 11.5 will not handle data more than 4000 character long, splitting the long char is the only way out.

Does any other version of SAP MII (12.0, 12.1 or 12.2) support data with more than 4000 character length?

Thanks and Regards,

Subin

[Former Member · Accepted solution]

Hi Subin,

As in 12.x you can upload the jdbc drivers, this will solve your problem.

Regards

Anshul

[Former Member · Accepted solution]

Thanks for the information Anshul.

Regards,

Subin

[Former Member]

Hi All,

I am facing the similar issue of inserting characters with more than 4000 size (CLOB data type in table)

The environment details are as follow

SAP MII 14.0 SP5 Patch 7

Oracle 11G

OJDBC5

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.

looking forward for some pointers

Thanks & Regards

Ravish Pandey