Oracle 12.1.0.1 Client, Output Parameters returning junk data
Posted: Mon 24 May 2021 18:49
Hello,
Using Delphi 10.1, compiling in 64 bit, Oracle Client 12.1.0.1, ODAC 11.4.3.
I have reproduced this using Oracle Server 19c UTF8 character set, and 18c WE8MSWIN1252 character set. So I believe it will happen with any server version and any character set.
TOraParam.AsString, is including some garbage characters at the end for output variables. It starts with #0, so GUI elements are hiding it, but you can see it in the debugger. See Screen shot: https://imgur.com/a/J2W3u5A
In that screen shot, the extra characters are all #0, but this is not always the case. For example, one of our users reported that "18.0.0.0 timesta" was being returned instead of just "18.0.0.0". The extra data is inconsistent and seems like random bits of memory.
This only happens with Oracle Client 12.1.0.1. It does not happen with Oracle Client 12.1.0.2 or newer. It was not a problem in ODAC version 11.3.2. I do not know exactly which ODAC version broke this, but I believe the relevant changes are in TOraParamDesc.GetItemAsWideString.
Using Delphi 10.1, compiling in 64 bit, Oracle Client 12.1.0.1, ODAC 11.4.3.
I have reproduced this using Oracle Server 19c UTF8 character set, and 18c WE8MSWIN1252 character set. So I believe it will happen with any server version and any character set.
Code: Select all
var
OraSession1: TOraSession;
SmartQuery1: TSmartQuery;
begin
OraSession1 := TOraSession.Create(nil);
SmartQuery1 := TSmartQuery.Create(nil);
SmartQuery1.Session := OraSession1;
OraSession1.Username := 'jdorlon';
OraSession1.Password := 'jdorlon';
OraSession1.Server := 'azure_19c_plug'; // database version does not matter.
OraSession1.LoginPrompt := False;
OraSession1.Connect;
SmartQuery1.SQL.Clear;
SmartQuery1.SQL.Add('BEGIN');
SmartQuery1.SQL.Add(' :my_user := user;');
SmartQuery1.SQL.Add(' :test_param := ''ABC'';');
SmartQuery1.SQL.Add('END;');
SmartQuery1.ParamByName('my_user').ParamType := ptOutput;
SmartQuery1.ParamByName('my_user').DataType := ftWideString;
SmartQuery1.ParamByName('test_param').ParamType := ptOutput;
SmartQuery1.ParamByName('test_param').DataType := ftWideString;
SmartQuery1.Execute;
Memo1.Lines.Add(SmartQuery1.ParamByName('my_user').asString);
Memo1.Lines.Add(SmartQuery1.ParamByName('test_param').asString);
end;
In that screen shot, the extra characters are all #0, but this is not always the case. For example, one of our users reported that "18.0.0.0 timesta" was being returned instead of just "18.0.0.0". The extra data is inconsistent and seems like random bits of memory.
This only happens with Oracle Client 12.1.0.1. It does not happen with Oracle Client 12.1.0.2 or newer. It was not a problem in ODAC version 11.3.2. I do not know exactly which ODAC version broke this, but I believe the relevant changes are in TOraParamDesc.GetItemAsWideString.