Oracle ANYDATA Data Type Demos

Oracle ANYDATA Data Type Version 11.2.0.3

General Information
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.
AUTHID	DEFINER
Dependencies	SELECT name FROM dba_dependencies WHERE referenced_name = 'ANYDATA' union SELECT referenced_name FROM dba_dependencies WHERE name = 'ANYDATA'; -- yields 168 objects
First Available	9i
Security Model	Owned by SYS with EXECUTE granted to PUBLIC
Source	{ORACLE_HOME}/rdbms/admin/dbmsany.sql
Subprograms	SELECT DISTINCT overload, object_name FROM dba_arguments WHERE package_name = 'ANYDATA' ORDER BY 2,1;

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

GETTYPENAME
Get the fully qualified type name for the ANYDATA	anydata.GetTypeName(self IN AnyData) RETURN VARCHAR2
	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

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 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;