Java Table Functions – Returning a SQL collection from a Java function.

One of the great features of Oracle is the ability to create a table function. That is, a function returning a collection type that can be used as the source for a query. These functions then allow a developer to provide a simple SQL api to query rows and columns from a variety of data sources. Any piece of information you can retrieve with PL/SQL may then be returned as a collection.

However, as robust as PL/SQL is, there are times when it simply lacks the functionality to achieve a particular purpose. When this happens it maybe useful to turn to Java stored procedures as an option. Alternately, it may be that a working Java class already exists and, even if possible, there could be little or no value in rewriting the class in PL/SQL.

A few weeks ago I wrote an article showing how to use a Java stored procedure to return a single value from a SQL Server database as an Oracle function usable within SQL or PL/SQL statements. In this article I’ll extend the same idea, but instead of returning just one value, the function will return a nested-table collection of objects.

For this function, the goal is to return a list of items ordered from each salesperson.

SELECT p.personid saldpersonid,
       p.fullname salesperson,
       o.customerid,
       o.orderid,
       o.orderdate,
       l.orderlineid,
       l.unitprice,
       l.quantity,
       i.stockitemname
  FROM application.people p
       JOIN sales.orders o ON o.salespersonpersonid = p.personid
       JOIN sales.orderlines l ON l.orderid = o.orderid
       JOIN warehouse.stockitems i ON i.stockitemid = l.stockitemid;

SALESPERSONID	SALESPERSON	CUSTOMERID	ORDERID	ORDERDATE	ORDERLINEID	UNITPRICE	QUANTITY	STOCKITEMNAME
13	Hudson Hollinworth	185	44773	2015-03-11	141507	2.9	182	Clear packaging tape 48mmx75m
15	Taj Shand	930	18428	2013-12-11	58433	18	24	"The Gu" red shirt XML tag t-shirt (Black) L
6	Sophia Hinton	85	29217	2014-06-19	92634	32	8	Animal with big feet slippers (Brown) L
7	Amy Trefl	1004	8919	2013-06-18	28345	32	2	Dinosaur battery-powered slippers (Green) M
3	Hudson Onslow	531	68540	2016-03-16	215660	18	40	20 mm Double sided bubble wrap 10m
2	Kayla Woodcock	402	44893	2015-03-12	141891	18.5	40	Office cube periscope (Black)

First, we’ll need an object type to hold each row’s data, and a collection type to return a set of those objects.

CREATE OR REPLACE TYPE wwi_salesperson_lineitem_type
   AS OBJECT
 (
     salespersonid INTEGER,
     salesperson VARCHAR2(50),
     customerid INTEGER,
     orderid INTEGER,
     orderdate DATE,
     orderlineid INTEGER,
     unitprice NUMBER,
     quantity INTEGER,
     stockitemname VARCHAR2(100)
 );
 /
 CREATE OR REPLACE TYPE wwi_salesperson_lineitem_tab 
     AS TABLE OF wwi_salesperson_lineitem_type;
 /

The JDBC and query portion is essentially the same as in the previous article; except instead of single value, we will iterate through a result set. For each row, append STRUCT object to a java array. When the array is full, we then use an ArrayDescriptor to create an oracle.sql.ARRAY from our java array. Also of note, the function will not only have a jdbc connection to the SQL Server database, it will also utilize the local default connection to the Oracle database as well.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED SDS.WWI_SALESPERSON_LINEITEMS
RESOLVER ((* SDS)(* SQLJDBC)(* PUBLIC))
as import java.sql.*;
import java.util.*;
import com.microsoft.sqlserver.jdbc.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class wwi_salesperson_lineitems {
    public static oracle.sql.ARRAY get_items(
                                             String p_server,
                                             String p_instance,
                                             int p_port,
                                             String p_database,
                                             String p_user,
                                             String p_password,
                                             int p_personid
                                            ) throws Exception
    {
       SQLServerDataSource ds = new SQLServerDataSource();
       ds.setServerName(p_server);
       if (p_instance != null) { ds.setInstanceName(p_instance); }
       ds.setPortNumber(p_port);
       ds.setDatabaseName(p_database);
       ds.setUser(p_user);
       ds.setPassword(p_password);

       Connection localconn    = DriverManager.getConnection("jdbc:default:connection:");
       StructDescriptor oneItem = StructDescriptor.createDescriptor("WWI_SALESPERSON_LINEITEM_TYPE", localconn);
       Object[] salesItem = new Object[9];

       Connection remoteconn = ds.getConnection();

       PreparedStatement pstmt = remoteconn.prepareStatement(
               "SELECT p.personid salespersonid,"
             + "       p.fullname salesperson,"
             + "       o.customerid,"
             + "       o.orderid,"
             + "       o.orderdate,"
             + "       l.orderlineid,"
             + "       l.unitprice,"
             + "       l.quantity,"
             + "       i.stockitemname,"
             + "       count(*) over() cnt"
             + "  FROM application.people   p"
             + "  JOIN sales.orders         o ON o.salespersonpersonid = p.personid"
             + "  JOIN sales.orderlines     l ON l.orderid = o.orderid"
             + "  JOIN warehouse.stockitems i ON i.stockitemid = l.stockitemid"
             + " WHERE ? < 1 or p.personid = ?");

       pstmt.setInt(1, p_personid);
       pstmt.setInt(2, p_personid);

       ResultSet rs = pstmt.executeQuery();
       STRUCT[] temparray = null;
       int i=0;
       while (rs.next()) {
          if (i==0) {
               temparray = new STRUCT[rs.getInt("cnt")];
          }
          salesItem[0] = rs.getInt("salespersonid");
          salesItem[1] = rs.getString("salesperson");
          salesItem[2] = rs.getInt("customerid");
          salesItem[3] = rs.getInt("orderid");
          salesItem[4] = rs.getDate("orderdate");
          salesItem[5] = rs.getInt("orderlineid");
          salesItem[6] = rs.getFloat("unitprice");
          salesItem[7] = rs.getInt("quantity");
          salesItem[8] = rs.getString("stockitemname");

          temparray[i++] = new STRUCT(oneItem, localconn, salesItem);
       }
       rs.close();

       remoteconn.close();

       ArrayDescriptor  SalesItemArray = ArrayDescriptor.createDescriptor("WWI_SALESPERSON_LINEITEM_TAB", localconn);
       return new ARRAY(SalesItemArray, localconn, temparray);
    }
}
/

