ANYDATA is a self-describing data type: a value of it holds a value of any type together with the name of that type. A single ANYDATA column can store numbers, strings, dates, and objects side by side, and code can ask each value what it is before reading it.
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
anydata.convertnumber(n) anydata.convertvarchar2(s) anydata.convertdate(d) anydata.convertobject(obj) v.gettypename() -- 'SYS.NUMBER', 'SYS.VARCHAR2', ... anydata.accessnumber(v) anydata.accessvarchar2(v) -- in SQL status := v.getnumber(n_out); -- in PL/SQL, returns a status
A Column of Any Type
FLIGHT_ATTRIBUTES stores attributes of a flight as name and value pairs, where each value keeps its own type.
Example:
create table flight_attributes (
flight_no varchar2(6),
name varchar2(30),
value sys.anydata -- any type, and it remembers which
);
insert into flight_attributes values ('NM150', 'delay_minutes', anydata.convertnumber(45));
insert into flight_attributes values ('NM150', 'gate', anydata.convertvarchar2('B12'));
insert into flight_attributes values ('NM150', 'boarding',
anydata.convertdate(to_date('2026-03-15 21:30', 'YYYY-MM-DD HH24:MI')));
commit;
select name, a.value.gettypename() as type_name,
case a.value.gettypename()
when 'SYS.NUMBER' then to_char(anydata.accessnumber(a.value))
when 'SYS.VARCHAR2' then anydata.accessvarchar2(a.value)
when 'SYS.DATE' then to_char(anydata.accessdate(a.value), 'DD-MON HH24:MI')
end as value
from flight_attributes a;
declare
v_value sys.anydata;
v_num number;
begin
select value into v_value from flight_attributes where name = 'delay_minutes';
if v_value.getnumber(v_num) = dbms_types.success then -- returns a status
dbms_output.put_line('delay: ' || v_num || ' minutes');
end if;
end;
/Output:
Table FLIGHT_ATTRIBUTES created. 1 row inserted. 1 row inserted. 1 row inserted. Commit complete. NAME TYPE_NAME VALUE ________________ _______________ _______________ delay_minutes SYS.NUMBER 45 gate SYS.VARCHAR2 B12 boarding SYS.DATE 15-MAR 21:30 delay: 45 minutes PL/SQL procedure successfully completed.
- The CONVERT functions wrap a number, a string, and a date into ANYDATA values.
- GETTYPENAME tells each value's type, and the CASE picks the matching ACCESS function to read it.
- In PL/SQL, GETNUMBER returns DBMS_TYPES.SUCCESS and the number in its OUT parameter.
The example drops the table afterward.
Things to Know
- ACCESS functions return NULL when the value is of another type; GET methods return a status instead.
- ANYDATA cannot be indexed or compared directly; keep values you search on in typed columns.
- Advanced Queuing uses ANYDATA payloads for queues that carry messages of several types.
Related Guides
Conclusion
ANYDATA stores values of any type with their type name, in columns, variables, and queue payloads. Convert values in with CONVERT functions, check GETTYPENAME, and read them with the matching ACCESS function or GET method.
