Understanding the character comparison difference between JDBC and SQL*Plus (Doc ID 1131104.1)

Last updated on MAY 03, 2011

Applies to:

JDBC - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

Char datatype implies fixed length, so data in a CHAR column in a table is appended with whitespaces if the inserted value is shorter than the defined length.

SQL> create table mytable( field char(4) ) ;

Table created.

SQL> insert into mytable values ('1' ) ;

1 row created.

SQL> select '('||field||')' from mytable;

'('||F
------
(1   )

SQL> select field, dump(field) dump from mytable;

FIEL DUMP
---- ----------------------------------------
1    Typ=96 Len=4: 49,32,32,32
In the previous example, a column with CHAR datatype implies filling the missing characters with blanks.

When performing a comparison with literal values, these are the results:

1) Comparing with a 4-character length literal:
SQL> select field from mytable where field='1   ';

FIEL
----
1

2) Comparing with a single char literal:
SQL> select field from mytable where field='1';

FIEL
----
1

In both cases, Oracle matches the table value ('1' plus 3 blanks) with both literal values, even though they are not the same.

In JDBC, the behavior seems different:
PreparedStatement ps = conn.prepareStatement("
       SELECT field FROM mytable WHERE field=?");

ps.setString(1, "1   ");
ResultSet rset = ps.executeQuery();
System.out.println("4 characters length:");
while (rset.next())
    System.out.println(rset.getString(1));
rset.close();

ps.setString(1, "1");
rset = ps.executeQuery();
System.out.println("Single character:");
while (rset.next())
    System.out.println(rset.getString(1));
rset.close();
System.out.println("End");

Yields:
4 characters length:
1
Single character:
End

In this case,  a single “1” does not match the value in the table. This is not the same result as SQL*Plus.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms