Pipelined PL/SQL and Java with ODCI

In my previous article I showed how to build table functions, including pipelined functions with PL/SQL. To provide a simple, minimal example I created a function that would generate a list of numbers from 1 to some value provided by the invoker.

CREATE OR REPLACE FUNCTION nt_function_pipelined(n IN INTEGER)
    RETURN numtab
    PIPELINED
IS
BEGIN
    FOR i IN 1 .. n
    LOOP
        PIPE ROW (i);
    END LOOP;

    RETURN;
END;

SELECT * FROM nt_function_pipelined(10);

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

You can extend the functionality of pipelined functions with the Oracle Data Cartridge Interface (ODCI). For the sake of simplicity and comparison, I will continue to use this number counter as my example but I’ll implement it with ODCI syntax using both PL/SQL and Java.

In most use cases of PL/SQL, I would lean toward the simpler PL/SQL pipelined syntax rather than using ODCI. Certainly for a real-world implementation of the number counter or similar simple function I would recommend an approach as above. So, the following is for illustration of minimal syntax for easy of presentation.

The ODCI table functions require 3 methods: ODCITABLESTART, ODCITABLEFETCH, and ODCITABLECLOSE. For our counter I have 2 attributes; g_limit the upper limit of the values to be returned and g_count, the number of values we have returned thus far.

CREATE OR REPLACE TYPE numcounter_plsql_odci AS OBJECT
(
    g_limit INTEGER,
    g_count INTEGER,
    STATIC FUNCTION odcitablestart(sctx    IN OUT numcounter_plsql_odci,
                                   p_limit     IN     INTEGER)
        RETURN NUMBER,
    MEMBER FUNCTION odcitablefetch(self        IN OUT numcounter_plsql_odci,
                                   p_numrows   IN     NUMBER,
                                   p_tab          OUT numtab)
        RETURN NUMBER,
    MEMBER FUNCTION odcitableclose(self IN numcounter_plsql_odci)
        RETURN NUMBER
);
/

ODCITABLESTART initializes the scan context, a self-referencing parameter being an instance of the type itself. Optionally it can take a list of arguments to set up the context. In this simple example I’m passing in the upper limit for the counter, just as I did for the native pl/sql implementation. For more complex operations we could include more parameters of other types.

ODCITABLEFETCH returns the next batch of rows for context. It requires three parameters. The scan context of self, the number of rows the client is requesting, and the collection of the values to be returned. The number of rows can be ignored if necessary, such a request for 10 values where you only have 7 values left, but if you want your function to support bulk processing, then honoring a request for multiple rows instead allows the client to work as efficiently as possible on the outputs. If you send more rows than were requested, the extras will be cached until another fetch call is made. Finally, you must initialize and populate the collection for the OUT parameter.

OCITABLECLOSE cleans up the context when fetching is complete, freeing used memory.

The Data Cartridge Interface also includes two additional methods; but they are optional: ODCITableDescribe and ODCITablePrepare. Briefly they can be used to create dynamic result sets of varying numbers and types of returned values. The examples in this article are intentionally minimal and do not need these methods. I will write a followup article with a more complex example illustrating the use of them.

Now that we have a type declared with the required methods, we need a type body to implement them.

CREATE OR REPLACE TYPE BODY numcounter_plsql_odci
AS
    STATIC FUNCTION odcitablestart(sctx      IN OUT numcounter_plsql_odci,
                                   p_limit   IN     INTEGER)
        RETURN NUMBER
    IS
    BEGIN
        sctx := numcounter_plsql_odci(g_limit => p_limit, g_count => 0);

        RETURN odciconst.success;
    END odcitablestart;

    MEMBER FUNCTION odcitablefetch(self        IN OUT numcounter_plsql_odci,
                                   p_numrows   IN     NUMBER,
                                   p_tab          OUT numtab)
        RETURN NUMBER
    IS
        v_num_rows   INTEGER;
    BEGIN
        IF g_count < g_limit
        THEN
            v_num_rows := LEAST(p_numrows, g_limit - g_count);
            p_tab := numtab();

            p_tab.EXTEND(v_num_rows);

            FOR i IN 1 .. v_num_rows
            LOOP
                p_tab(i) := g_count + i;
            END LOOP;

            g_count := g_count + v_num_rows;
        END IF;

        RETURN odciconst.success;
    END odcitablefetch;

    MEMBER FUNCTION odcitableclose(self IN numcounter_plsql_odci)
        RETURN NUMBER
    IS
    BEGIN
        RETURN odciconst.success;
    END odcitableclose;
END;
/

ODCITABLESTART creates an instance of the class setting the limit to the passed in value. It also initializes the counter to 0. If the limit is 0 or negative then no values will be returned.

