Using Table Functions

picture_as_pdf

This is an old revision of the document!

Table functions are a cool thing. Use it to dynamically create records directly in the database with all available database features. Not all database systems support table functions, but Oracle and PostgreSql do. Here's an example for a table function with Oracle.

First, we need the table function:

-- the type
DROP TYPE type_testtable;
DROP TYPE type_testrow;
 
CREATE TYPE type_testrow AS OBJECT (
  id          NUMBER,
  description VARCHAR2(50)
);
/
 
CREATE TYPE test_testtable IS TABLE OF type_testrow;
/
 
-- the table function
CREATE OR REPLACE FUNCTION get_tablefunction(pRowCount IN NUMBER) RETURN type_testtable AS
  result type_testtable := type_testtable();
BEGIN
  FOR i IN 1 .. pRowCount LOOP
    result.extend;
    result(result.LAST) := type_testrow(i, 'Description for record ' || i);
  END LOOP;
  RETURN result;
END;
/

To use the table function with a DBStorage, try following:

DBStorage dbs = new DBStorage();
dbs.setDBAccess(dba);
dbs.setFromClause("TABLE(get_tablefunction(:COUNT))");
dbs.open();
 
DirectObjectConnection con = new DirectObjectConnection();
con.put("dbs", dbs);
 
MasterConnection macon = new MasterConnection(con);
macon.open();
 
RemoteDataSource rds = new RemoteDataSource(macon);
rds.open();
 
RemoteDataBook rdb = new RemoteDataBook();
rdb.setDataSource(rds);
rdb.setName("dbs");
rdb.setFilter(new Equals("COUNT", BigDecimal.valueOf(20)));
rdb.open();
 
rdb.fetchAll();