——————————————————————————–
— sys.anydata.sql
——————————————————————————–
— Using the ANYDATA object
— Startin with Oracl9i and up, ANYDATA is described
— by Oracle as a “self describing data instance type”.
— So this means it doesnt matter what kind of datatype you use
— to insert into this kind of table.
——————————————————————————–
/* Create a anydata table on a Oracle database (9i and higher) */
create table tabelle( kolonne sys.anydata );
/* Insert any data in this anydata table. List of Convert Functions see at the bottom */
insert into tabelle values(sys.anydata.convertvarchar2(‘www.osfinance.net’));
insert into tabelle values(sys.anydata.convertdate(sysdate));
insert into tabelle values(sys.anydata.convertnumber(111222333));
/* Selecting what kind of types are available on the anydata table */
select t.kolonne.getTypeName() from tabelle t;
/* Selecting the values from the anydata table */
declare
n number;
v varchar2(60);
d date;
begin
for r in (select t.kolonne from tabelle t)
loop
case r.kolonne.getTypeName
when ‘SYS.NUMBER’
then
if r.kolonne.getNumber(n) = dbms_types.success
then
dbms_output.put_line(‘a number: ‘ ||n);
end if;
when ‘SYS.VARCHAR2’
then
if r.kolonne.getVarchar2(v)= dbms_types.success
then
dbms_output.put_line(‘a varchar2: ‘ ||v);
end if;
when ‘SYS.DATE’
then
if r.kolonne.getDate(d)= dbms_types.success
then
dbms_output.put_line(‘a date: ‘ ||d);
end if;
end case;
end loop;
end;
/* List of Convert Functions
* ConvertNumber(num IN NUMBER) RETURN AnyData
* ConvertDate(dat IN DATE) RETURN AnyData
* ConvertChar(c IN CHAR) RETURN AnyData
* ConvertVarchar(c IN VARCHAR) RETURN AnyData
* ConvertVarchar2(c IN VARCHAR2) RETURN AnyData
* ConvertRaw(r IN RAW) RETURN AnyData
* ConvertBlob(b IN BLOB) RETURN AnyData
* ConvertClob(c IN CLOB) RETURN AnyData
* ConvertBfile(b IN BFILE) RETURN AnyData
* ConvertObject(obj IN “(object_type)”) RETURN AnyData
* ConvertRef(rf IN REF “(object_type)”) RETURN AnyData
* ConvertCollection(col IN “(COLLECTION_1)”) RETURN AnyData
*/
/* List of Get Methods
* GetNumber(self IN AnyData, num OUT NOCOPY NUMBER) RETURN PLS_INTEGER
* GetDate(self IN AnyData, dat OUT NOCOPY DATE) RETURN PLS_INTEGER
* GetChar(self IN AnyData, c OUT NOCOPY CHAR) RETURN PLS_INTEGER
* GetVarchar(self IN AnyData, c OUT NOCOPY VARCHAR) RETURN PLS_INTEGER
* GetVarchar2(self IN AnyData, c OUT NOCOPY VARCHAR2) RETURN PLS_INTEGER
* GetRaw(self IN AnyData, r OUT NOCOPY RAW) RETURN PLS_INTEGER
* GetBlob(self IN AnyData, b OUT NOCOPY BLOB) RETURN PLS_INTEGER
* GetClob(self IN AnyData, c OUT NOCOPY CLOB) RETURN PLS_INTEGER
* GetBfile(self IN AnyData, b OUT NOCOPY BFILE) RETURN PLS_INTEGER
* GetObject(self IN AnyData, obj OUT NOCOPY “(object_type)”) RETURN PLS_INTEGER
* GetRef(self IN AnyData, rf OUT NOCOPY REF “(object_type)”) RETURN PLS_INTEGER
* GetCollection(self IN AnyData, col OUT NOCOPY “(collection_type)”) RETURN PLS_INTEGER
*/