All Products
Search
Document Center

PolarDB:ANYDATA

Last Updated:Mar 22, 2023

An ANYDATA type contains an instance of a given type and a description of the type. An ANYDATA can be persistently stored in databases.

You can call CONVERT * only once to construct an ANYDATA in its entirety. The data of different types serve as explicit CAST functions when a data type in PolarDB for PostgreSQL(Compatible with Oracle) is converted into ANYDATA.

STATIC FUNCTION ConvertInteger( num IN  INTEGER ) RETURN AnyData,
  STATIC FUNCTION ConvertNumber( num IN  NUMBER ) RETURN AnyData,
  STATIC FUNCTION ConvertChar( c IN  CHAR ) RETURN AnyData,
  STATIC FUNCTION ConvertVarchar( c IN  VARCHAR ) RETURN AnyData,
  STATIC FUNCTION ConvertVarchar2( c IN  VARCHAR2 ) RETURN AnyData,
  STATIC FUNCTION ConvertNVarchar( c IN  NVARCHAR ) RETURN AnyData,
  STATIC FUNCTION ConvertNVarchar2( c IN  NVARCHAR2 ) RETURN AnyData,
  STATIC FUNCTION ConvertDate( ts IN  TIMESTAMP ) RETURN AnyData,
  STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP ) RETURN AnyData,
  STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP WITH TIME ZONE ) RETURN AnyData,
  STATIC FUNCTION ConvertIntervalYM(inv IN INTERVAL YEAR TO MONTH ) RETURN AnyData,
  STATIC FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND ) RETURN AnyData, 
    STATIC FUNCTION ConvertClob(c IN CLOB) RETURN ANYDATA,
  /*Don't support input AnyData type*/
  STATIC FUNCTION ConvertObject(obj IN AnyElement) RETURN AnyData,

Access functions are available based on SQL. These functions do not throw exceptions if a type mismatch occurs. Instead, NULL is returned if the ANYDATA type does not match the type of access. If you need to use only ANYDATA functions of the appropriate types that are returned in a query, you must use the WHERE clause that uses GETTYPENAME, and select a type such as SYS.NUMBER. Each of these functions returns the value of a specified data type in a SYS.ANYDATA wrapper.

/* Get the fully qualified Type Name for the AnyData */
  MEMBER FUNCTION GetTypeName( self IN AnyData) RETURN VARCHAR2 DETERMINISTIC,

  /* Access functions for AnyData */
  MEMBER FUNCTION AccessInteger( self IN AnyData ) RETURN INTEGER , 
  MEMBER FUNCTION AccessNumber( self IN AnyData ) RETURN NUMBER ,
  MEMBER FUNCTION AccessChar( self IN AnyData ) RETURN CHAR ,
  MEMBER FUNCTION AccessVarchar( self IN AnyData ) RETURN VARCHAR ,
  MEMBER FUNCTION AccessVarchar2( self IN AnyData ) RETURN VARCHAR2 ,
  MEMBER FUNCTION AccessNVarchar( self IN AnyData ) RETURN NVARCHAR ,
  MEMBER FUNCTION AccessNVarchar2( self IN AnyData ) RETURN NVARCHAR2 ,
  MEMBER FUNCTION AccessDate( self IN AnyData ) RETURN DATE ,
  MEMBER FUNCTION AccessTimestamp( self IN AnyData ) RETURN TIMESTAMP ,
  MEMBER FUNCTION AccessTimestampTZ( self IN AnyData ) RETURN TIMESTAMP WITH TIME ZONE ,
  MEMBER FUNCTION AccessIntervalYM( self IN AnyData ) RETURN INTERVAL YEAR TO MONTH ,
  MEMBER FUNCTION AccessIntervalDS( self IN AnyData ) RETURN INTERVAL DAY TO SECOND ,   
  MEMBER FUNCTION AccessClob(self IN ANYDATA) return CLOB,
  
  /* Gets the current data value */
  MEMBER FUNCTION GetInteger( self IN AnyData, num OUT NOCOPY INTEGER) RETURN PLS_INTEGER ,
  MEMBER FUNCTION GetNumber( self IN AnyData, num OUT NOCOPY NUMBER) RETURN PLS_INTEGER ,
  MEMBER FUNCTION GetChar( self IN AnyData, c OUT NOCOPY CHAR) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetVarchar( self IN AnyData, c OUT NOCOPY VARCHAR) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetVarchar2( self IN AnyData, c OUT NOCOPY VARCHAR2) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetNVarchar( self IN AnyData, c OUT NOCOPY NVARCHAR) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetNVarchar2( self IN AnyData, c OUT NOCOPY NVARCHAR2) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetDate( self IN AnyData, ts OUT NOCOPY DATE) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetTimestamp( self IN AnyData, ts OUT NOCOPY TIMESTAMP) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetTimestampTZ( self IN AnyData, ts OUT NOCOPY TIMESTAMP WITH TIME ZONE) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetIntervalYM( self IN AnyData, inv OUT NOCOPY INTERVAL YEAR TO MONTH) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetIntervalDS( self IN AnyData, inv OUT NOCOPY INTERVAL DAY TO SECOND) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetClob(self IN ANYDATA, c OUT NOCOPY CLOB) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetObject( self IN AnyData, obj OUT NOCOPY AnyElement) RETURN PLS_INTEGER

Examples

CREATE TABLE t1( c1 SYS.AnyData);
INSERT INTO t1 VALUES(AnyData.ConvertInteger(12));
INSERT INTO t1 VALUES(AnyData.ConvertNumber(10000));
INSERT INTO t1 VALUES(AnyData.ConvertChar('m'));

SELECT 
SYS.AnyData.GetTypeName(c1) typename, 
(CASE SYS.AnyData.GetTypeName(c1)
WHEN 'SYS.INTEGER' THEN SYS.AnyData.AccessNumber(c1)::varchar2
WHEN 'SYS.NUMBER' THEN SYS.AnyData.AccessNumber(c1)::varchar2
WHEN 'SYS.CHAR' THEN SYS.AnyData.AccessChar(c1)::varchar2
WHEN NULL THEN 'NULL' 
ELSE (
  CASE WHEN SYS.AnyData.GetTypeName(c1) IS NULL THEN 'NULL'
  ELSE CONCAT('**** unkonwn ****'::varchar2,SYS.AnyData.GetTypeName(c1))
  END
  )
END) ct, getAnyData(c1), c1
FROM t1 t1;
           typename           |                 ct                  |             getanydata              |                                  c1                                  
------------------------------+-------------------------------------+-------------------------------------+----------------------------------------------------------------------
 SYS.INTEGER                  | 12                                  | 12                                  | (SYS.INTEGER,12)
 SYS.NUMBER                   | 10000                               | 10000                               | (SYS.NUMBER,10000)
 SYS.CHAR                     | m                                   | m                                   | (SYS.CHAR,m)                      | 
(3 rows)