Extra white space using cursor_sharing FORCE

[mgorga]

Dear,
we are using literals in some of our queries and we are getting some extra white spaces at the literal. For example:
SELECT 'test' as TEST
FROM DUAL;

Instead of getting "test", the expected value, we get "test " with some extra blanks at the end. This error only occurs if the parameter cursor_sharing of Oracle is configured as "FORCE" or "SIMILAR", and works with "EXACT". That problem occurs using dbexpoda.dll version 6.5.8.0 and works using version 4.20.0.8.

Now the only solution is to force "EXACT" in all connections. Then we need to execute "ALTER SESSION SET cursor_sharing='SIMILAR'" on connection time. This increase the connection time and we will be force to test again critical sections of our code.

What the difference between dll versions? We would like to know why it is happening before make any change in production environment. I can send a code example to reproduce the error if it is necessary.

Best regards,
Mario

[AlexP]

Hello,

We cannot reproduce the problem. The below sample returns correct data. Please modify it so that the problem can be reproduced and send it back to us.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, Data.SqlExpr, DBXDevartOracle;

var
  SQLConnection: TSQLConnection;
  SQLQuery: TSQLQuery;

begin
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.DriverName := 'DevartOracle';
    SQLConnection.Params.Values['Database'] := 'orcl';
    SQLConnection.Params.Values['User_Name'] := 'scott';
    SQLConnection.Params.Values['Password'] := 'tiger';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Connected := True;
    SQLConnection.ExecuteDirect('alter session set cursor_sharing=force');
    SQLQuery := TSQLQuery.Create(nil);
    try
      SQLQuery.SQLConnection := SQLConnection;
      SQLQuery.SQL.Text := 'SELECT ''test'' as TEST FROM DUAL';
      SQLQuery.Open;
      assert(length(SQLQuery.Fields[0].AsString) = length('test'));
      Writeln('OK');
    finally
      SQLQuery.Free;
    end;
  finally
    SQLConnection.Free;
    readln;
  end;
end.

[mgorga]

Hello,
we are using Delphi 7 so the code has some differences. The main changes:
1. We use another DriverName and another connection string.
2. To be sure that the error is reproduced, it is necessary to use random literal. This forces Oracle to recalculate the execution plan of the query. We simulate it using "Test"+RandomNumber on the select.

The example modified:

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils,
  SqlExpr,
  CRSQLConnection;

var
  SQLConnection: TSQLConnection;
  SQLQuery: TSQLQuery;

begin
  Randomize;
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.DriverName := 'Oracle Net (Core Lab)';
    SQLConnection.GetDriverFunc := 'getSQLDriverORANET';
    SQLConnection.LibraryName := 'dbexpoda.dll';
    SQLConnection.VendorLib := 'dbexpoda.dll';

    SQLConnection.Params.Values['Database'] := '127.0.0.1::XE';
    SQLConnection.Params.Values['User_Name'] := 'scott';
    SQLConnection.Params.Values['Password'] := 'tiger';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Connected := True;
    SQLConnection.ExecuteDirect('alter session set cursor_sharing=force');
    SQLQuery := TSQLQuery.Create(nil);
    try
      SQLQuery.SQLConnection := SQLConnection;
      SQLQuery.SQL.Text := Format('SELECT ''test%d'' as TEST FROM DUAL', [Random(100)]);
      SQLQuery.Open;
      Writeln(Format('Length %d', [Length(SQLQuery.Fields[0].AsString)]));
      Writeln(SQLQuery.Fields[0].AsString + '.');
      Writeln('OK');
    finally
      SQLQuery.Free;
    end;
  finally
    SQLConnection.Free;
    readln;
  end;
end.

Best regards,
Mario

[AlexP]

To display a correct string, you should set the TrimFixedChar property to True:

Code: Select all

procedure TForm1.SQLConnection1AfterConnect(DataSet: TDataSet);
const
  coTrimFixedChar = TSQLConnectionOption(103); // boolean
begin
  SQLConnection1.SQLConnection.SetOption(coTrimFixedChar, Integer(True));
end;

[mgorga]

Thank you,
it works for literals but with the parameter "coTrimFixedChar" if I read data from CHAR o NCHAR type the spaces are trimmed too. So we need to evaluate if we can change it.

Is it possible to change another parameter to get exactly the same behaviour using dbexpoda.dll version 6.5 than using version 4.20?

Best regards,
Mario

[AlexP]

Please clarify: what do you mean by "read data from CHAR o NCHAR parameter "?

[mgorga]

Sorry,
If I read a field of type CHAR o NCHAR on the select statement, the value is trimmed too. If I read a field of type Varchar2 the value it is not trimmed. I suppose it is because the first type has fixed length and in varchar2 the length is variable.
We import data from external databases and we need to support all type of database fields and to be able to import exactly the same data.

Best regards,
Mario

[AlexP]

This issue may be due to the following known Oracle bugs:

support.oracle.com/epmos/faces/ui/km/BugDisplay.jspx?id=5553553
support.oracle.com/epmos/faces/ui/km/BugDisplay.jspx?id=3128363

We will continue investigation of the issue and inform you as soon as we have any results.

[mgorga]

Dear Alex,
Do you have any estimated time to solve the issue? We will release a new version in few weeks and we need this information to apply or not a workaround.

Best regards,
Mario

[AlexP]

As I wrote earlier, these problems are related to Oracle and included into their bug report. See the status of these bugs via the links provided above.