Getting given below error while loading Postgres JSONB data into Oracle BLOG data type column.
SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type]]
Postgres SQL type is JSONB -jdoc
Oralce SQL Type is BLOB -jdoc
Refer the given below Code.
import java.sql.Blob;
@Data
Employee{
private Blob jdoc;
}
Row mapper
@Bean("employee_RowMapper")
public RowMapper employee_entityRowMapper() {
return new RowMapper<Employee>(){
@Override
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee employee = new Employee();
System.out.println("JDOC is "+rs.getString("jdoc"));
if(StringUtils.isNotBlank(rs.getString("jdoc"))) {
Blob blobData = new SerialBlob(rs.getString("jdoc").getBytes());
employee.setJdoc(blobData);
}
return employee;
}
};
}
SQLParameterSourceProvider
@Bean(name = "employee_entity_TableSqlParameterSourceProvider")
public ItemSqlParameterSourceProvider employee_entityTableSqlParameterSourceProvider() {
return new ItemSqlParameterSourceProvider<Employee>() {
@Override
public SqlParameterSource createSqlParameterSource(Employee item) {
MapSqlParameterSource mapSqlParameterSource= new MapSqlParameterSource();
mapSqlParameterSource.addValue("jdoc",item.getJdoc(),Types.BLOB);
return mapSqlParameterSource;
}
};
}
Note : I have tried to load as string , while loading as string i am getting given below error.
ORA-01465: invalid hex number in oracle while using BLOB
i have tried with this function as well - utl_raw.cast_to_raw in my update script- even though i am getting the same error. [1]: Oracle - Update BLOB with PL/SQL