CREATE OR REPLACE Type t_Au_Delivery_Service As Object
(
Yes_No Integer,
Delivery_Price Number,
Note Varchar2(1000)
);
create table AU_AUCTION_PARAM
(
id_au_auction_param NUMBER not null,
id_au_auction NUMBER not null,
id_au_param_lib NUMBER not null,
au_param_value SYS.ANYDATA
);
Declare
d t_Au_Delivery_Service;
Begin
d:= t_Au_Delivery_Service(1,1,'');
d.Yes_No := 1;
d.Delivery_Price := 13.765;
d.Note := 'any text';
Insert Into Au_Auction_Param
(Id_Au_Auction,
Id_Au_Param_Lib,
Au_Param_Value)
Values
(1,
1,
Sys.Anydata.Convertobject(d));
End;
and this select get error ora-22636:
select t.au_param_value from AU_AUCTION_PARAM t -- ???
how to select object from anydata field?
Cast it as the type you're storing it as:
create or replace type t_au_delivery_service as object (
yes_no integer,
delivery_price number,
note varchar2( 1000 )
);
/
create table au_auction_param (
id_au_auction_param number not null,
id_au_auction number not null,
id_au_param_lib number not null,
au_param_value sys.anydata
);
declare
d t_au_delivery_service;
begin
d := t_au_delivery_service( 1,1,'' );
d.yes_no := 1;
d.delivery_price := 13.765;
d.note := 'any text';
insert into au_auction_param (
id_au_auction_param, id_au_auction,id_au_param_lib,au_param_value
) values (
1, 1, 1, sys.anydata.convertobject( d )
);
end;
/
select cast(t.au_param_value as t_au_delivery_service)
from au_auction_param t;
T_AU_DELIVERY_SERVICE(1, 13.765, 'any text')Though I have to ask:
Why are you storing this in anydata?
Storing object types is enough of a faff already with adding this layer of complexity. Can you make the type of your column the object itself?
drop table au_auction_param purge;
create table au_auction_param (
id_au_auction_param number not null,
id_au_auction number not null,
id_au_param_lib number not null,
au_param_value t_au_delivery_service
);
declare
d t_au_delivery_service;
begin
d := t_au_delivery_service( 1,1,'' );
d.yes_no := 1;
d.delivery_price := 13.765;
d.note := 'any text';
insert into au_auction_param (
id_au_auction_param, id_au_auction,id_au_param_lib,au_param_value
) values (
1, 1, 1, d
);
end;
/
select t.au_param_value
from au_auction_param t;
T_AU_DELIVERY_SERVICE(1, 13.765, 'any text')