When I create a type that contains timestamps and intervals, then create a nested table from that type, and try to view the parent table and nested table using a TOraSmartQuery and TOraNestedTable, ODAC gives me errors. There are no errors if the timestamps and intervals are not present.
Here is the SQL to create the objects WITH timestamps and intervals, which throws the error.
Code: Select all
drop table test_ls;
drop type STATUS_HISTORY_TAB_TYPE;
drop type STATUS_HISTORY_TYPE;
CREATE OR REPLACE TYPE STATUS_HISTORY_TYPE AS OBJECT
(
STATUS VARCHAR2(30),
PROCESSED_RECS INTEGER,
ERROR_RECS INTEGER,
ASSIGNED_TS TIMESTAMP,
ELAPSED_TIME INTERVAL DAY(3) TO SECOND(3)
);
/
CREATE OR REPLACE TYPE STATUS_HISTORY_TAB_TYPE IS TABLE OF STATUS_HISTORY_TYPE;
/
CREATE TABLE TEST_LS
(
LOAD_STATISTICS_PK NUMBER NOT NULL,
LOAD_TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
FILE_NAME VARCHAR2(100 BYTE) NOT NULL,
HISTORY_STATUSES STATUS_HISTORY_TAB_TYPE,
SOME_INTERVAL INTERVAL DAY TO SECOND(3),
COMMENTS VARCHAR2(4000)
)
NESTED TABLE HISTORY_STATUSES STORE AS TEST_LS_NT;
insert into TEST_LS
values(1, systimestamp, 'TEST_1', new STATUS_HISTORY_TAB_TYPE(new STATUS_HISTORY_TYPE('S1', 12, 1, systimestamp - 3/24, interval '30' second),
new STATUS_HISTORY_TYPE('S2', 101, 0, systimestamp - 2/24, interval '1' second),
new STATUS_HISTORY_TYPE('S3', 99, 14, systimestamp - 1/24, interval '9' second)),
interval '98767' second, 'Test 1 comment');
insert into TEST_LS
values(1, systimestamp, 'TEST_2', new STATUS_HISTORY_TAB_TYPE(new STATUS_HISTORY_TYPE('S4', 22, 1, systimestamp - 3.1/24, interval '31' second),
new STATUS_HISTORY_TYPE('S5', 201, 0, systimestamp - 2.1/24, interval '2' second),
new STATUS_HISTORY_TYPE('S6', 199, 14, systimestamp - 1.1/24, interval '91' second)),
interval '121' second, 'Test 2 comment');
insert into TEST_LS
values(1, systimestamp, 'TEST_3', new STATUS_HISTORY_TAB_TYPE(new STATUS_HISTORY_TYPE('S7', 32, 1, systimestamp - 3.2/24, interval '19' second),
new STATUS_HISTORY_TYPE('S8', 301, 0, systimestamp - 2.2/24, interval '7' second),
new STATUS_HISTORY_TYPE('S9', 299, 14, systimestamp - 1.2/24, interval '8' second)),
interval '0' second,'Test 3 comment');
commit;
Code: Select all
drop table test_ls;
drop type STATUS_HISTORY_TAB_TYPE;
drop type STATUS_HISTORY_TYPE;
CREATE OR REPLACE TYPE STATUS_HISTORY_TYPE AS OBJECT
(
STATUS VARCHAR2(30),
PROCESSED_RECS INTEGER,
ERROR_RECS INTEGER
);
/
CREATE OR REPLACE TYPE STATUS_HISTORY_TAB_TYPE IS TABLE OF STATUS_HISTORY_TYPE;
/
CREATE TABLE TEST_LS
(
LOAD_STATISTICS_PK NUMBER NOT NULL,
LOAD_TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
FILE_NAME VARCHAR2(100 BYTE) NOT NULL,
HISTORY_STATUSES STATUS_HISTORY_TAB_TYPE,
SOME_INTERVAL INTERVAL DAY TO SECOND(3),
COMMENTS VARCHAR2(4000)
)
NESTED TABLE HISTORY_STATUSES STORE AS TEST_LS_NT;
insert into TEST_LS
values(1, systimestamp, 'TEST_1', new STATUS_HISTORY_TAB_TYPE(new STATUS_HISTORY_TYPE('S1', 12, 1),
new STATUS_HISTORY_TYPE('S2', 101, 0),
new STATUS_HISTORY_TYPE('S3', 99, 14)),
interval '98767' second, 'Test 1 comment');
insert into TEST_LS
values(1, systimestamp, 'TEST_2', new STATUS_HISTORY_TAB_TYPE(new STATUS_HISTORY_TYPE('S4', 22, 1),
new STATUS_HISTORY_TYPE('S5', 201, 0),
new STATUS_HISTORY_TYPE('S6', 199, 14)),
interval '121' second, 'Test 2 comment');
insert into TEST_LS
values(1, systimestamp, 'TEST_3', new STATUS_HISTORY_TAB_TYPE(new STATUS_HISTORY_TYPE('S7', 32, 1),
new STATUS_HISTORY_TYPE('S8', 301, 0),
new STATUS_HISTORY_TYPE('S9', 299, 14)),
interval '0' second,'Test 3 comment');
commit;
Code: Select all
unit Unit2;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, Vcl.Grids, Vcl.DBGrids, OraCall, DBAccess, Ora, OraSmart, MemDS,
Vcl.ExtCtrls;
type
TForm2 = class(TForm)
dsNested: TDataSource;
NT: TOraNestedTable;
sqMaster: TSmartQuery;
dsMaster: TDataSource;
Session: TOraSession;
DBGrid1: TDBGrid;
DBGrid2: TDBGrid;
Splitter1: TSplitter;
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form2: TForm2;
implementation
{$R *.dfm}
procedure TForm2.FormCreate(Sender: TObject);
begin
session.username := 'JDORLON';
session.password := 'jdorlon';
session.server := 'AZURE_12C_PLUG';
session.Connected := True;
sqMaster.sql.add('select * from TEST_LS');
sqMaster.Open;
NT.DataSetField := TDataSetField(sqMaster.FieldByName('HISTORY_STATUSES'));
NT.Open;
end;
end.
Code: Select all
object Form2: TForm2
Left = 0
Top = 0
Caption = 'Form2'
ClientHeight = 385
ClientWidth = 577
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 Splitter1: TSplitter
Left = 0
Top = 164
Width = 577
Height = 6
Cursor = crVSplit
Align = alTop
ExplicitTop = 120
end
object DBGrid1: TDBGrid
Left = 0
Top = 0
Width = 577
Height = 164
Align = alTop
DataSource = dsMaster
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object DBGrid2: TDBGrid
Left = 0
Top = 170
Width = 577
Height = 215
Align = alClient
DataSource = dsNested
TabOrder = 1
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object dsNested: TDataSource
DataSet = NT
Left = 62
Top = 140
end
object NT: TOraNestedTable
Left = 27
Top = 141
end
object sqMaster: TSmartQuery
Session = Session
ObjectView = True
Left = 29
Top = 102
end
object dsMaster: TDataSource
DataSet = sqMaster
Left = 64
Top = 105
end
object Session: TOraSession
LoginPrompt = False
Left = 29
Top = 71
end
end
Delphi 10.1 Berlin
ODAC 10.1.3
I can send a demo project if needed, but you should have everything you need to reproduce the problem.
Thank you.