Page 1 of 1
Extra white space using cursor_sharing FORCE
Posted: Fri 12 Jun 2015 13:52
by 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
Re: Extra white space using cursor_sharing FORCE
Posted: Mon 15 Jun 2015 08:48
by 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.
Re: Extra white space using cursor_sharing FORCE
Posted: Mon 15 Jun 2015 10:22
by 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
Re: Extra white space using cursor_sharing FORCE
Posted: Mon 15 Jun 2015 11:21
by 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;
Re: Extra white space using cursor_sharing FORCE
Posted: Mon 15 Jun 2015 12:36
by 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
Re: Extra white space using cursor_sharing FORCE
Posted: Tue 16 Jun 2015 04:52
by AlexP
Please clarify: what do you mean by "read data from CHAR o NCHAR parameter "?
Re: Extra white space using cursor_sharing FORCE
Posted: Tue 16 Jun 2015 07:18
by 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
Re: Extra white space using cursor_sharing FORCE
Posted: Wed 17 Jun 2015 12:10
by 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.
Re: Extra white space using cursor_sharing FORCE
Posted: Wed 29 Jul 2015 12:58
by 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
Re: Extra white space using cursor_sharing FORCE
Posted: Thu 30 Jul 2015 09:25
by 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.