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.
