René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

April 22, 2007: Emulating tables with PL/SQL (Part III)

With PL/SQL, it's possible to create functions that return a nested table type of an object type. If the return value of such a function is given as the argument to the table() operator, it behaves like an ordinary table. I have already written about this in On emulating tables with nested tables and On emulating tables with PL/SQL (Part II). In these examples, the structure of the returned table (that is: the column names and their datatypes) is fixed. However, it is possible to create a function that returns a table whose data types and column names are constructed dynamically. This article tries to discuss this.
First, I need to create a type that implements the methods for pipelined and parallel tables:
create type some_type as object ( 

  -- store the types of the returned rows:
  row_types        anytype,

  -- Only one row should be retured. I need a flag
  -- that tells me, if the row was already returned
  row_was_returned number,

  -- do: This function needs not be implemented because of the 'pipelined using' construct.
  -- Behind the scenes, ODCITableDescribe and so on are called through the call to table(
  static function do               (do_param in number) return anydataset pipelined using some_type,

  static function ODCITableDescribe(record_table out anytype, do_param in number) return number, 
  static function ODCITablePrepare (sctx  out some_type, tab_func_info in sys.ODCITabFuncInfo, do_param in number) return number, 
  static function ODCITableStart   (sctx  in out some_type, do_param in number) return number, 
  member function ODCITableFetch   (self  in out some_type, nrows in number, record_out out anydataset) return number,
  member function ODCITableClose   (self  in some_type ) return number
The type body specifies how the returned record will look like (ODCITableDescribe) and what data is returned (ODCITableFetch):
create or replace type body some_type as 

  static function ODCITableDescribe(record_table out anytype, do_param in number) return number is
    record_structure anytype; 
    dbms_output.put_line('ODCITableDescribe, do_param: ' || do_param);

    -- The type that describes the structure of a record is created. This type
    -- must be of dbms_types.typecode_object.
    anytype.begincreate(dbms_types.typecode_object, record_structure);

    -- Adding some attributes to the type being created. These attributes specify the
    -- column names, their datatype and the precision
    -- The first column (named col1) will be a varchar2(10)
                aname     => 'col_1',
                typecode  =>  dbms_types.typecode_varchar2,
                prec      =>  null,
                scale     =>  null,
                len       =>  10,
                csid      =>  null,
                csfrm     =>  null

    -- The second column will be a number(6.2). Prec(ision) and scale must
    -- be stated.
                aname     => 'col_2',
                typecode  =>  dbms_types.typecode_number,
                prec      =>  6,
                scale     =>  2,
                len       =>  null,
                csid      =>  null,
                csfrm     =>  null
    -- The third column will be a date. Neither preciscion, scale nor len
    -- need be specified.
                aname     => 'col_3',
                typecode  =>  dbms_types.typecode_date,
                prec      =>  null,
                scale     =>  null,
                len       =>  null,
                csid      =>  null,
                csfrm     =>  null

    -- I am done adding attributes:

    -- Of course, in this example, I didn't really create a dynamic return type
    -- since all three columns will always be the same. But I thought I just want to
    -- show the idea.

    -- Now, after creating the record structure, I also need a to create a nested table
    -- of that record structure type. This is indicated with dbms_types.typecode_table.
    anytype.begincreate(dbms_types.typecode_table, record_table); 

    record_table.setinfo(null, null, null, null, null, record_structure, dbms_types.typecode_object, 0); 

    return odciconst.success;

  exception when others then 
    -- indicate that an error has occured somewhere.
    return odciconst.error;

  -- ODCITablePrepare creates an instance of some_type and returns it through the sctx out parameter.
  static function ODCITablePrepare(sctx out some_type, tab_func_info in sys.ODCITabFuncInfo, do_param in number) return number is 
    prec         pls_integer; 
    scale        pls_integer; 
    len          pls_integer; 
    csid         pls_integer; 
    csfrm        pls_integer; 
    record_desc  anytype; 
    aname        varchar2(30); 
    dummy        pls_integer; 
    dbms_output.put_line('ODCITablePrepare, do_param: ' || do_param);

    -- this is a bit mystic, imho: Through tab_func_info.RetType, it's possible to access the record_table that
    -- was created in ODCITableDescribe.
    -- 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); 

    -- Now, I am ready to construct an instance of some_type.
    -- The first parameter will be stored in the member row_types, the second in row_was_returned.
    sctx := some_type(record_desc, 0); 
    return odciconst.success; 

  static function ODCITableStart(sctx in out some_type, do_param in number ) return number is
    dbms_output.put_line('ODCITableStart, do_param: ' || do_param);
    return odciconst.success; 

  member function ODCITableFetch(self in out some_type, nrows in number, record_out out anydataset) return number is
    dbms_output.put_line('ODCITableFetch, nrows:' || nrows);
    record_out := null;

    if row_was_returned = 1 then 
      -- record_out being null indicates last record was already fetched 
      return ODCIconst.success; 
    end if;

    row_was_returned := 1;

    anydataset.begincreate(dbms_types.typecode_object, self.row_types, record_out); 


    -- Setting the returned values:
    record_out.setnumber  (  5.9);
    record_out.setdate    (sysdate-10);


    return odciconst.success; 

  member function ODCITableClose(self in some_type) return number is
    return odciconst.success; 

Now, let's see this type in action. I set the arraysize to four because it influences the number of records to be fetched in ODCITableFetch. I also set serveroutput on to see the messages emitted with dbms_output.
set serveroutput on
set arraysize    4
Now, the select statement...
select * from table(;
.. and its output:
col_1           col_2 col_3
---------- ---------- ---------
foo               5.9 12-APR-07
Here's the output from the dbms_output messages. As can be seen, nrows is set to 4 the second time ODCITableFetch was called. This number corresponds to the number set in arraysize. Also, the paramter do_param (42) corresponds to the value passed to the do method.
ODCITablePrepare, do_param: 42
ODCITableStart, do_param: 42
ODCITableFetch, nrows:1
ODCITableFetch, nrows:4
To verify if the datatypes correspond to the expected ones, I create a table with the create table ... as select ... construct...
create table dyn_table as select * from table( where 1 = 0;
.. and then desrcibe the table:
desc dyn_table
Name                                      Null?    Type
----------------------------------------- -------- ------------------
col_1                                              VARCHAR2(10)
col_2                                              NUMBER(6,2)
col_3                                              DATE

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.