Java Statement executeQuery returns different results than in SQLDeveloper when the same query is run

Question

I'm trying to retrieve results from a Oracle 11.2.0.3 database like in the example given at http://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

String query = createQuery(); // SQL query to be used
System.out.println(query);
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);
System.out.println("output of first 10 results");
while(rs.next()){
    if (i < 10){
        String val1= rs.getString(1);
        String val2 = rs.getString(8);
        String val3 = rs.getString(13);
        System.out.println("val1: " + val1 + 
            ", val2: " + val2 + ", val3: " + val3);
    }
    i++;
}

However, some of the rows returned are different from when I run the same query in SQLDeveloper connected to the same DB schema. Actually, some of the rows returned in the ResultSet do not match my query.

I am logging into the DB with the same user for both. The java application is using the ojdbc.jar provided at http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html for the Oracle Database 11g Release 2 (11.2.0.3) JDBC Drivers

What could cause such a scenario to happen? There are no changes being made to the tables involved.

The sanitized query:

SELECT DISTINCT T1.COL1, T1.COL2, T1.COL3, T1.COL4, T1.COL5, T1.COL6, T1.COL7, T1.COL8, T1.COL9, COL10, T1.COL11, T1.COL12, T1.COL13
FROM VIEW1 T1, VIEW2 T2
WHERE T1.COL1 = T2.COL1 
AND ( (NLSSORT(T1.COL8, 'NLS_SORT=BINARY_AI')=NLSSORT(TO_DATE('2014-05-12 15:25:02', 'YYYY-MM-DD HH24:MI:SS'), 'NLS_SORT=BINARY_AI')
AND T1.COL13<'Example') 
OR (NLSSORT(T1.COL8, 'NLS_SORT=BINARY_AI')<NLSSORT(TO_DATE('2014-05-12 15:25:02', 'YYYY-MM-DD HH24:MI:SS'), 'NLS_SORT=BINARY_AI')) )
AND ( T2.ANOTHERCOL = 'SOMEVALUE' AND T1.COL1 = T2.COL)
ORDER BY NLSSORT(COL8, 'NLS_SORT=BINARY_AI') DESC, COL8 DESC, T1.COL13 DESC

In the output, I get: val1: anid, val2: 2014-05-12 15:29:39, val3: doesnotmatter

As far as I'm aware, that row should not be returned since 2014-05-12 15:29:39 is not less than 2014-05-12 15:25:02. And indeed that row is not found when I run the query in SQLDeveloper.

Does your query have an order by clause? Can you share (the query, that is)? — Mureinik
– Mureinik, Commented Jun 6, 2014 at 17:21
It's actually a variable column name (generated in createQuery) that can be used as the primary ordering column, it could be a vchar column as well. — user1071777
– user1071777, Commented Jun 6, 2014 at 17:49

vav · Accepted Answer · 2014-06-06 17:52:05Z

2

I guess that col8 is of type date, and I think you problem is in

(NLSSORT(T1.COL8, 'NLS_SORT=BINARY_AI')=NLSSORT(TO_DATE('2014-05-12 15:25:02', 'YYYY-MM-DD HH24:MI:SS'), 'NLS_SORT=BINARY_AI')

your actions:

convert '2014...' to date
convert result to string
convert col8 to string using default format for date column

if your SQL Developer and your java client have different default format for date - you will get different result

I would recomend to change that line to

T1.COL8 = TO_DATE('2014-05-12 15:25:02', 'YYYY-MM-DD HH24:MI:SS')

Also, you don't need NLSSORT in WHERE clause, there is no sorting there.

Now I am thinking that I am wrong.. just don't want to delete it all :)

second try...

one date is    31322D6D61792D313400
another one is 31322D6D61792D313400

they are no less that the other

Query to check

select        
NLSSORT(TO_DATE('2014-05-12 15:25:02', 
'YYYY-MM-DD HH24:MI:SS'), 'NLS_SORT=BINARY_AI'),    
NLSSORT(TO_DATE('2014-05-12 15:29:39', 
'YYYY-MM-DD HH24:MI:SS'), 'NLS_SORT=BINARY_AI')
from dual

Any differences if instead of function, modify session?:

ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
ALTER SESSION SET NLS_SORT = 'BINARY_AI';

edited Jun 6, 2014 at 17:52

answered Jun 6, 2014 at 17:45

vav

4,6942 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1071777 Over a year ago

Yes, in this case it is a date column, but it can be a vchar column too.

user1071777 Over a year ago

I got 31342D30352D31322031353A32353A303200 for 15:25:02 and 31342D30352D31322031353A32393A333900 for 15:29:39 when I tried your query to check in SQLDeveloper.

vav Over a year ago

that is because your default date format is different

user1071777 Over a year ago

Ah, if my SQLDeveloper is set to RR-MM-DD (default) then I actually get the same results as the java application. Thanks!

Collectives™ on Stack Overflow

Java Statement executeQuery returns different results than in SQLDeveloper when the same query is run

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related