Database PL/SQL Packages and ANYDATA Types

An ANYDATA TYPE contains an instance of a given type, plus a description of the type. In this sense, an ANYDATA is self-describing. An ANYDATA can be persistently stored in the database.

Using ANYDATA TYPE

• Restrictions

  Persistent storage of ANYDATA instances whose type contains embedded LOBs other than BFILEs is not currently supported.

• Operational Notes

  • Construction

    There are 2 ways to construct an ANYDATA. The CONVERT* calls enable construction of the ANYDATA in its entirety with a single call. They serve as explicit CAST functions from any type in the Oracle ORDBMS to ANYDATA.

    STATIC FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return ANYDATA,
    STATIC FUNCTION ConvertBfile(b IN BFILE) RETURN ANYDATA,
    STATIC FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return ANYDATA,
    STATIC FUNCTION ConvertBlob(b IN BLOB) RETURN ANYDATA,
    STATIC FUNCTION ConvertChar(c IN CHAR) RETURN ANYDATA,
    STATIC FUNCTION ConvertClob(c IN CLOB) RETURN ANYDATA,
    STATIC FUNCTION ConvertCollection(col IN "collection_type") RETURN ANYDATA,
    STATIC FUNCTION ConvertDate(dat IN DATE) RETURN ANYDATA,
    STATIC FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return ANYDATA,
    STATIC FUNCTION ConvertIntervalYM(invIN INTERVAL YEAR TO MONTH) return ANYDATA,
    STATIC FUNCTION ConvertNchar(nc IN NCHAR) return ANYDATA,
    STATIC FUNCTION ConvertNClob(nc IN NCLOB) return ANYDATA,
    STATIC FUNCTION ConvertNumber(num IN NUMBER) RETURN ANYDATA,
    STATIC FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return ANYDATA,
    STATIC FUNCTION ConvertObject(obj IN "") RETURN ANYDATA,
    STATIC FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA,
    STATIC FUNCTION ConvertRef(rf IN REF "") RETURN ANYDATA,
    STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP) return ANYDATA,
    STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP WITH TIMEZONE) return ANYDATA,
    STATIC FUNCTION ConvertTimestampLTZ(ts IN TIMESTAMP WITH LOCAL TIMEZONE) return ANYDATA,
    STATIC FUNCTION ConvertURowid(rid IN UROWID) return ANYDATA,
    STATIC FUNCTION ConvertVarchar(c IN VARCHAR) RETURN ANYDATA,
    STATIC FUNCTION ConvertVarchar2(c IN VARCHAR2) RETURN ANYDATA
    

    The second way to construct an ANYDATA is a piece by piece approach. The BEGINCREATE Static Procedure call begins the construction process and ENDCREATE Member Procedure call finishes the construction process. In between these two calls, the individual attributes of an object type or the elements of a collection can be set using SET* calls. For piece by piece access of the attributes of objects and elements of collections, the PIECEWISE Member Procedure should be invoked prior to GET* calls.

    Note: The ANYDATA has to be constructed or accessed sequentially starting from its first attribute (or collection element). The BEGINCREATE call automatically begins the construction in a piece-wise mode. There is no need to call PIECEWISE immediately after BEGINCREATE. ENDCREATE should be called to finish the construction process (before which any access calls can be made).

  • Access

    Access functions are available based on SQL. These functions do not throw exceptions on type-mismatch. Instead, they return NULL if the type of the ANYDATA does not correspond to the type of access. If you wish to use only ANYDATA functions of the appropriate types returned in a query, you should use a WHERE clause which uses GETTYPENAME and choose the type you are interested in (say "SYS.NUMBER"). Each of these functions returns the value of a specified datatype inside a SYS.ANYDATA wrapper.

    MEMBER FUNCTION AccessBDouble(self IN ANYDATA) return BINARY_DOUBLE DETERMINISTIC ,
    MEMBER FUNCTION AccessBfile(self IN ANYDATA) return BFILE ,
    MEMBER FUNCTION AccessBFloat(self IN ANYDATA) return BINARY_FLOAT  DETERMINISTIC ,
    MEMBER FUNCTION AccessBlob(self IN ANYDATA) return BLOB ,
    MEMBER FUNCTION AccessChar(self IN ANYDATA) return CHAR ,
    MEMBER FUNCTION AccessClob(self IN ANYDATA) return CLOB ,
    MEMBER FUNCTION AccessDate(self IN ANYDATA) return DATE ,
    MEMBER FUNCTION AccessIntervalYM(self IN ANYDATA) return INTERVAL YEAR TO MONTH ,
    MEMBER FUNCTION AccessIntervalDS(self IN ANYDATA) return INTERVAL DAY TO SECOND ,
    MEMBER FUNCTION AccessNchar(self IN ANYDATA) return NCHAR ,
    MEMBER FUNCTION AccessNClob(self IN ANYDATA) return NCLOB 
    MEMBER FUNCTION AccessNumber(self IN ANYDATA) return NUMBER ,
    MEMBER FUNCTION AccessNVarchar2(self IN ANYDATA) return NVARCHAR2 ,
    MEMBER FUNCTION AccessRaw(self IN ANYDATA) return RAW ,
    MEMBER FUNCTION AccessTimestamp(self IN ANYDATA) return TIMESTAMP ,
    MEMBER FUNCTION AccessTimestampLTZ(self IN ANYDATA) return TIMESTAMP WITH LOCAL TIMEZONE ,
    MEMBER FUNCTION AccessTimestampTZ(self IN ANYDATA) return TIMESTAMP WITH TIMEZONE ,
    MEMBER FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC 
    MEMBER FUNCTION AccessVarchar(self IN ANYDATA) return VARCHAR ,
    MEMBER FUNCTION AccessVarchar2(self IN ANYDATA) return VARCHAR2