• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Resultset order issue

 
Santosh Pokhyan
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The java SP call is returning resultset with order different than SP called from within the db. I am using Oracle 9i as DB and JDk 1.4.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you use "ORDER BY" in your query ?
 
Santosh Pokhyan
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes it has Order By. I am callling SP from Java and SP all the required stuff. When the SP is called within the Oracle its returning ordered result whereas resultset in java is showing them unordered or in a different order. The contents contain some special chars. Before including this special chars it was good.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So presumably you have Java code that reorders it? The driver will not change the order of the ResultSet if you have specified an order by clause in your SQL.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It could be caused by different NLS settings between your Oracle tool (SqlPlus?) and Java, especially if the problem begun after introducing special characters. Try to check the NLS_SORT session parameter in both environments (select value from v$parameter where name='nls_sort', but you need the SELECT_CATALOG_VIEW role).

See also http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/ch7.htm, especially the part about ORDER BY clause. If you cannot obtain the privilege needed to check session parameters, you could use NLSSORT function to override the session parameter and see if it helps.
 
Santosh Pokhyan
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin,

This seems like I am approaching the real problem However, i need furthur help from you as I am aware of such issues in past. How can J2EE environment be verified and changed for NLS_SORT?
 
Santosh Pokhyan
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin,

One typos, I am not aware of such issues in past.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not that experienced with JDBC, so I can't offer a Java-native solution. However, we can do a little test.

Try to execute the following SQL statement before you call the stored procedure:
Try it both with Java and SqlPlus and tell me whether the records come in the same order after this.
 
Santosh Pokhyan
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin,

With your help I could able to figure out and resolve the issue. Its NLS_SORT issue.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic