5

One of the jobs of OJDBC is to map Oracle data types to Java types.

However, we noticed that if we give a CHAR data type, it is not mapped to java.lang.String. The versions showing this behavior are: OJDBC7 v12.1.0.2 and OJDBC6 v12.1.0.1. The older versions did indeed map the CHAR data type to: java.lang.String.

On digging deeper, we discovered that there is a class: StructMetaData within the oracle.jdbc.driver package of OJDBC that implements the Oracle data type to Java Type mapping. There is a method within it: 'getColumnClassName(int arg0)' that is worthy of attention. We noticed that for OJDBC v7, the cases mapped to java.lang.String are as follows:

    int arg1 = this.getColumnType(arg0);
    switch (arg1) {
    case -104:
        return "oracle.sql.INTERVALDS";
    case -103:
        return "oracle.sql.INTERVALYM";
    case -102:
        return "oracle.sql.TIMESTAMPLTZ";
    case -101:
        return "oracle.sql.TIMESTAMPTZ";
    case -15:
    case -9:
    case 12:
        return "java.lang.String";
     ...

However, within older OJDBC implementations, it looked like this:

    int arg1 = this.getColumnType(arg0);
    switch (arg1) {
    case -104:
        return "oracle.sql.INTERVALDS";
    case -103:
        return "oracle.sql.INTERVALYM";
    case -102:
        return "oracle.sql.TIMESTAMPLTZ";
    case -101:
        return "oracle.sql.TIMESTAMPTZ";
    case -15:
    case -9:
    case 1:
    case 12:
        return "java.lang.String";
    ...

There is an additional case mapped to java.lang.String in the latter case viz. 'case 1'. This 'case 1' is not mapped to java.lang.String in the first code snippet shown above.

On looking deeper, this 'case 1' is mapped to CHAR within the getColumnTypeName(int arg0 ) method of the same StructMetaData class:

public String getColumnTypeName(int arg0) throws SQLException {
    int arg1 = this.getColumnType(arg0);
    int arg2 = this.getValidColumnIndex(arg0);
    switch (arg1) {
    case -104:
        return "INTERVALDS";
    case -103:
        return "INTERVALYM";
    case -102:
        return "TIMESTAMP WITH LOCAL TIME ZONE";
    case -101:
        return "TIMESTAMP WITH TIME ZONE";
    case -15:
        return "NCHAR";
    case -13:
        return "BFILE";
    case -9:
        return "NVARCHAR";
    case -2:
        return "RAW";
    case 1:
        return "CHAR";
 ...

Because of this, if we use OJDBC 7 or OJDBC6 v12.1.0.1 and specify CHAR as the data type for a column, the following code returns null on invocation for this column's index:

 for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
     ...
     resultSetMetaData.getColumnClassName(columnIndex)
     ...

If I replace an older version of the OJDBC jar (for example: 11.2.0.3), then the same code returns: java.lang.String. Is this a bug or was it removed by design? Has anybody faced the same issue before?

Sean Mickey
  • 7,618
  • 2
  • 32
  • 58
DolphinJava
  • 2,682
  • 1
  • 23
  • 37
  • 1
    Have you checked the release notes of the driver? If you think this is wrong, open an SR with My Oracle Support. I was most pleased with the solutions offered. – Michael-O May 19 '16 at 20:47

1 Answers1

1

Good catch...!

It really looks like a bug; maybe the only argument against is that it would be an incredibly huge overlook by Oracle.

Pro bug:

  • backward compatibility problem. Updates of the jdbc driver would disrupt existing applications that are explicitly relying on the CHAR declaration
  • This note about JPublisher Data Type and Java-to-Java Type Mappings describes the mapping as of 12c

(note the first row, related to the CHAR type, which maps to java.lang.String)

| SQL and PL/SQL Data Type | Oracle Mapping   | JDBC Mapping
|------------------------- |------------------|-----------------------------------------------
| CHAR, CHARACTER, LONG,   |                  | 
|STRING, VARCHAR, VARCHAR2 | oracle.sql.CHAR  | java.lang.String
| NCHAR, NVARCHAR2         | oracle.sql.NCHAR | oracle.sql.NString 
| NCLOB                    | oracle.sql.NCLOB | oracle.sql.NCLOB

Against bug:

  • A fancy hypothesis could argue that Oracle is trying to dismiss CHARs from the supported types. Indeed, the CHAR type has really no benefit over the VARCHAR2, and a few downsides make it a no-choice. In the past I loved the fact that a "CHAR" communicates to other developers your intention to define an item with a predefined and invariably fixed length, but it doesn't even enforce this (it just pads the string, if you fill it with too a short value). If you are interested, there is a section in the excellent book Expert Oracle Database Architecture - Oracle Database 9i, 10g, and | Thomas Kyte | Apress dedicated to the topic. You can read an excerpt in Ask Tom "Char Vs Varchar", at the point where the author quotes his own book:

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. ....

[then a great example follows in the same post; it is very well worth reading]

The fact that CHAR are no good, of course doesn't justify Oracle from breaking existing applications with no clear notice; so the hypothesis that it is a bug is clearly more sensible.

Given that theoretically it wouldn't have downsides, as an extreme workaround you might alter all involved tables to redefine their CHAR types as VARCHAR2 (if you are entitled to do so, and it is feasible).

Antonio
  • 1,178
  • 14
  • 14