JDBC 11.2.0.1 Adds A Space At The End Of A Select Statement When Using Bind Variables. While 11.1 Does Not Add This Space.

(Doc ID 1104693.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

On : 11.2.0.1 version, Thin JDBC driver

If a sql statement is run with JDBC Driver 11.2.0.1 generated a new SQL_ID for the execution on this version.

If the same program is run using JDBC 10.2.0.4 and/or 11.1.0.7 they share the same SQL_ID.

Here are the results of these tests:

1) Run a java program having a select statement using a bind variable 5 times using the 11.2.0.1 JDBC Driver with JDK 1.5

Looking into the v$sql view we can see it was executed 5 times:


SQL_ID         SQL_TEXT                                  EXECUTIONS
------------- ------------------------------------------ --------------------
7jtghjhanx3wg  select * from inv_language where ID > :1     5




2) Now run the same java program 5 times using the 10.2.0.4 JDBC Driver with JDK 1.5

We can see it was executed 5 times under a new sql id:


SQL_ID           SQL_TEXT                                  EXECUTIONS
---------------  ----------------------------------------- --------------------
7jtghjhanx3wg    select * from inv_language where ID > :1       5
0p9zh16pv58pm    select * from inv_language where ID > :1       5



4) Now run the same java program 5 times using the 11.1.0.7 JDBC Driver with JDK 1.5


SQL_ID         SQL_TEXT                                   EXECUTIONS
-------------- -----------------------------------------  -------- --------------------
7jtghjhanx3wg  select * from inv_language where ID > :1   5
0p9zh16pv58pm  select * from inv_language where ID > :1   10

Here we can see the statement executed with 10.2.0.4 and 11.1.0.7 were shared, but the one executed with 11.2.0.1 was not.


5)  Checking the length of the statements we have the following:

SQL> select sql_id, length(SQL_FULLTEXT), length(SQL_TEXT) from v$sql where sql_id in  (select sql_id from v$sql where sql_text like '%inv_language%' );


SQL_ID         LENGTH(SQL_FULLTEXT) LENGTH(SQL_TEXT)
-------------  -------------------- ----------------
7jtghjhanx3wg  41                    41
0p9zh16pv58pm  40                    40


Here we can see they have different size.

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