| 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; |
Free