ANYTYPE describes a data type: its type code, name, and, for object types, each attribute. Code that receives values it does not know in advance, for example in an ANYDATA, can use it to find out what the value contains and handle it generically.
Code for This Guide
The main examples are in the examples/pkg-generic-types folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
typecode := anydata_value.gettype(anytype_out);
typecode := anytype_value.getinfo(prec, scale, len, csid, csfrm,
schema_name, type_name, version, count);
typecode := anytype_value.getattreleminfo(position, prec, scale, len, csid, csfrm,
attr_type_out, attr_name_out);Type codes are constants in DBMS_TYPES, such as TYPECODE_OBJECT and TYPECODE_VARCHAR2.
Describe an Object Inside ANYDATA
Example:
create type seat_t as object (seat_no varchar2(4), cabin varchar2(8), window boolean);
/
declare
v_data sys.anydata := anydata.convertobject(seat_t('12A', 'ECONOMY', true));
v_type sys.anytype;
v_code pls_integer;
v_prec pls_integer; v_scale pls_integer; v_len pls_integer; v_csid pls_integer;
v_csfrm pls_integer; v_schema varchar2(128); v_name varchar2(128); v_version varchar2(30);
v_count pls_integer; v_attr_type sys.anytype; v_attr_name varchar2(128);
begin
v_code := v_data.gettype(v_type); -- the type of the value inside
v_code := v_type.getinfo(v_prec, v_scale, v_len, v_csid, v_csfrm, v_schema, v_name,
v_version, v_count);
dbms_output.put_line(v_schema || '.' || v_name || ': typecode ' || v_code
|| case v_code when dbms_types.typecode_object then ' (object)' end
|| ', ' || v_count || ' attributes');
for i in 1 .. v_count loop
v_code := v_type.getattreleminfo(i, v_prec, v_scale, v_len, v_csid, v_csfrm,
v_attr_type, v_attr_name);
dbms_output.put_line(' ' || v_attr_name || ': typecode ' || v_code);
end loop;
end;
/Output:
Type SEAT_T compiled NIMBUS.SEAT_T: typecode 108 (object), 3 attributes SEAT_NO: typecode 9 CABIN: typecode 9 WINDOW: typecode 252 PL/SQL procedure successfully completed.
- GETTYPE returns 108, DBMS_TYPES.TYPECODE_OBJECT, and the ANYTYPE of the value.
- GETINFO gives the type's schema and name, NIMBUS.SEAT_T, and its 3 attributes.
- GETATTRELEMINFO describes each attribute: two VARCHAR2 attributes, typecode 9, and the BOOLEAN attribute WINDOW, typecode 252.
The example drops the type afterward.
Things to Know
- ANYTYPE can also build new transient types at run time with BEGINCREATE, ADDATTR, and ENDCREATE.
- Compare type codes with the DBMS_TYPES constants rather than with numbers.
- For collections, GETATTRELEMINFO describes the element type.
Related Guides
Conclusion
ANYTYPE describes types at run time: their type code, name, and attributes. Use it with ANYDATA to handle values of types your code does not know in advance.
