Oracle Data Cartridge Interface example: Multiplication table

spec.plsql creates the tq84_odci type:

create or replace type tq84_odci
   authid definer
as object (

    rec_desc         anytype,
  
    nof_rows         integer,
    nof_cols         integer,
  
    r_current        integer,
 --
 -- multiplication_table returns a multiplication table with r+1 rows and c+1
 -- columns.
 -- In the type body, there is no method named multiplication_table, rather,
 -- behind the scenes, ODCITableDescribe and so forth are called to
 -- determine table strcture (rec_desc) and fill the returned records
 -- with values.
 --
    static function multiplication_table(
       r in number,
       c in number
    )
    return anydataset pipelined using tq84_odci,

 --
 -- Constructor. Called in odciTablePrepare.
 -- Returned object assigned to scan context (sctx)
 --
    constructor function tq84_odci(
       self in out nocopy tq84_odci,
       rowTypes in anytype,
       r        in integer,
       c        in integer) return self as result,
  
 --
 -- odciTableDescribe: determine number of columns of result
 -- set and their data types. Return this information
 -- through rec_desc.
 --
    static function odciTableDescribe(
        rec_desc       out nocopy anytype,
        ---------------------------------
        r               in        integer,
        c               in        integer
    )
    return number,
  
    static function odciTablePrepare (
         sctx           out nocopy tq84_odci,
         tab_func_info  in         sys.ODCITabFuncInfo,
         ------------------------------------
         r              in         integer,
         c              in         integer
    )
    return number,

    static function odciTableStart(
         sctx           in out nocopy tq84_odci,
         ---------------------------------------
         r              in            integer,
         c              in            integer
    )
    return number,
  
 --
 -- Fetch one (or more) records and create the associated data:
 --
    member function odciTableFetch   (
       self             in out nocopy tq84_odci,
       nofRowsExpected  in            number,
       record_out      out     nocopy anyDataSet
    )
    return number,
  
    member function odciTableClose(
       self in tq84_odci
    )
    return number

);
/

show errors

body.plsql creates the type's body:

create or replace type body tq84_odci as

    constructor function tq84_odci( -- {
       self     in out nocopy tq84_odci,
       rowTypes in anytype,
       r        in integer,
       c        in integer
    ) return self as result
    is
    begin

        rec_desc  := rowTypes;
        nof_rows  := r;
        nof_cols  := c;
        r_current := 0;

        return;

    end tq84_odci; -- }

    static function odciTableDescribe( -- {
            rec_desc     out nocopy anytype,
            r             in        integer,
            c             in        integer
    ) return number is

      colTypes anytype;
    begin
    --
    -- Dynamically create a type (colTypes) whose attributes correspond
    -- to the columns of the table that is to be returned.
    --
       anytype.beginCreate(dbms_types.typecode_object, colTypes);

    --
    -- Special treatment of first column/attribute
    --
       colTypes.addAttr( -- {
              aname     =>'X',
              typecode  => dbms_types.typecode_varchar2,
              prec      => null,
              scale     => null,
              len       =>   11,
              csid      => null,
              csfrm     => null
       ); -- }

    --
    -- Add rest of attributes
    --
       for i in 2 .. c+1 loop -- {

           colTypes.addattr(
              aname     =>'Y_' || (i-1),
              typecode  => dbms_types.typecode_varchar2,
              prec      => null,
              scale     => null,
              len       =>   11,
              csid      => null,
              csfrm     => null
           );

       end loop; -- }
    --
    -- Done adding attributes
    --
       colTypes.endcreate;

    --
    -- Now, after creating the record structure, A nested table containing
    -- the types needs to be created:
    --
       anytype.beginCreate(dbms_types.typecode_table, rec_desc);
       rec_desc.setinfo( -- {
            prec        => null,
            scale       => null,
            len         => null,
            csid        => null,
            csfrm       => null,
            atype       => colTypes,
            elem_tc     => dbms_types.typecode_table,
            elem_count  => 0
       ); -- }
       rec_desc.endcreate();

      return odciconst.success;

    exception when others then
        return odciconst.error;
    end odciTableDescribe; -- }

    static function odciTablePrepare( -- {
        sctx          out nocopy tq84_odci,
        tab_func_info in         sys.odciTabFuncInfo,
        r             in         integer,
        c             in         integer
    )
    return number
 --
 -- Create an actual instance of tq84_odci
 --
    is
     --
     -- Same type as was defined in odciTableDescribe.
     -- Will be assigned below from tab_func_info.retType
     --
        record_desc  anytype;

        prec         pls_integer ; -- only used for function call
        scale        pls_integer ; -- only used for function call
        len          pls_integer ; -- only used for function call
        csid         pls_integer ; -- only used for function call
        csfrm        pls_integer ; -- only used for function call
        aname        varchar2(30); -- only used for function call
        dummy        pls_integer ; -- only used for function call

    begin

     -- With GetAttrElemInfo, I can get the record_structure that was created in odciTableDescribe.
     -- This record_structure is returned in the out parameter record_desc.
     -- The parameters prec, scale, len, csid, csfrm and aname are ignored.
        dummy := tab_func_info.retType.getAttrElemInfo(null, prec, scale, len, csid, csfrm, record_desc, aname);

     -- Ready to construct an instance of tq84_odci.
     -- The first parameter will be stored in the member rec_desc, the second in row_was_returned.

        sctx := tq84_odci(record_desc, r+1, c+1);

        return odciconst.success;
    end odciTablePrepare; -- }

    static function odciTableStart(
         sctx in out nocopy tq84_odci,
         r    in            integer,
         c    in            integer) return number is -- {
    begin
        return odciconst.success;
    end odciTableStart; -- }

    member function odciTableFetch( -- {
       self            in out nocopy tq84_odci,
       nofRowsExpected in            number,
       record_out      out    nocopy anydataset
    )
    return number is
       len number := 10;
    begin

        r_current := r_current + 1;

        record_out := null;

        if r_current > nof_rows then -- {
        -- Last record reached: finished!
           return odciconst.success;
        end if; -- }

        anydataset.begincreate(dbms_types.typecode_object, self.rec_desc, record_out);
        record_out.addinstance;
        record_out.piecewise();

        if r_current = 1 then -- {

          record_out.setVarchar2(lpad('x', len));
          for c_current in 2 .. nof_cols loop -- {
              record_out.setVarchar2(lpad(c_current-1, 10));
          end loop; -- }

        -- }
        else -- {

           record_out.setVarchar2(lpad(r_current-1, len));
           for c_current in 2 .. nof_cols loop -- {
               record_out.setvarchar2(lpad((c_current-1) * (r_current-1), len));
           end loop; -- }

        end if; -- }

        record_out.endcreate;

        return odciconst.success;
    end odciTableFetch; -- }

    member function odciTableClose(self in tq84_odci) return number is -- {
    begin
        return odciconst.success;
    end odciTableClose; -- }

end;
/

show errors

Update 2020-11-20: it turns out that this example does not work if cursor_sharing is set to force: it throws the error message ORA-29913: error in executing ODCITABLEDESCRIBE callout.

This is because with this setting, Oracle calls the function with bind variables and odciTableDescribe receives null for the parameters r and c.

Therefore, in such an environment, the session must be altered in order to make the example run:

alter session set cursor_sharing = exact;