Oracle AnyData Data Type Version 11.1-from cyber
| 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; |