ODCITABLEFETCH will return as many values as requested by the p_numrows parameter or enough values to reach the pre-defined limit, which ever is less. Thus, if you initialize with a limit of 25 and fetch requests of 10 values at a time, it will return 10 rows in the first fetch, 10 rows in the second fetch, then 5 rows on the third fetch.

First the fetch checks to see if there are more rows to be returned. If not, it simply exits. If there are more rows to return, it will allocate a collection to hold the number of rows to be returned and then using a FOR-loop, populates it. The counter is incremented by the number of rows actually returned and then exits.

OCITABLECLOSE we have nothing special to cleanup other than the context, which ODCI handles for us, so it simply exits.

To use this implementation we need an exposed API. This can be done in either of two ways. Most commonly it is with a PL/SQL function declaration with a USING clauses to reference the ODCI implementation type. Here I'm creating it as a standalone function for illustration, but it could be declared within a package too.

CREATE OR REPLACE FUNCTION nt_odci_pipelined(n IN INTEGER)
    RETURN numtab
    PIPELINED USING numcounter_plsql_odci;
/

SQL> select * from nt_odci_pipelined (5);

COLUMN_VALUE
------------
           1
           2
           3
           4
           5

Alternately, we can include a static function within the type declaration itself. Note the GENERATE function, other than the STATIC keyword, the syntax is similar to the standalone function.

CREATE OR REPLACE TYPE numcounter_plsql_impl AS OBJECT
(
    g_limit INTEGER,
    g_count INTEGER,
    STATIC FUNCTION generate(n IN INTEGER)
        RETURN numtab
        PIPELINED USING numcounter_plsql_impl,
    STATIC FUNCTION odcitablestart(p_context   IN OUT numcounter_plsql_impl,
                                   p_limit     IN     INTEGER)
        RETURN NUMBER,
    MEMBER FUNCTION odcitablefetch(self        IN OUT numcounter_plsql_impl,
                                   p_numrows   IN     NUMBER,
                                   p_tab          OUT numtab)
        RETURN NUMBER,
    MEMBER FUNCTION odcitableclose(self IN numcounter_plsql_impl)
        RETURN NUMBER
);
/

SQL> SELECT * FROM numcounter_plsql_odci.generate(5);

COLUMN_VALUE
------------
           1
           2
           3
           4
           5

You can also create a java stored procedure with the ODCI syntax. It requires the same three ODCI methods as the pl/sql implementation as well as SQLData methods for getSQLTypeName, readSQL, and writeSQL.

The java syntax is more verbose than the pl/sql, but follows the same general pattern.

create or replace and compile java source named "numCounter_java_odci" as
import java.io.*;
import java.util.*;
import oracle.sql.*;
import java.sql.*;
import java.math.BigDecimal;
import oracle.CartridgeServices.*;

//===========================================================================
// This defines the context connecting multiple calls to the fetch routine
//===========================================================================
public class numCounterCtx {
    int g_count;
    int g_limit;
    public numCounterCtx(int p_limit) {
        g_limit = p_limit;
    }
}

public class numCounter_java_odci implements SQLData {
    //=============================================================================
    // Define return codes
    //=============================================================================
    final static BigDecimal SUCCESS = new BigDecimal(0);
    final static BigDecimal ERROR = new BigDecimal(1);

