CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle AnyData Data Type
Version 11.1
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsany.sql
First Available 9i
Dependencies DBMS_ANYDATA_LIB
 
ACCESSBDOUBLE
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessBDouble(self IN AnyData) RETURN BINARY_DOUBLE
TBD
 
ACCESSBFILE
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessBFile(self IN AnyData) RETURN BFILE
TBD
 
ACCESSBFLOAT
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessBFloat(self IN AnyData) RETURN BINARY_FLOAT
TBD
 
ACCESSBLOB
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessBLOB(self IN AnyData) RETURN BLOB
TBD
 
ACCESSCHAR
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessCHAR(self IN AnyData) RETURN CHAR
TBD
 
ACCESSCLOB
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessCLOB(self IN AnyData) RETURN CLOB
TBD
 
ACCESSDATE
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessDate(self IN AnyData) RETURN DATE
TBD
 
ACCESSINTERVALDS
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessIntervalDS(elf IN AnyData) RETURN DSINTERVAL_UNCONSTRAINED
TBD
 
ACCESSINTERVALYM
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessIntervalYM(elf IN AnyData) RETURN YMINTERVAL_UNCONSTRAINED
TBD
 
ACCESSNCHAR
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessNChar(self IN AnyData) RETURN NCHAR
TBD
 
ACCESSNCLOB
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessNClob(self IN AnyData) RETURN NCLOB
TBD
 
ACCESSNVARCHAR2
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessNVarchar2(self IN AnyData) RETURN NVARCHAR2
TBD
 
ACCESSNUMBER
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessNumber(self IN AnyData) RETURN NUMBER
TBD
 
ACCESSRAW
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessRAW(self IN AnyData) RETURN RAW
TBD
 
ACCESSTIMESTAMP
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessTimestamp(self IN AnyData)
RETURN TIMESTAMP_UNCONSTRAINED
TBD
 
ACCESSTIMESTAMPLTZ
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessTimestampLTZ(self IN AnyData) 
RETURN TIMESTAMP_LTZ_UNCONSTRAINED
TBD
 
ACCESSTIMESTAMPTZ
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessTimestampTZ(self IN AnyData)
RETURN TIMESTAMP_TZ_UNCONSTRAINED
TBD
 
ACCESSUROWID
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessURowid(self IN AnyData) RETURN UROWID
TBD
 
ACCESSVARCHAR
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessVarchar(self IN AnyData) RETURN VARCHAR
TBD
 
ACCESSVARCHAR2
Access functions for AnyData based on Built-ins are provided for SQL queriability. anydata.AccessVARCHAR2(self IN AnyData) RETURN VARCHAR2
TBD
 
BEGINCREATE
Begins creation process on a new AnyData anydata.BeginCreate(
dtype IN OUT NOCOPY AnyType,
adata    OUT NOCOPY AnyData)
 
 
CONVERTBDOUBLE
Convert a BINARY DOUBLE to data type AnyData anydata.ConvertBDouble(dbl IN BINARY DOUBLE) RETURN AnyData
TBD
 
CONVERTBFILE
Convert a BFILE to data type AnyData anydata.ConvertBfile(b IN BFILE) RETURN AnyData
TBD
 
CONVERTBFLOAT
Convert a BINARY FLOAT to data type AnyData anydata.ConvertBFloat(fl IN BINARY FLOAT) RETURN AnyData
TBD
 
CONVERTBLOB
Convert a BLOB to data type AnyData anydata.ConvertBlob(b IN BLOB) RETURN AnyData
TBD
 
CONVERTCHAR
Convert a CHAR to data type AnyData anydata.ConvertChar(c IN CHAR) RETURN AnyData
TBD
CONVERTCLOB
Convert a CLOB to data type AnyData anydata.ConvertClob(c IN CLOB) RETURN AnyData
TBD
 
CONVERTCOLLECTION
Convert a COLLECTION to data type AnyData anydata.ConvertCollection(col IN "<COLLECTION_1>") RETURN AnyData
TBD
 
CONVERTDATE
Convert a DATE to data type AnyData anydata.ConvertDate(dat IN DATE) RETURN AnyData
See demos below
 
CONVERTINTERVALDS
Convert an INTERVAL DAY TO  SECOND to data type AnyData anydata.ConvertIntervalDS(inv IN DSINTERVAL_UNCONSTRAINED)
RETURN AnyData
TBD
 
CONVERTINTERVALYM
Convert an INTERVAL YEAR TO MONTH to data type AnyData anydata.ConvertIntervalDS(inv IN YMINTERVAL_UNCONSTRAINED)
RETURN AnyData
TBD
 
CONVERTNCHAR
Convert a NCHAR to data type AnyData anydata.ConvertNChar(nc IN NUMBER) RETURN AnyData
TBD
 
