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(some_type.do(...))
  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; 
  begin 
    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)
    record_structure.addattr(
                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.
    record_structure.addattr(
                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.
    record_structure.addattr(
                aname     => 'col_3',
                typecode  =>  dbms_types.typecode_date,
                prec      =>  null,
                scale     =>  null,
                len       =>  null,
                csid      =>  null,
                csfrm     =>  null
    ); 

    -- I am done adding attributes:
    record_structure.endcreate; 

    -- 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); 
    record_table.endcreate(); 

    return odciconst.success;

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

  -- 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; 
  begin 
    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; 
  end; 


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


  member function ODCITableFetch(self in out some_type, nrows in number, record_out out anydataset) return number is
  begin 
    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); 

    record_out.addinstance;
    record_out.piecewise(); 

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

    record_out.endcreate;

    return odciconst.success; 
  end; 


  member function ODCITableClose(self in some_type) return number is
  begin
    dbms_output.put_line('ODCITableClose');
    return odciconst.success; 
  end; 

end; 
/
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(some_type.do(42));
.. 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
ODCITableClose
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(some_type.do(33)) 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.

Warning: require(): open_basedir restriction in effect. File(/var/www/virtual/adp-gmbh.ch/forum/comment.inc) is not within the allowed path(s): (/home/httpd/vhosts/renenyffenegger.ch/:/tmp/) in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2007/04/22.php on line 470

Warning: require(/var/www/virtual/adp-gmbh.ch/forum/comment.inc): failed to open stream: Operation not permitted in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2007/04/22.php on line 470

Fatal error: require(): Failed opening required '/var/www/virtual/adp-gmbh.ch/forum/comment.inc' (include_path='.:/home/httpd/vhosts/renenyffenegger.ch') in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2007/04/22.php on line 470