17 Oracle Event Processing JDBC Data Cartridge

This chapter describes the Oracle Event Processing JDBC data cartridge, an Oracle Continuous Query Language (Oracle CQL) extension through which you execute a SQL query against a database and use its returned results in a CQL query.

When using functionality provided by the cartridge, you are associating a SQL query with a JDBC cartridge function definition. Then, from a CQL query, you can call the JDBC cartridge function, which executes the associated SQL query against the database. The function call must be enclosed in the TABLE clause, which lets you use the SQL query results as a CQL relation in the CQL query making that function call.

For information the TABLE clause, see Section 17.2.2.2, "Using the TABLE Clause."

This chapter includes the following sections:

For more information, see:

17.1 Understanding the Oracle Event Processing JDBC Data Cartridge

Oracle Event Processing streams contain streaming data, and a database typically stores historical data. Use the Oracle Event Processing JDBC data cartridge to associate historical data (stored in one or more tables) with the streaming data coming from Oracle Event Processing streams. The Oracle Event Processing JDBC data cartridge executes arbitrary SQL query against a database and uses the results in the CQL query. This section describes how to associate streaming and historical data using the Oracle Event Processing JDBC data cartridge.

This section describes:

17.1.1 Data Cartridge Name

The Oracle Event Processing JDBC data cartridge uses the cartridge ID com.oracle.cep.cartridge.jdbc. This ID is reserved and cannot be used by any other cartridges.

For more information, see Section 17.1.4, "Oracle Event Processing JDBC Data Cartridge Application Context".

17.1.2 Scope

The Oracle Event Processing JDBC data cartridge supports arbitrarily complex SQL statements with the following restrictions:

  • You may use only native SQL types in the SELECT list of the SQL query.

  • You may not use user-defined types and complex database types in the SELECT list.

  • You must provide alias names for every SELECT list column in the SQL query.

For more information, see Section 17.1.3, "Datatype Mapping".

Note:

To use the Oracle Event Processing JDBC data cartridge, your data source must use Oracle JDBC driver version 11.2 or higher.

For more information, see "Configuring Access to a Different Database Driver or Driver Version" in the Oracle Fusion Middleware Administrator's Guide for Oracle Event Processing

17.1.3 Datatype Mapping

This section describes Oracle Event Processing JDBC data cartridge datatype mapping.

For reference, consider the Oracle Event Processing JDBC data cartridge context function that Example 17-1 shows.

Example 17-1 Oracle Event Processing 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>
...

For more information, see Section 17.1.2, "Scope".

17.1.4 Oracle Event Processing JDBC Data Cartridge Application Context

To use the Oracle Event Processing JDBC data cartridge, you must declare and configure one or more application-scoped JDBC cartridge context while developing an application, as described in the following steps:

17.1.4.1 Declare a JDBC Cartridge Context in the EPN File

To declare a JDBC cartridge context in the EPN file:

  1. Edit your Oracle Event Processing application EPN assembly file to add the required namespace and schema location entries as shown in Example 17-2.

  2. Add an entry with the tag jdbc-context in the EPN file and specify the id attribute, as shown in Example 17-3. The id represents the name of this application-scoped context and is used in CQL queries that reference functions defined in this context. The id is also used when this context is configured in the application configuration file.

Example 17-2 EPN Assembly File: Oracle Event Processing JDBC Data Cartridge Namespace and Schema Location

<?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/spring-wlevs-v11_1_1_6.xsd"
  http://www.oracle.com/ns/ocep/jdbc
  http://www.oracle.com/ns/ocep/jdbc/ocep-jdbc.xsd">

Example 17-3 shows how to create an Oracle Event Processing JDBC data cartridge application context named JdbcCartridgeOne in an EPN assembly file.

Example 17-3 jdbc:jdbc-context Element in EPN Assembly File

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

