problem to bookmark record after update in oracle 10

fabriziodc · Post by **fabriziodc** » Mon 10 Jan 2011 12:09

I use Odac 6.90

If I use a select with no order prior to version 10 of oracle and I do a refresh after a change (as in code below) points to the same record that I had pointed earlier. If I do the same with oracle 10 it points on a different record. I noticed that after every update on the table by the refresh does not ever get a result set ordered in the same way. I do not want to put order not to sacrifice performance. Can you tell me if there is a property or method to prevent that happening, and get the same effect on older versions of oracle?

begin
try
Index := DataSet.GetBookmark;
TOraDataSet(DataSet).Refresh;
if (not DataSet.IsEmpty) then TOraDataSet(DataSet).GotoBookmark(Index);
finally
DataSet.FreeBookmark(Index);
end;
end;

Thanks a lot

AlexP · Post by **AlexP** » Mon 10 Jan 2011 14:54

Hello,

This problem is connected with the behavior of Oracle.
If you change data the current bookmark can be broken, it is not connected with the Oracle version or ODAC version.
We can't change this behavior, but you can use the Locate method instead of the GotoBookmark method.

fabriziodc · Post by **fabriziodc** » Mon 10 Jan 2011 16:32

I do not want to use the "locate" it will penalize the performance, So why the gotobookmark worked perfectly until the oracle 9?
In fact, running the command "alter session set optimizer_mode = 'rule'" on my "oracle 10" and now is working. But I can not use this command because already the version 11 will be deprecated. So the problem can not be gotobookmark but something has changed from 10 and earlier versions of oracle and the ODAC on the refresh, because at every refresh, after update, the result set changes.

AlexP · Post by **AlexP** » Tue 11 Jan 2011 13:22

Hello,

If you are using a bookmark, and after setting it you have deleted a record before it, the bookmark will be broken (it will point to another record) in all Oracle versions.In this case the bookmark will point to the next record (f you have deleted one record) or it will point to the bookmark+N record (bookmark+the number of records deleted before bookmark). If you delete a record after the bookmark, the bookmark will be correct.

Please execute the following example on Oracle 9:

Code: Select all

CREATE TABLE TEST_TABLE(
ID NUMBER,
TEXT VARCHAR2(50)
);
INSERT INTO TEST_TABLE VALUES(1,'text1');
INSERT INTO TEST_TABLE VALUES(2,'text2');
INSERT INTO TEST_TABLE VALUES(3,'text3');

var
  Index: Pointer;
  OraQuery: TOraQuery;
  OraSession: TOraSession;
begin
  OraSession:= TOraSession.Create(nil);
  OraSession.Server:= '...';
  OraSession.Username:= '...';
  OraSession.Password:= '...';
  OraSession.Connect;
  OraQuery:= TOraQuery.Create(nil);
  OraQuery.Session:= OraSession;
  OraQuery.SQL.Text:= 'SELECT * FROM TEST_TABLE';
  OraQuery.Open;
  OraQuery.Next; //goto ID= 2
  ShowMessage(OraQuery.FieldByName('TEXT').AsString); //show current record
  Index := OraQuery.GetBookmark; // set bookmark to id = 2
  OraSession.ExecSQL('DELETE FROM ALEXP WHERE ID=1', []); // delete first record
  OraQuery.Refresh;
  if (not OraQuery.IsEmpty) then
  begin
    OraQuery.GotoBookmark(Index);
    ShowMessage(OraQuery.FieldByName('TEXT').AsString);//show current record
  end;
  OraQuery.FreeBookmark(Index);

and the bookmark will point to the record with id = 3 instead of the one with id = 2

fabriziodc · Post by **fabriziodc** » Tue 11 Jan 2011 13:51

AlexP,

thank you for your reply. The situation in the example you posted is clear; the problem is that the same also happens after an UPDATE, non only after a delete... Moreover, the ALTER SESSION command I posted before changes the behavior of the refresh + GotoBookmark commands, as it works just fine (as in Oracle 8 and 9).

As I said before, I would prefer to avoid using the Locate method after an update due to performance reasons.

Thank you in advance for any help.

AlexP · Post by **AlexP** » Wed 12 Jan 2011 15:07

Hello,

We returned records in the order that would have been used by Oracle server for returning records.
So if Oracle changes the order of the returned records after inserting/updating/deleting we can't change this behavior.