Hey:
I reproduced this issue for you in a separate sample than our app. Here is all the information:
- Oracle 10g version: 10.2.0.4.0 32 bit running on linux RH
- Oracle 12c version: 12.1.0.2.0 64 bit running on Oracle Linux 7
- devart dbexpress Oracle driver 6.6.9
- windows app 32 bit
- Delphi XE8 SP1 running onw windows 7 64 bit SP1
This is the sql that you have to run in both databases before you run the test app:
Code: Select all
--drop table TEST_CODE1_TB;
--drop table TEST_CODE2_TB;
create table TEST_CODE1_TB (CD NUMBER(2) NULL);
create table TEST_CODE2_TB (CD NUMBER(2) NULL);
INSERT INTO TEST_CODE1_TB VALUES(-20);
INSERT INTO TEST_CODE2_TB VALUES(-10);
COMMIT;
-- drop public synonym TEST_CODE_TB_SYN;
create or replace view test_code_v
as
with x as (
select * from TEST_CODE2_TB
)
SELECT CAST(
(CASE
WHEN T1.CD < 0 THEN T1.CD
when t1.cd = 0 then t2.cd
when t1.cd < 10 then null
ELSE cast('-1' as number(2))
END) AS NUMBER(2)) AS Val
FROM TEST_CODE1_TB T1 cross join x T2;
This particular sql view is contrived however it mirrors somewhat the production code.
- You need to add this in the dbxconnections.ini (add the name of your db and your user name - start with Oracle 10g):
Code: Select all
[TestDB]
BlobSize=-1
DataBase=<tnsnames.ora entry>
DriverName=DevartOracle
ErrorResourceFile=
LocaleCode=0000
Password=
Oracle TransIsolation=ReadCommitted
RoleName=Normal
User_Name=<user name>
LongStrings=True
EnableBCD=True
InternalName=
FetchAll=False
CharLength=0
Charset=
UseQuoteChar=False
UseUnicode=True
- Delphi code:
MainUnit.dfm (replace xxx with the proper information):
Code: Select all
object Form1: TForm1
Left = 0
Top = 0
Caption = 'Form1'
ClientHeight = 412
ClientWidth = 852
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object DBGrid1: TDBGrid
Left = 288
Top = 64
Width = 320
Height = 120
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object SQLQuery1: TSQLQuery
MaxBlobSize = -1
Params = <>
SQL.Strings = (
'select * from test_code_v')
SQLConnection = SQLConnection1
Left = 56
Top = 344
object SQLQuery1VAL: TFMTBCDField
FieldName = 'VAL'
Precision = 32
end
end
object DataSource1: TDataSource
DataSet = ClientDataSet1
Left = 80
Top = 160
end
object DataSetProvider1: TDataSetProvider
DataSet = SQLQuery1
Left = 80
Top = 272
end
object ClientDataSet1: TClientDataSet
Aggregates = <>
Params = <>
ProviderName = 'DataSetProvider1'
Left = 80
Top = 216
end
object SQLConnection1: TSQLConnection
ConnectionName = 'TestDB'
DriverName = 'DevartOracle'
Params.Strings = (
'BlobSize=-1'
'DataBase=xxx'
'DriverName=DevartOracle'
'ErrorResourceFile='
'LocaleCode=0000'
'Password='
'Oracle TransIsolation=ReadCommitted'
'RoleName=Normal'
'User_Name=xxx'
'LongStrings=True'
'EnableBCD=True'
'InternalName='
'FetchAll=False'
'CharLength=0'
'Charset='
'UseQuoteChar=False'
'UseUnicode=True')
Connected = True
Left = 80
Top = 104
end
end
MainUnit.pas:
Code: Select all
unit MainUnit;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBXDevartOracle, Data.DB, Data.SqlExpr, Data.FMTBcd, Vcl.Grids, Vcl.DBGrids,
Datasnap.DBClient, Datasnap.Provider;
type
TForm1 = class(TForm)
SQLQuery1: TSQLQuery;
DataSource1: TDataSource;
DataSetProvider1: TDataSetProvider;
ClientDataSet1: TClientDataSet;
DBGrid1: TDBGrid;
SQLConnection1: TSQLConnection;
SQLQuery1VAL: TFMTBCDField;
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.FormCreate(Sender: TObject);
begin
SQLConnection1.Connected := true;
ClientDataSet1.Active := true;
end;
end.
I haven't added the project dpr and droj files but basically I created a VCL forms application.
The crux of the issue is that, when using 10g and adding adding all fields to the TSQLQuery component, the driver stores the precision of the VAL column as 32! And here is the interesting thing. It is because of the WITH clause in the view. If you re-write the view query without the with clause, the precision/scale are detected properly, i.e. 2/0. In 12c apparently they fixed it and it works even with the WITH clause.
One more thing, when you test adding the field to the TSQLQuery component make sure you disconnect the TSQLConnection object first. It seems that Delphi caches the field and it doesn't really re-retrieve the field meta-data from the database.
Let me know if now you can reproduce the issue, otherwise I will provide more information if you need it.
Thanks