PolarDB:ANYDATA

You can use the CONVERT * function to encapsulate various data types as ANYDATA. In a PolarDB for PostgreSQL (Compatible with Oracle) cluster, explicit instructions must be provided for the conversion. Examples:

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

1. Create a test function named getAnyData to return the value of the specified type from the input data v_data of AnyData type.

   CREATE OR REPLACE FUNCTION getAnyData(v_data IN AnyData) RETURN VARCHAR2 
   IS
   	  v_int INTEGER;
   	  v_num NUMBER;
   	  v_char CHAR;
   	  v_varchar VARCHAR;
   	  v_varchar2 VARCHAR2;
   	  v_nvarchar NVARCHAR;
   	  v_nvarchar2 NVARCHAR2;  
   	  v_date DATE;
   	  v_timestamp TIMESTAMP;
   	  v_timestamptz TIMESTAMP WITH TIME ZONE;
   	  v_intervalds INTERVAL DAY TO SECOND;
   	  v_intervalym INTERVAL YEAR TO MONTH;
   	  v_clob CLOB;
   	  v_blob BLOB;
   	  v_object AnyData;
   	  v_re_data VARCHAR2;
   BEGIN
   	  IF v_data IS NOT NULL THEN 
   		 CASE v_data.getTypeName
   		 WHEN 'SYS.INTEGER' THEN
   		  IF (v_data.getInteger(v_int) = 0) THEN
   			 v_re_data := v_int;
   		  END IF;  
   		 WHEN 'SYS.NUMBER' THEN
   		  IF (v_data.getNumber(v_num) = 0) THEN
   			 v_re_data := v_num;
   		  END IF;
   		 WHEN 'SYS.CHAR' THEN
   		  IF (v_data.getChar(v_char) = 0) THEN
   			 v_re_data := v_char;
   		  END IF;	
   		 WHEN 'SYS.VARCHAR' THEN
   		  IF (v_data.getVarchar(v_varchar) = 0) THEN
   			 v_re_data := v_varchar;
   		  END IF;	  	  
   		 WHEN 'SYS.VARCHAR2' THEN
   		  IF (v_data.getVarchar2(v_varchar2) = 0) THEN
   			 v_re_data := v_varchar2;
   		  END IF;	 
   		 WHEN 'SYS.NVARCHAR' THEN
   		  IF (v_data.getNVarchar(v_nvarchar) = 0) THEN
   			 v_re_data := v_nvarchar;
   		  END IF;		  
   		 WHEN 'SYS.NVARCHAR2' THEN
   		  IF (v_data.getNVarchar2(v_nvarchar2) = 0) THEN
   			 v_re_data := v_nvarchar2;
   		  END IF;		  
   		 WHEN 'SYS.DATE' THEN
   		  IF (v_data.getDate(v_date) = 0) THEN
   			 v_re_data := v_date;
   		  END IF;
   		 WHEN 'SYS.TIMESTAMP' THEN
   		  IF (v_data.getTimestamp(v_timestamp) = 0) THEN
   			 v_re_data := v_timestamp;
   		  END IF;
   		 WHEN 'SYS.TIMESTAMP WITH TIME ZONE' THEN
   		  IF (v_data.getTimestampTZ(v_timestamptz) = 0) THEN
   			 v_re_data := v_timestamptz;
   		  END IF;	
   		 WHEN 'SYS.INTERVAL DAY TO SECOND' THEN
   		  IF (v_data.getIntervalDS(v_intervalds) = 0) THEN
   			 v_re_data := v_intervalds;
   		  END IF;	
   		 WHEN 'SYS.INTERVAL YEAR TO MONTH' THEN
   		  IF (v_data.getIntervalYM(v_intervalym) = 0) THEN
   			 v_re_data := v_intervalym;
   		  END IF;	  
   		 WHEN 'SYS.CLOB' THEN
   		  IF (v_data.getClob(v_clob) = 0) THEN
   			 v_re_data := v_clob;
   		  END IF;
   		 WHEN 'SYS.BLOB' THEN
   		  IF (v_data.getBlob(v_blob) = 0) THEN
   			 v_re_data := v_blob;
   		  END IF;
   		 WHEN 'PUBLIC.TEST_TYPE' THEN
   		  IF (v_data.getClob(v_clob) = 0) THEN
   			 v_re_data := v_clob;
   		  END IF;
   		 ELSE
   		   v_re_data := ' ** unkonwn ** ' || v_data.getTypeName;
   		 END CASE;
   	 ELSE 
   		RETURN NULL;
   	 END IF;
      RETURN v_re_data;
   END getAnyData;

2. Execute the getAnyData function to convert the data type.

   --- Prepare data.
   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'));
   
   --- Execute the getAnyData function.
   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;

   Sample result:

     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)