Oracle IN OUT changes between Delphi XE to Delphi 10 Seattle

[Apparel21]

Hi

We are upgrading our source code from Delphi XE to Delphi 10 Seattle and at the same time we are upgrading the ODAC component.

Delphi XE is using odac80d15pro.exe
and
Delphi 10 Seattle is using odac96d23pro.exe

We are now receiving Oracle errors with some of our IN OUT arguments in procedures and package procedures. We currently don't pre-allocate buffers anywhere for parameters and there hasn't been a problem with this in the Delphi XE version.

It appears now in Delphi 10 Seattle that if the parameter value being passed IN has less memory allocated than what is coming OUT, an exception is raised.

Please note this still works in XE but no longer works in Seattle.

Here is a sample to reproduce.

Delphi XE successfully allocated a string and returned the correct result from the procedure whereas the following exception was thrown in Seattle: "Project raised exception class EOraError with message 'ORA-06502: PL/SQL: numeric or value error: character string buffer too small".


Oracle Procedure Source Code:

Code: Select all

create or replace procedure ParameterTest(StringParameter IN OUT VARCHAR2) is
begin
  StringParameter := 'My value of 25 characters';
end;

Delphi Source Code:

Code: Select all

procedure TestParams;
var
  LConnection: TOraSession;
  LSql: TOraSql;
begin
  LConnection := TOraSession.Create(Self);
  try
    LConnection.Server := '###';
    LConnection.Username := '###';
    LConnection.Password := '###';
    LConnection.Connect;
    
    LSql := TOraSql.Create(Self);
    try
      LSql.Connection := LConnection;
      LSql.SQL.Add('BEGIN ParameterTest(:StringParameter); END;');
      LSql.ParamByName('StringParameter').AsString := 'In'; // Note: this is now seen as a two character buffer for the output but in XE this was not a problem.
      LSql.Execute;
      MessageBox(Handle, PWideChar(LSql.ParamByName('StringParameter').AsString), 'Out', 0);
 
    finally
      LSql.Free;
    end;
 
  finally
    LConnection.Free;
  end;
end;

Is something now broken or has there been an intentional breaking change?

Cheers

[MaximG]

Try to specify the type of the 'StringParameter' when using it:

Code: Select all

 LSql.ParamByName('StringParameter').ParamType := ptInputOutput; 

[Apparel21]

Try to specify the type of the 'StringParameter' when using it:

Code: Select all

 LSql.ParamByName('StringParameter').ParamType := ptInputOutput; 

This is exactly what we wanted to avoid - having to edit 10+ years of code. We can obviously fix it in a number of ways however we are going to have a massive task on our hands.

More information from the object inspector in XE after setting SQL:
LSql.ParamByName('StringParameter'),r
(FCollection:$2B80C80; FID:0; FParamRef:nil; FNativeStr:''; FData:Unassigned; FPrecision:0; FNumericScale:0; FNull:True; FName:'StringParameter'; FDataType:ftUnknown; FBound:True; FParamType:ptUnknown; FSize:0; FParamObjectClass:TParamObject; FParamObject:nil; FNational:False)

This works with FParamType set to ptUnknown in XE.

Maybe we're missing a setting that works with ParamCheck := True that will determine this information via metadata from the database?

It just works in XE but now doesn't in Seattle.

[MaximG]

The behavior you describe was really present in ODAC 8, but it was wrong. By numerous requests from our customers, this bug has been fixed about 5 years ago. Unfortunately, in the current versions of ODAC, you must explicitly define the type of InputOutput parameter.