CONVERTNCLOB
Convert a NCLOB to data type AnyData anydata.ConvertNClob(nc IN CLOB) RETURN AnyData
TBD
 
CONVERTNUMBER
Convert a NUMBER to data type AnyData anydata.ConvertNumber(num IN NUMBER) RETURN AnyData
See demos below
 
CONVERTNVARCHAR2
Convert a NVARCHAR2 to data type AnyData anydata.ConvertNVarchar2(nc IN VARCHAR) RETURN AnyData
TBD
 
CONVERTOBJECT
Convert an OBJECT to data type AnyData

Overload 1
anydata.ConvertObject(obj IN "<ADT_1>") RETURN AnyData
TBD
Convert a OBJECT to data type AnyData

Overload 2
anydata.ConvertObject(obj IN "<OPAQUE_1>") RETURN AnyData
TBD
 
CONVERTRAW
Convert a RAW to data type AnyData anydata.ConvertRaw(r IN RAW) RETURN AnyData
TBD
 
CONVERTREF
Convert a REF to data type AnyData anydata.ConvertRef(rf IN REF "<ADT_1>") RETURN AnyData
TBD
 
CONVERTTIMESTAMP
Convert a TIMESTAMP to data type AnyData anydata.ConvertRef(ts IN TIMESTAMP_UNCONSTRAINED) RETURN AnyData
TBD
 
CONVERTTIMESTAMPLTZ
Convert a TIMESTAMP WITH LOCAL TIMEZONE to data type AnyData anydata.ConvertRef(ts IN TIMESTAMP_LTZ_UNCONSTRAINED) RETURN AnyData
TBD
 
CONVERTTIMESTAMPTZ
Convert a TIMESTAMP WITH TIMEZONE to data type AnyData anydata.ConvertRef(ts IN TIMESTAMP_TZ_UNCONSTRAINED) RETURN AnyData
TBD
 
CONVERTUROWID
Convert a UROWID to data type AnyData anydata.ConvertURowid(rid IN UROWID) RETURN AnyData
TBD
 
CONVERTVARCHAR
Convert a VARCHAR to data type AnyData anydata.ConvertVarchar(c IN VARCHAR) RETURN AnyData
TBD
 
CONVERTVARCHAR2
Convert a VARCHAR2 to data type AnyData anydata.ConvertVarchar2(c IN VARCHAR2) RETURN AnyData
See demos below
 
ENDCREATE
Begins creation process on a new ANYDATA anydata.EndCreate(self IN OUT NOCOPY ANYDATA);
TBD
 
GETBDOUBLE
Begins creation process on a new ANYDATA anydata.GetBDouble(self IN AnyData, dbl OUT NOCOPY BINARY_DOUBLE)
RETURN PLS_INTEGER
TBD
 
GETBFILE
Begins creation process on a new ANYDATA anydata.GetBfile(self IN AnyData, b OUT NOCOPY BFILE) RETURN PLS_INTEGER
TBD
 
GETBFLOAT
Begins creation process on a new ANYDATA anydata.GetBFloat(self IN AnyData, fl OUT NOCOPY BINARY_FLOAT)
RETURN PLS_INTEGER
TBD
 
GETBLOB
Begins creation process on a new ANYDATA anydata.GetBlob(self IN AnyData, b OUT NOCOPY BLOB) RETURN PLS_INTEGER
TBD
 
GETCHAR
Begins creation process on a new ANYDATA anydata.GetChar(self IN AnyData, c OUT NOCOPY CHAR) RETURN PLS_INTEGER
TBD
 
GETCLOB
Begins creation process on a new ANYDATA anydata.GetClob(self IN AnyData, c OUT NOCOPY CLOB) RETURN PLS_INTEGER
TBD
 
GETCOLLECTION
Begins creation process on a new ANYDATA anydata.GetCollection(self IN AnyData, col OUT NOCOPY "<COLLECTION_1>")
RETURN PLS_INTEGER
TBD
 
GETDATE
Begins creation process on a new ANYDATA anydata.GetDate(self IN AnyData, dat OUT NOCOPY DATE) RETURN PLS_INTEGER
TBD
 
GETINTERVALDS
Begins creation process on a new ANYDATA anydata.GetIntervalDS(
self IN     AnyData,
inv  IN OUT NOCOPY DSINTERVAL_UNCONSTRAINED)
RETURN PLS_INTEGER
TBD
 
GETINTERVALYM
Begins creation process on a new ANYDATA anydata.GetIntervalYM(
self IN     AnyData,
inv  IN OUT NOCOPY YMINTERVAL_UNCONSTRAINED)
RETURN PLS_INTEGER
TBD
 
GETNCHAR
Begins creation process on a new ANYDATA anydata.GetNChar(self IN AnyData, nc OUT NOCOPY NCHAR)
RETURN PLS_INTEGER
TBD
 
