Handle session failure

[etam]

In my program some statements are executed on OraSession AfterConnect.
The most important is to get a password protected role:

Code: Select all

set role my_role identified by "my_password";

also schema propery is used, so

Code: Select all

alter session set current_schema=my_schema;

also executed. (Connection user is my_user which is different from my_schema, and has no table in his own schema.)

When session unexpectedly disconnects (f.e. killed), the statements above should be rerun, before next query executed (or after first error).
It would be no problem, if query wouldn't automatically recreate the session (I don't know how), or it could be handled somehow, but I cannot find an even for it.

So I made this:

Code: Select all

procedure TMyDb.MyOrasessionError(Sender: TObject; E: EDAError; var Fail: Boolean);
// handling OnError event
begin
   if E is EOraError then begin
      if 28 = E.ErrorCode then begin   // ORA-00028: your sesson has been killed
         MySessionConnect; // Sets main session parameters
      end;
   end;
end;

The problem is that I can see in DBMonitor the statements to be executed, but next query get the "table not exists" error.
If I give direct table select grant (grant select on my_schema.my_table to my_user;),
then 'select * from my_schema.my_table;' works,
but 'select * from my_table;' doesn't.

It seems required statements ran, without consequences.

I use ODAC 5.70.0.28 with Delphi7, Oracle 9.2.0.4.0.

[Plash]

This problem is fixed in the last ODAC builds. AfterConnect event handler is called when session is reconnected after an error. You should update your ODAC to the last build of ODAC 5 or ODAC 6.