Then of course we need a pl/sql api to expose the java function.

CREATE OR REPLACE FUNCTION sds.wwi_get_salesperson_items(
            p_server     IN VARCHAR2,
            p_instance   IN VARCHAR2,
            p_port       IN NUMBER,
            p_database   IN VARCHAR2,
            p_user       IN VARCHAR2,
            p_password   IN VARCHAR2,
            p_personid   IN NUMBER)
    RETURN wwi_salesperson_lineitem_tab
IS
    LANGUAGE JAVA
    NAME 'wwi_salesperson_lineitems.get_items(
                  java.lang.String, java.lang.String, int, 
                  java.lang.String, java.lang.String, java.lang.String,
                  int) 
          return oracle.sql.ARRAY' ;

Finally, use the function in a query. I'll look up the first rows for salesid 7.

SELECT *
  FROM TABLE(wwi_get_salesperson_items('my_remote_server',
                                       NULL,
                                       1433,
                                       'WideWorldImporters',
                                       'wwi',
                                       'pa55w0rd',
                                       7))
ORDER BY orderdate

SALESPERSONID	SALESPERSON	CUSTOMERID	ORDERID	ORDERDATE	ORDERLINEID	UNITPRICE	QUANTITY	STOCKITEMNAME
7	Amy Trefl	949	19	2013-01-01	70	13	7	DBA joke mug - it depends (White)
7	Amy Trefl	949	59	2013-01-01	69	99	60	10 mm Anti static bubble wrap (Blue) 50m
7	Amy Trefl	964	8	2013-01-01	30	13	1	Developer joke mug - old C developers never die (White)
7	Amy Trefl	173	23	2013-01-01	80	18	24	Halloween zombie mask (Light Brown) L
7	Amy Trefl	173	23	2013-01-01	83	13	3	IT joke mug - that behavior is by design (White)
7	Amy Trefl	949	59	2013-01-01	68	18	108	"The Gu" red shirt XML tag t-shirt (White) 7XL
7	Amy Trefl	77	51	2013-01-01	31	18	24	"The Gu" red shirt XML tag t-shirt (Black) XL
7	Amy Trefl	964	8	2013-01-01	27	32	4	USB food flash drive - banana
7	Amy Trefl	77	9	2013-01-01	33	32	4	Animal with big feet slippers (Brown) S
7	Amy Trefl	173	23	2013-01-01	81	32	2	Dinosaur battery-powered slippers (Green) XL
7	Amy Trefl	105	47	2013-01-01	10	230	9	Ride on toy sedan car (Pink) 1/12 scale

Another use case, possibly more common, is reading file lists from the host server. The idea is the same, create an object to hold the rows, a collection to hold the objects, iterate through files creating a STRUCT to add to an array, and finally convert the array to an oracle.sql.ARRAY. By default most users won't have access to read the server directories. If this functionality is required, your DBA will need to grant the appropriate permissions.

Again, first create the SQL objects.

CREATE OR REPLACE TYPE file_type
     AS OBJECT
 (
     file_name VARCHAR2(512),
     file_size NUMBER(15, 0),
     file_type VARCHAR2(1),
     last_modified DATE,
     readable VARCHAR2(1),
     writeable VARCHAR2(1)
 );
/

CREATE OR REPLACE TYPE file_table
    AS TABLE OF file_type;
/

