Oracle AnyData Data Type
Version 12.1.0.1

General Information
Library Note [an error occurred while processing this directive]
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 162 objects
Documented Yes
Exceptions Uses DBMS_TYPES exceptions
First Available 9i
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 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;

Related Topics
AnyDataSet
Collections
Data Types
DBMS_TYPES
Types

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved