Dynamischer Rückgabetyp bei Table Functions

Heut geht's um den Einsatz von Table Functions, die einen dynamischen Rückgabetyp besitzen.

Der normale Fall sieht dabei wie folgt aus:

Man erstellt einen Objekt-Typ und gibt die Attribute mit Namen und Typ an.

create type val_t as object
(
 v1 number,
 v2 number,
 v3 number
);
/

Jetzt fehlt noch der Table-Typ von dem gerade erstellen Objekt-Typ.

create type val_tab as table of val_t;
/

Die Table-Function soll nun das Einmaleins bis 3 ausgeben.

create or replace function multiplication_table return val_tab pipelined is
 v_column_count integer := 3;
begin
 for i in 1..v_column_count loop
  pipe row ( val_t (i*1,i*2,i*3) );
 end loop;
 return;
end multiplication_table;

Soweit so gut, jedoch wollen wir auch das Einmaleins bis 5, 10, 100 oder einer anderen natürlichen Zahl n, ohne dafür jeweils eine eigene Table Function samt den notwendigen Objekt-Typen erstellen zu wollen.

Dazu sind einige Funktionen zu implementieren, welche es erlauben, den Rückgabetyp dynamisch zu erstellen. Für die Aufgabe ergibt sich folgenden Struktur:

create or replace type multiplication_table as object
(
 v_row_types anytype,
 v_column_count integer,
 v_rows_processed integer,
 
 static function show_table(
  p_column_count_in in integer
 ) return anydataset pipelined using multiplication_table,
 
 static function ODCITableDescribe(
  rtype out anytype, 
  p_column_count_in in integer
 ) return number,
 
 static function ODCITablePrepare(
  sctx out multiplication_table, 
  tf_info SYS.ODCITabFuncInfo,
  p_column_count_in in integer
 ) return number,
 
 static function ODCITableStart(
  sctx in out multiplication_table, 
  p_column_count_in in integer
 ) return number,

 member function ODCITableFetch(
  self in out multiplication_table, 
  nrows in number, 
  rws out anydataset
 ) return number,

 member function ODCITableClose(
  self in multiplication_table 
 ) return number
);

Dabei wird der Rückgabetyp durch die Funktion ODCITableDescribe beschrieben, also die Anzahl und Typen der Attribute festgelegt. Konkret für die Lösung der Aufgabe bedeutet dies, das für das Einmaleins bis n auch n Attribute vorhanden sein müssen. Die Funktion ODCITableFetch liefert dann die Zeilen zurück, indem die Bestandteile des Objekt-Typs mit Werten gefüllt werden.

create or replace type body multiplication_table as

  static function ODCITableDescribe(
   rtype out anytype, 
   p_column_count_in in integer
  ) return number as
   v_record_structure anytype;
  begin
   anytype.begincreate(dbms_types.typecode_object, v_record_structure);
   for i in 1 .. p_column_count_in loop
    v_record_structure.addattr(   
       ANAME     => '#' || to_char(i),
       TYPECODE  => dbms_types.typecode_number,
       PREC      => null,
       SCALE     => null,
       LEN       => null,
       CSID      => null,    
       CSFRM     => null,
       ATTR_TYPE => null
     );
   end loop;
   v_record_structure.endcreate();
   anytype.begincreate(dbms_types.typecode_table, rtype); 
   rtype.setinfo(
    null, 
    null, 
    null, 
    null, 
    null, 
    v_record_structure, 
    dbms_types.typecode_object, 
    0); 
   rtype.endcreate(); 
   return odciconst.success;
  end ODCITableDescribe;

  static function ODCITablePrepare(
   sctx out multiplication_table, 
   tf_info SYS.ODCITabFuncInfo,
   p_column_count_in in integer
  ) return number as
   prec pls_integer; 
   scale pls_integer; 
   len pls_integer; 
   csid pls_integer; 
   csfrm pls_integer; 
   record_desc anytype; 
   aname varchar2(30); 
   dummy pls_integer;
  begin 
   dummy := tf_info.RetType.GetAttrElemInfo(
    null, 
    prec, 
    scale, 
    len, 
    csid, 
    csfrm, 
    record_desc, 
    aname); 
   sctx := multiplication_table(record_desc, p_column_count_in, 0); 
   return odciconst.success; 
  end ODCITablePrepare;

  static function ODCITableStart(
   sctx in out multiplication_table, 
   p_column_count_in in integer
  ) return number as
  begin
   return odciconst.success; 
  end ODCITableStart;

  member function ODCITableFetch(
   self in out multiplication_table, 
   nrows in number, 
   rws out anydataset
  ) return number as
  begin
   rws := null; 
   if (self.v_rows_processed < self.v_column_count) then
    self.v_rows_processed := self.v_rows_processed + 1;
    anydataset.begincreate(dbms_types.typecode_object, self.v_row_types, rws); 
    rws.addinstance;
    rws.piecewise();
    for i in 1 .. self.v_column_count loop
     rws.setnumber(self.v_rows_processed * i);
    end loop;
    rws.endcreate;
   end if;
   return odciconst.success;
  end ODCITableFetch;

  member function ODCITableClose(
   self in multiplication_table 
  ) return number as
  begin
   return odciconst.success; 
  end ODCITableClose;

end;

Ein Aufruf mit dem Argument 4 liefert dann:

select * from table(multiplication_table.show_table(4));

#1      #2      #3      #4                     
------- ------- ------- -------
1       2       3       4                      
2       4       6       8                      
3       6       9       12                     
4       8       12      16                     

Während die Wahl des Arguments 6 einen Objekt-Typ mit zwei zusätzlichen Spalten zur Folge hat:

select * from table(multiplication_table.show_table(6));

#1      #2      #3      #4      #5      #6                 
------- ------- ------- ------- ------- -------
1       2       3       4       5       6              
2       4       6       8       10      12           
3       6       9       12      15      18               
4       8       12      16      20      24              
5       10      15      20      25      30
6       12      18      24      30      36

Weitere Informationen dazu findet man im Data Cartridge Developer's Guide.