ORA-22370

[Marco Erdmann]

I sent this to support, but did not receive an answer yet. In order to adress a wider audience, I repost that topic here:

We currently experience an ORA-22370 error in code that was working previously. We first found the problem when upgrading our Oracle client version to 11.2.0.2. The error is also visible if the database is 11.2.0.2, but here it gets weird. I have tested the following combinations (instant client against Enterprise Edition) now:

Client 11.2.0.1, DB 11.2.0.2 => Error
Client 11.2.0.2, DB 11.2.0.1 => Error
Client 11.2.0.2, DB 11.2.0.2 => Error

Client 11.2.0.1, DB 11.2.0.1 => No error
Client 10.2.0.4, DB 11.2.0.2 => No error

The last result is most astonishing. The problem seems to be located in the creation of a "table of sys.anydata" in the object. Please find a Delphi project attached, that demonstrates the problem. We are using Delphi XE Professional Update 1 and the latest set of DOA components from the registered users page (4.1.2.2).

Here is the script to create the objects:

-- create user
create user appuser identified by appuser;
grant connect, resource to appuser;

-- as appuser
create or replace type inner_type is table of sys.anydata;
/

create or replace type outer_type as object
(
  attr inner_type,
  constructor function outer_type(self in out outer_type) return self as result

);
/

create or replace type body outer_type is

  constructor function outer_type(self in out outer_type) return self as result is
  begin
    self.attr := inner_type();
    return;
  end;

end;
/

And here is the Delphi command line project that creates the error:

program dmo;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Oracle;

var
  ASession: TOracleSession;
  AQuery: TOracleQuery;
  AObject: TOracleObject;

begin
  try
    ASession := TOracleSession.Create(nil);
    try
      ASession.LogonUsername := 'appuser';
      ASession.LogonPassword := 'appuser';
      ASession.LogonDatabase := 'db';
      ASession.LogOn;

      if ASession.Connected then
      begin
        AObject := TOracleObject.Create(ASession, '"APPUSER"."OUTER_TYPE"', '');
        try
          AQuery := TOracleQuery.Create(nil);
          try
            AQuery.Session := ASession;
            AQuery.SQL.Append('begin');
            AQuery.SQL.Append('  :function_result := "APPUSER"."OUTER_TYPE"();');
            AQuery.SQL.Append('end;');
            AQuery.DeclareVariable('function_result', otObject);
            AQuery.SetComplexVariable('function_result', AObject);
            AQuery.Execute;
          finally
            AQuery.Free;
          end;
        finally
          AObject.Free;
        end;
      end;
      ASession.LogOff;
    finally
      ASession.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

Thanks in advance for any help you can provide. I am out of ideas how to "re-phrase" the affected code.

 

[Marco Kalter]

This is what we sent as reply to your support request on the 23rd, but which apparently did not reach you:

It seems like an 11.2.0.2 bug. I tested your demo app on 11.2.0.1 and 11.1.0.7 and 11.1.0.6 and it works fine. I don't have 11.2.0.2 available at the moment.

Did you contact Oracle Support about this issue?

 

[Marco Erdmann]

Thanks for your reply. I am sorry, no, the answer did not reach me. I have not contacted Oracle support yet. The code works e.g. within an anonymous block in PL/SQL-Developer. As Oracle won't deal with Delphi, I suppose, I might have to whip something together in another language triggering the problem.

The funny thing is that it works with a 10g client against 11.2.0.2, but is triggered by a 11.2.0.2 client against a 11.2.0.1 database. This is definitely strange.