SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland

9Mar/070

using the anydata datatype in oracle


--------------------------------------------------------------------------------
-- 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
*/

Related Posts:

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.