Problem with Oracle Objects: Values are corrupt

[StephanAC]

Hi there,

we have a serious problem with PL-SQL Developer (8.0.1.1502) and Direct Oracle Access (4.0.7). We use Oracle 10.2.0.4.0.

We use Oracle Objects with inheritance. If a pointer/varchar, etc. is used in the hierarchy the fields in the last object in the hierarchy are corrupt.
In our example the Datefield has invalid values, it seems that the value has to be shifted by 2 bytes.

In Delphi with Direct Oracle Access we retrieve invalid values.
Using the PL/SQl-Developer we geht a .

Example:
create or replace type Level1 as object
(
"ID1" NUMBER
) NOT INSTANTIABLE NOT FINAL
/
CREATE OR REPLACE TYPE Level2 UNDER Level1
(
"ID2" NUMBER
)
NOT INSTANTIABLE NOT FINAL;
/
CREATE OR REPLACE TYPE Level3 UNDER Level2
(
REF3 REF LEVEl1
)
NOT INSTANTIABLE NOT FINAL;
/
CREATE OR REPLACE TYPE Level4 UNDER Level3
(
"ID4" NUMBER
)
NOT INSTANTIABLE NOT FINAL;
/
create or replace type Level5 UNDER Level4
(
date5 DATE
) INSTANTIABLE NOT FINAL;
/
CREATE TABLE level_table OF LEVEL1;

insert into level_table VALUES(LEVEL5(1,2,null,4, to_date('2010/10/23 09:10:11','yyyy/mm/dd hh24:mi:ss')));

select treat(value(trf) as LEVEL5),
treat(value(trf) as LEVEL5).date5 from level_table trf

Cheers,
Stephan

 

[StephanAC]

Sorry I missed the corresponding Delphi Code:


var
orqueTemp : TOracleQuery;
intLauf : TVariableData;
refTemp : TOracleReference;
obj : TOracleObject;
result :Variant;
begin
orqueTemp := TOracleQuery.Create(NIL);
orqueTemp.Session := TOracleSession.Create(Owner);
orqueTemp.Session.LogonUsername := 'foo';
orqueTemp.Session.LogonPassword := 'bar';
orqueTemp.Session.LogonDatabase := 'prob';
orqueTemp.Session.LogOn();
orqueTemp.Clear();
orqueTemp.SQL.add('begin');
orqueTemp.SQL.add('SELECT TREAT(ref(p) AS ref LEVEL5) INTO :ATIMES FROM level_table p where id1 =1;');
orqueTemp.SQL.add('end;');
refTemp := TOracleReference.Create(orqueTemp.Session, 'LEVEL5');
orqueTemp.DeclareVariable('ATIMES', otReference);
orqueTemp.SetComplexVariable('ATIMES', refTemp);
orqueTemp.Execute;
obj := refTemp.pin(poAny, plNone);
result := obj.getAttr('date5'); //

 

[Marco Kalter]

Thank you for reporting this. We'll check it out.

 

[StephanAC]

Hi there,

do you have any new information about the issue ?

Cheers,
Stephan

 

[Marco Kalter]

Not yet. I will keep you posted.

 

[StephanAC]

Hmm no new information yet ?