Skip to Content
avatar image
Former Member

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

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 ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Sep 05, 2012 at 07:28 PM

    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

    Add comment
    10|10000 characters needed characters exceeded