You can use the Oracle Event Processing JDBC data cartridge to execute a SQL query against a database and use the 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.
Oracle recommends the Oracle JDBC data cartridge for accessing relational database tables from an Oracle CQL statement.
For information the TABLE clause, see Using the TABLE Clause.
This chapter includes the following sections:
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:
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 Oracle Event Processing JDBC Data Cartridge Application Context.
The Oracle Event Processing JDBC data cartridge supports arbitrarily complex SQL statements with the following restrictions:
You can use only native SQL types in the SELECT
list of the SQL query.
You cannot use user-defined types and complex database types in the SELECT
list.
You can provide alias names for every SELECT
list column in the SQL query. If you provide alias names, make sure the select list is consistent with the return type property names.
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 Oracle Fusion Middleware Administering Oracle Event Processing
Use the param
element to specify the parameters for JDBC functions. The parameters are specified as name
and value
pairs. The name
attribute specifies event data of the specified type
. The type
attribute can be any Oracle CQL data type. See Oracle Fusion Middleware Oracle CQL Language Reference for Oracle Event Processing for information about Oracle CQL data types.
The following example shows an example configuration file that uses param
and type
pairs to specify parameters for the getDetailsByOrderIdName
function.
The RetEvent
class used in the example is an example of how to return a complex type as a table function. The full code for this class is shown in Using the Oracle Event Processing JDBC Data Cartridge.
... <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> ...
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:
To declare a JDBC cartridge context in the EPN file:
jdbc-context
in the EPN file and specify the id
attribute. 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.<?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 Event Processing 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:
For more information, see Oracle Fusion Middleware Developing Applications for Oracle Event Processing .
In general, you use the Oracle Event Processing JDBC data cartridge as follows:
Within the jc:jdbc-cxt
element in the component configuration file, you can define a JDBC cartridge context function using the function
child element.
... <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:
Each function
element supports the attributes that Table 3-1 lists.
Table 3-1 function Element Attributes
|
Each function
element supports the following child elements:
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 3-2 lists.
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.
Datatype names are case sensitive. Use the case that the com.bea.wlevs.ede.api.Type
class specifies.
For more information, see Table 3-3.
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 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:
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.
You must provide alias names for every SELECT
list column in the JDBC cartridge context function.
Datatype Support – Table 3-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 3-3 SQL Column Types and Oracle Event Processing Type Equivalents
|
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.
For more information, see 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:
Using the Oracle Event Processing JDBC data cartridge, you can define JDBC cartridge context functions that take multiple input parameters.
The following example shows an Oracle Event Processing JDBC data cartridge application context that defines an JDBC cartridge context function that takes two input 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> ...
Using the Oracle Event Processing JDBC data cartridge, you can define JDBC cartridge context functions that invoke PL/SQL functions that the database defines.
The following example shows an Oracle Event Processing JDBC data cartridge application context that defines a JDBC cartridge context function that invokes PL/SQL function getOrderAmt
.
... <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> ...
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
.
The following example shows an Oracle Event Processing JDBC data cartridge application context that defines a 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> ...
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.
The following example 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.
<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 description desc ></sql> </function> <function name="getDetails"> <param name="inpOrderId" type="int" /> <sql><![CDATA[ return-component-type 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>
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 Oracle Fusion Middleware Developing Applications for Oracle Event Processing .
Consider the 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 Using the TABLE Clause.
Consider the Oracle Event Processing JDBC data cartridge SQL statement. The TABLE clause is described in Oracle Fusion Middleware Oracle CQL Language Reference for Oracle Event Processing.
... <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 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.
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 Oracle Fusion Middleware Oracle CQL Language Reference for Oracle Event Processing and 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.