Chapter 2. ANYDATA

Table of Contents

2.1. Overview
2.2. Methods
2.2.1. CONVERT
2.2.2. GET
2.2.3. ACCESS
2.2.4. GETTYPENAME

This chapter describes the basic concepts and methods of ANYDATA type.

2.1. Overview

ANYDATA can hold any data type.

2.2. Methods

2.2.1. CONVERT

Creates an ANYDATA instance with a given data type.

The following are the specifications of CONVERT method series.

  • Prototypes

    STATIC FUNCTION ConvertNumber(num IN NUMBER) return AnyData;
    STATIC FUNCTION ConvertDate(dat IN DATE) 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 ConvertRaw(r IN RAW) return AnyData;
    STATIC FUNCTION ConvertBlob(b IN BLOB) return AnyData;
    STATIC FUNCTION ConvertClob(c IN CLOB) return AnyData;
    STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP_UNCONSTRAINED) return AnyData;
    STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP_TZ_UNCONSTRAINED) 
    return AnyData;
    STATIC FUNCTION ConvertTimestampLTZ(ts IN TIMESTAMP_LTZ_UNCONSTRAINED) 
    return AnyData;
    STATIC FUNCTION ConvertIntervalYM(inv IN YMINTERVAL_UNCONSTRAINED) 
    return AnyData;
    STATIC FUNCTION ConvertIntervalDS(inv IN DSINTERVAL_UNCONSTRAINED) 
    return AnyData;
    STATIC FUNCTION ConvertNchar(nc IN NCHAR) return AnyData;
    STATIC FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return AnyData;
    STATIC FUNCTION ConvertNClob(nc IN NCLOB) return AnyData;
    STATIC FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return AnyData;
    STATIC FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return AnyData;
    STATIC FUNCTION ConvertObject(obj IN "<object>") return AnyData;
    STATIC FUNCTION ConvertCollection(col IN "<collection>") return AnyData;    
  • Parameters

    ParameterDescription
    num, da, c, r, b, ts, inv, nc, fl, dbl, obj, col

    Type to convert to ANYDATA.

    • obj: a user-defined object type.

    • col: a global collection type created through ddl.

  • Example

    declare
        var sys.anydata;
        nvar number := 10;
    begin
        var := sys.anydata.convertNumber(nvar);
    end;
    /
    
    
    create or replace type SIMPLE_OBJ is object ( c1 number, c2 number);
    /
    
    declare
         v1 SIMPLE_OBJ;
         result sys.anydata;
    begin
        v1 := SIMPLE_OBJ (10, 20);
    
         result := sys.anydata.convertobject(v1);
    end;
    /
    
    create or replace type SIMPLE_COLL  as table of varchar(128);
    /
    
    declare
       any1 sys.anydata;
       any2 sys.anydata;
    
       type LOCAL_COLL is table of varchar(128);
       var1 SIMPLE_COLL ;
       var2 LOCAL_COLL;
    
       res1 SIMPLE_COLL ;
       status pls_integer;
    begin
       var1 := SIMPLE_COLL ('a', 'b');
       any1 := sys.anydata.convertCollection(var1);
    
        begin
            var2 := LOCAL_COLL('a', 'b');
            any2 := sys.anydata.convertCollection(var2);
        exception when dbms_types.incorrect_usage then
            dbms_output.put_line('Anydata does not support local collection type');
        end;
    
    end;
    /

2.2.2. GET

Converts an ANYDATA type instance into a particular type. A return value of DBMS_TYPES.SUCCESS indicates a successful execution.

The following are the specifications of GET method series.

  • Prototype

    MEMBER FUNCTION GetNumber(self IN AnyData, num OUT NOCOPY NUMBER) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetDate(self IN AnyData, dat OUT NOCOPY DATE) 
    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 GetRaw(self IN AnyData, r OUT NOCOPY RAW) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetBlob(self IN AnyData, b OUT NOCOPY BLOB) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetClob(self IN AnyData, c OUT NOCOPY CLOB) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetTimestamp(self IN AnyData, 
                                 ts OUT NOCOPY TIMESTAMP_UNCONSTRAINED) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetTimestampTZ(self IN AnyData, 
                                   ts OUT NOCOPY TIMESTAMP_TZ_UNCONSTRAINED) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetTimestampLTZ(self IN AnyData, 
                                    ts OUT NOCOPY TIMESTAMP_LTZ_UNCONSTRAINED) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetIntervalYM(self IN AnyData, 
                                  inv IN OUT NOCOPY YMINTERVAL_UNCONSTRAINED) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetIntervalDS(self IN AnyData, 
                                  inv IN OUT NOCOPY DSINTERVAL_UNCONSTRAINED) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetNchar(self IN AnyData, nc OUT NOCOPY NCHAR) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetNVarchar2(self IN AnyData, nc OUT NOCOPY NVARCHAR2) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetNClob(self IN AnyData, nc OUT NOCOPY NCLOB) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetBFloat(self IN AnyData, fl OUT NOCOPY BINARY_FLOAT) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetBDouble(self IN AnyData, dbl OUT NOCOPY BINARY_DOUBLE) 
    return PLS_INTEGER;
    MEMBER FUNCTION GetCollection(self IN AnyData, obj OUT NOCOPY "<collection>") 
    return PLS_INTEGER;
    MEMBER FUNCTION GetObject(self IN AnyData, obj OUT NOCOPY "<object>") 
    return PLS_INTEGER;
  • Parameters

    ParameterDescription
    selfANYDATA type instance. Read-only.
    num, da, c, r, b, ts, inv, nc, fl, dbl, obj, colVariables to retrieve from the ANYDATA type.
  • Exception

    ExceptionDescription
    DBMS_TYPES.TYPE_MISMATCHOccurs when a type-mismatch occurs during a GET* call.
  • Example

    declare
        var sys.anydata;
        nvar number := 10;
        rvar number;
        status pls_integer;
    begin
        var := sys.anydata.convertNumber(nvar);
        status := var.getnumber(rvar);
        dbms_output.put_line( rvar );
    end;
    /
    
    declare
         v1 SIMPLE_OBJ;
         v2 SIMPLE_OBJ;
         result sys.anydata;
         flags pls_integer;
    begin
        v1 := SIMPLE_OBJ (10, 20);
    
         result := sys.anydata.convertobject(v1);
    
         flags := result.getobject(v2);
    
         dbms_output.put_line (v2.c1);
         dbms_output.put_line (v2.c2);
    end;
    /
    
    declare
       any1 sys.anydata;
    
       var1 SIMPLE_COLL ;
       res1 SIMPLE_COLL ;
       status pls_integer;
    begin
       var1 := SIMPLE_COLL ('a', 'b');
       any1 := sys.anydata.convertCollection(var1);
       status := any1.getcollection(res1);
       dbms_output.put_line(res1(1));
       dbms_output.put_line(res1(2));
    end;
    /

