Hello,
I suppose there is a bug in DOA 4.0.6.2 with PL/SQL-tables of type
string and UTF8 charactersets.
Following scenario:- you have a Database with UTF8 Characterset
- TOracleSession.BytesPerCharacter is set to bcAutoDetect
- TOracleSession.MaxVarChar returns 1333 (= 4000 / 3)
- Create a PL/SQL-Table with StringSize 4000 in Delphi
aTestTab := TPLSQLTable.Create(10, 4000);
- Call a PL/SQL Package-Procedure (Generated with package wizard)
aTestPck.Fill(aTestTab);
procedure TTestPck.Fill(Pqsorten: TPLSQLTable);
begin
GetQuery;
OCPQuery.DeclareVariable('PQSORTEN', otString);
OCPQuery.DimPLSQLTable('PQSORTEN', Pqsorten.TableSize, Pqsorten.StringSize);
OCPQuery.SetVariable('PQSORTEN', Pqsorten.ValueArray);
OCPQuery.SQL.Add('begin');
OCPQuery.SQL.Add(' "TEST_PCK"."FILL"(PQSORTEN => :PQSORTEN);');
OCPQuery.SQL.Add('end;');
OCPQuery.Execute;
end;
- OCPQuery.DimPLSQLTable calls TVariableData.ReDim in OracleTypes.pas
Following code is executed in this Method:
if Session <> nil then
Max := TOracleSession(Session).MaxVarchar
else
Max := 2000;
FreeMem(buf, bufsize * ArraySize);
// For OCI7, we cannot bind arrays > 2000 characters
if (BufType in [otString, otChar]) and (BufSize > Max + 1) then BufSize := Max + 1;
if NewSize > 0 then bufsize := NewSize + 1;
GetMem(buf, bufsize * NewDim);BufSize is set to (4000 + 1) and not to Session.MaxVarChar (1333 + 1), because the statement
if NewSize > 0 then bufsize := NewSize + 1
comes after the statement
if (BufType in [otString,otChar) and (BufSize > Max + 1) then...
- Then TOracleQuery.BindVariables is called and following code
will be executed:
// Determine the maximum number of characters for a string or char variable
lBufSize := bufsize;
if (lBufType in [otString, otChar]) and (BufType <> otPLSQLString) and
(lBufSize > Session.MaxVarchar + 1) then
lBufSize := Session.MaxVarchar + 1;The value of lBufSize is now (1333 + 1) = 1334
- The problem / bug occurs now in the following statement
bindbuf := lbuf;
OCICall(OCIBindByName(stmthp, bindhp, errhp, PChar(Name), -1, lbuf,
lBufSize, lBufType, indp, lp, nil, maxsiz, cursiz, OCI_DEFAULT));lbuf points to a memory area (TVariableData.buf) where 4001 bytes are reserverd for each string in the PL/SQL-Table and every string in the array starts at the beginning of his own 4001 byte buffer
- But in the call to OCIBindByName you tell the oci-layer that the buffer size of
each string is 1334 (and not 4001) and thats why OCIBindByName send corrupted data
to the database server. OCIBindByName can not calculate the correct offsets in
the memory buffer where each string starts.
Solution:- Either correct TVariableData.ReDim so that bufsize is set to 1334
- or don't set the variable lBufSize to Session.MaxVarVar in TOracleQuery.BindVariables
which is imho the better solution, because this enables a transmission of strings with
max. 4000 bytes regardless how many characters in this 4000 bytes fit.
Maybe this issue is related to the topic "unicode problem with long strings".
Regards
Frank
