How to insert clob column values in Oracle 11g database having more than 32000 characters from SAP MII

[Former Member]

In our project there is a situation where very large error messages are getting generated. So initially our database was planned to have the errormessage column as LONG datatype and simple insertion to Oracle database was happening through simple SQL fixed queries in MII.

Now for the changed situation was planning to make that column CLOB datatype .

How can we insert large values to CLOB columns using MII 12.1 ?

[Former Member]

Unfortunately, I think this solution doesn't work for MII 12.1 but works for MII 12.2.

The solution is as follows:

1 - We're using Oracle 10g and it's needed to use the most recent driver:

"Oracle Database 10g Release 2 (10.2.0.5), (10.2.0.4), (10.2.0.3), (10.2.0.2), (10.2.0.1.0) drivers"

It can be downloaded here:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

It's important to use ""Oracle Database 10g Release 2" to be independent from  property "SetBigStringTryClob = true".

Deploy this driver in SAP MII Portal -> System Resources -> JDBC Drivers. To make sure this driver is properly deployed, delete the older driver and test if SQL connection still working. Next, deploy the newer driver.

2 - In SQL Query, you need to use "Typed Parameters". I think "Typed Parameters" is a SAP MII 12.2 feature. So, set the Parameter Type of CLOB variable to "String".

3 - If you're using Fixed Query, remove the single quote in '[Param.X]':

Ex:

DECLARE
INS CLOB;
BEGIN
INS := '[Param.1]';
INSERT INTO clob_tab VALUES(INS);
END;
 
to

DECLARE
INS CLOB;
BEGIN
INS := [Param.1];
INSERT INTO clob_tab VALUES(INS);
END;
 

I think this change is necessary because [Param.1] behaves like "?" in Prepared Statement.

4 - This solution worked with "Command" mode in SQL Query. But we've faced some problems with "FixedQueryWithOutput" Mode.

After this configuration, we can to persist more than 32 Kbytes in a CLOB column.

Regards,

Alexandre Sardinha