Oracle AnyDataSet Data Type
Version 12.1.0.1

General Information
Library Note [an error occurred while processing this directive]
Purpose Note: The following demo was published in the OTN Forums by ascheffer on 9 May 2006. I am replicating it here because it is, to quote Billy Verreynne: "... excellent!" The only change to the original code I've made is formatting and addressing the PL/SQL warnings it generates by use of AUTHID and NOCOPY.
AUTHID DEFINER
Dependencies
ANYTYPE OLAPRANCURIMPL_T POINTINPOLYGON_IMP_T
CUBE_TABLE OLAPRC_TABLE SDO_POINTINPOLYGON
DBMS_ANYDATASET_LIB OLAP_TABLE SYS_NT_COLLECT
DBMS_FREQUENT_ITEMSET   SYS_NT_COLLECT_IMP
Documented Yes
First Available 9i
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsany.sql
Subprograms
ADDINSTANCE GETOBJECT (2) SETCOLLECTION
BEGINCREATE GETRAW SETDATE
ENDCREATE GETREF SETINTERVALDS
GETBDOUBLE GETTIMESTAMP SETINTERVALYM
GETBFILE GETTIMESTAMPLTZ SETNCHAR
GETBFLOAT GETTIMESTAMPTZ SETNCLOB
GETBLOB GETTYPE SETNUMBER
GETCHAR GETTYPENAME SETNVARCHAR2
GETCLOB GETUROWID SETOBJECT (2)
GETCOLLECTION GETVARCHAR SETRAW
GETCOUNT GETVARCHAR2 SETREF
GETDATE PIECEWISE SETTIMESTAMP
GETINSTANCE SETBDOUBLE SETTIMESTAMPLTZ
GETINTERVALDS SETBFILE SETTIMESTAMPTZ
GETINTERVALYM SETBFLOAT SETUROWID
GETNCHAR SETBLOB SETVARCHAR
GETNCLOB SETCHAR SETVARCHAR2
GETNUMBER SETCLOB Demo
GETNVARCHAR2    
 
Demo
Create User Defined Data Type Header CREATE OR REPLACE TYPE NColPipe AUTHID DEFINER AS OBJECT (
 l_parm VARCHAR2(10),    -- the parameter given to the table function
 rows_requested NUMBER,  -- the parameter given to the table function
 ret_type       ANYTYPE, -- the return type of the table function
 rows_returned  NUMBER,  -- the number of rows currently returned by the table function

 STATIC FUNCTION ODCITableDescribe(rtype      OUT NOCOPY ANYTYPE,
                                   p_parm     IN         VARCHAR2,
                                   p_rows_req IN         NUMBER := 2)
 RETURN NUMBER,

 STATIC FUNCTION ODCITablePrepare(sctx       OUT NOCOPY NColPipe,
                                  ti         IN         sys.ODCITabFuncInfo,
                                  p_parm     IN         VARCHAR2,
                                  p_rows_req IN         NUMBER := 2 )
 RETURN NUMBER,

 STATIC FUNCTION ODCITableStart(sctx       IN OUT NOCOPY NColPipe,
                                p_parm     IN            VARCHAR2,
                                p_rows_req IN            NUMBER := 2 )
 RETURN NUMBER,

 MEMBER FUNCTION ODCITableFetch(self  IN OUT NOCOPY NColPipe,
                                nrows IN            NUMBER,
                                outset   OUT NOCOPY ANYDATASET)
 RETURN NUMBER,

 MEMBER FUNCTION ODCITableClose(self IN NColPipe) RETURN NUMBER,

 STATIC FUNCTION show(p_parm     IN VARCHAR2,
                      p_rows_req IN NUMBER := 2)
 RETURN ANYDATASET PIPELINED USING NColPipe);
