Unanswered: JDBC resultset "ORDER BY using INDEX hint"
Hi!I'm using Oracle JDBC thin driver (classes12.zip)and Oracle 9i Releasse(9.0.1) to create a ResultSet object. when I append an order by clause to the basic select query, then when the updateRow() method
attempts to run, I receive the error message. As It is mentioned in Oracle documnentation, It is one of "JDBC Resultset limitation".
Now i try to force oracle to use an index for the query that implements the ordering i desire.
The query "SELECT /*+INDEX_DESC(r indexdatum)*/ artikelnummer,artikelsequence,datum from auftragdaten_level_sub22 r where datum > '10-JAN-1999'"
This work fine through "SQL Plus" and gives me ordered results.
But when i try following
ps = connection.prepareStatement("SELECT /*+INDEX_DESC(r indexdatum)*/ artikelnummer,artikelsequence,datum from auftragdaten_level_sub22 r where datum > '10-JAN 1999'",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet .CONCUR_UPDATABLE);
ResultSet rs1 = ps.executeQuery();
It neglects the INDEX hint and does not give me ordered resulset.
a.There is any workaround to solve this problem
b.Or If there is another method that i might use to perform updates of ordered Resultset.
Please help me.
As I know ,One cannot use ORDER BY clause in select statement when Resultset is in UPDATABLE mode. That's why i am forcing oracle to use Index to make ordered resultset. For example below SELECT statement has no ORDER BY clause but it gives records in DESCENDING order (salary).
SELECT /*+INDEX_DESC(r indexsal)*/ name, depart, salary drom emp r where salary>1000;
This query works well from "SQL PLUS" but not through JDBC even it has no order by clause.