Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    5

    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.
    Regards
    Raj Kumar

  2. #2
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    I was wondering if you could simply add an "order by xxxx" clause to your select statment. This would give you an ordered result set.
    Have you tried this?

  3. #3
    Join Date
    May 2002
    Posts
    5
    Hi! Phoneix,

    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.

    Raj

  4. #4
    Join Date
    May 2002
    Posts
    5
    Hi!

    After adding rowid field before index field in select Statement, It works fine.

    Raj Kumar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •