Search code examples
javaoracle-databasecharacter-encodingojdbc

Invalid characters using Oracle 12c and ojdbc7 / ojdbc8 driver

After persisting data through our REST API in Oracle 12c database the special characters are not recognized (they appear as weird characters) when selecting the data from the database.

Could it be Oracle Database configuration? I'll add all the data here:

  • Oracle version: 12.1.0.2.0
  • Java Driver version: ojdbc7-12.1.0.1.jar (latest available)
  • Java version: JDK 8u162 (latest available)
  • Application Server: WildFly 9.0.1

If we downgrade the driver to OJDBC 6 and it works for some characters that were already present in the database.

We've also added the orai18n.jar driver into the JBoss classpath but nothing changed.

Oracle Database NLS configuration

enter image description here

POST

Very simple post operation with JSON payload:

enter image description here

GET

After inserting into the database we get the data but we are not able to see the special characters:

enter image description here

Update: I've added another column with type NVARCHAR2, got this in response:

enter image description here

Code

We isolated the problem in this simple project with a simple REST service class. We considered that Hibernate could be the cause but we have implemented a pure JDBC API and we got the same results.

@Path("/parameter")
@Stateless
public class ParameterResource {

    @PersistenceContext
    private EntityManager em;

    @POST
    public void post(Parameter p) throws Exception {
        System.out.println("Value on POST: " + p.getValue());
        em.persist(p);
    }

    @GET
    @Path("/{id}")
    public Parameter get(@PathParam("id") String id) throws Exception {
        Parameter p = em.find(Parameter.class, id);
        System.out.println("Value on GET: " + p.getValue());
        return p;
    }

}

Table DDL is the following:

Name   Null?    Type
------ -------- -------------- 
ID     NOT NULL VARCHAR2(20)   
VALUE           VARCHAR2(200)  
VALUEN          NVARCHAR2(200)

Console output is the following, so there should be no problem with the REST API because data seems to be "corrupted" right at the database interface:

enter image description here

Even added a filter to make sure we were using the correct encoding back and forth the Web services.

@WebFilter("myFilter")
public class MyFilter implements Filter {

    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        chain.doFilter(request, response);
    }

    @Override
    public void destroy() {
    }

}

SQL Developer

The problem is also happening when using SQL Developer, which happens to run on Java and uses OJDBC 8 driver to connect.

enter image description here

Solution

  • Your database is a US7ASCII database. Therefore the only characters you can store in a VARCHAR2 columns are ASCII characters. For non-ASCII characters you must use an NVARCHAR2 column. I highly doubt that this could have worked with older versions of the JDBC driver or maybe you were using a different database with another character set such as UTF8.