17.1.4.2 Configure the JDBC Cartridge Context in the Application Configuration File

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:

  1. Before adding the JDBC context configuration, add the required namespace entry to the configuration XML file, as shown in the following example:

    <?xml version="1.0" encoding="UTF-8"?>
    <jdbcctxconfig:config xmlns:jdbcctxconfig="http://www.bea.com/ns/wlevs/config/application"
        xmlns:jc="http://www.oracle.com/ns/ocep/config/jdbc/">
    
  2. The JDBC cartridge context configuration is done under the parent level tag jdbc-ctx. A context defines one or more functions, each of which is associated with a single SQL query. The configuration also specifies the data source representing the database against which the SQL queries are to be executed. Each function can have input parameters that are used to pass arguments to the SQL query defining the function, and each function specifies the return-component-type. Since the call to this function is always enclosed within a TABLE clause, the function always returns a Collection type. The return-component-type property indicates the type of the component of that collection.

    The value of the name property must match the value used for the id attribute in the EPN file, as shown in Example 17-3.

    Example 17-4 shows how to reference the jdbc:jbdc-context in an Oracle CQL query. In this case, the query uses link name JdbcCartridgeOne (defined in Example 17-3) to propagate this application context to the Oracle Event Processing JDBC data cartridge. The Oracle CQL query in Example 17-4 invokes the function getDetailsByOrderIdName associated with Oracle Event Processing JDBC data cartridge application context JdbcCartridgeOne.

    Example 17-4 jc:jdbc-ctx Element in Component Configuration File

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

For more information, see "How to Configure Oracle Event Processing JDBC Data Cartridge Application Context" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

17.2 Using the Oracle Event Processing JDBC Data Cartridge

In general, you use the Oracle Event Processing JDBC data cartridge as follows:

  1. Declare and define an Oracle Event Processing JDBC cartridge application-scoped context.

    For more information, see Section 17.1.4, "Oracle Event Processing JDBC Data Cartridge Application Context".

  2. Define one or more SQL statements in the jc:jdbc-ctx element in the component configuration file.

    For more information, see Section 17.2.1, "Defining SQL Statements: function Element."

  3. If you specify the function element return-component-type child element as a Java bean, implement the bean and ensure that the class is on your Oracle Event Processing application classpath.

    Example 17-5 shows a typical implementation.

    Example 17-5 Example return-component-type Class

    package com.oracle.cep.example.jdbc_cartridge;
     
    public class RetEvent
    {
        public String employeeName;
        public String employeeEmail;
        public 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();
        }
    }
    

    You must declare the fields as public.

    The return-component-type class for a JDBC cartridge context function must have a one-to-one mapping for fields in the SELECT list of the SQL query that defines the function. In other words, every field in the SELECT list of the SQL query defining a function must have a corresponding field (matching name) in the Java class that is declared to be the return-component-type for that function; otherwise Oracle Event Processing throws an error. For example, note how the SELECT items in the function in Example 17-4 match the field names in Example 17-5.

    For more information, see:

  4. Define one or more Oracle CQL queries that call the SQL statements defined in the jc:jdbc-ctx element using the Oracle CQL TABLE clause and access the returned results by SQL SELECT list alias names.

    For more information, see Section 17.2.2, "Defining Oracle CQL Queries With the Oracle Event Processing JDBC Data Cartridge."

17.2.1 Defining SQL Statements: function Element

Within the jc:jdbc-cxt element in the component configuration file, you can define a JDBC cartridge context function using the function child element as Example 17-6 shows.

Example 17-6 Oracle Event Processing 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>
...

You may define one or more function elements within a given jc:jdbc-cxt element.

This section describes:

17.2.1.1 function Element Attributes

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

Table 17-1 function Element Attributes

Attribute Description

name

The name of the JDBC cartridge context function.

The combination of name and signature must be unique within a given Oracle Event Processing JDBC data cartridge application context. For more information, see Section 17.2.1.3.4, "Overloading JDBC Cartridge Context Functions".


17.2.1.2 function Element Child Elements

Each function element supports the following child elements:

17.2.1.2.1 param

The param child element specifies an optional input parameter.

The SQL statement may take zero or more parameters. Each parameter is defined in a param element.

The param child element supports the attributes that Table 17-2 lists.

Table 17-2 param Element Attributes

Attribute Description

name

The name of the input parameter.

A valid parameter name is formed by a combination of A-Z,a-z,0-9 and _ (underscore).

