To declare a JDBC cartridge context in the EPN file:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:osgi="http://www.springframework.org/schema/osgi"
       xmlns:wlevs="http://www.bea.com/ns/wlevs/spring"
       xmlns:jdbc="http://www.oracle.com/ns/ocep/jdbc"
       xsi:schemaLocation="
  http://www.springframework.org/schema/beans
  http://www.springframework.org/schema/beans/spring-beans.xsd
  http://www.springframework.org/schema/osgi
  http://www.springframework.org/schema/osgi/spring-osgi.xsd
  http://www.bea.com/ns/wlevs/spring
  http://www.bea.com/ns/wlevs/spring/http://www.bea.com/ns/wlevs/spring/ocep-epn.xsd
  http://www.oracle.com/ns/ocep/jdbc
  http://www.oracle.com/ns/ocep/jdbc/ocep-jdbc.xsd">

The following example shows how to create an Oracle Stream Explorer JDBC data cartridge application context named JdbcCartridgeOne in an EPN assembly file.

<jdbc:jdbc-context id="JdbcCartridgeOne"/>

To configure the JDBC cartridge context, add the configuration details in the component configuration file that is typically placed under the application's /wlevs directory. This configuration is similar to configuring other EPN components such as channel and processor.

To configure the JDBC cartridge context in the application configuration file:

Each function element supports the attributes that Table 3-1 lists.

Consider the Oracle Stream Analytics JDBC data cartridge SQL statement.

...
<jc:jdbc-ctx>
    <name>JdbcCartridgeOne</name> 
    <data-source>StockDS</data-source>
    <function name="getDetailsByOrderIdName">
        <param name="inpOrderId" type="int" />
        <param name="inpName" type="char" />
        <return-component-type>
            com.oracle.cep.example.jdbc_cartridge.RetEvent
        </return-component-type>
        <sql><![CDATA[
            SELECT
                Employee.empName as employeeName,
                Employee.empEmail as employeeEmail,
                OrderDetails.description as description
            FROM
                 PlacedOrders, OrderDetails , Employee
            WHERE
                PlacedOrders.empId = Employee.empId AND
                PlacedOrders.orderId = OrderDetails.orderId AND
                Employee.empName = :inpName AND
                PlacedOrders.orderId = :inpOrderId
        ></sql>
    </function>
</jc:jdbc-ctx>
...

The Oracle CQL query in the below example invokes the JDBC cartridge context function defined in the above example.

<processor>
        <name>Proc</name>
        <rules>
            <query id="q1"><![CDATA[
                RStream(
                   select
                     currentOrder.orderId,
                     details.orderInfo.employeeName,
                     details.orderInfo.employeeEmail,
                     details.orderInfo.description
                     details.orderInfo.getEmployeeNameLength()
                   from
                     OrderArrival[now] as currentOrder,
                     TABLE(getDetailsByOrderIdName@JdbcCartridgeOne(
                               currentOrder.orderId, currentOrder.empName
                           ) as orderInfo
                     ) as details
                )
            ></query>
        </rules>
</processor>

You must wrap the Oracle Stream Analytics JDBC data cartridge context function invocation in an Oracle CQL query TABLE clause.

You access the result set using:

TABLE_CLAUSE_ALIAS.JDBC_CARTRIDGE_FUNCTION_ALIAS.SQL_SELECT_LIST_ALIAS
or
TABLE_CLAUSE_ALIAS.JDBC_CARTRIDGE_FUNCTION_ALIAS.METHOD_NAME

Where:

• TABLE_CLAUSE_ALIAS: the outer AS alias of the TABLE clause.

• JDBC_CARTRIDGE_FUNCTION_ALIAS: the inner AS alias of the JDBC cartridge context function.

• SQL_SELECT_LIST_ALIAS: the JDBC cartridge context function SELECT list alias.

• METHOD_NAME: the name of the method that the return-component-type class provides.

You access the JDBC cartridge context function result set in the Oracle CQL query using:

details.orderInfo.employeeName
details.orderInfo.employeeEmail
details.orderInfo.description
details.orderInfo.getEmployeeNameLength()

The component type of the collection type returned by the JDBC data cartridge function is defined by the function element return-component-type child element. Because the function is always called from within an Oracle CQL TABLE clause, it always returns a collection type.

You can access both fields and methods of the return-component-type in an Oracle CQL query.

package com.oracle.cep.example.jdbc_cartridge;
 
public class RetEvent
{
    String employeeName;
    String employeeEmail;
    String description;

    /* Default constructor is mandatory */
    public RetEvent1() {}

    /* May contain getters and setters for the fields */

    public String getEmployeeName() {
        return this.employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }

    ...

    /* May contain other helper methods */

    public int getEmployeeNameLength() {
        return employeeName.length();
    }
}

This class provides helper methods, like getEmployeeNameLength, that you can invoke within the Oracle CQL query.

For more information, see return-component-type.

Following is a JDBC cartridge context that defines a function that has a native CQL type bigint as return-component-type.

<jc:jdbc-ctx>
    <name>JdbcCartridgeOne</name>
    <data-source>myJdbcDataSource</data-source>
    <function name="getOrderAmt">
       <param name="inpId" type="int" />
       <return-component-type>bigint</return-component-type> <!-- native CQL as return component type -->
       <sql><![CDATA[
                SELECT
                  getOrderAmt(:inpId) as orderAmt
                FROM (select :inpId as iid from
                  dual)>
       </sql>
    </function>
</jc:jdbc-ctx>

The following example shows how the getOrderAmt function in the above example can be used in a CQL query.

<query id="q1"><![CDATA[
                 RStream(
                   select
                     currentOrder.orderId,
                     details.orderInfo as orderAmt
                   from
                     OrderArrival[now] as currentOrder,
                     TABLE(getOrderAmt@JdbcCartridgeTwo(currentOrder.orderId) as orderInfo of bigint) as details
                 )
 ></query>

Note that the alias orderInfo itself is of type bigint and can be accessed as details.orderInfo as orderAmt in the select list of the CQL query.

The "of bigint" clause used inside the TABLE construct is optional. If specified, the type mentioned should match the return-component-type.