Next, create the java class to read the directories and return a collection. I'll overload the class to allow for user specification of file size units (bytes vs kilobytes, megabytes, or gigabytes.) File permissions are protected separately from the permissions needed to view the file name, type, and size. I included a limited exception handler to simply return "?" as unknown if the user receives an error while trying to read them. As with the previous example, the function needs to establish a jdbc connection to the local default connection in order to create the SQL objects.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList"
as 
import java.io.*;
import java.sql.*;
import java.util.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class DirList{
    public static oracle.sql.ARRAY getListasObjects(String p_directory)
    throws Exception
    {
        return getListasObjects(p_directory,"Bytes");
    }

   public static oracle.sql.ARRAY getListasObjects(String p_directory, String p_size_units)
    throws Exception
      {
        Connection conn    = DriverManager.getConnection("jdbc:default:connection:");
        StructDescriptor oneFile = StructDescriptor.createDescriptor("FILE_TYPE", conn);
        Object[] fileattributes = new Object[6];

        File[] files = new File(p_directory).listFiles();
        STRUCT[] temparray = new STRUCT[files.length];
        for( int i = 0; i < files.length; i++ ) {
            fileattributes[0] = files[i].getName();

            String tempunits = p_size_units.toUpperCase();
            if (tempunits.equals("G") || tempunits.equals("GB") )
                 fileattributes[1] = Math.ceil((files[i].length() + 1.0) /1024/1024/1024);
            else if (tempunits.equals("M") || tempunits.equals("MB") )
                 fileattributes[1] =  Math.ceil((files[i].length() + 1.0) /1024/1024);
            else if (tempunits.equals("K")  || tempunits.equals("KB") )
                 fileattributes[1] =  Math.ceil((files[i].length() + 1.0) /1024);
            else {
                 fileattributes[1] = files[i].length();
            }

            fileattributes[2] = (files[i].isDirectory() ? "D" : "F");
            fileattributes[3] = new java.sql.Timestamp(files[i].lastModified());

            // If you don't have permissions granted to read or write
            // you'll get an error simply by looking at the attribute
            // For example...
            // ORA-29532: Java call terminated by uncaught Java exception:
            // java.security.AccessControlException:
            // the Permission (java.io.FilePermission YOURFILE write)
            // has not been granted to YOURUSER.
            // The PL/SQL to grant this is
            // dbms_java.grant_permission('YOURUSER',
            //                            'SYS:java.io.FilePermission',
            //                            'YOURFILE',
            //                            'write')
            try {
               fileattributes[4] = (files[i].canRead() ? "Y" : "N");
            } catch (java.security.AccessControlException e) {
               fileattributes[4] = "N";
             
            }
            try {
               fileattributes[5] = (files[i].canWrite() ? "Y" : "N");
            } catch (java.security.AccessControlException e) {
               fileattributes[5] = "N";
            }
            temparray[i] = new STRUCT(oneFile, conn, fileattributes);
        }

        ArrayDescriptor  FileArray = ArrayDescriptor.createDescriptor("FILE_TABLE", conn);
        return new ARRAY(FileArray, conn, temparray);
    }
}

Instead of creating a standalone function, this class will be exposed through a pl/sql package in order to invoke the overloaded methods. This could also have been implemented through a default value in the pl/sql wrapper function. This implementation more directly mirrors the java api which may be more appropriate for other examples.

CREATE OR REPLACE PACKAGE files
AS
    FUNCTION dir_list(p_directory IN VARCHAR2, p_size_units IN VARCHAR2 DEFAULT NULL)
        RETURN file_table;
END;
/

CREATE OR REPLACE PACKAGE BODY files
AS
    FUNCTION dir_list1(p_directory IN VARCHAR2)
        RETURN file_table
    AS
        LANGUAGE JAVA
        NAME 'DirList.getListasObjects( java.lang.String ) return oracle.sql.ARRAY' ;

    FUNCTION dir_list2(p_directory IN VARCHAR2, p_size_units IN VARCHAR2)
        RETURN file_table
    AS
        LANGUAGE JAVA
        NAME 'DirList.getListasObjects( java.lang.String, java.lang.String ) return oracle.sql.ARRAY' ;

    FUNCTION dir_list(p_directory IN VARCHAR2, p_size_units IN VARCHAR2 DEFAULT NULL)
        RETURN file_table
    IS
    BEGIN
        RETURN CASE WHEN p_size_units IS NULL THEN 
                           dir_list1(p_directory) 
                    ELSE 
                           dir_list2(p_directory, p_size_units)
               END;
    END;
END;
/

And then finally, use the package to look up the contents of a directory. This does assume permissions have been granted and the directory and files exist.

select * from files.dir_list('/tmp/customer_files');

FILE_NAME	FILE_SIZE	FILE_TYPE	LAST_MODIFIED	READABLE	WRITEABLE
messages.json	722	F	2019-03-21 01:19:29	Y	?
datadump.csv	3709	F	2019-03-21 01:19:13	Y	?
sales.xml	2473	F	2019-03-21 01:18:56	Y	?

I hope these example help. A natural question to follow up might be "could these functions returned a pipelined collection?" Yes, they can through the Data Cartridge api (ODCI) and I hope to provide examples of those options in a future article.

Questions and comments, as always, are welcome.