Возможно ли просматривать начинку объектных типов независимо от их типа. Для этого в Oracle есть поддержка таких универсальных типов как ANYTYPE, ANYDATA и ANYDATASET. Ниже представлен пример рефлексии объектных типов, с элементами типа VARCHAR2, NUMBER и DATE, при желании список поддерживаемых типов можно расширить (см. документацию).
-- sql
create or replace type order_t as object (
order_id number,
order_date date,
description varchar2(1000)
)
-- pl/sql
declare
v_order order_t := order_t(123456, sysdate, 'Description text');
v_anydata anydata;
-- print object information (reflection)
procedure objectInfo(p_object in out anydata) -- out for piecewise
is
l_typecode pls_integer;
l_anytype anytype;
l_result pls_integer;
l_varchar2 varchar2(32767);
l_number number;
l_date date;
-- type-level metadata
type typeinfo_r is record (
prec pls_integer
,scale pls_integer
,len pls_integer
,csid pls_integer
,csfrm pls_integer
,schema_name varchar2(30)
,type_name varchar2(30)
,version varchar2(30)
,count pls_integer
);
typeinfo typeinfo_r;
-- attribute-level metadata
type attrinfo_r is record (
prec pls_integer
,scale pls_integer
,len pls_integer
,csid pls_integer
,csfrm pls_integer
,attr_elt_type anytype
,aname varchar2(32767)
);
attrinfo attrinfo_r;
begin
-- get information for the ANYDATA instance
l_typecode := p_object.getType(l_anytype);
dbms_output.put_line('Typecode: ' || l_typecode);
if l_typecode = dbms_types.typecode_object then -- if given object
p_object.piecewise();
-- get type-level metadata
l_typecode := l_anytype.GetInfo(typeinfo.prec,
typeinfo.scale,
typeinfo.len,
typeinfo.csid,
typeinfo.csfrm,
typeinfo.schema_name,
typeinfo.type_name,
typeinfo.version,
typeinfo.count);
dbms_output.put_line('-------------------------------------');
dbms_output.put_line('Typename: ' || typeinfo.type_name);
dbms_output.put_line('Attributes: ' || typeinfo.count);
for i in 1..typeinfo.count loop -- loop by atrributes
-- get attribute-level metadata
l_typecode := l_anytype.getAttrElemInfo(i,
attrinfo.prec,
attrinfo.scale,
attrinfo.len,
attrinfo.csid,
attrinfo.csfrm,
attrinfo.attr_elt_type,
attrinfo.aname);
dbms_output.put_line('-------------------------------------');
dbms_output.put_line('Attribute: ' || attrinfo.aname);
dbms_output.put_line('Typecode: ' || l_typecode);
dbms_output.put_line('Length: ' || nvl(attrinfo.len, attrinfo.prec));
if l_typecode = dbms_types.typecode_varchar2 then -- varchar2
l_result := p_object.getVarchar2(c => l_varchar2);
dbms_output.put_line('Value: ' || l_varchar2);
elsif l_typecode = dbms_types.typecode_number then -- number
l_result := p_object.getNumber(num => l_number);
dbms_output.put_line('Value: ' || l_number);
elsif l_typecode = dbms_types.typecode_date then -- date
l_result := p_object.getDate(dat => l_date);
dbms_output.put_line('Value: ' || to_char(l_date, 'dd.mm.yyyy'));
else
raise_application_error(-20000, 'Unexpected ' || i || 'st attribute typecode: ' || l_typecode);
end if;
end loop;
end if;
end;
begin
v_anydata := anydata.convertObject(v_order);
objectInfo(v_anydata);
end;
Typecode: 108
-------------------------------------
Typename: ORDER_T
Attributes: 3
-------------------------------------
Attribute: ORDER_ID
Typecode: 2
Length: 0
Value: 123456
-------------------------------------
Attribute: ORDER_DATE
Typecode: 12
Length:
Value: 15.10.2015
-------------------------------------
Attribute: DESCRIPTION
Typecode: 9
Length: 1000
Value: Description text