ODAC with Net option and multithreading

[eric]

Hi,

I create a multithreaded application with one connection per thread.
For each TOraSession, Options.Net is set to TRUE.

Sometimes, OCI_NO_DATA error orccurs. I find that strange owning to the fact that I use the Net option....

NB: Options.UseOCI7 is set to TRUE

Questions:
- is ODAC still using OCI call when I use Net Option ?
- Which type of problem can generate OCI_NO_DATA error ?

[Paul]

ODAC with Net option does not use Oracle client.
We fixed such problems with Net option in the last ODAC version. Please try
ODAC 5.50 available for download from our site.
TOraSession.Options.UseOCI7 has no effect when you use Net option.

[eric]

I'm usng ODAC 5.1 with Delphi 6.
ODAC 5.50 Beta seems to be for Delphi 2005.
Is there a way to use ODAC 5.50 Beta with Delphi 6 ?

[cis-wurzen]

Eric this topic should answer your question.

[Guy le Mar]

Hi there,

I'm getting the same problem (sporadic OCI_NO_DATA error) using ODAC 5.50 in Delphi 7. This occurs during TOraQuery.Execute();

I have multiple threads each with it's own TOraSession executing select statements.

Eric - did you overcome this problem? Are there any other suggestions?

Thanks,
Guy

[Alex]

Sorry but we couldn't reproduce the problem.
Please send us complete small sample to demonstrate it and include script to create server objects to ODAC support address, also specify your ORACLE server/client versions.

[Eric]

Hi there,

I'm getting the same problem (sporadic OCI_NO_DATA error) using ODAC 5.50 in Delphi 7. This occurs during TOraQuery.Execute();

I have multiple threads each with it's own TOraSession executing select statements.

Eric - did you overcome this problem? Are there any other suggestions?

Thanks,
Guy

Hello,
I still did not solve the problem. I also get ODAC 5.50 Delphi 6 version, but I have not been able yet to test it.
Nevertheless, your answer worries me...
Are you using Net option ?

[Paul]

We reproduce your problem and fixed it in ODAC 5.50.0.15. Please look for
the new ODAC build at the beginning of the next week.

[Eric]

Hello,

I recovered the last version, namely version 5.50.0.15, and I still have a small problem.

Indeed, it happens sometimes that a stored procedure is not compiled. In this case, one obtains a Ora-20003 error.

BDE was capable of compile the stored procedure and one could thus take again the treatment after validation of an error message.

In order to have the same operating mode, I overloaded the TOraStoredProc component, and more particularly the Prepare method.

In the Prepare method, I try, by the means of a inherited, to prepare the stored procedure. If I obtain a Ora-20003 error, then I try to compile the stored procedure.
The problem is that I must know if I am in a transaction at the time of the preparation of the stored procedure. To do that, I look at if the Session property is assigned.
In the previous version, that functioned very well. In the new version, I obtain an access violation during the test of assignment of the session.

Here is the code:

Code: Select all

procedure TNewOraStoredProc.Prepare;
var
  Query               : TOraQuery;
  ObjectType          : String;
  flInTransaction  : boolean;
begin
  flInTransaction := false;
  if (Assigned(Session)) then // Here, I get an access violation with 5.50.0.15 version
    flInTransaction := Session.InTransaction; 
  try
    inherited;
  except
    on e: exception do
    begin
      if (e is EOraError) then
      begin
        if (EOraError(e).ErrorCode = 20003) then
        begin
          if (flInTransaction) then Session.Rollback;
          ObjectType := '';
          Query := TOraQuery.Create(nil);
          Query.Session := Session;
          try
            try
              Query.SQL.Add('SELECT Object_Type ');
              Query.SQL.Add('FROM User_Objects');
              Query.SQL.Add('WHERE UPPER(Object_Name) = :TheObjectName');
              Query.ParamByName('TheObjectName').AsString := UpperCase(StoredProcName);
              Query.Open;
              if (not Query.Eof) then
              begin
                ObjectType := Query.FieldByName('Object_Type').AsString;
              end;
              Query.Close;
              if (ObjectType  '') then
              begin
                Query.SQL.Clear;
                Query.SQL.Add('ALTER ' + ObjectType + ' ' + StoredProcName + ' COMPILE ');
                Query.ExecSql;
                if (not flInTransaction) then inherited;
              end;
            finally
              Query.Free;
            end;
          except
            on e: exception do
            begin
              if (flInTransaction) then Session.StartTransaction;
              raise;
            end;
          end;
          if (not Prepared) then
          begin
            if (flInTransaction) then Session.StartTransaction;
            raise;
          end
          else
          if (flInTransaction) then
          begin
            Session.StartTransaction;
            raise;
          end;
        end
        else raise;
      end
      else raise;
    end;
  end;
end;

The subject contains Multithreading because the problem seems exist only during a treatment in multithread.
In multithread, each thread use it's own TOraSession. When I need to use a stored procedure, I do that:

Code: Select all

var
  sp : TNewOraStoredProc;
begin
  sp := TNewOraStoredProc.Create(nil);
  sp.Session := ThreadSession;
  sp.Prepare;  // Here, I get an access violation with 5.50.0.15 version
  ......
end;

Any idea ?

Thanks,
Eric

[Paul]

Can you reproduce this error when TOraSession.Options.Net=False?

[Eric]

Can you reproduce this error when TOraSession.Options.Net=False?

I do not understand anything any more.
I took your advice and tested the application without the Net option.
With the Oracle Client, I did not have an error.
I have remakes a test with .Net option, and I did not have an error too.
It's very strange...