Page 1 of 1
ftMemo: bug if on/out param does not receive value
Posted: Mon 14 Jul 2008 13:42
by 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?
Posted: Tue 15 Jul 2008 08:04
by 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.
Posted: Thu 24 Jul 2008 16:48
by 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