/
User Defined Data Type Body CREATE OR REPLACE TYPE BODY NColPipe AS
---------------------------------------
 STATIC FUNCTION ODCITableDescribe(rtype      OUT NOCOPY ANYTYPE,
                                   p_parm     IN         VARCHAR2,
                                   p_rows_req IN         NUMBER := 2 ) RETURN NUMBER IS
  atyp anytype;
 BEGIN
   anytype.begincreate(dbms_types.typecode_object, atyp);
   IF p_parm = 'one' THEN
     atyp.addattr('one', dbms_types.typecode_varchar2, NULL, NULL, 10, NULL, NULL);
   ELSIF p_parm = 'two' THEN
     atyp.addattr('one', dbms_types.typecode_varchar2, NULL, NULL, 10, NULL, NULL);
     atyp.addattr('two', dbms_types.typecode_varchar2, NULL, NULL, 10, NULL, NULL);
   ELSE
     atyp.addattr(p_parm || '1', dbms_types.typecode_varchar2, NULL, NULL, 10, NULL, NULL);
     atyp.addattr(p_parm || '2', dbms_types.typecode_varchar2, NULL, NULL, 10, NULL, NULL);
     atyp.addattr(p_parm || '3', dbms_types.typecode_number, 10, 0, NULL, NULL, NULL);
   END IF;
   atyp.endcreate;
   anytype.begincreate(dbms_types.typecode_table, rtype);
   rtype.SetInfo(NULL, NULL, NULL, NULL, NULL, atyp, dbms_types.typecode_object, 0);
   rtype.endcreate();
   RETURN odciconst.success;
 EXCEPTION
   WHEN OTHERS THEN
     RETURN odciconst.error;
 END;
 ---------------------------------------
 STATIC FUNCTION ODCITablePrepare(sctx       OUT NOCOPY NColPipe,
                                  ti         IN         sys.ODCITabFuncInfo,
                                  p_parm     IN         VARCHAR2,
                                  p_rows_req IN         NUMBER := 2) RETURN NUMBER IS
  elem_typ sys.anytype;
  prec     PLS_INTEGER;
  scale    PLS_INTEGER;
  len      PLS_INTEGER;
  csid     PLS_INTEGER;
  csfrm    PLS_INTEGER;
  tc       PLS_INTEGER;
  aname    VARCHAR2(30);
 BEGIN
  tc := ti.RetType.GetAttrElemInfo(1, prec, scale, len, csid, csfrm, elem_typ, aname);
  sctx := NColPipe(p_parm, p_rows_req, elem_typ, 0);
  RETURN odciconst.success;
 END;
 ---------------------------------------
 STATIC FUNCTION ODCITableStart(sctx       IN OUT NOCOPY NColPipe,
                                p_parm     IN            VARCHAR2,
                                p_rows_req IN            NUMBER := 2) RETURN NUMBER IS
 BEGIN
   RETURN odciconst.success;
 END;
 ---------------------------------------
 MEMBER FUNCTION ODCITableFetch(self   IN OUT NOCOPY NColPipe,
                                nrows  IN            NUMBER,
                                outset    OUT NOCOPY ANYDATASET) RETURN NUMBER IS
 BEGIN
   anydataset.begincreate( dbms_types.typecode_object, self.ret_type, o utset );
   FOR i IN self.rows_returned + 1 .. self.rows_requested LOOP
     outset.addinstance;
     outset.piecewise();

     IF self.l_parm = 'one' THEN
       outset.setvarchar2(TO_CHAR(i));
     ELSIF self.l_parm = 'two' THEN
       outset.setvarchar2(TO_CHAR(i));
       outset.setvarchar2( 'row: ' || TO_CHAR(i));
     ELSE
       outset.setvarchar2( 'row: ' || TO_CHAR(i));
       outset.setvarchar2( 'row: ' || TO_CHAR(i));
       outset.setnumber( i );
     END IF;

     self.rows_returned := self.rows_returned + 1;
   END LOOP;
   outset.endcreate;
   RETURN odciconst.success;
 END;
 -------------------------------
 MEMBER FUNCTION ODCITableClose(self IN NColPipe) RETURN NUMBER IS
 BEGIN
   RETURN odciconst.success;
 END;
END;
/
Type Tests SELECT * FROM TABLE( NColPipe.show('test', 3));

SELECT * FROM TABLE( NColPipe.show('two', 5));

SELECT * FROM TABLE( NColPipe.show('one'));

Related Topics
AnyData
Collections
Data Types
DBMS_TYPES
Types

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved