1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.