An exception is thrown when try to access a column of type CLOB using catalog function Oracle.getColumnValueByName() after performed a database query with Oracle.executeQuery().

Exception:
java.lang.RuntimeException: java.sql.SQLException: Conversion to String failed
    at com.tibco.cep.modules.db.functions.JDBCHelper.getNextPage(SourceFile:944)
    at be.gen.Rules.fetchFromDatabaseRule$fetchFromDatabaseRule_a.execute(fetchFromDatabaseRule.java:43)
    at com.tibco.cep.kernel.core.rete.ReteWM.resolveConflict(SourceFile:322)
    at com.tibco.cep.kernel.core.rete.ReteWM.case(SourceFile:298)
    at com.tibco.cep.kernel.core.rete.ReteWM.fireRepeatEvent(SourceFile:2124)
    at com.tibco.cep.runtime.service.time.BETimeManager$RepeatTimeEventTask.execute(BETimeManager.java:280)
    at com.tibco.cep.kernel.helper.HiResTimer$HiResTimerThread.a(SourceFile:287)
    at com.tibco.cep.kernel.helper.HiResTimer$HiResTimerThread.run(SourceFile:224)
Caused by: java.sql.SQLException: Conversion to String failed
    at oracle.sql.Datum.stringValue(Datum.java:191)
    at oracle.sql.Datum.stringValue(Datum.java:202)
...

 

How to load CLOB data type field from Oracle database

All Operating Systems (Database Oracle)

This is a known product issue (CR BE-22767).

As a workaround please create an Oracle Stored Procedure that returns the CLOB value as an output parameter and execute the StoredProcedure in your Rule/RuleFunction using below code.

eg. (Stored procedure tst_clob with a single input and a CLOB output parameter):

===================================================================
Database.setCurrentConnection( "/SharedResources/connORACLE" );
try {
    Object proc = Database.sp.initStoredProc("call tst_clob(?,?)");
    int id = 1;
    String outStr;
    Database.sp.setInputParameter( proc,1, id );
    Database.sp.setOutputParameterType( proc, 2, 0);
    Database.sp.executeStoredProc( proc );
    outStr = Database.sp.getObjectAtIndex(proc, 2);
    Database.sp.closeStoredProc( proc );
    Object procCursor = Database.sp.initStoredProc("call Order_Demo.FindOrders(?)");
}
catch (Exception ex)
{    
    System.debugOut("RuleFunctions.executeSPReturnCblob(): ##### ERROR on execution error " + ex@message + " -> stack trace -> " + ex@stackTrace);
}
finally {
    Database.unsetConnection();
}        
===================================================================

Note:
Attached to the article a sample project. A sample SQL file included in folder /sql that includes sqlplus commands to creates the StoredProcedure, inserts a single row and executes the StoredProcedure.
The catalog functions requires to configure DBConcepts, that's why CDD file includes the Oracle JDBC connection in the cluster area (DBConcepts) and DBConcepts enabled for the InferenceAgent ProcessUnit.