~~NOTRANS~~
~~Title: Using Table Functions~~
[[https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm|Table or pipelined functions]] are a cool thing. Use them to dynamically create records directly --  without a physical table -- in the database with all available database features. Not all database systems support table functions but Oracle and [[https://postgresrocks.enterprisedb.com/t5/The-Knowledgebase/Oracle-Workaround-PIPELINED/ta-p/152|PostgreSql]] do. Here's an example for a table function with Oracle.

First, we need the table function:

<code plsql>
-- 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;
/
</code>

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

<code java>
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();
</code>