I need to pass Clob to following SQL as ?1 parameter
select * from TEST_TABLE t
where
t.id in (select /*+ CARDINALITY(sinval_table, 10) */ single_value
from json_table(?1 , '$[*]' COLUMNS ( single_value number PATH '$')) sinval_table)
In Hibernate 5 to do that I extended TypedParameterValue
public class ClobListType<T> extends TypedParameterValue {
public ClobListType(Collection<T> elements) {
super(new CustomType(new ClobIdUserType()), JsonTableUtil.toJsonArray(elements));
}
and implemented own user type and in nullSafeSet create and set Clob
public class ClobIdUserType implements UserType {
private static final int SQL_TYPE = Types.CLOB;
@Override
public void nullSafeSet(PreparedStatement statement, Object value, int index, SharedSessionContractImplementor session)
throws HibernateException, SQLException {
if (value != null && statement != null) {
final Connection connection = statement.getConnection().getMetaData().getConnection();
final Clob clob = connection.createClob();
clob.setString(1, (String) value);
statement.setClob(index, clob);
} else {
Objects.requireNonNull(statement).setNull(index, SQL_TYPE);
}
}
I was trying to do the same with Hibernate 6
String jsonArray = JsonTableUtil.toJsonArray(ids);
TypedParameterValue type = new TypedParameterValue<>(new CustomType(new ClobIdUserType(), new TypeConfiguration()), JsonTableUtil.toJsonArray(ids));
query.setParameter(1, type);
I create instance of TypedParameterValue with custom type ClobIdUserType, method nullSafeSet it’s not being called and I’m getting error
Error Msg = ORA-01729: database link name expected
Any idea how to solve that ?