Oracle ANYDATA.ACCESSXXXX Built-ins

In my previous article ANYDATA, we have seen how to store a value in a table column or PL/SQL variable of type ANYDATA. Now we will move further to know, how to access the stored values.

Similar to ANYDATA.ConvertXXXX built-ins, Oracle has provided ANYDATA.AccessXXXX built-ins to read the values of ANYDATA column or Variable.

The below example will show how to use AccessXXXX built-in with table column and PL/SQL variable.
Ex:
SELECT ANYDATA.AccessDate(id) from tab_anydata.
PL/SQL Block:
DECLARE
v_any ANYDATA;
BEGIN
v_any:= ANYDATA.convertNumber(1001);
DBMS_OUTPUT.PUT_LINE('Value in V_ANY variable is : ' v_any.AccessNumber());
v_any:=ANYDATA.convertDate('01-Jul-09');
DBMS_OUTPUT.PUT_LINE('Value in V_ANY variable is : ' v_any.AccessDate());
END;

Below are the list of Access built-ins, that are available with Oracle 9i.
MEMBER FUNCTION AccessBDouble(self IN ANYDATA) return BINARY_DOUBLE
MEMBER FUNCTION AccessBfile(self IN ANYDATA) return BFILE
MEMBER FUNCTION AccessBFloat(self IN ANYDATA) return BINARY_FLOAT
MEMBER FUNCTION AccessBlob(self IN ANYDATA) return BLOB
MEMBER FUNCTION AccessChar(self IN ANYDATA) return CHAR
MEMBER FUNCTION AccessClob(self IN ANYDATA) return CLOB
MEMBER FUNCTION AccessDate(self IN ANYDATA) return DATE
MEMBER FUNCTION AccessIntervalYM(self IN ANYDATA) return INTERVAL YEAR TO MONTH
MEMBER FUNCTION AccessIntervalDS(self IN ANYDATA) return INTERVAL DAY TO SECOND
MEMBER FUNCTION AccessNchar(self IN ANYDATA) return NCHAR
MEMBER FUNCTION AccessNClob(self IN ANYDATA) return NCLOB
MEMBER FUNCTION AccessNumber(self IN ANYDATA) return NUMBER
MEMBER FUNCTION AccessNVarchar2(self IN ANYDATA) return NVARCHAR2
MEMBER FUNCTION AccessRaw(self IN ANYDATA) return RAW
MEMBER FUNCTION AccessTimestamp(self IN ANYDATA) return TIMESTAMP
MEMBER FUNCTION AccessTimestampLTZ(self IN ANYDATA) return TIMESTAMP WITH LOCAL
MEMBER FUNCTION AccessTimestampTZ(self IN ANYDATA) return TIMESTAMP WITH
MEMBER FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC
MEMBER FUNCTION AccessVarchar(self IN ANYDATA) return VARCHAR
MEMBER FUNCTION AccessVarchar2(self IN ANYDATA) return VARCHAR2
Note: We don't have AccessXXXX built-in to read the values of Object, Collection and REF type.

I will explain you on how to read the Object, Collection and REF values stored in ANYDATA variable, in my next article.

ANYDATA.ConvertXXXX Built-ins - 9i New Features

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

ANYDATA - Oracle's Road To Generic Data Types

ANYDATA is a new data type introduced with Oracle 9i. As the name implies, it allows the user to store values of any data type (Oracle Defined/User defined) in it.

ANYDATA data type, could be used for defining a column in Database Table or to declare a variable in PL/SQL block.

EX:

CREATE TABLE tab_anydata (id ANYDATA);

PL/SQL Block:

DECLARE

v_any ANYDATA;

BEGIN

NULL;

END;

Built-ins to work with ANYDATA.

• convertXXXX
• accessXXXX
• getXXXX
• getTypeName
• getType

You will come to learn about the above specified built-ins in my succeeding articles on ANYDATA data type.

Key: ORACLE, ANYDATA, 9i New Features, Generic Data Types