CLOB and int-jdbc:stored-proc-outbound-gateway

Question

I'm trying to invoke a stored procedure using the following component:

int-jdbc:stored-proc-outbound-gateway

It works fine, it calls the procedure but I can't seem to be able to log the CLOB in a proper CLOB format. I've tried the following xml:

<int-jdbc:stored-proc-outbound-gateway
        id="auditGatewayProcedure" request-channel="auditGatewayInbound"
        data-source="dataSource" stored-procedure-name="PKG_TEMP.PR_AUDIT"
        return-value-required="false" ignore-column-meta-data="true">
        <int-jdbc:sql-parameter-definition
            name="IN_MSG_ID" />
        <int-jdbc:sql-parameter-definition
            name="IN_GUID" />
        <int-jdbc:sql-parameter-definition
            name="IN_CLOB" />
        <int-jdbc:parameter name="IN_MSG_ID" expression="payload.msgId" />
        <int-jdbc:parameter name="IN_CLOB" expression="payload.xmlPayload" />
        <int-jdbc:parameter name="IN_GUID" expression="payload.guid" />
</int-jdbc:stored-proc-outbound-gateway>

With that in mind, I'm passing the following payload (with the proper getters and setters):

private long id;
private String msgId;
private Clob xmlPayload;
private String guid;

The CLOB is of type: java.sql.Clob

The procedure I'm calling is this simple stuff:

procedure PR_BRIDGE_AUDIT(in_msg_id IN varchar2,
                                 in_guid IN varchar2,
                                 in_clob IN Clob) is begin
    insert into tb_temp_all_messages(id,msg_id,xml_payload,guid) values (TB_TEMP_ALL_MESSAGES_SEQ.NEXTVAL,in_msg_id, in_clob, in_guid);

end;

What happens is that the clob column is inserted with the following value:

org.hibernate.lob.SerializableClob@186fdd6

The second try I did was forcing the type in the spring component, like this:

<int-jdbc:sql-parameter-definition
            name="IN_CLOB" type="CLOB"/>

But the following exception is thrown: java.lang.ClassCastException: org.hibernate.lob.SerializableClob cannot be cast to oracle.sql.CLOB

Am I doing something wrong?

I tried to google it but nothing of value (as far as I saw) appeared.

Thanks in advance!

UPDATE

So, after some tries and the answer from Artem, the solutions are two, it seems:

given this XML:

<int-jdbc:stored-proc-outbound-gateway
        id="auditGatewayProcedure" request-channel="auditGatewayInbound"
        data-source="dataSource" stored-procedure-name="PKG_TGT_BRIDGE.PR_BRIDGE_AUDIT"
        return-value-required="false" ignore-column-meta-data="true">
        <int-jdbc:sql-parameter-definition
            name="IN_MSG_ID" />
        <int-jdbc:sql-parameter-definition
            name="IN_GUID" />
        <int-jdbc:sql-parameter-definition
            name="IN_CLOB" type="CLOB"/>
        <int-jdbc:parameter name="IN_MSG_ID" expression="payload.msgId" />
        <int-jdbc:parameter name="IN_CLOB" expression="payload.stringClob" />
        <int-jdbc:parameter name="IN_GUID" expression="payload.guid" />
</int-jdbc:stored-proc-outbound-gateway>

In the EEM class you can either pass a string containing the Clob (stringClob). Or create a properly oracle.sql.CLOB type with what has been said by Artem and pass into the IN_CLOB parameter the oracle.sql.CLOB created.

Hope this helped,

Thanks

Answer 1

The issue that you really must create oracle.sql.CLOB yourself and within the same transaction as for that <int-jdbc:stored-proc-outbound-gateway>.

The code to create a CLOB may look like:

public CLOB convertToClob(String value) {
    CLOB c = CLOB.createTemporary(getNativeConnection(), false, CLOB.DURATION_SESSION);
    c.setString(1L, value);
    return c;
}

private Connection getNativeConnection() {
    return DataSourceUtils.getConnection(this.dataSource).getMetaData().getConnection();
}