CODEWITHSUNDEEP
javasqloracle-databasejdbc
user1071777
user1071777

Reputation: 1307

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

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.

Upvotes: 1

Views: 3026

Answers (1)

vav
vav

Reputation: 4684

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:

  1. convert '2014...' to date
  2. convert result to string
  3. 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';

Upvotes: 2

Related Questions