Persist data into BLOB / CLOB datatype in Oracle DB using Apache Camel Framework

Question

Need to store data (XML) to BLOB datatype in oracle DB using Apache Camel framework. Executing normal queries were fine. But when I tried to persist data into BLOB, the oracle DB wanted a compatible type. I tried using utl_raw.cast_to_raw while trying to convert raw data into BLOB compatible type.

This works fine for smaller data but when data is huge like a large request XML with more than 2048 characters are there, error SQL Error: ORA-06502: PL/SQL: numeric or value error: raw variable length too long is thrown.

Can someone please help let me know how data more than 2048 characters could be stored in a BLOB datatype using Apache Camel ?

Any help is much appreciated.

It would help if you could mention which camel component you use to call the database (camel-sql, camel-jdbc, etc.) and show an example of the code you tried which led to the error. — Cyäegha
It is Camel - SqlComponent. // <bean id="sql" class="org.apache.camel.component.sql.SqlComponent"> <property name="dataSource" ref="dataSource" /> </bean> — Patrick
As you have not yet posted any code. Maybe this SO answer helps to find the right solution stackoverflow.com/questions/34530594/… — SubOptimal
I posted an answer with the obvious examples which should work, but it's hard to know if it's the correct answer without an actual code example of what didn't work for you. — Cyäegha

Cyäegha Cyäegha · Accepted Answer · 2016-09-13T12:49:24

Here's an exemple that works fine when tested with HSQLDB (I don't have an Oracle database handy to test, but it should work the same):

// This is just to have some relatively large ammount of data to insert in the BLOB, details don't matter
private static final byte[] LARGE_DATA.getBytes(StandardCharsets.UTF_8);
static {
    final StringBuilder builder = new StringBuilder(10000);
    for (int i = 0; i < 4000; i++) {
        builder.append('a');
    }
    LARGE_STRING = builder.toString();
}

from("direct:clobTest")
.process(new Processor() {
    @Override
    public void process(Exchange exchange) throws Exception {
        exchange.getIn().setHeader("myParam", LARGE_DATA);
    }
})
.to("sql:INSERT INTO clob_table(clob_col) VALUES(:#myParam)");

Alternatively, it also works with an input stream instead of a byte array:

final InputStream is = new ByteArrayInputStream(LARGE_DATA);
exchange.getIn().setHeader("myParam", is);

Persist data into BLOB / CLOB datatype in Oracle DB using Apache Camel Framework

1 Answers