Here is a code excerpt that shows the method signature for a method that implements a table UDF with dynamic result row specification:
public class UDFExample { ... public static void getStoreData( int store, /* input arg */ java.lang.Object[] c1, /* output arg */ java.lang.Object[] c2, /* output arg */ java.lang.Object[] c3, /* output arg */ java.lang.Object[] c4) /* output arg */ { ... } ... }
If the JAR file for the UDFExample class is called UDFExample.jar, the following statement registers UDFExample.jar and the UDFExample class with the database, and creates an SQL identifier called JarUDF for the JAR file:
CALL SQLJ.INSTALL_JAR('CJ!udfsrc/UDFExample.jar','JarUDF',0);
The corresponding CREATE FUNCTION statement to define the UDF looks like this:
CREATE FUNCTION getStoreData (FileToRead INTEGER) RETURNS TABLE VARYING COLUMNS (4) LANGUAGE Java NO SQL EXTERNAL NAME 'JarUDF:UDFExample.getStoreData' PARAMETER STYLE Java;
Here is an example of an INSERT ... SELECT statement that invokes the table function in the FROM clause:
INSERT INTO Sales_Table SELECT * FROM TABLE (getStoreData(9005) RETURNS (Store INTEGER, Item INTEGER, Quantity INTEGER)) AS tf;