GETNCLOB
Begins creation process on a new ANYDATA anydata.GetNClob(self IN AnyData, nc OUT NOCOPY NCLOB)
RETURN PLS_INTEGER
TBD
 
GETNUMBER
Begins creation process on a new ANYDATA anydata.GetNumber(self IN AnyData, num OUT NOCOPY NUMBER)
RETURN PLS_INTEGER
TBD
GETNVARCHAR2
Begins creation process on a new ANYDATA anydata.GetNVarchar2(self IN AnyData, nc OUT NOCOPY NVARCHAR2)
RETURN PLS_INTEGER
TBD
 
GETOBJECT
Begins creation process on a new ANYDATA
Overload 1
anydata.GetObject(self IN AnyData, obj OUT NOCOPY "<ADT_1>")
RETURN PLS_INTEGER
TBD
Overload 2 anydata.GetObject(self IN AnyData, obj OUT NOCOPY "<OPAQUE_1>")
RETURN PLS_INTEGER
TBD
 
GETRAW
Begins creation process on a new ANYDATA anydata.GetRaw(self IN AnyData, r OUT NOCOPY RAW) RETURN PLS_INTEGER
TBD
 
GETREF
Begins creation process on a new ANYDATA anydata.GetRef(self IN AnyData, rf OUT NOCOPY REF "<ADT_1>")
RETURN PLS_INTEGER
TBD
 
GETTIMESTAMP
Begins creation process on a new ANYDATA anydata.GetTimestamp(
self IN  AnyData,
ts   OUT NOCOPY TIMESTAMP_UNCONSTRAINED)
RETURN PLS_INTEGER
TBD
 
GETTIMESTAMPLTZ
Begins creation process on a new ANYDATA anydata.GetTimestampLTZ(
self IN  AnyData,
ts   OUT NOCOPY TIMESTAMP_LTZ_UNCONSTRAINED)
RETURN PLS_INTEGER
TBD
 
GETTIMESTAMPTZ
Begins creation process on a new ANYDATA anydata.GetTimestampTZ(
self IN  AnyData, 
ts   OUT NOCOPY TIMESTAMP_TZ_UNCONSTRAINED)
RETURN PLS_INTEGER
TBD
 
GETTYPE
Gets the Type of the ANYDATA anydata.GetType(self IN AnyData, typ OUT NOCOPY AnyType)
RETURN PLS_INTEGER
TBD
 
GETTYPENAME
Get the fully qualified type name for the ANYDATA anydata.GetTypeName(self IN AnyData) RETURN VARCHAR2
See demos below
 
GETVARCHAR
Begins creation process on a new ANYDATA anydata.GetVarchar(self IN AnyData, c OUT NOCOPY VARCHAR)
RETURN PLS_INTEGER
TBD
 
GETVARCHAR2
Begins creation process on a new ANYDATA anydata.GetVarchar2(self IN AnyData, c OUT NOCOPY VARCHAR)
RETURN PLS_INTEGER
TBD
 
PIECEWISE
Sets the MODE of access of the current data value to be an attribute at a time (if the data value is of TYPECODE_OBJECT) anydata.PieceWise(self IN OUT NOCOPY AnyData)
TBD
 
