Problem with TPLSQLTable and UTF8-Database

[fneuhaus]

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;

What happens:

- 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

 

[Marco Kalter]

The problem is not related, but we'll check it out and will try to fix it.

 

[walter.egger]

I think it is the very same problem and it would be really nice if you could ever fix it.....

Walter

 

[Marco Kalter]

The problem here is that the buffer size is not calculated correctly. The referenced problem is that you cannot pass more than 1333 characters in a bind variable if the character size is 3 bytes, even though you can pass more than 1333 characters is you use a constant instead of a bind variable.