I'll now show you what all this means, first with a program intended to return a string version of any variable's contents. For now, this program deals only with numbers, strings, dates, objects, and REFs, but you could extend it to almost any other datatype.
/* File on web: printany.fun */ 1 CREATE OR REPLACE FUNCTION printany (adata IN ANYDATA) 2 RETURN VARCHAR2 3 AS 4 aType ANYTYPE; 5 retval VARCHAR2(32767); 6 result_code PLS_INTEGER; 7 BEGIN 8 CASE adata.GetType(aType) 9 WHEN DBMS_TYPES.TYPECODE_NUMBER THEN 10 RETURN 'NUMBER: ' || TO_CHAR(adata.AccessNumber); 11 WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN 12 RETURN 'VARCHAR2: ' || adata.AccessVarchar2; 13 WHEN DBMS_TYPES.TYPECODE_CHAR THEN 14 RETURN 'CHAR: ' || RTRIM(adata.AccessChar); 15 WHEN DBMS_TYPES.TYPECODE_DATE THEN 16 RETURN 'DATE: ' || TO_CHAR(adata.AccessDate, 'YYYY-MM-DD hh24:mi:ss'); 17 WHEN DBMS_TYPES.TYPECODE_OBJECT THEN 18 EXECUTE IMMEDIATE 'DECLARE ' || 19 ' myobj ' || adata.GetTypeName || '; ' || 20 ' myad anydata:=:ad; ' || 21 'BEGIN ' || 22 ':res:= myad.GetObject(myobj); ' || 23 ':ret:= myobj.print(); ' || 24 'END;' 25 USING IN adata, OUT result_code, OUT retval; 26 retval:= adata.GetTypeName || ': ' || retval; 27 WHEN DBMS_TYPES.TYPECODE_REF THEN 28 EXECUTE IMMEDIATE 'DECLARE ' || 29 ' myref ' || adata.GetTypeName || '; ' || 30 ' myobj ' || SUBSTR(adata.GetTypeName, 31 INSTR(adata.GetTypeName, ' ')) || '; ' || 32 ' myad anydata:=:ad; ' || 33 'BEGIN ' || 34 ':res:= myad.GetREF(myref); ' || 35 ' UTL_REF.SELECT_OBJECT(myref, myobj);' || 36 ':ret:= myobj.print(); ' || 37 'END;' 38 USING IN adata, OUT result_code, OUT retval; 39 retval:= adata.GetTypeName || ': ' || retval; 40 ELSE 41 retval:= '<data of type ' || adata.GetTypeName ||'>'; 42 END CASE; 43 44 RETURN retval; 45 46 EXCEPTION 47 WHEN OTHERS 48 THEN 49 IF INSTR(SQLERRM, 'component ''PRINT'' must be declared') > 0 50 THEN 51 RETURN adata.GetTypeName || ': <no print() function>'; 52 ELSE 53 RETURN 'Error: ' || SQLERRM; 54 END IF; 55 END;Here are just a few highlights.
Line 1
This function receives a single argument of type ANYDATA. In order to call the function, you must convert your variable to an ANYDATA; for example:
DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertNumber(3.14159)));Explicit conversions are sort of the dark side of ANYDATA.
Line 5
In cases where I need a temporary variable to hold the result, I assume that 32K will be big enough. Remember that PL/SQL dynamically allocates memory for large VARCHAR2s, so it won't be a memory pig unless required.
Line 6
The value of result_code (see lines 25 and 38) is irrelevant for the operations in this example, but is required by the ANYDATA API.
Line 8
The ANYDATA type includes a method called GetType that returns a code corresponding to the datatype. Here is its specification:
MEMBER FUNCTION ANYDATA.GetType (OUT NOCOPY ANYTYPE) RETURN typecode_integer ;To use this method, though, you have to declare an ANYTYPE variable into which Oracle will store detailed information about the type that you've encapsulated.
Lines 9, 11, 13, 15, 17, 27
These expressions rely on the constants that Oracle provides in the built-in package DBMS_TYPES.
Lines 10, 12, 14, 16
These statements use the ANYDATA.ConvertNNN member functions introduced in Oracle9i Release 2. In Release 1, you can use the GetNNN member procedures for a similar result, although they require the use of a temporary local variable.
Lines 18-25
To get an object to print itself without doing a lot of data dictionary contortions, this little dynamic anonymous block will construct an object of the correct type and invoke its print() member method.
Lines 28-38
The point of this is to dereference the pointer and return the referenced object's content. Well, it will work if there's a print().
Lines 49-51
In the event that I'm trying to print an object with no print member method, the compiler will return an error at runtime that I can detect in this fashion. In this case the code will just punt and return a generic message.
Let's take a look at some simple invocations and what this returns:
DECLARE achar CHAR(20):= 'fixed-length string'; abook book_t:= NEW book_t(id => 12345, title => 'my book', pages => 100); sref REF serial_t; asub subject_t:= subject_t('The World', NULL);BEGIN DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertNumber(3.141592654))); DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertChar(achar))); DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(abook))); DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(asub))); SELECT TREAT(REF(c) AS REF serial_t) INTO sref FROM catalog_items c WHERE title = 'Time'; DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertRef(sref)));END;This yields:
NUMBER: 3.141592654CHAR: fixed-length stringSCOTT.BOOK_T: id=12345; title=my book; publication_date=; isbn=; pages=100SCOTT.SUBJECT_T: <no print() function>REF SCOTT.SERIAL_T: id=10004; title=Time; publication_date=; issn=0040-781X;open_or_closed=OpenAs you can see, using ANYDATA is not as convenient as true inheritance hierarchies because ANYDATA requires explicit conversions. On the other hand, it does make possible the creation of a table column or object attribute that will hold any type of data.[10]
[10] As of this writing, it is impossible to store in a table an ANYDATA encapsulating an object that has evolved or that is part of a type hierarchy.
