Currently, I am building up a transient type in Oracle (11.2.0.2 and 11.2.0.3) PL/SQL to encapsulate access of views in a table producing function. However, I encounter something I don’t understand in the results of the ANYTYPE.GetAttrElemInfo. According to the documentation, it returns “the typecode of the attribute or collection element”. These typecodes are listed in package DBMS_TYPES. The ony numeric type defined in here is the NUMERIC type (with integer value 2), no subtypes are present here.
However, when I call the method on an object attribute that is defined as an subtype (like float or decimal), I will get a number that is not present in DBMS_TYPES. Does anyone has any pointers to documentation describing this behaviour? Is this a known issue?
I have an example script that shows / reproduces this behaviour: First a type object is defined with a number, float and decimal attribute. The anonymous PLSQL block will display the results of the anytype.GetAttrElemInfo call on the three attributes.
CREATE OR REPLACE TYPE TESTTYPE AS OBJECT
(
ID NUMBER(38),
ID2 FLOAT(126),
id3 DECIMAL
);
/
DECLARE
adata ANYDATA;
atype ANYTYPE;
tt TESTTYPE;
v_typenum PLS_INTEGER;
v_typecode PLS_INTEGER;
v_precision PLS_INTEGER;
v_scale PLS_INTEGER;
v_length PLS_INTEGER;
v_csid PLS_INTEGER;
v_csfrm PLS_INTEGER;
v_attr_elt_type ANYTYPE;
v_aname VARCHAR2(30);
BEGIN
tt := TESTTYPE( 1, 123, 10);
adata := anydata.convertObject( tt);
v_typenum := adata.gettype( atype);
FOR i IN 1 … 3
LOOP
v_typecode := atype.GetAttrElemInfo( i, v_precision, v_scale, v_LENGTH, v_csid, v_csfrm, v_attr_elt_type, v_aname);
DBMS_OUTPUT.put_line( ‘data of attribute ’ || i || ’ is:’ || v_typecode);
END LOOP;
END;
/
Hopefully anyone can help me.