    //=============================================================================
    // These GET, READ, and WRITE methods are required for the SQLData interface
    // They are not used within the ODCI table code directly
    //=============================================================================
    private BigDecimal key;
    String sql_type;
    public String getSQLTypeName() throws SQLException {
        return sql_type;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        sql_type = typeName;
        key = stream.readBigDecimal();
    }

    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeBigDecimal(key);
    }
    
    //=============================================================================
    // The ODCI pipeline functionality requires START, FETCH, and CLOSE routines
    //=============================================================================
    static public BigDecimal ODCITableStart(STRUCT[] sctx, int p_limit)
    throws SQLException {
        //=========================================================================
        // Initialize our context with the input parameter
        //  get a key for it to use later on to connect the fetch calls. 
        //=========================================================================
        numCounterCtx ctx = new numCounterCtx(p_limit);
        int key;
        try {
            key = ContextManager.setContext(ctx);
        } catch (CountException ce) {
            return ERROR;
        }

        //==========================================================================
        // create an instance of the pl/sql type with the context key
        //==========================================================================
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        Object[] plsqlImpl = new Object[1];
        plsqlImpl[0] = new BigDecimal(key);
        StructDescriptor sd = new StructDescriptor("NUMCOUNTER_PLSQL_IMPL", conn);
        sctx[0] = new STRUCT(sd, conn, plsqlImpl);

        return SUCCESS;
    }

    public BigDecimal ODCITableFetch(BigDecimal nrows, ARRAY[] outSet)
    throws SQLException {
        //=================================
        // get our context context key
        //=================================
        numCounterCtx ctx;
        try {
            ctx = (numCounterCtx) ContextManager.getContext(key.intValue());
        } catch (InvalidKeyException ik) {
            return ERROR;
        }

        //==============================================================================================
        // From our context determine how many we have already counted.
        // If we have not reached the limit yet, then fill up a list with some values and return them.
        // Each fetch call will request a certain number of rows.
        // Use the requested value unless there are fewer values left than the requested limit.
        //==============================================================================================
        if (ctx.g_limit > ctx.g_count) {
            // Determine how many rows will be returned in this fetch
            int v_returned_rows = nrows.intValue();
            if (v_returned_rows > (ctx.g_limit - ctx.g_count)) {
                v_returned_rows = (ctx.g_limit - ctx.g_count);
            }

            // Allocate a list and fill it up with the next set of values
            ArrayList returned_values = new ArrayList();
            for (int i = ctx.g_count + 1; i <= ctx.g_count + v_returned_rows; i++) {
                returned_values.add(i);
            }
            ctx.g_count = ctx.g_count + v_returned_rows;

            // Create an instance of the SQL collection type
            // converting the list into an array that maps to the type.
            Connection conn = DriverManager.getConnection("jdbc:default:connection:");
            ArrayDescriptor ad = new ArrayDescriptor("NUMTAB", conn);
            outSet[0] = new ARRAY(ad, conn, returned_values.toArray());
        }
        return SUCCESS;
    }

    public BigDecimal ODCITableClose() throws SQLException {

        //====================================
        // we are done, clean up the context
        //====================================
        numCounterCtx ctx;
        try {
            ctx = (numCounterCtx) ContextManager.clearContext(key.intValue());
        } catch (InvalidKeyException ik) {
            return ERROR;
        }

        return SUCCESS;
    }
}
/

Of special note is the ODCITableStart function must create an instance of the scan context but it must also create a StructDescriptor of a pl/sql type that will be the exposed interface to the java stored procedure. This is similar to the pl/sql type above except each ODCI function is declared to use java class methods instead of the type body. Also, the type only has one attribute, a key integer to hold the context BigDecimal value.

CREATE OR REPLACE TYPE numcounter_plsql_impl
    AS OBJECT
(
    key INTEGER,
    STATIC FUNCTION generate(p_limit IN INTEGER)
        RETURN numtab
        PIPELINED USING numcounter_plsql_impl,
    STATIC FUNCTION odcitablestart(sctx         OUT numcounter_plsql_impl,
                                   p_limit   IN     NUMBER)
        RETURN NUMBER
    AS
        LANGUAGE JAVA
        NAME 'numCounter_java_odci.ODCITableStart(oracle.sql.STRUCT[], int) return java.math.BigDecimal' ,
    MEMBER FUNCTION odcitablefetch(self     IN OUT numcounter_plsql_impl,
                                   nrows    IN     NUMBER,
                                   outset      OUT numtab)
        RETURN NUMBER
    AS
        LANGUAGE JAVA
        NAME 'numCounter_java_odci.ODCITableFetch(java.math.BigDecimal, oracle.sql.ARRAY[]) return java.math.BigDecimal' ,
    MEMBER FUNCTION odcitableclose(self IN numcounter_plsql_impl)
        RETURN NUMBER
    AS
        LANGUAGE JAVA
        NAME 'numCounter_java_odci.ODCITableClose() return java.math.BigDecimal'
);
/

As with the pl/sql ODCI type using its own body, you can create a standalone (or packaged) function or expose a static method within the type for invocation. Here I'm using the generate method within the type. Creating a standalone function to use the type would be identical to the pl/sql implementation above.

SQL> SELECT * FROM numcounter_plsql_impl.generate(5);

COLUMN_VALUE
------------
           1
           2
           3
           4
           5

These minimal ODCI examples are just to illustrate the core syntax requirements. For a real application requiring a number counter or similarly simple function I would recommend the native pl/sql implementation.

In learning to use these features I found most examples were helpful in providing real-world use cases, but due to the verbosity of the syntax it was sometimes difficult to read the required ODCI code interlaced with that of the sample application code. So I created these snippets to (hopefully) distinguish and highlight the ODCI syntax by keeping the application a trivial counter.

I hope you find these helpful, and if you want more can find more sophisticated examples in the Data Cartridge Developer's Guide. Thank you for reading. Questions and comments, as always, are welcome.