type

The data type of the parameter.


Datatype Support – You may specify only Oracle CQL native com.bea.wlevs.ede.api.Type data types for the input parameter param element type attribute.

Note:

Datatype names are case sensitive. Use the case that the com.bea.wlevs.ede.api.Type class specifies.

For more information, see Table 17-3.

17.2.1.2.2 return-component-type

The return-component-type child element specifies the return type of the function. This child element is mandatory.

This represents the component type of the collection type returned by the JDBC data cartridge function. Because the function is always called from within an Oracle CQL TABLE clause, it always returns a collection type.

For more information, see Section 17.2.2.2, "Using the TABLE Clause."

Datatype Support – You may specify any one of the following types as the value of the return-component-type element:

  • Oracle CQL native com.bea.wlevs.ede.api.Type datatype.

  • Oracle CQL extensible Java cartridge type, such as a Java bean.

For more information, see:

17.2.1.2.3 sql

The sql child element specifies a SQL statement. This child element is mandatory.

Each function element may contain one and only one, single-line, SQL statement. You define the SQL statement itself within a <![CDATA[]]> block.

Within the SQL statement, you specify input parameters by param element name attribute using a colon (:) prefix as shown in Example 17-6.

Note:

You must provide alias names for every SELECT list column in the JDBC cartridge context function.

Datatype SupportTable 17-3 lists the SQL types you may use in your Oracle Event Processing JDBC data cartridge context functions and their corresponding Oracle Event Processing Java type and com.bea.wlevs.ede.api.Type type.

Table 17-3 SQL Column Types and Oracle Event Processing Type Equivalents

SQL Type Oracle Event Processing Java Type com.bea.wlevs.ede.api.Type

NUMBER

java.math.BigDecimal

bigdecimal

NUMBER

long

bigint

RAW

byte[]

byte

CHAR, VARCHAR

java.lang.String

char

NUMBER

double

double

FLOAT, NUMBER

float

float

INTEGER, NUMBER

int

int

TIMESTAMP

java.sql.Timestamp

timestamp


Note:

In cases where the size of the Java type exceeds that of the SQL type, your Oracle Event Processing application must restrict values to the maximum size of the SQL type. The choice of type to use on the CQL side should be driven by the range of values in the database column. For example, if the SQL column is a number that contains values in the range of integer, use the "int" type on CQL side. If you choose an incorrect type and encounter out-of-range values, Oracle Event Processing throws a numeric overflow error.

Note:

The Oracle Event Processing JDBC data cartridge does not support Oracle Spatial data types.

For more information, see Section 17.2.1.3, "function Element Usage."

17.2.1.3 function Element Usage

This section provides examples of different JDBC cartridge context functions you can define using the Oracle Event Processing JDBC data cartridge, including:

17.2.1.3.1 Multiple Parameter JDBC Cartridge Context Functions

Using the Oracle Event Processing JDBC data cartridge, you can define JDBC cartridge context functions that take multiple input parameters.

Example 17-7 shows an Oracle Event Processing JDBC data cartridge application context that defines an JDBC cartridge context function that takes two input parameters.

Example 17-7 Oracle JDBC Data Cartridge Context Functions With Multiple Parameters

...
<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>
...
17.2.1.3.2 Invoking PL/SQL Functions

Using the Oracle Event Processing JDBC data cartridge, you can define JDBC cartridge context functions that invoke PL/SQL functions that the database defines.

Example 17-8 shows an Oracle Event Processing JDBC data cartridge application context that defines a JDBC cartridge context function that invokes PL/SQL function getOrderAmt.

Example 17-8 Oracle JDBC Data Cartridge Context Function Invoking PL/SQL Functions

...
<function name="getOrderAmount">
    <param name="inpId" type="int" />
    <return-component-type>
        com.oracle.cep.example.jdbc_cartridge.RetEvent
    </return-component-type>
    <sql><![CDATA[
            SELECT getOrderAmt(:inpId) as orderAmt
            FROM dual 
    ]]></sql>
</function>
...
17.2.1.3.3 Complex JDBC Cartridge Context Functions

