2 Configure Oracle JDBC and Oracle Spatial Data Cartridges

This chapter describes how to configure the Oracle JDBC cartridge and Oracle Spatial cartridge, which extend Oracle Continuous Query Language (CQL) for use with Oracle Stream Analytics.

This chapter includes the following sections:

2.1 How to Configure Oracle Spatial Application Context

You define an application context for an instance of Oracle Spatial using element spatial:context in your Oracle Stream Analytics application's Event Processing Network (EPN) assembly file.

All constructors and methods from com.oracle.cartridge.spatial.Geometry and Oracle Spatial functions are aware of spatial:context. For example, the SRID is automatically set from the value in the Oracle Spatial application context.

For more information, see "SDO_SRID" in the Oracle Spatial Developer's Guide at: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_objrelschema.htm#SPATL492

To configure Oracle Spatial application context:

In Oracle JDeveloper, open the EPN diagram.
Import the package com.oracle.cep.cartridge.spatial into your Oracle Stream Analytics application's MANIFEST.MF file.
Right-click the EPN node and select Configure Spatial Context > New Spatial Context.

Edit the EPN file to add the required namespace and schema location entries as follows:

<?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:spatial="http://www.oracle.com/ns/ocep/spatial/"
       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/ocep-epn.xsd  
http://www.oracle.com/ns/ocep/spatial
  http://www.oracle.com/ns/ocep/spatial/ocep-spatial.xsd">

Edit the EPN file to add a spatial:context element as follows.
```
<spatial:context id="SpatialGRS80" />
```
Assign a value to the id attribute that is unique in this EPN.

This is the name you will use to reference this application context in subsequent Oracle CQL queries.

Note:

The id value must not equal the Oracle Spatial name spatial.

Configure the other attributes of the spatial:context element to suit your application requirements.

Table 2-1 lists the attributes of the spatial:context element.

Table 2-1 spatial:context Element Attributes

Attribute	Description
`anyinteract-tolerance`	The default tolerance for contain or inside operator. Default: 0.0000005
`rof`	Defines the Reciprocal Of Flattening (`ROF`) parameter used for buffering and projection. Default: 298.257223563
`sma`	Defines the Semi-Major Axis (`SMA`) parameter used for buffering and projection. Default: 6378137.0
`srid`	SRID integer. Valid values are: `CARTESIAN`: for cartesian coordinate system. `LAT_LNG_WGS84_SRID`: for WGS84 coordinate system. An integer value from the Oracle Spatial `SDO_COORD_SYS` table `COORD_SYS_ID` column. Default : `LAT_LNG_WGS84_SRID`
`tolerance`	The minimum distance to be ignored in geometric operations including buffering. Default: 0.000000001

The following example shows how to create a spatial context named SpatialGRS80 in an EPN assembly file using the Geodetic Reference System 1980 (GRS80) coordinate system (srid="4269").

<spatial:context id="SpatialGRS80" srid="4269" sma="63787.0" rof="298.25722101" />

Create Oracle CQL queries that reference this application context by name.
The following example shows how to reference a spatial:context in an Oracle CQL query. In this case, the query uses link name SpatialGRS80 to propagate this application context to the Oracle Spatial. The spatial:context attribute settings of SpatialGRS80 are applied to the createPoint method call. Because the application context defines the SRID, you do not need to pass that argument into the createPoint method.
```
<view  id="createPoint">
    select com.oracle.cep.cartridge.spatial.Geometry.createPoint@SpatialGRS80(lng, lat, 0d)
    from CustomerPos[NOW]
</view>
```

2.2 How to Configure Oracle JDBC Data Cartridge Application Context

You define an application context for an instance of an Oracle JDBC data cartridge using:

A jdbc:jdbc-context element in the EPN assembly file.
A jc:jdbc-ctx element in the component configuration file.

The jc:jdbc-ctx element:
- references one and only one jdbc:jdbc-context
- references one and only one data-source
- defines one or more SQL functions

Note:

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

To configure Oracle JDBC data cartridge application context:

Open the EPN editor in the Oracle JDeveloper.
Right-click the EPN node and select Configure Spatial Context > New Spatial Context.

Edit the EPN file to add the required namespace and schema location entries as follows:

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

Edit the EPN file to add a jdbc:jdbc-context element as follows.
```
<jdbc:jdbc-context id="JdbcCartridgeOne"/>
```
Assign a value to the id attribute that is unique in this EPN.

This is the name you will use to reference this application context in subsequent Oracle CQL queries.

Note:

The id value must not equal the Oracle JDBC data cartridge name jdbc.
Right-click the desired processor and select Go to Configuration Source.

Edit the component configuration file to add the required namespace entries as follows:

<?xml version="1.0" encoding="UTF-8"?>
<wlevs:config
    xmlns:wlevs="http://www.bea.com/ns/wlevs/config/application"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jc="http://www.oracle.com/ns/ocep/config/jdbc
    xsi:schemaLocation="
       http://www.oracle.com/ns/ocep/config/jdbc
       http://www.oracle.com/ns/ocep/config/jdbc/ocep_jdbc_context_config.xsd">

Edit the component configuration file to add a jc:jdbc-ctx element as follows.
```
<jc:jdbc-ctx>
</jc:jdbc-ctx>
```
Add a name child element whose value is the name of the Oracle JDBC application context you defined in the EPN assembly file as follows.
```
<jc:jdbc-ctx>
    <name>JdbcCartridgeOne</name>
</jc:jdbc-ctx>
```
Add a data-source child element whose value is the name of a datasource defined in the Oracle Stream Analytics server config.xml file.
The following example shows how to specify the data source named StockDS.
```
<jc:jdbc-ctx>
    <name>JdbcCartridgeOne</name> 
    <data-source>StockDS</data-source>
</jc:jdbc-ctx>
```

Create one or more SQL functions using the function child element as follows.

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

Note:

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

Create Oracle CQL queries that invoke the SQL functions using the Oracle JDBC data cartridge application context.

The following example shows how to reference a jdbc:jdbc-context in an Oracle CQL query. In this case, the query uses link name JdbcCartridgeOne to propagate this application context to the Oracle JDBC data cartridge. The Oracle CQL query in invokes the function getDetailsByOrderIdName defined by Oracle JDBC data cartridge context JdbcCartridgeOne.

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