Whenever we need to pass the value for a database table column of type ANYDATA or to store a variable in PL/SQL block of type ANYDATA, we need to use the ANYDATA.CONVERTXXXX built-in, where XXXX will be the data type on which the value will be stored in ANYDATA column/variable.
Ex:
INSERT INTO tab_anydataVALUES(ANYDATA.convertNumber(1001));
INSERT INTO tab_anydata VALUES(ANYDATA.convertDate('01-Jul-09'));
PL/SQL :
DECLARE
v_any ANYDATA;
BEGIN
v_any:= ANYDATA.convertNumber(1001);
v_any:=ANYDATA.convertDate('01-Jul-09');
END;
Below are the list of convert built-ins, that are available with Oracle 9i.
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 "<object_type>") RETURN ANYDATA,
STATIC FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA,
STATIC FUNCTION ConvertRef(rf IN REF "<object_type>") 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,
Thus by using the above mentioned functions, values of different data types could be stored in column or variable of type ANYDATA .
So far we have seen, how to create a column or variable of type ANYDATA and to pass/store the values to it.
I will give the information on how to access the column or variable of type ANYDATA in my next article.
Key: ORACLE, ANYDATA, ANYDATA.ACCESS BUILT-INS, 9i New Features
