PolarDB:ANYDATA

Convert\* functions

Each Convert* function is a static function that wraps a typed value into an AnyData instance.

ConvertDate accepts a TIMESTAMP value, not a DATE value.

Signatures:

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

Access* functions are available in SQL. They do not throw exceptions on a type mismatch — if the stored type does not match the requested access type, NULL is returned instead.

To restrict a query to rows where Access* returns a meaningful value, add a WHERE clause that filters on GetTypeName, for example WHERE SYS.AnyData.GetTypeName(c1) = 'SYS.NUMBER'.

GetTypeName returns a VARCHAR2 value with the following format:

Each Access* function returns the stored value wrapped in a SYS.ANYDATA wrapper.

Signatures:

/* 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

Get\* functions

Get* functions are designed for PL/SQL use. Each function writes the retrieved value to an OUT NOCOPY parameter and returns a PLS_INTEGER status code. A return value of 0 indicates success.

Signatures:

/* Get* functions write the current data value to an OUT NOCOPY parameter */
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

Example 1: Extract a typed value from AnyData in PL/SQL

This example creates a getAnyData function that inspects the type stored in an AnyData value using getTypeName, then calls the appropriate Get* function to extract the value as VARCHAR2.

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;

Example 2: Use AnyData in a SQL query

This example inserts values of different types into an AnyData column, then queries them using AccessNumber and AccessChar (for SQL access) and the getAnyData PL/SQL function defined above.

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

-- Query with type-aware access
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)