Extra white space using cursor_sharing FORCE

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
mgorga
Posts: 5
Joined: Fri 12 Jun 2015 10:08

Extra white space using cursor_sharing FORCE

Post by mgorga » Fri 12 Jun 2015 13:52

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Extra white space using cursor_sharing FORCE

Post by AlexP » Mon 15 Jun 2015 08:48

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
Posts: 5
Joined: Fri 12 Jun 2015 10:08

Re: Extra white space using cursor_sharing FORCE

Post by mgorga » Mon 15 Jun 2015 10:22

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Extra white space using cursor_sharing FORCE

Post by AlexP » Mon 15 Jun 2015 11:21

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
Posts: 5
Joined: Fri 12 Jun 2015 10:08

Re: Extra white space using cursor_sharing FORCE

Post by mgorga » Mon 15 Jun 2015 12:36

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Extra white space using cursor_sharing FORCE

Post by AlexP » Tue 16 Jun 2015 04:52

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

mgorga
Posts: 5
Joined: Fri 12 Jun 2015 10:08

Re: Extra white space using cursor_sharing FORCE

Post by mgorga » Tue 16 Jun 2015 07:18

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Extra white space using cursor_sharing FORCE

Post by AlexP » Wed 17 Jun 2015 12:10

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
Posts: 5
Joined: Fri 12 Jun 2015 10:08

Re: Extra white space using cursor_sharing FORCE

Post by mgorga » Wed 29 Jul 2015 12:58

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Extra white space using cursor_sharing FORCE

Post by AlexP » Thu 30 Jul 2015 09:25

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.

Post Reply