ftMemo: bug if on/out param does not receive value

[Softwarer]

ODAC 6.10

Code: Select all

var
  S: TOraSession;
  Q: TOraQuery;

procedure WriteParam (Name: string);
var L: integer; S: string;
begin
  with Q.Params.ParamByName (Name) do
  begin
    S := AsString;
    L := System.Length (S);
    if L < 20
      then WriteLn (Name, ' = [', S, ']')
      else WriteLn ('Length (', Name, ') = ', L);
  end;
end;

begin
  S := TOraSession.Create (nil);
  S.ConnectString := 'test/[email protected]';
  Q := TOraQuery.Create (S);
  Q.SQL.Text := 'begin :a := :b; if 1 < 0 then :b := ''abcde''; end if; end;';
  Q.ParamByName ('a').ParamType := ptInputOutput;
  Q.ParamByName ('a').DataType := ftMemo;
  Q.ParamByName ('a').AsString := '';
  Q.ParamByName ('b').ParamType := ptInputOutput;
  Q.ParamByName ('b').DataType := ftMemo;
  Q.ParamByName ('b').AsString := 'fuckedshit';
  WriteParam ('a');
  WriteParam ('b');
  WriteLn ('-----');
  Q.ExecSQL;
  WriteParam ('a');
  WriteParam ('b');
  ReadLn;
end.

Writes:

Code: Select all

a = []
b = [fuckedshit]
-----
a = [fuckedshit]
Length (b) = 65514

As I guess, parameter 'b', should keep it's original value. It works while using ordinal data types, but fails with long strings. I guess if happens because ODAC uses lobs for ftMemo rather than usual TParam.Value. Does anybody know a workaround?

[Plash]

This is an Oracle bug. If you remove the line

Code: Select all

if 1 < 0 then :b := ''abcde''; end if;

from the PL/SQL block, the value of B parameter will not be changed.

As a workaround you can use CLOB parameters instead of LONG. To use CLOB parameters set the DataType property of parameters to ftOraClob and set TemporaryLobUpdate option of TOraQuery to True.

[Softwarer]

Thanks for an answer; sorry, I was very busy with another project.

Unfortunately, this workaround isn't acceptable for my application. This module is an engine, which should, among others, execute arbitrary PL/SQL statement passed to it. Converting string parameters to CLOB aren't OK because it fails if trying to execute something like nvl (:param, 'value').

Currently I'm using this workaround:

Code: Select all

procedure TSpOraQuery.FixVarcharPLSQLBug;
const
  CoverDecl = '  cover%0:s varchar2(32000) := :%0:s;'#13#10;
  CoverBack = '  :%0:s := cover%0:s;'#13#10;
  CoverAll  = 'declare'#13#10'%s'#13#10'begin'#13#10'%s'#13#10'%s'#13#10'end;';
var
  i: integer;
  Cover1, Cover2: string;
begin
  if FBugFixed then exit;
  if TOraSQL (FCommand).SQLType  SQL_PLSQL then exit;
  for i := 0 to Params.Count - 1 do
  begin
    if not (Params [i].ParamType in [ptOutput, ptInputOutput]) then continue;
    if Params [i].DataType  ftMemo then continue;
    Cover1 := Cover1 + Format (CoverDecl, [Params [i].Name]);
    Cover2 := Cover2 + Format (CoverBack, [Params [i].Name]);
    FBugFixed := true;
  end;
  if not FBugFixed then exit;
  SQL.Text := Format (CoverAll, [Cover1, Cover2, SQL.Text]);
end;

but it isn't nice too