Passing object with object attributes to database by stored procedure

[Worad]

Good afternoon

I have the problem with passing object with object attribute to the database by stored procedure.
When object is passed as a parameter to stored procedure, it's simple attitubtes(number, datetime and so on) are passed, but complex attribute - object are not passed.

The following code illustrates it.

In Oracle:
create or replace type TNumber authid current_user as object
(
  FValue number
) not final;
/
create or replace type Pers authid current_user as object
(
  FID number(28),
  FV TNumber,
  constructor function Pers return self as result
) not final;

create or replace type body Pers as
  constructor function Pers return self as result is
  begin
    return;
  end;
end;
/
create table tbl_pers of pers;
/
create or replace package pkgMain is
  procedure InsertPers(p_Pers in Pers);
end pkgMain;
/
create or replace package body pkgMain is
  procedure InsertPers(p_Pers in Pers) is
  begin
    insert into tbl_pers values(p_Pers);
  end;
end pkgMain;
/
In Delphi:
  TPkgmain generated by package wizard.
procedure Test;
var
  O1: TOracleObject;
begin
  O1 := TOracleObject.Create(orcSession, 'pers', '');
  O1.SetAttr('FID', 1);
  O1.ObjAttr('FV').SetAttr('FValue', 2);;
  ShowMessage('O1.FID='+string(O1.GetAttr('FID'))+';O1.FV='+string(O1.ObjAttr('FV').GetAttr('FValue')));
  Pkgmain.Insertpers(O1);
  orcSession.Commit;
  O1.Free;
end;
//
ShowMessage shows 'O1.FID=1;O1.FV=2'.
//
After this command "select * from tbl_pers" in "SQL Window"(PL/SQL Developer) returns:
FID	FV.FValue
1		
//FV.FValue is empty because it is null.
//
When I execute
"begin
  pkgmain.InsertPers(new Pers(3, TNumber(4)));
  commit;
end;" in "Test Window"(PL/SQL Developer)
then command "select * from tbl_pers" in "SQL Window" returns:
FID	FV.FValue
3	<test.TNUMBER>*	4

Any ideas what can be wrong?

What do I to do to pass object with object attribute by stored procedure from Delphi code using DOA?

 

[Marco Kalter]

This seems like a bug. We'll check it out.

 

[Worad]

Originally posted by Marco Kalter:
This seems like a bug. We'll check it out.

Thank you.

Have you any idea how it can be possible to pass oracle object with object attributes to stored procedure?
I use TOracleQuery whith creating any oracle object in PL/SQL block and bind variables of ordinal type (number, datetime and so on) to parameters of object constructor.
For example:

begin
  pkgMain.InsertPers(new Pers(:v_FID, new TNumber(:v_FV_FValue)));
end;

Any idea how it can be simplified?

 

[Marco Kalter]

It can only be simplified by passing a complete object instance, but apparently this does not always work.

 

[Worad]

Originally posted by Marco Kalter:
It can only be simplified by passing a complete object instance, but apparently this does not always work.

Thank you.