[an error occurred while processing this directive]
Oracle | AnyData Type
Oracle AnyData Data Type Version 21c
General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose
The ANYDATA type is a type that can be used to hold any SQL or user-defined type. It picks up the data type dynamically from what it is being asked to store.
ANYDATA is a persisstent type containing an instance of a given type plus a self-referencing description of the type.
AUTHID
DEFINER
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'ANYDATA'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'ANYDATA';
SELECT object_name, object_type
FROM dba_objects_ae
WHERE object_name like '%ANY%'
AND owner = 'SYS';
Create Demo Table
conn uwclass/uwclass@pdbdev
CREATE TABLE t (mycol sys.anyData);
desc t
set describe depth all
cl scr
desc t
Load Table with Data
INSERT INTO t
VALUES (sys.anyData.convertNumber(5));
INSERT INTO t
VALUES (sys.anyData.convertDate(SYSDATE));
INSERT INTO t
VALUES (sys.anyData.convertVarchar2('hello world'));
COMMIT;
SELECT from table
col typename format a20
SELECT mytab.mycol.gettypeName() typeName
FROM t mytab;
Function For Retrieving From AnyData Data Type
Unfortunately, there isn't a method to display the contents of prompt ANYDATA in a query (most useful in programs that will fetch the data,
prompt figure out what it is and do something with it -- eg: the application has some intelligence as to how to handle the data)
col getdata format a20
CREATE OR REPLACE FUNCTION getData(p_x IN sys.anyData)
RETURN VARCHAR2 IS
l_num NUMBER;
l_date DATE;
l_varchar2 VARCHAR2(4000);
BEGIN
CASE p_x.gettypeName
WHEN 'SYS.NUMBER' THEN
IF (p_x.getNumber(l_num) = dbms_types.success) THEN
l_varchar2 := l_num;
END IF;
WHEN 'SYS.DATE' THEN
IF (p_x.getDate(l_date) = dbms_types.success) THEN
l_varchar2 := l_date;
END IF;
WHEN 'SYS.VARCHAR2' THEN
IF (p_x.getVarchar2(l_varchar2) = dbms_types.success) THEN
NULL;
END IF;
ELSE
l_varchar2 := '** unknown **';
END CASE;