Date and Timestamp attributes in Oracle Objects

[m.ghilardi]

ODAC seems to have some weird issues when treating date and timestamp attributes of Oracle Objects.
Sorry for the length of this post. This is an issue I've been investigating for quite a long time, I hope that this will help the developers to resolve the issue.

Es. 1 – date works

Code: Select all

Create type type1 as object(
	a_id1   number,
	a_date1 date,
	a_id2   number,
	a_date2 date 
) not final;

Create table table1 of type1;

Insert into table1 values (1,to_date( '2012','yyyy'),2,to_date( '2011','yyyy'));

Select value(x) from table1 x; --works

Drop table table1;
Drop type type1;

Es2 – date fails. Invalid argument to date encode

Code: Select all

Create type type1 as object(
	a_id1   number,
	a_date1 date 
) not final;

Create type type2 under type1(
	a_id2   number,
	a_date2 date 
) not final;

Create table table2 of type2;

Insert into table2 values (1,to_date( '2012','yyyy'),2,to_date( '2011','yyyy'));

Select *  from table2 x; -- ok
Select value(x) from table2 x; -- Invalid argument to date encode

Drop table table2;
Drop type type2;
Drop type type1;

Es3 – timestamp works

Code: Select all

Create type type1 as object(
	a_id1   number,
	a_date1 timestamp 
) not final;

Create type type2 under type1(
	a_id2   number,
	a_date2 timestamp
) not final;

Create table table2 of type2;

Insert into table2 values (1,systimestamp, 2, systimestamp)

Select value(x) from table2 x; -- it works!

Drop table table2;
Drop type type2;
Drop type type1;

Es4 – timestamp causes overflow on select

Code: Select all

Create type type1 as object(
  a_id1   number(18)
) not final;

Create type type2 under type1(
  a_id2   number(18),
  a_id3   number(18),
  a_date1 timestamp, 
  a_date2 timestamp,
  a_id4   number(18)
) not final;

Create type type3 under type2(
  a_id5   number(18)
) not final;

Create table table3 of type3;

Insert into table3 values (1, 2, 3, systimestamp,systimestamp, 4, 5)

Select value(x) from table3 x; -- Numeric Overflow on 2,3: got -1,0E126 -1,0E126

Drop table table3;
drop type type3;
Drop type type2;
Drop type type1;

Es6 – timestamp fails OCI_INVALID_HANDLE

Code: Select all

Create type type1 as object(
  a_id1   number(18)
) not final;

Create type type2 under type1(
  a_id2  number(18), 
  a_id3  number(18),
  a_id4  number(18),
  a_date1 timestamp,
  a_date2 timestamp
) not final;

Create type type3 under type2(
  a_id5   number(18)
) not final;

Create table table3 of type3;

Insert into table3 values (1, 2, 3, 4, systimestamp, systimestamp, 5)

Select value(x) from table3 x; -- OCI_INVALID_HANDLE

Drop table table3;
drop type type3;
Drop type type2;
Drop type type1;

Es7 – workaround?

Code: Select all

--…after Es6…
alter type type1 add attribute a_date0 timestamp cascade;
Select value(x) from table3 x; -- works

[AlexP]

Hello,

We cannot reproduce the problem. Please try to reproduce the problem on the latest ODAC version 9.2.7

[m.ghilardi]

Hello,
thanks for the prompt reply.
I was able to reproduce the problem using the latest ODAC version 9.2.7.

Setup.
Oracle 11g Express or Standard.
Client Oracle 11g or 12c.

SQL script (execute it using Toad/SQL Developer/sqlplus, it doesn't matter)

Code: Select all

Create type type1 as object(
   a_id1   number,
   a_date1 date
) not final;

Create type type2 under type1(
   a_id2   number,
   a_date2 date
) not final;

Create table table2 of type2;

Insert into table2 values (1,to_date( '2012','yyyy'),2,to_date( '2011','yyyy'));

I am using Rad Studio XE4 (also tested in Rad Studio XE2 with ODAC 8.6 and 9.1)
Create a C++ VCL project

Unit1.dfm

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 279
  ClientWidth = 475
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnShow = FormShow
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 96
    Top = 112
    Width = 320
    Height = 120
    DataSource = OraDataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object OraSession1: TOraSession
    Username = 'user'
    Server = 'localhost'
    Left = 360
    Top = 72
    EncryptedPassword = '9CFF9EFF8DFF96FF9DFF9AFF93FFCEFF'
  end
  object OraQuery1: TOraQuery
    Session = OraSession1
    SQL.Strings = (
      'Select value(x) from table2 x')
    Left = 160
    Top = 40
  end
  object OraDataSource1: TOraDataSource
    DataSet = OraQuery1
    Left = 16
    Top = 56
  end
end

Unit1.h

Code: Select all

//---------------------------------------------------------------------------

#ifndef Unit1H
#define Unit1H
//---------------------------------------------------------------------------
#include <System.Classes.hpp>
#include <Vcl.Controls.hpp>
#include <Vcl.StdCtrls.hpp>
#include <Vcl.Forms.hpp>
#include "DBAccess.hpp"
#include "MemDS.hpp"
#include "Ora.hpp"
#include "OraCall.hpp"
#include <Data.DB.hpp>
#include <Vcl.DBGrids.hpp>
#include <Vcl.Grids.hpp>
//---------------------------------------------------------------------------
class TForm1 : public TForm
{
__published:	// IDE-managed Components
	TOraSession *OraSession1;
	TOraQuery *OraQuery1;
	TDBGrid *DBGrid1;
	TOraDataSource *OraDataSource1;
	void __fastcall FormShow(TObject *Sender);
private:	// User declarations
public:		// User declarations
	__fastcall TForm1(TComponent* Owner);
};
//---------------------------------------------------------------------------
extern PACKAGE TForm1 *Form1;
//---------------------------------------------------------------------------
#endif

Unit1.cpp

Code: Select all

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "DBAccess"
#pragma link "MemDS"
#pragma link "Ora"
#pragma link "OraCall"
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
	: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::FormShow(TObject *Sender)
{
	OraQuery1->Open();
}
//---------------------------------------------------------------------------

With this simple form you will be able to reproduce every example i provided in the first post.

Hope this helps.

[AlexP]

Thank you for the information. We have reproduced the problem and will investigate the reasons of such behavior.

[AlexP]

We have already fixed this problem. The fix will be included to the next build.

[AlexP]

We have already fixed this problem. This fix will be included in the new build.