2.2.3. ACCESS

Converts an ANYDATA type instance into a particular type. These functions do not throw exceptions. Unlike the Get series, they return a NULL when a type-mismatch occurs.

The following are the specifications of Access method series.

  • Prototype

    MEMBER FUNCTION AccessNumber(self IN AnyData) return NUMBER DETERMINISTIC;
    MEMBER FUNCTION AccessDate(self IN AnyData) return DATE DETERMINISTIC;
    MEMBER FUNCTION AccessChar(self IN AnyData) return CHAR DETERMINISTIC;
    MEMBER FUNCTION AccessVarchar(self IN AnyData) return VARCHAR DETERMINISTIC;
    MEMBER FUNCTION AccessVarchar2(self IN AnyData) return VARCHAR2 DETERMINISTIC;
    MEMBER FUNCTION AccessRaw(self IN AnyData) return RAW DETERMINISTIC;
    MEMBER FUNCTION AccessBlob(self IN AnyData) return BLOB DETERMINISTIC;
    MEMBER FUNCTION AccessClob(self IN AnyData) return CLOB DETERMINISTIC;
    MEMBER FUNCTION AccessTimestamp(self IN AnyData)
    return TIMESTAMP_UNCONSTRAINED DETERMINISTIC;
    MEMBER FUNCTION AccessTimestampTZ(self IN AnyData)
    return TIMESTAMP_TZ_UNCONSTRAINED DETERMINISTIC;
    MEMBER FUNCTION AccessTimestampLTZ(self IN AnyData)
    return TIMESTAMP_LTZ_UNCONSTRAINED DETERMINISTIC;
    MEMBER FUNCTION AccessIntervalYM(self IN AnyData)
    return YMINTERVAL_UNCONSTRAINED DETERMINISTIC;
    MEMBER FUNCTION AccessIntervalDS(self IN AnyData) 
    return DSINTERVAL_UNCONSTRAINED DETERMINISTIC;
    MEMBER FUNCTION AccessNchar(self IN AnyData) return NCHAR DETERMINISTIC;
    MEMBER FUNCTION AccessNVarchar2(self IN AnyData) return NVARCHAR2 DETERMINISTIC;
    MEMBER FUNCTION AccessBFloat(self IN AnyData) return BINARY_FLOAT DETERMINISTIC;
    MEMBER FUNCTION AccessBDouble(self IN AnyData) return BINARY_DOUBLE DETERMINISTIC;
    
  • Parameter

    ParameterDescription
    selfANYDATA type instance. Read-only.
  • Example

    declare
        var sys.anydata;
        nvar number := 10;
        rvar number;
        status pls_integer;
    begin
        var := sys.anydata.convertNumber(nvar);
        dbms_output.put_line( var.accessnumber );
        dbms_output.put_line( var.accessvarchar ); -- null 출력
    
    end;
    /

2.2.4. GETTYPENAME

Returns the type name of ANYDATA.

The following are the specifications of GETTYPENAME method.

  • Prototype

        MEMBER FUNCTION GetTypeName(self IN AnyData) return VARCHAR;
  • Parameter

    ParameterDescription
    selfANYDATA type instance. Read-only.
  • Example

     create or replace type SIMPLE_COLL  as table of varchar(128);
    /
    
    create or replace type SIMPLE_OBJ is object ( c1 number, c2 number);
    /
    
    declare
        var1 sys.anydata;
        var2 sys.anydata;
        var3 sys.anydata;
        num number := 10;
        obj SIMPLE_OBJ;
        coll SIMPLE_COLL;
    begin
        obj := SIMPLE_OBJ (10, 20);
        coll := SIMPLE_COLL('a', 'b');
        var1 := sys.anydata.convertNumber(num);
        var2 := sys.anydata.convertobject(obj);
        var3 := sys.anydata.convertCollection(coll);
        dbms_output.put_line(var1.gettypename );
        dbms_output.put_line(var2.gettypename );
        dbms_output.put_line(var3.gettypename );
    end;
    /