Oracle | AnyData Type

Oracle AnyData Data Type Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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'; -- yields 165 objects

Documented Yes

Exceptions Uses DBMS_TYPES exceptions

First Available 9.0

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsany.sql

Subprograms

ACCESSDOUBLE	CONVERTNCHAR	GETTIMESTAMP
ACCESSBFILE	CONVERTNCLOB	GETTIMESTAMPLTZ
ACCESSBFLOAT	CONVERTNUMBER	GETTIMESTAMPTZ
ACCESSBLOB	CONVERTNVARCHAR2	GETTYPE
ACCESSCHAR	CONVERTOBJECT (2)	GETTYPENAME
ACCESSCLOB	CONVERTRAW	GETVARCHAR
ACCESSDATE	CONVERTREF	GETVARCHAR2
ACCESSINTERVALDS	CONVERTTIMESTAMP	PIECEWISE
ACCESSINTERVALYM	CONVERTTIMESTAMPLTZ	SETBDOUBLE
ACCESSNCHAR	CONVERTTIMESTAMPTZ	SETBFILE
ACCESSNCLOB	CONVERTUROWID	SETBFLOAT
ACCESSNUMBER	CONVERTVARCHAR	SETBLOB
ACCESSNVARCHAR2	CONVERTVARCHAR2	SETCHAR
ACCESSRAW	ENDCREATE	SETCLOB
ACCESSTIMESTAMP	GETBDOUBLE	SETCOLLECTION
ACCESSTIMESTAMPLTZ	GETBFILE	SETDATE
ACCESSTIMESTAMPTZ	GETBFLOAT	SETINTERVALDS
ACCESSUROWID	GETBLOB	SETINTERVALYM
ACCESSVARCHAR	GETCHAR	SETNCHAR
ACCESSVARCHAR2	GETCLOB	SETNCLOB
BEGINCREATE	GETCOLLECTION	SETNUMBER
CONVERTBDOUBLE	GETDATE	SETNVARCHAR2
CONVERTBFILE	GETINTERVALDS	SETOBJECT (2)
CONVERTBFLOAT	GETINTERVALYM	SETRAW
CONVERTBLOB	GETNCHAR	SETREF
CONVERTCHAR	GETNCLOB	SETTIMESTAMP
CONVERTCLOB	GETNUMBER	SETTIMESTAMPLTZ
CONVERTCOLLECTION	GETNVARCHAR2 (2)	SETTIMESTAMPTZ
CONVERTDATE	GETOBJECT	SETVARCHAR
CONVERTINTERVALDS	GETRAW	SETVARCHAR2
CONVERTINTERVALYM	GETREF	Demo

CONVERTCOLLECTION

Convert a COLLECTION to data type AnyData anydata.ConvertCollection(col IN "<COLLECTION_1>") RETURN AnyData

See Collections Demos

CONVERTDATE

Convert a DATE to data type AnyData anydata.ConvertDate(dat IN DATE) RETURN AnyData

See Demos Below

CONVERTNUMBER

Convert a NUMBER to data type AnyData anydata.ConvertNumber(num IN NUMBER) RETURN AnyData

See Demos Below

CONVERTVARCHAR2

Convert a VARCHAR2 to data type AnyData anydata.ConvertVarchar2(c IN VARCHAR2) RETURN AnyData

See Demos Below

GETTYPENAME

Get the fully qualified type name for the ANYDATA anydata.GetTypeName(self IN AnyData) RETURN VARCHAR2

See Demos Below

Demos

Basic conn / as sysdba desc 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; RETURN l_varchar2; END getData; / SELECT getData(mycol) GETDATA FROM t;