ANYDATA.AccessXXXX

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