How to insert a Clob into a Oracle table with Slick 3 and Oracle 12?

We have a table with a CLOB column (to save JSON data)
As we understand (from the docs) slick support LOB types (http://slick.lightbend.com/doc/3.1.1/schemas.html)
We are able to query the table succesfuly. Including the CLOB column.

We are not able to insert a register with a Clob. We are converting a String to java.sql.Clob with:

private java.sql.Clob stringToClob(String source)
{
    try
    {
        return new javax.sql.rowset.serial.SerialClob(source.toCharArray());
    }
    catch (Exception e)
    {
        log.error("Could not convert string to a CLOB",e);
        return null;
    }
}

but in the end the exception from slick is the following:

java.lang.ClassCastException: javax.sql.rowset.serial.SerialClob cannot be cast to oracle.sql.CLOB

Is this possible?

Solution

We finally found a workaround as follows:

According to the column definition in slick

def column[C](n: String, options: ColumnOption[C]*)(implicit tt: TypedType[C]): Rep[C]

You can specify how the column is going to be translated between the driver and your code. If you want to use the out-of-the-box translations fine but for Oracle the translation for the CLOB type doesn't seem to work properly.

What we did was to define the column as a String but letting Slick to handle the translation with our custom code. The column definiton is the following:

def myClobColumn = column[String]( "CLOBCOLUMN" )( new StringJdbcType )

asd

Being StringJdbcType our custom code to solve the translation between our String to be inserted (up to 65535 bytes) and an Oracle CLOB.

The code for StringJdbcType is as follows:

class StringJdbcType extends driver.DriverJdbcType[String] {
  def sqlType = java.sql.Types.VARCHAR
  // Here's the solution
  def setValue( v: String, p: PreparedStatement, idx: Int ) = {
    val conn = p.getConnection
    val clob = conn.createClob()
    clob.setString( 1, v )
    p.setClob( idx, clob )
  }
  def getValue( r: ResultSet, idx: Int ) = scala.io.Source.fromInputStream( r.getAsciiStream( "DSPOLIZARIESGO" ) )( Codec.ISO8859 ).getLines().mkString
  def updateValue( v: String, r: ResultSet, idx: Int ) = r.updateString( idx, v )
  override def hasLiteralForm = false
}

The setValue function was our salvation because we could build an Oracle CLOB with the already instantiated PreparedStatement and the String comming from our domain. In our implementation we only had to do the plumbing and dirty work for the Oracle CLOB.

In sum, the extension point offered by Slick in driver.DriverJdbcType[A] was what we actually used to make the thing work.