My Oracle Support Banner

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

Last updated on AUGUST 16, 2019

Applies to:

JDBC - Version 11.1.0.7 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 <TABLE_NAME>( field char(4) ) ;

Table created.

SQL> insert into <TABLE_NAME> values ('1' ) ;

1 row created.

SQL> select '('||field||')' from <TABLE_NAME>;

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

SQL> select field, dump(field) dump from <TABLE_NAME>;

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 <TABLE_NAME> where field='1   ';

FIEL
----
1


2) Comparing with a single char literal:

SQL> select field from <TABLE_NAME> 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 <TABLE_NAME> 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.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.