Using the Oracle Event Processing JDBC data cartridge, you can define arbitrarily complex JDBC cartridge context functions including subqueries, aggregation, GROUP BY, ORDER BY, and HAVING.

Example 17-9 shows an Oracle Event Processing JDBC data cartridge application context that defines a complex JDBC cartridge context function.

Example 17-9 Oracle Event Processing JDBC Data Cartridge Complex JDBC Cartridge Context Function

...
<function name="getHighValueOrdersPerEmp">
    <param name="limit" type="int"/>
    <param name="inpName" type="char"/>
    <return-component-type>
        com.oracle.cep.example.jdbc_cartridge.RetEvent
    </return-component-type>
        <sql><![CDATA[
            select description as description,  sum(amt) as totalamt, count(*) as numTimes
            from  OrderDetails
                where orderid in (
                    select orderid from PlacedOrders where empid in (
                        select empid from Employee where  empName = :inpName
                    )
                )
            group by description 
            having sum(amt) > :limit
        ]]></sql>
</function> 
...
17.2.1.3.4 Overloading JDBC Cartridge Context Functions

Using the Oracle Event Processing JDBC data cartridge, you can define JDBC cartridge context functions with the same name in the same application context provided that each function has a unique signature.

Example 17-10 shows an Oracle Event Processing JDBC data cartridge application context that defines two JDBC cartridge context functions named getDetails. Each function is distinguished by a unique signature.

Example 17-10 Oracle JDBC Data Cartridge Context Function Overloading

<jc:jdbc-ctx>
    <name>JdbcCartridgeOne</name> 
    <data-source>StockDS</data-source>
         <function name="getDetails">
            <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
                      ORDER BY <!—SQL query using ORDER BY -->
                        description desc
            ]]></sql>
        </function>
        <function name="getDetails">
            <param name="inpOrderId" type="int" />
            <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
                             PlacedOrders.orderId = :inpOrderId
            ]]></sql>
        </function>
</jc:jdbc-ctx>

17.2.2 Defining Oracle CQL Queries With the Oracle Event Processing JDBC Data Cartridge

This section describes how to define Oracle CQL queries that invoke SQL statements using the Oracle Event Processing JDBC data cartridge, including:

For more information, see "Querying an Event Stream with Oracle CQL" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

17.2.2.1 Using SELECT List Aliases

Consider the Oracle Event Processing JDBC data cartridge context function that Example 17-11 shows.

Example 17-11 Oracle Event Processing JDBC Data Cartridge Context Function

<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>

You must assign an alias to each column in the SELECT list. When you invoke the JDBC cartridge context function in an Oracle CQL query, you access the columns in the result set by their SQL SELECT list aliases.

For more information, see Section 17.2.2.2, "Using the TABLE Clause".

17.2.2.2 Using the TABLE Clause

Consider the Oracle Event Processing JDBC data cartridge SQL statement that Example 17-12 shows.

Example 17-12 Oracle Event Processing 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 Example 17-13 invokes the JDBC cartridge context function that Example 17-12 defines.

Example 17-13 Oracle CQL Query Invoking an Oracle Event Processing JDBC Data Cartridge Context Function

<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 Event Processing 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.

    In Example 17-13, details.

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

    In Example 17-13, orderInfo.

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

    In Example 17-12, employeeName, employeeEmail, and description.

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

    In Example 17-13, getEmployeeNameLength().

As Example 17-13 shows, 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. If the getDetailsByORderIdName JDBC cartridge context function called in Example 17-13 is defined as Example 17-12 shows, then orderInfo is of type com.oracle.cep.example.jdbc_cartridge.RetEvent.

You can access both fields and methods of the return-component-type in an Oracle CQL query. In Example 17-12, the return-component-type specifies a Java bean implemented as Example 17-14 shows.

Example 17-14 Example return-component-type Class

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:

17.2.2.3 Using a Native CQL Type as a return-component-type

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

Example 17-15 CQL Type bigint as a 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>

Example 17-16 shows how the getOrderAmt function in Example 17-15 can be used in a CQL query.

Example 17-16 getOrderAmt Function 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, which is bigint in Example 17-15.