SETBDOUBLE
Sets the current data value of type BINARY DOUBLE anydata.SetBDouble(
self      IN OUT NOCOPY AnyData, 
dbl       IN     BINARY_DOUBLE,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETBFILE
Sets the current data value of type BFILE anydata.SetBFile(
self      IN OUT NOCOPY AnyData,
b         IN     BFILE,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETBFLOAT
Sets the current data value of type BINARY FLOAT anydata.SetBFloat(
self      IN OUT NOCOPY AnyData,
fl        IN     BINARY_FLOAT,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETBLOB
Sets the current data value of type BLOB anydata.SetBLOB(
self      IN OUT NOCOPY AnyData,
b         IN     BLOB,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETCHAR
Sets the current data value of type CHAR anydata.SetCHAR(
self      IN OUT NOCOPY AnyData,
c         IN     CHAR,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETCLOB
Sets the current data value of type CLOB anydata.SetCLOB(
self      IN OUT NOCOPY AnyData,
c         IN     CLOB,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETCOLLECTION
Sets the current data value of type COLLECTION

Overload 1
anydata.SetCollection(
self      IN OUT NOCOPY AnyData,
col       IN     "<COLLECTION_1>",
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETDATE
Sets the current data value of type DATE anydata.SetDate(
self      IN OUT NOCOPY AnyData,
dat       IN     DATE,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETINTERVALDS
Sets the current data value of type INTERVALDS anydata.SetIntervalDS(
self      IN OUT NOCOPY AnyData,
inv       IN     DSINTERVAL_UNCONSTRAINED,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETINTERVALYM
Sets the current data value of type INTERVALYM anydata.SetIntervalYM(
self      IN OUT NOCOPY AnyData,
inv       IN     YMINTERVAL_UNCONSTRAINED,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETNCHAR
Sets the current data value of type NCHAR anydata.SetNChar(
self      IN OUT NOCOPY AnyData,
nc        IN     NCHAR,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETNCLOB
Sets the current data value of type NCLOB anydata.SetNClob(
self      IN OUT NOCOPY AnyData,
nc        IN     NCLOB,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETNUMBER
Sets the current data value of type NUMBER anydata.SetNumber(
self      IN OUT NOCOPY AnyData,
num       IN     NUMBER,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETNVARCHAR2
Sets the current data value of type NVARCHAR2 anydata.SetNVarchar2(
self      IN OUT NOCOPY AnyData,
nc        IN NVARCHAR2,
last_elem IN BOOLEAN DEFAULT FALSE)
TBD
 
SETOBJECT
Sets the current data value of type OBJECT
Overload 1
anydata.SetObject(
self      IN OUT NOCOPY AnyData,
obj       IN     "<ADT_1>",
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
Overload 2 anydata.SetObject(
self      IN OUT NOCOPY AnyData,
obj       IN     "<OPAQUE_1>",
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETRAW
Sets the current data value of type RAW anydata.SetRaw(
self      IN OUT NOCOPY AnyData,
r         IN     RAW,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETREF
Sets the current data value of type REF anydata.SetRef(
self      IN OUT NOCOPY AnyData,
rf        IN     REF "<ADT_1>",
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETTIMESTAMP
Sets the current data value of type TIMESTAMP anydata.SetTimestamp(
self      IN OUT NOCOPY AnyData,
ts        IN     TIMESTAMP_UNCONSTRAINED,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETTIMESTAMPLTZ
Sets the current data value of type TIMESTAMPLTZ anydata.SetTimestampLTZ(
self      IN OUT NOCOPY AnyData,
ts        IN     TIMESTAMP_LTZ_UNCONSTRAINED,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETTIMESTAMPTZ
Sets the current data value of type TIMESTAMPTZ anydata.SetTimestampTZ(
self      IN OUT NOCOPY AnyData, 
ts        IN     TIMESTAMP_TZ_UNCONSTRAINED,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETVARCHAR
Sets the current data value of type VARCHAR anydata.SetVarchar(
self      IN OUT NOCOPY AnyData,
c         IN     VARCHAR,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
SETNVARCHAR2
Sets the current data value of type VARCHAR2 anydata.SetVarchar2(
self      IN OUT NOCOPY AnyData,
c         IN     VARCHAR2,
last_elem IN     BOOLEAN DEFAULT FALSE)
TBD
 
Demo

General
conn / as sysdba

desc anyData

SELECT object_name, object_type
FROM dba_objects
WHERE object_name like '%ANY%'
AND owner = 'SYS';

Create Demo Table
conn uwclass/uwclass

CREATE TABLE t (mycol sys.anyData);

desc t

set describe depth all

cl scr

desc t

Load Table with Data
INSERT INTO t
VALUES (sys.anyData.convertNumber(5));

INSERT INTO t
VALUES (sys.anyData.convertDate(SYSDATE));

INSERT INTO t
VALUES (sys.anyData.convertVarchar2('hello world'));

COMMIT;
SELECT From Table col typename format a20

SELECT mytab.mycol.gettypeName() typeName
FROM t mytab;

Function For Retrieving From AnyData Data Type
Unfortunately, they don't have a method to display the contents of prompt ANYDATA in a query (most useful in programs that will fetch the data, prompt figure out what it is and do something with it -- eg: the application has some intelligence as to how to handle the data)

col getdata format a20

CREATE OR REPLACE FUNCTION getData(p_x IN sys.anyData)
RETURN VARCHAR2 IS
 l_num      NUMBER;
 l_date     DATE;
 l_varchar2 VARCHAR2(4000);
BEGIN
  CASE p_x.gettypeName
  WHEN 'SYS.NUMBER' THEN
    IF (p_x.getNumber(l_num) = dbms_types.success) THEN
      l_varchar2 := l_num;
    END IF;
  WHEN 'SYS.DATE' THEN
    IF (p_x.getDate(l_date) = dbms_types.success) THEN
      l_varchar2 := l_date;
    END IF;
  WHEN 'SYS.VARCHAR2' THEN
    IF (p_x.getVarchar2(l_varchar2) = dbms_types.success) THEN
      NULL;
    END IF;
  ELSE
    l_varchar2 := '** unknown **';
  END CASE;

  RETURN l_varchar2;
END getData;
/

SELECT getData(mycol) GETDATA
FROM t;
 
Related Topics
Data Types
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [323 users online]    © 2010 psoug.org