Oracle Fusion Middleware CQL Language Reference for Oracle Complex Event Processing, 11g Release 1 (11.1.1.6.0)

11g Release 1 (11.1.1.6.0)

E12048-06

Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

- Syntax Diagrams

Part I Understanding Oracle CQL

1 Introduction to Oracle CQL

- 1.1 Fundamentals of Oracle CQL

- 1.1.1 Streams and Relations

- 1.1.1.1 Streams

- 1.1.1.2 Relations

- 1.1.1.3 Relations and Oracle CEP Tuple Kind Indicator

- 1.1.2 Relation-to-Relation Operators

- 1.1.3 Stream-to-Relation Operators (Windows)

- 1.1.3.1 Range, Rows, and Slide

- 1.1.3.2 Partition

- 1.1.3.3 Default Stream-to-Relation Operator

- 1.1.4 Relation-to-Stream Operators

- 1.1.4.1 Default Relation-to-Stream Operator

- 1.1.5 Stream-to-Stream Operators

- 1.1.6 Queries, Views, and Joins

- 1.1.7 Pattern Recognition

- 1.1.8 Event Sources and Event Sinks

- 1.1.8.1 Event Sources

- 1.1.8.2 Event Sinks

- 1.1.8.3 Connecting Event Sources and Event Sinks

- 1.1.9 Table Event Sources

- 1.1.9.1 Relational Database Table Event Sources

- 1.1.9.2 XML Table Event Sources

- 1.1.9.3 Function Table Event Sources

- 1.1.10 Cache Event Sources

- 1.1.11 Functions

- 1.1.12 Data Cartridges

- 1.1.13 Time

- 1.2 Oracle CQL Statements

- 1.2.1 Lexical Conventions

- 1.2.2 Syntactic Shortcuts and Defaults

- 1.2.3 Documentation Conventions

- 1.3 Oracle CQL and SQL Standards

- 1.4 Oracle CEP Server and Tools Support

- 1.4.1 Oracle CEP Server

- 1.4.2 Oracle CEP Tools

- 1.4.2.1 Oracle CEP IDE for Eclipse

- 1.4.2.2 Oracle CEP Visualizer

2 Basic Elements of Oracle CQL

- 2.1 Datatypes

- 2.1.1 Oracle CQL Built-in Datatypes

- 2.1.2 Handling Other Datatypes Using Oracle CQL Data Cartridges

- 2.1.3 Handling Other Datatypes Using a User-Defined Function

- 2.2 Datatype Comparison Rules

- 2.2.1 Numeric Values

- 2.2.2 Date Values

- 2.2.3 Character Values

- 2.2.4 Datatype Conversion

- 2.2.4.1 Implicit Datatype Conversion

- 2.2.4.2 Explicit Datatype Conversion

- 2.2.4.3 SQL Datatype Conversion

- 2.2.4.4 Oracle Data Cartridge Datatype Conversion

- 2.2.4.5 User-Defined Function Datatype Conversion

- 2.3 Literals

- 2.3.1 Text Literals

- 2.3.2 Numeric Literals

- 2.3.2.1 Integer Literals

- 2.3.2.2 Floating-Point Literals

- 2.3.3 Datetime Literals

- 2.3.4 Interval Literals

- 2.3.4.1 INTERVAL DAY TO SECOND

- 2.4 Format Models

- 2.4.1 Number Format Models

- 2.4.2 Datetime Format Models

- 2.5 Nulls

- 2.5.1 Nulls in Oracle CQL Functions

- 2.5.2 Nulls with Comparison Conditions

- 2.5.3 Nulls in Conditions

- 2.6 Comments

- 2.7 Aliases

- 2.7.1 Defining Aliases Using the AS Operator

- 2.7.1.1 Aliases in the relation_variable Clause

- 2.7.1.2 Aliases in Window Operators

- 2.7.2 Defining Aliases Using the Aliases Element

- 2.7.2.1 How to Define a Data Type Alias Using the Aliases Element

- 2.8 Schema Object Names and Qualifiers

- 2.8.1 Schema Object Naming Rules

- 2.8.2 Schema Object Naming Guidelines

- 2.8.3 Schema Object Naming Examples

3 Pseudocolumns

- 3.1 Introduction to Pseudocolumns

- 3.2 ELEMENT_TIME Pseudocolumn

- 3.2.1 Understanding the Value of the ELEMENT_TIME Pseudocolumn

- 3.2.1.1 ELEMENT_TIME for a System-Timestamped Stream

- 3.2.1.2 ELEMENT_TIME for an Application-Timestamped Stream

- 3.2.2 Using the ELEMENT_TIME Pseudocolumn in Oracle CQL Queries

- 3.2.2.1 Using ELEMENT_TIME With SELECT

- 3.2.2.2 Using ELEMENT_TIME With GROUP BY

- 3.2.2.3 Using ELEMENT_TIME With PATTERN

4 Operators

- 4.1 Introduction to Operators

- 4.1.1 What You May Need to Know About Unary and Binary Operators

- 4.1.2 What You May Need to Know About Operator Precedence

- Arithmetic Operators

- Concatenation Operator

- Alternation Operator

- Range-Based Stream-to-Relation Window Operators

- S[now]

- S[range T]

- S[range T1 slide T2]

- S[range unbounded]

- S[range C on E]

- Tuple-Based Stream-to-Relation Window Operators

- S [rows N]

- S [rows N1 slide N2]

- Partitioned Stream-to-Relation Window Operators

- S [partition by A1,..., Ak rows N]

- S [partition by A1,..., Ak rows N range T]

- S [partition by A1,..., Ak rows N range T1 slide T2]

- IStream Relation-to-Stream Operator

- DStream Relation-to-Stream Operator

- RStream Relation-to-Stream Operator

5 Expressions

- 5.1 Introduction to Expressions

- aggr_distinct_expr

- aggr_expr

- arith_expr

- arith_expr_list

- case_expr

- decode

- func_expr

- object_expr

- order_expr

- xml_agg_expr

- xmlcolattval_expr

- xmlelement_expr

- xmlforest_expr

- xml_parse_expr

6 Conditions

- 6.1 Introduction to Conditions

- 6.1.1 Condition Precedence

- 6.2 Comparison Conditions

- 6.3 Logical Conditions

- 6.4 LIKE Condition

- 6.4.1 Examples

- 6.5 Range Conditions

- 6.6 Null Conditions

- 6.7 Compound Conditions

- 6.8 IN Condition

- 6.8.1 Using IN and NOT IN as a Set Operation

- 6.8.2 Using IN and NOT IN as a Membership Condition

- 6.8.3 NOT IN and Null Values

7 Common Oracle CQL DDL Clauses

- 7.1 Introduction to Common Oracle CQL DDL Clauses

- array_type

- attr

- attrspec

- complex_type

- const_bigint

- const_int

- const_string

- const_value

- identifier

- l-value

- methodname

- non_mt_arg_list

- non_mt_attr_list

- non_mt_attrname_list

- non_mt_attrspec_list

- non_mt_cond_list

- param_list

- qualified_type_name

- query_ref

- time_spec

- xml_attribute_list

- xml_attr_list

- xqryargs_list

Part II Functions

8 Built-In Single-Row Functions

- 8.1 Introduction to Oracle CQL Built-In Single-Row Functions

- concat

- hextoraw

- length

- lk

- nvl

- prev

- rawtohex

- systimestamp

- to_bigint

- to_boolean

- to_char

- to_double

- to_float

- to_timestamp

- xmlcomment

- xmlconcat

- xmlexists

- xmlquery

9 Built-In Aggregate Functions

- 9.1 Introduction to Oracle CQL Built-In Aggregate Functions

- 9.1.1 Built-In Aggregate Functions and the Where, Group By, and Having Clauses

- avg

- count

- first

- last

- max

- min

- sum

- xmlagg

10 Colt Single-Row Functions

- 10.1 Introduction to Oracle CQLBuilt-In Single-Row Colt Functions

- beta

- beta1

- betaComplemented

- binomial

- binomial1

- binomial2

- binomialComplemented

- bitMaskWithBitsSetFromTo

- ceil

- chiSquare

- chiSquareComplemented

- errorFunction

- errorFunctionComplemented

- factorial

- floor

- gamma

- gamma1

- gammaComplemented

- getSeedAtRowColumn

- hash

- hash1

- hash2

- hash3

- i0

- i0e

- i1

- i1e

- incompleteBeta

- incompleteGamma

- incompleteGammaComplement

- j0

- j1

- jn

- k0

- k0e

- k1

- k1e

- kn

- leastSignificantBit

- log

- log10

- log2

- logFactorial

- logGamma

- longFactorial

- mostSignificantBit

- negativeBinomial

- negativeBinomialComplemented

- normal

- normal1

- normalInverse

- poisson

- poissonComplemented

- stirlingCorrection

- studentT

- studentTInverse

- y0

- y1

- yn

11 Colt Aggregate Functions

- 11.1 Introduction to Oracle CQL Built-In Aggregate Colt Functions

- 11.1.1 Oracle CQL Colt Aggregate Function Signatures and Tuple Arguments

- 11.1.2 Colt Aggregate Functions and the Where, Group By, and Having Clauses

- autoCorrelation

- correlation

- covariance

- geometricMean

- geometricMean1

- harmonicMean

- kurtosis

- lag1

- mean

- meanDeviation

- median

- moment

- pooledMean

- pooledVariance

- product

- quantile

- quantileInverse

- rankInterpolated

- rms

- sampleKurtosis

- sampleKurtosisStandardError

- sampleSkew

- sampleSkewStandardError

- sampleVariance

- skew

- standardDeviation

- standardError

- sumOfInversions

- sumOfLogarithms

- sumOfPowerDeviations

- sumOfPowers

- sumOfSquaredDeviations

- sumOfSquares

- trimmedMean

- variance

- weightedMean

- winsorizedMean

12 java.lang.Math Functions

- 12.1 Introduction to Oracle CQL Built-In java.lang.Math Functions

- abs

- abs1

- abs2

- abs3

- acos

- asin

- atan

- atan2

- cbrt

- ceil1

- cos

- cosh

- exp

- expm1

- floor1

- hypot

- IEEERemainder

- log1

- log101

- log1p

- pow

- rint

- round

- round1

- signum

- signum1

- sin

- sinh

- sqrt

- tan

- tanh

- todegrees

- toradians

- ulp

- ulp1

13 User-Defined Functions

- 13.1 Introduction to Oracle CQL User-Defined Functions

- 13.1.1 Types of User-Defined Functions

- 13.1.1.1 User-Defined Single-Row Functions

- 13.1.1.2 User-Defined Aggregate Functions

- 13.1.2 User-Defined Function Datatypes

- 13.1.3 User-Defined Functions and the Oracle CEP Server Cache

- 13.2 Implementing a User-Defined Function

- 13.2.1 How to Implement a User-Defined Single-Row Function

- 13.2.2 How to Implement a User-Defined Aggregate Function

Part III Data Cartridges

14 Introduction to Data Cartridges

- 14.1 Understanding Data Cartridges

- 14.1.1 Data Cartridge Name

- 14.1.2 Data Cartridge Application Context

- 14.2 Oracle CQL Data Cartridge Types

15 Oracle Java Data Cartridge

- 15.1 Understanding the Oracle Java Data Cartridge

- 15.1.1 Data Cartridge Name

- 15.1.2 Class Loading

- 15.1.2.1 Application Class Space Policy

- 15.1.2.2 No Automatic Import Class Space Policy

- 15.1.2.3 Server Class Space Policy

- 15.1.2.4 Class Loading Example

- 15.1.3 Method Resolution

- 15.1.4 Datatype Mapping

- 15.1.4.1 Java Datatype String and Oracle CQL Datatype CHAR

- 15.1.4.2 Literals

- 15.1.4.3 Arrays

- 15.1.4.4 Collections

- 15.1.5 Oracle CQL Query Support for the Oracle Java Data Cartridge

- 15.2 Using the Oracle Java Data Cartridge

- 15.2.1 How to Query Using the Java API

- 15.2.2 How to Query Using Exported Java Classes

16 Oracle Spatial

- 16.1 Understanding Oracle Spatial

- 16.1.1 Data Cartridge Name

- 16.1.2 Scope

- 16.1.2.1 Geometry Types

- 16.1.2.2 Element Info Array

- 16.1.2.3 Ordinates and Coordinate Systems and the SDO_SRID

- 16.1.2.4 Geometric Index

- 16.1.2.5 Geometric Relation Operators

- 16.1.2.6 Geometric Filter Operators

- 16.1.2.7 Geometry API

- 16.1.3 Datatype Mapping

- 16.1.4 Oracle Spatial Application Context

- 16.2 Using Oracle Spatial

- 16.2.1 How to Access the Geometry Types That the Oracle Spatial Java API Supports

- 16.2.2 How to Create a Geometry

- 16.2.3 How to Access Geometry Type Public Methods and Fields

- 16.2.4 How to Use Geometry Relation Operators

- 16.2.5 How to Use Geometry Filter Operators

- 16.2.6 How to Use the Default Geodetic Coordinates

- 16.2.7 How to Use Other Geodetic Coordinates

- ANYINTERACT

- bufferPolygon

- CONTAIN

- createElemInfo

- createGeometry

- createLinearPolygon

- createPoint

- createRectangle

- distance

- einfogenerator

- FILTER

- get2dMbr

- INSIDE

- NN

- ordsgenerator

- to_Geometry

- to_JGeometry

- WITHINDISTANCE

17 Oracle CEP JDBC Data Cartridge

- 17.1 Understanding the Oracle CEP JDBC Data Cartridge

- 17.1.1 Data Cartridge Name

- 17.1.2 Scope

- 17.1.3 Datatype Mapping

- 17.1.4 Oracle CEP JDBC Data Cartridge Application Context

- 17.1.4.1 Declare a JDBC Cartridge Context in the EPN File

- 17.1.4.2 Configure the JDBC Cartridge Context in the Application Configuration File

- 17.2 Using the Oracle CEP JDBC Data Cartridge

- 17.2.1 Defining SQL Statements: function Element

- 17.2.1.1 function Element Attributes

- 17.2.1.2 function Element Child Elements

- 17.2.1.3 function Element Usage

- 17.2.2 Defining Oracle CQL Queries With the Oracle CEP JDBC Data Cartridge

- 17.2.2.1 Using SELECT List Aliases

- 17.2.2.2 Using the TABLE Clause

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

Part IV Using Oracle CQL

18 Oracle CQL Queries, Views, and Joins

- 18.1 Introduction to Oracle CQL Queries, Views, and Joins

- 18.1.1 How to Create an Oracle CQL Query

- 18.2 Queries

- 18.2.1 Query Building Blocks

- 18.2.1.1 Select, From, Where Block

- 18.2.1.2 Select Clause

- 18.2.1.3 From Clause

- 18.2.1.4 Where Clause

- 18.2.1.5 Group By Clause

- 18.2.1.6 Order By Clause

- 18.2.1.7 Having Clause

- 18.2.1.8 Binary Clause

- 18.2.1.9 IDStream Clause

- 18.2.2 Simple Query

- 18.2.3 Built-In Window Query

- 18.2.4 MATCH_RECOGNIZE Query

- 18.2.5 Relational Database Table Query

- 18.2.6 XMLTable Query

- 18.2.7 Function TABLE Query

- 18.2.8 Cache Query

- 18.2.9 Sorting Query Results

- 18.2.10 Detecting Differences in Query Results

- 18.2.11 Parameterized Queries

- 18.2.11.1 Parameterized Queries in Oracle CQL Statements

- 18.2.11.2 The bindings Element

- 18.2.11.3 Run-Time Query Naming

- 18.2.11.4 Lexical Conventions for Parameter Values

- 18.2.11.5 Parameterized Queries at Runtime

- 18.2.11.6 Replacing Parameters Programmatically

- 18.3 Views

- 18.3.1 Views and Joins

- 18.3.2 Views and Schemas

- 18.4 Joins

- 18.4.1 Inner Joins

- 18.4.2 Outer Joins

- 18.4.2.1 Left Outer Join

- 18.4.2.2 Right Outer Join

- 18.4.2.3 Outer Join Look-Back

- 18.5 Oracle CQL Queries and the Oracle CEP Server Cache

- 18.5.1 Creating Joins Against the Cache

- 18.5.1.1 Cache Key First and Simple Equality

- 18.5.1.2 No Arithmetic Operations on Cache Keys

- 18.5.1.3 No Full Scans

- 18.5.1.4 Multiple Conditions and Inequality

- 18.6 Oracle CQL Queries and Relational Database Tables

- 18.7 Oracle CQL Queries and Oracle Data Cartridges

19 Pattern Recognition With MATCH_RECOGNIZE

- 19.1 Understanding Pattern Recognition With MATCH_RECOGNIZE

- 19.1.1 MATCH_RECOGNIZE and the WHERE Clause

- 19.1.2 Referencing Singleton and Group Matches

- 19.1.3 Referencing Aggregates

- 19.1.3.1 Running Aggregates and Final Aggregates

- 19.1.3.2 Operating on the Same Correlation Variable

- 19.1.3.3 Referencing Variables That Have not Been Matched Yet

- 19.1.3.4 Referencing Attributes not Qualified by Correlation Variable

- 19.1.3.5 Using count With *, identifier.*, and identifier.attr

- 19.1.3.6 Using first and last

- 19.1.4 Using prev

- 19.2 MEASURES Clause

- 19.2.1 Functions Over Correlation Variables in the MEASURES Clause

- 19.3 PATTERN Clause

- 19.3.1 Pattern Quantifiers and Regular Expressions

- 19.3.2 Grouping and Alternation in the PATTERN Clause

- 19.4 DEFINE Clause

- 19.4.1 Functions Over Correlation Variables in the DEFINE Clause

- 19.4.2 Referencing Attributes in the DEFINE Clause

- 19.4.3 Referencing One Correlation Variable From Another in the DEFINE Clause

- 19.5 PARTITION BY Clause

- 19.6 ORDER BY Clause

- 19.7 ALL MATCHES Clause

- 19.8 WITHIN Clause

- 19.9 DURATION Clause

- 19.9.1 Fixed Duration Non-Event Detection

- 19.9.2 Recurring Non-Event Detection

- 19.10 INCLUDE TIMER EVENTS Clause

- 19.11 SUBSET Clause

- 19.12 MATCH_RECOGNIZE Examples

- 19.12.1 Pattern Detection

- 19.12.2 Pattern Detection With PARTITION BY

- 19.12.3 Pattern Detection With Aggregates

- 19.12.4 Pattern Detection With the WITHIN Clause

- 19.12.5 Fixed Duration Non-Event Detection

20 Oracle CQL Statements

- 20.1 Introduction to Oracle CQL Statements

- Query

- View

Index

List of Examples

- 1-1 Typical Oracle CQL Statements

- 1-2 Stock Ticker Data Stream

- 1-3 Channel Schema Definition

- 1-4 filterFanoutProcessor Oracle CQL Query Using priceStream

- 1-5 Using channel Element selector Child Element to Control Which Query Results are Output to a Channel

- 1-6 Oracle CEP Tuple Kind Indicator in Relation Output

- 1-7 Relation-to-Relation Operation

- 1-8 Query Without Stream-to-Relation Operator

- 1-9 Equivalent Query

- 1-10 Relation-to-Stream Operation

- 1-11 Stream-to-Stream Operation

- 1-12 Query and View id Attribute

- 1-13 Typical Oracle CQL Processor Configuration Source File

- 1-14 Oracle CQL: Without Whitespace Formatting

- 1-15 Oracle CQL: With Whitespace Formatting

- 2-1 Enum Datatype ProcessStatus

- 2-2 Event Using Enum Datatype ProcessStatus

- 2-3 User-Defined Function to Evaluate Enum Datatype

- 2-4 Registering the User-Defined Function in Application Assembly File

- 2-5 Using the User-Defined Function to Evaluate Enum Datatype in an Oracle CQL Query

- 2-6 Invalid Event XSD: xsd:dateTime is Not Supported

- 2-7 Valid Event XSD: Using xsd:string Instead of xsd:dateTime

- 2-8 XML Event Payload

- 2-9 Comparing Intervals

- 2-10 Using the AS Operator in the SELECT Statement

- 2-11 Using the AS Operator After a Window Operator

- 2-12 aliases Element in a Processor Component Configuration File

- 2-13 Adding an aliases Element to a Processor

- 2-14 Adding a type-alias Element to a Processor

- 2-15 Adding the source and target Elements

- 2-16 Accessing the Methods and Fields of an Aliased Type

- 3-1 ELEMENT_TIME Pseudocolumn in a Select Statement

- 3-2 Input Stream

- 3-3 Output Relation

- 3-4 Query With GROUP BY

- 3-5 View

- 3-6 Query

- 3-7 ELEMENT_TIME Pseudocolumn in a Pattern

- 4-1 Concatenation Operator (||)

- 4-2 Alternation Operator (|)

- 4-3 S [now] Query

- 4-4 S [now] Stream Input

- 4-5 S [now] Relation Output at Time 5000000000 ns

- 4-6 S [range T] Query

- 4-7 S [range T] Stream Input

- 4-8 S [range T] Relation Output

- 4-9 S [range T1 slide T2] Query

- 4-10 S [range T1 slide T2] Stream Input

- 4-11 S [range T1 slide T2] Relation Output

- 4-12 S [range unbounded] Query

- 4-13 S [range unbounded] Stream Input

- 4-14 S [range unbounded] Relation Output at Time 5000 ms

- 4-15 S [range unbounded] Relation Output at Time 205000 ms

- 4-16 S [range C on E] Constant Value: Query

- 4-17 S [range C on E] Constant Value: Stream Input

- 4-18 S [range C on E] Constant Value: Relation Output

- 4-19 S [range C on E] INTERVAL Value: Query

- 4-20 S [range C on E] INTERVAL Value: Stream Input

- 4-21 S [range C on E] INTERVAL Value: Relation Output

- 4-22 S [rows N] Query

- 4-23 S [rows N] Stream Input

- 4-24 S [rows N] Relation Output at Time 1003 ms

- 4-25 S [rows N] Relation Output at Time 1007 ms

- 4-26 S [rows N] Relation Output at Time 2001 ms

- 4-27 S [rows N1 slide N2] Query

- 4-28 S [rows N1 slide N2] Stream Input

- 4-29 S [rows N1 slide N2] Relation Output

- 4-30 S[partition by A1, ..., Ak rows N] Query

- 4-31 S[partition by A1, ..., Ak rows N] Stream Input

- 4-32 S[partition by A1, ..., Ak rows N] Relation Output

- 4-33 S[partition by A1, ..., Ak rows N range T] Query

- 4-34 S[partition by A1, ..., Ak rows N range T] Stream Input

- 4-35 S[partition by A1, ..., Ak rows N range T] Relation Output

- 4-36 S[partition by A1, ..., Ak rows N range T1 slide T2] Query

- 4-37 S[partition by A1, ..., Ak rows N range T1 slide T2] Stream Input

- 4-38 S[partition by A1, ..., Ak rows N range T1 slide T2] Relation Output

- 4-39 IStream

- 4-40 DStream

- 4-41 RStream

- 5-1 aggr_distinct_expr for COUNT

- 5-2 aggr_expr for COUNT

- 5-3 arith_expr

- 5-4 arith_expr_list

- 5-5 CASE Expression: SELECT * Query

- 5-6 CASE Expression: SELECT * Stream Input

- 5-7 CASE Expression: SELECT * Relation Output

- 5-8 CASE Expression: SELECT Query

- 5-9 CASE Expression: SELECT Stream S0 Input

- 5-10 CASE Expression: SELECT Stream S1 Input

- 5-11 CASE Expression: SELECT Relation Output

- 5-12 Arithmetic Expression and DECODE Query

- 5-13 Arithmetic Expression and DECODE Relation Input

- 5-14 Arithmetic Expression and DECODE Relation Output

- 5-15 func_expr for PREV

- 5-16 func_expr for XMLQUERY

- 5-17 func_expr for SUM

- 5-18 Data Cartridge TABLE Query

- 5-19 Type Declaration Using an Oracle CQL Data Cartridge Link

- 5-20 Field Access Using an Oracle CQL Data Cartridge Link

- 5-21 Type Declaration Using an Oracle CQL Data Cartridge Link

- 5-22 Type Declaration Using an Oracle CQL Data Cartridge Link

- 5-23 order_expr

- 5-24 xml_agg_expr Query

- 5-25 xml_agg_expr Relation Input

- 5-26 xml_agg_expr Relation Output

- 5-27 xmlcolattval_expr Query

- 5-28 xmlcolattval_expr Relation Input

- 5-29 xmlcolattval_expr Relation Output

- 5-30 xmlelement_expr Query

- 5-31 xmlelement_expr Relation Input

- 5-32 xmlelement_expr Relation Output

- 5-33 xmlforest_expr Query

- 5-34 xmlforest_expr Relation Input

- 5-35 xmlforest_expr Relation Output

- 5-36 xml_parse_expr Content: Query

- 5-37 xml_parse_expr Content: Relation Input

- 5-38 xml_parse_expr Content: Relation Output

- 5-39 xml_parse_expr Document: Query

- 5-40 xml_parse_expr Document: Relation Input

- 5-41 xml_parse_expr Document: Relation Output

- 5-42 xml_parse_expr Wellformed: Query

- 5-43 xml_parse_expr Wellformed: Relation Input

- 5-44 xml_parse_expr Wellformed: Relation Output

- 6-1 S [range C on E] INTERVAL Value: Query

- 6-2 S [range C on E] INTERVAL Value: Stream Input

- 6-3 S [range C on E] INTERVAL Value: Relation Output

- 7-1 Declaring an Oracle CQL Data Cartridge Array in an Event Type

- 7-2 Accessing an Oracle CQL Data Cartridge Array in an Oracle CQL Query

- 7-3 attr Clause

- 7-4 Data Cartridge Field Access

- 7-5 Data Cartridge Method Access

- 7-6 Data Cartridge Constructor Invocation

- 7-7 xml_attr_list

- 8-1 concat Function Query

- 8-2 concat Function Stream Input

- 8-3 concat Function Relation Output

- 8-4 Concatenation Operator (||) Query

- 8-5 Concatenation Operator (||) Stream Input

- 8-6 Concatenation Operator (||) Relation Output

- 8-7 hextoraw Function Query

- 8-8 hextoraw Function Stream Input

- 8-9 hextoraw Function Relation Output

- 8-10 length Function Query

- 8-11 length Function Stream Input

- 8-12 length Function Relation Output

- 8-13 lk Function Query

- 8-14 lk Function Stream Input

- 8-15 lk Function Relation Output

- 8-16 nvl Function Query

- 8-17 nvl Function Stream Input

- 8-18 nvl Function Relation Output

- 8-19 prev(identifier1.identifier2) Function Query

- 8-20 prev(identifier1.identifier2) Function Stream Input

- 8-21 prev(identifier1.identifier2) Function Relation Output

- 8-22 prev(identifier1.identifier2, const_int1) Function Query

- 8-23 prev(identifier1.identifier2, const_int1) Function Stream Input

- 8-24 prev(identifier1.identifier2, const_int1) Function Relation Output

- 8-25 Partition Containing the Event at 8000

- 8-26 prev(identifier1.identifier2, const_int1, const_int2) Function Query

- 8-27 prev(identifier1.identifier2, const_int1, const_int2) Function Stream Input

- 8-28 prev(identifier1.identifier2, const_int1, const_int2) Function Relation Output

- 8-29 rawtohex Function Query

- 8-30 rawtohex Function Stream Input

- 8-31 rawtohex Function Relation Output

- 8-32 systimestamp Function Query

- 8-33 systimestamp Function Stream Input

- 8-34 systimestamp Function Relation Output

- 8-35 to_bigint Function Query

- 8-36 to_bigint Function Stream Input

- 8-37 to_bigint Function Relation Output

- 8-38 to_boolean Function Query

- 8-39 to_boolean Function Stream Input

- 8-40 to_boolean Function Relation Output

- 8-41 to_char Function Query

- 8-42 to_char Function Stream Input

- 8-43 to_char Function Relation Output

- 8-44 to_double Function Query

- 8-45 to_double Function Stream Input

- 8-46 to_double Function Relation Output

- 8-47 to_float Function Query

- 8-48 to_float Function Stream Input

- 8-49 to_float Function Relation Output

- 8-50 to_timestamp Function Query

- 8-51 to_timestamp Function Stream Input

- 8-52 to_timestamp Function Relation Output

- 8-53 xmlcomment Function Query

- 8-54 xmlcomment Function Stream Input

- 8-55 xmlcomment Function Relation Output

- 8-56 xmlconcat Function Query

- 8-57 xmlconcat Function Stream Input

- 8-58 xmlconcat Function Relation Output

- 8-59 xmlexists Function Query

- 8-60 xmlexists Function Stream Input

- 8-61 xmlexists Function Relation Output

- 8-62 xmlquery Function Query

- 8-63 xmlquery Function Stream Input

- 8-64 xmlquery Function Relation Output

- 9-1 Invalid Use of count

- 9-2 Valid Use of count

- 9-3 avg Function Query

- 9-4 avg Function Stream Input

- 9-5 avg Function Relation Output

- 9-6 count Function Query

- 9-7 count Function Stream Input

- 9-8 count Function Relation Output

- 9-9 first Function Query

- 9-10 first Function Stream Input

- 9-11 first Function Relation Output

- 9-12 last Function Query

- 9-13 last Function Stream Input

- 9-14 last Function Relation Output

- 9-15 max Function Query

- 9-16 max Function Stream Input

- 9-17 max Function Relation Output

- 9-18 min Function Query

- 9-19 min Function Stream Input

- 9-20 min Function Relation Output

- 9-21 sum Query

- 9-22 sum Stream Input

- 9-23 sum Relation Output

- 9-24 xmlagg Query

- 9-25 xmlagg Relation Input

- 9-26 xmlagg Relation Output

- 9-27 xmlagg and ORDER BY Query

- 9-28 xmlagg and ORDER BY Relation Input

- 9-29 xmlagg and ORDER BY Relation Output

- 10-1 beta Function Query

- 10-2 beta Function Stream Input

- 10-3 beta Function Relation Output

- 10-4 beta1 Function Query

- 10-5 beta1 Function Stream Input

- 10-6 beta1 Function Relation Output

- 10-7 betaComplemented Function Query

- 10-8 betaComplemented Function Stream Input

- 10-9 betaComplemented Function Relation Output

- 10-10 binomial Function Query

- 10-11 binomial Function Stream Input

- 10-12 binomial Function Relation Output

- 10-13 binomial1 Function Query

- 10-14 binomial1 Function Stream Input

- 10-15 binomial1 Function Relation Output

- 10-16 binomial2 Function Query

- 10-17 binomial2 Function Stream Input

- 10-18 binomial2 Function Relation Output

- 10-19 binomialComplemented Function Query

- 10-20 binomialComplemented Function Stream Input

- 10-21 binomialComplemented Function Relation Output

- 10-22 bitMaskWithBitsSetFromTo Function Query

- 10-23 bitMaskWithBitsSetFromTo Function Stream Input

- 10-24 bitMaskWithBitsSetFromTo Function Relation Output

- 10-25 ceil Function Query

- 10-26 ceil Function Stream Input

- 10-27 ceil Function Relation Output

- 10-28 chiSquare Function Query

- 10-29 chiSquare Function Stream Input

- 10-30 chiSquare Function Relation Output

- 10-31 chiSquareComplemented Function Query

- 10-32 chiSquareComplemented Function Stream Input

- 10-33 chiSquareComplemented Function Relation Output

- 10-34 errorFunction Function Query

- 10-35 errorFunction Function Stream Input

- 10-36 errorFunction Function Relation Output

- 10-37 errorFunctionComplemented Function Query

- 10-38 errorFunctionComplemented Function Stream Input

- 10-39 errorFunctionComplemented Function Relation Output

- 10-40 factorial Function Query

- 10-41 factorial Function Stream Input

- 10-42 factorial Function Relation Output

- 10-43 floor Function Query

- 10-44 floor Function Stream Input

- 10-45 floor Function Relation Output

- 10-46 gamma Function Query

- 10-47 gamma Function Stream Input

- 10-48 gamma Function Relation Output

- 10-49 gamma1 Function Query

- 10-50 gamma1 Function Stream Input

- 10-51 gamma1 Function Relation Output

- 10-52 gammaComplemented Function Query

- 10-53 gammaComplemented Function Stream Input

- 10-54 gammaComplemented Function Relation Output

- 10-55 getSeedAtRowColumn Function Query

- 10-56 getSeedAtRowColumn Function Stream Input

- 10-57 getSeedAtRowColumn Function Relation Output

- 10-58 hash Function Query

- 10-59 hash Function Stream Input

- 10-60 hash Function Relation Output

- 10-61 hash1 Function Query

- 10-62 hash1 Function Stream Input

- 10-63 hash1 Function Relation Output

- 10-64 hash2 Function Query

- 10-65 hash2 Function Stream Input

- 10-66 hash2 Function Relation Output

- 10-67 hash3 Function Query

- 10-68 hash3 Function Stream Input

- 10-69 hash3 Function Relation Output

- 10-70 i0 Function Query

- 10-71 i0 Function Stream Input

- 10-72 i0 Function Relation Output

- 10-73 i0e Function Query

- 10-74 i0e Function Stream Input

- 10-75 i0e Function Relation Output

- 10-76 i1 Function Query

- 10-77 i1 Function Stream Input

- 10-78 i1 Function Relation Output

- 10-79 i1e Function Query

- 10-80 i1e Function Stream Input

- 10-81 i1e Function Relation Output

- 10-82 incompleteBeta Function Query

- 10-83 incompleteBeta Function Stream Input

- 10-84 incompleteBeta Function Relation Output

- 10-85 incompleteGamma Function Query

- 10-86 incompleteGamma Function Stream Input

- 10-87 incompleteGamma Function Relation Output

- 10-88 incompleteGammaComplement Function Query

- 10-89 incompleteGammaComplement Function Stream Input

- 10-90 incompleteGammaComplement Function Relation Output

- 10-91 j0 Function Query

- 10-92 j0 Function Stream Input

- 10-93 j0 Function Relation Output

- 10-94 j1 Function Query

- 10-95 j1 Function Stream Input

- 10-96 j1 Function Relation Output

- 10-97 jn Function Query

- 10-98 jn Function Stream Input

- 10-99 jn Function Relation Output

- 10-100 k0 Function Query

- 10-101 k0 Function Stream Input

- 10-102 k0 Function Relation Output

- 10-103 k0e Function Query

- 10-104 k0e Function Stream Input

- 10-105 k0e Function Relation Output

- 10-106 k1 Function Query

- 10-107 k1 Function Stream Input

- 10-108 k1 Function Relation Output

- 10-109 k1e Function Query

- 10-110 k1e Function Stream Input

- 10-111 k1e Function Relation Output

- 10-112 kn Function Query

- 10-113 kn Function Stream Input

- 10-114 kn Function Relation Output

- 10-115 leastSignificantBit Function Query

- 10-116 leastSignificantBit Function Stream Input

- 10-117 leastSignificantBit Function Relation Output

- 10-118 log Function Query

- 10-119 log Function Stream Input

- 10-120 log Function Relation Output

- 10-121 log10 Function Query

- 10-122 log10 Function Stream Input

- 10-123 log10 Function Relation Output

- 10-124 log2 Function Query

- 10-125 log2 Function Stream Input

- 10-126 log2 Function Relation Output

- 10-127 logFactorial Function Query

- 10-128 logFactorial Function Stream Input

- 10-129 logFactorial Function Relation Output

- 10-130 logGamma Function Query

- 10-131 logGamma Function Stream Input

- 10-132 logGamma Function Relation Output

- 10-133 longFactorial Function Query

- 10-134 longFactorial Function Stream Input

- 10-135 longFactorial Function Relation Output

- 10-136 mostSignificantBit Function Query

- 10-137 mostSignificantBit Function Stream Input

- 10-138 mostSignificantBit Function Relation Output

- 10-139 negativeBinomial Function Query

- 10-140 negativeBinomial Function Stream Input

- 10-141 negativeBinomial Function Relation Output

- 10-142 negativeBinomialComplemented Function Query

- 10-143 negativeBinomialComplemented Function Stream Input

- 10-144 negativeBinomialComplemented Function Relation Output

- 10-145 normal Function Query

- 10-146 normal Function Stream Input

- 10-147 normal Function Relation Output

- 10-148 normal1 Function Query

- 10-149 normal1 Function Stream Input

- 10-150 normal1 Function Relation Output

- 10-151 normalInverse Function Query

- 10-152 normalInverse Function Stream Input

- 10-153 normalInverse Function Relation Output

- 10-154 poisson Function Query

- 10-155 poisson Function Stream Input

- 10-156 poisson Function Relation Output

- 10-157 poissonComplemented Function Query

- 10-158 poissonComplemented Function Stream Input

- 10-159 poissonComplemented Function Relation Output

- 10-160 stirlingCorrection Function Query

- 10-161 stirlingCorrection Function Stream Input

- 10-162 stirlingCorrection Function Relation Output

- 10-163 studentT Function Query

- 10-164 studentT Function Stream Input

- 10-165 studentT Function Relation Output

- 10-166 studentTInverse Function Query

- 10-167 studentTInverse Function Stream Input

- 10-168 studentTInverse Function Relation Output

- 10-169 y0 Function Query

- 10-170 y0 Function Stream Input

- 10-171 y0 Function Relation Output

- 10-172 y1 Function Query

- 10-173 y1 Function Stream Input

- 10-174 y1 Function Relation Output

- 10-175 yn Function Query

- 10-176 yn Function Stream Input

- 10-177 yn Function Relation Output

- 11-1 Invalid Use of count

- 11-2 Valid Use of count

- 11-3 autoCorrelation Function Query

- 11-4 autoCorrelation Function Stream Input

- 11-5 autoCorrelation Function Relation Output

- 11-6 correlation Function Query

- 11-7 correlation Function Stream Input

- 11-8 correlation Function Relation Output

- 11-9 covariance Function Query

- 11-10 covariance Function Stream Input

- 11-11 covariance Function Relation Output

- 11-12 geometricMean Function Query

- 11-13 geometricMean Function Stream Input

- 11-14 geometricMean Function Relation Output

- 11-15 geometricMean1 Function Query

- 11-16 geometricMean1 Function Stream Input

- 11-17 geometricMean1 Function Relation Output

- 11-18 harmonicMean Function Query

- 11-19 harmonicMean Function Stream Input

- 11-20 harmonicMean Function Relation Output

- 11-21 kurtosis Function Query

- 11-22 kurtosis Function Stream Input

- 11-23 kurtosis Function Relation Output

- 11-24 lag1 Function Query

- 11-25 lag1 Function Stream Input

- 11-26 lag1 Function Relation Output

- 11-27 mean Function Query

- 11-28 mean Function Stream Input

- 11-29 mean Function Relation Output

- 11-30 meanDeviation Function Query

- 11-31 meanDeviation Function Stream Input

- 11-32 meanDeviation Function Relation Output

- 11-33 median Function Query

- 11-34 median Function Stream Input

- 11-35 median Function Relation Output

- 11-36 moment Function Query

- 11-37 moment Function Stream Input

- 11-38 moment Function Relation Output

- 11-39 pooledMean Function Query

- 11-40 pooledMean Function Stream Input

- 11-41 pooledMean Function Relation Output

- 11-42 pooledVariance Function Query

- 11-43 pooledVariance Function Stream Input

- 11-44 pooledVariance Function Relation Output

- 11-45 product Function Query

- 11-46 product Function Stream Input

- 11-47 product Function Relation Output

- 11-48 quantile Function Query

- 11-49 quantile Function Stream Input

- 11-50 quantile Function Relation Output

- 11-51 quantileInverse Function Query

- 11-52 quantileInverse Function Stream Input

- 11-53 quantileInverse Function Relation Output

- 11-54 rankInterpolated Function Query

- 11-55 rankInterpolated Function Stream Input

- 11-56 rankInterpolated Function Relation Output

- 11-57 rms Function Query

- 11-58 rms Function Stream Input

- 11-59 rms Function Relation Output

- 11-60 sampleKurtosis Function Query

- 11-61 sampleKurtosis Function Stream Input

- 11-62 sampleKurtosis Function Relation Output

- 11-63 sampleKurtosisStandardError Function Query

- 11-64 sampleKurtosisStandardError Function Stream Input

- 11-65 sampleKurtosisStandardError Function Relation Output

- 11-66 sampleSkew Function Query

- 11-67 sampleSkew Function Stream Input

- 11-68 sampleSkew Function Relation Output

- 11-69 sampleSkewStandardError Function Query

- 11-70 sampleSkewStandardError Function Stream Input

- 11-71 sampleSkewStandardError Function Relation Output

- 11-72 sampleVariance Function Query

- 11-73 sampleVariance Function Stream Input

- 11-74 sampleVariance Function Relation Output

- 11-75 skew Function Query

- 11-76 skew Function Stream Input

- 11-77 skew Function Relation Output

- 11-78 standardDeviation Function Query

- 11-79 standardDeviation Function Stream Input

- 11-80 standardDeviation Function Relation Output

- 11-81 standardError Function Query

- 11-82 standardError Function Stream Input

- 11-83 standardError Function Relation Output

- 11-84 sumOfInversions Function Query

- 11-85 sumOfInversions Function Stream Input

- 11-86 sumOfInversions Function Relation Output

- 11-87 sumOfLogarithms Function Query

- 11-88 sumOfLogarithms Function Stream Input

- 11-89 sumOfLogarithms Function Relation Output

- 11-90 sumOfPowerDeviations Function Query

- 11-91 sumOfPowerDeviations Function Stream Input

- 11-92 sumOfPowerDeviations Function Relation Output

- 11-93 sumOfPowers Function Query

- 11-94 sumOfPowers Function Stream Input

- 11-95 sumOfPowers Function Relation Output

- 11-96 sumOfSquaredDeviations Function Query

- 11-97 sumOfSquaredDeviations Function Stream Input

- 11-98 sumOfSquaredDeviations Function Relation Output

- 11-99 sumOfSquares Function Query

- 11-100 sumOfSquares Function Stream Input

- 11-101 sumOfSquares Function Relation Output

- 11-102 trimmedMean Function Query

- 11-103 trimmedMean Function Stream Input

- 11-104 trimmedMean Function Relation Output

- 11-105 variance Function Query

- 11-106 variance Function Stream Input

- 11-107 variance Function Relation Output

- 11-108 weightedMean Function Query

- 11-109 weightedMean Function Stream Input

- 11-110 weightedMean Function Relation Output

- 11-111 winsorizedMean Function Query

- 11-112 winsorizedMean Function Stream Input

- 11-113 winsorizedMean Function Relation Output

- 12-1 abs Function Query

- 12-2 abs Function Stream Input

- 12-3 abs Function Stream Output

- 12-4 abs1 Function Query

- 12-5 abs1 Function Stream Input

- 12-6 abs1 Function Stream Output

- 12-7 abs2 Function Query

- 12-8 abs2 Function Stream Input

- 12-9 abs2 Function Stream Output

- 12-10 abs3 Function Query

- 12-11 abs3 Function Stream Input

- 12-12 abs3 Function Stream Output

- 12-13 acos Function Query

- 12-14 acos Function Stream Input

- 12-15 acos Function Stream Output

- 12-16 asin Function Query

- 12-17 asin Function Stream Input

- 12-18 asin Function Stream Output

- 12-19 atan Function Query

- 12-20 atan Function Stream Input

- 12-21 atan Function Stream Output

- 12-22 atan2 Function Query

- 12-23 atan2 Function Stream Input

- 12-24 atan2 Function Stream Output

- 12-25 cbrt Function Query

- 12-26 cbrt Function Stream Input

- 12-27 cbrt Function Stream Output

- 12-28 ceil1 Function Query

- 12-29 ceil1 Function Stream Input

- 12-30 ceil1 Function Stream Output

- 12-31 cos Function Query

- 12-32 cos Function Stream Input

- 12-33 cos Function Stream Output

- 12-34 cosh Function Query

- 12-35 cosh Function Stream Input

- 12-36 cosh Function Stream Output

- 12-37 exp Function Query

- 12-38 exp Function Stream Input

- 12-39 exp Function Stream Output

- 12-40 expm1 Function Query

- 12-41 expm1 Function Stream Input

- 12-42 expm1 Function Stream Output

- 12-43 floor1 Function Query

- 12-44 floor1 Function Stream Input

- 12-45 floor1 Function Stream Output

- 12-46 hypot Function Query

- 12-47 hypot Function Stream Input

- 12-48 hypot Function Stream Output

- 12-49 IEEERemainder Function Query

- 12-50 IEEERemainder Function Stream Input

- 12-51 IEEERemainder Function Stream Output

- 12-52 log1 Function Query

- 12-53 log1 Function Stream Input

- 12-54 log1 Function Stream Output

- 12-55 log101 Function Query

- 12-56 log101 Function Stream Input

- 12-57 log101 Function Stream Output

- 12-58 log1p Function Query

- 12-59 log1p Function Stream Input

- 12-60 log1p Function Stream Output

- 12-61 pow Function Query

- 12-62 pow Function Stream Input

- 12-63 pow Function Stream Output

- 12-64 rint Function Query

- 12-65 rint Function Stream Input

- 12-66 rint Function Stream Output

- 12-67 round Function Query

- 12-68 round Function Stream Input

- 12-69 round Function Stream Output

- 12-70 round1 Function Query

- 12-71 round1 Function Stream Input

- 12-72 round1 Function Stream Output

- 12-73 signum Function Query

- 12-74 signum Function Stream Input

- 12-75 signum Function Stream Output

- 12-76 signum1 Function Query

- 12-77 signum1 Function Stream Input

- 12-78 signum1 Function Relation Output

- 12-79 sin Function Query

- 12-80 sin Function Stream Input

- 12-81 sin Function Stream Output

- 12-82 sinh Function Query

- 12-83 sinh Function Stream Input

- 12-84 sinh Function Stream Output

- 12-85 sqrt Function Query

- 12-86 sqrt Function Stream Input

- 12-87 sqrt Function Stream Output

- 12-88 tan Function Query

- 12-89 tan Function Stream Input

- 12-90 tan Function Stream Output

- 12-91 tanh Function Query

- 12-92 tanh Function Stream Input

- 12-93 tanh Function Stream Output

- 12-94 todegrees Function Query

- 12-95 todegrees Function Stream Input

- 12-96 todegrees Function Stream Output

- 12-97 toradians Function Query

- 12-98 toradians Function Stream Input

- 12-99 toradians Function Stream Output

- 12-100 ulp Function Query

- 12-101 ulp Function Stream Input

- 12-102 ulp Function Stream Output

- 12-103 ulp1 Function Query

- 12-104 ulp1 Function Stream Input

- 12-105 ulp1 Function Relation Output

- 13-1 MyMod.java User-Defined Single-Row Function

- 13-2 Single-Row User Defined Function for an Oracle CQL Processor

- 13-3 Accessing a User-Defined Single-Row Function in Oracle CQL

- 13-4 Variance.java User-Defined Aggregate Function

- 13-5 Aggregate User Defined Function for an Oracle CQL Processor

- 13-6 Accessing a User-Defined Aggregate Function in Oracle CQL

- 15-1 Address.java Class

- 15-2 Event Type Repository

- 15-3 Channel

- 15-4 Oracle CQL Query

- 15-5 Address.java Class

- 15-6 Event Type Repository

- 15-7 Channel

- 15-8 Oracle CQL Query

- 16-1 EPN Assembly File: Oracle Spatial Namespace and Schema Location

- 16-2 spatial:context Element in EPN Assembly File

- 16-3 Referencing spatial:context in an Oracle CQL Query

- 16-4 Oracle CEP Event Using Oracle Spatial Types

- 16-5 Oracle CQL Query Using Oracle Spatial Geometry Types

- 16-6 Creating a Point Geometry Using a Geometry Static Method

- 16-7 Accessing Geometry Type Public Methods and Fields

- 16-8 Using Geometry Relation Operators

- 16-9 Using Geometry Filter Operators

- 16-10 Using the Default Geodetic Coordinates in an Oracle CQL Query

- 16-11 spatial:context Element in EPN Assembly File

- 16-12 Referencing spatial:context in an Oracle CQL Query

- 16-13 Oracle CQL Query Using Geometric Relation Operator ANYINTERACT

- 16-14 Oracle CQL Query Using Geometry.bufferPolygon

- 16-15 Oracle CQL Query Using Geometric Relation Operator CONTAIN

- 16-16 Oracle CQL Query Using Geometry.createElemInfo

- 16-17 Oracle CQL Query Using Geometry.createGeometry

- 16-18 Oracle CQL Query Using Geometry.createLinearPolygon

- 16-19 Oracle CQL Query Using Geometry.createPoint

- 16-20 Oracle CQL Query Using Geometry.createRectangle

- 16-21 Oracle CQL Query Using Geometry.distance

- 16-22 Oracle CQL Query Using Oracle Spatial Geometry Types

- 16-23 Oracle CQL Query Using Geometric Relation Operator FILTER

- 16-24 Oracle CQL Query Using Geometry.get2dMbr

- 16-25 Oracle CQL Query Using Geometric Relation Operator INSIDE

- 16-26 Oracle CQL Query Using Geometric Relation Operator NN

- 16-27 Oracle CQL Query Using Oracle Spatial Geometry Types

- 16-28 Oracle CQL Query Using Geometry.to_Geometry

- 16-29 Oracle CQL Query Using Geometry.to_JGeometry

- 16-30 Oracle CQL Query Using Geometric Relation Operator WITHINDISTANCE

- 17-1 Oracle CEP JDBC Data Cartridge SQL Statement

- 17-2 EPN Assembly File: Oracle CEP JDBC Data Cartridge Namespace and Schema Location

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

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

- 17-5 Example return-component-type Class

- 17-6 Oracle CEP JDBC Data Cartridge SQL Statement

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

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

- 17-9 Oracle CEP JDBC Data Cartridge Complex JDBC Cartridge Context Function

- 17-10 Oracle JDBC Data Cartridge Context Function Overloading

- 17-11 Oracle CEP JDBC Data Cartridge Context Function

- 17-12 Oracle CEP JDBC Data Cartridge SQL Statement

- 17-13 Oracle CQL Query Invoking an Oracle CEP JDBC Data Cartridge Context Function

- 17-14 Example return-component-type Class

- 17-15 CQL Type bigint as a return-component-type

- 17-16 getOrderAmt Function in a CQL Query

- 18-1 Typical Oracle CQL Query

- 18-2 Fully Qualified Stream Element Names

- 18-3 Simple Query

- 18-4 Built-In Window Query

- 18-5 MATCH_RECOGNIZE Query

- 18-6 XMLTABLE Query

- 18-7 Data Cartridge TABLE Query

- 18-8 Invalid Data Cartridge TABLE Query

- 18-9 DIFFERENCE USING Clause

- 18-10 Specifying the usinglist in a DIFFERENCE USING Clause

- 18-11 Parameterized Oracle CQL Query

- 18-12 Equivalent Queries at Runtime

- 18-13 Using Views Instead of Subqueries

- 18-14 Using View Names to Distinguish Between Stream Elements of the Same Name

- 18-15 Schema With Event Attribute Names Only

- 18-16 Fully Qualified Stream Element Names

- 18-17 Inner Joins

- 18-18 Outer Joins

- 18-19 Left Outer Joins

- 18-20 Right Outer Joins

- 18-21 Outer Join Look-Back

- 19-1 Pattern Matching With MATCH_RECOGNIZE

- 19-2 MATCH_RECOGNIZE and the WHERE Clause

- 19-3 Implied Running Aggregate

- 19-4 Invalid Use of Aggregate Function

- 19-5 Referencing a Variable That has not Been Matched Yet: Invalid

- 19-6 Referencing a Variable That has not Been Matched Yet: Valid

- 19-7 Referencing Attributes not Qualified by Correlation Variable

- 19-8 MATCH_RECOGNIZE Query Using count(A.*)

- 19-9 Use of the prev Function: Valid

- 19-10 Use of the prev Function: Invalid

- 19-11 Using Functions Over Correlation Variables

- 19-12 Using Functions Over Correlation Variables: to_timestamp

- 19-13 Using Functions Over Correlation Variables: count

- 19-14 Undefined Correlation Name

- 19-15 Referencing One Correlation Variable From Another

- 19-16 Input Stream S1

- 19-17 MATCH_RECOGNIZE Query Using Input Stream S1

- 19-18 ALL MATCHES Clause Query

- 19-19 ALL MATCHES Clause Stream Input

- 19-20 ALL MATCHES Clause Stream Output

- 19-21 MATCH_RECOGNIZE with Fixed Duration DURATION Clause Query

- 19-22 MATCH_RECOGNIZE with Fixed Duration DURATION Clause Stream Input

- 19-23 MATCH_RECOGNIZE with Fixed DURATION Clause Stream Output

- 19-24 MATCH_RECOGNIZE with Variable Duration DURATION MULTIPLES OF Clause Query

- 19-25 MATCH_RECOGNIZE with Variable Duration DURATION MULTIPLES OF Clause Stream Input

- 19-26 MATCH_RECOGNIZE with Variable Duration DURATION MULTIPLES OF Clause Stream Output

- 19-27 MATCH_RECOGNIZE with SUBSET Clause Query

- 19-28 MATCH_RECOGNIZE with SUBSET Clause Stream Input

- 19-29 MATCH_RECOGNIZE with SUBSET Clause Stream Output

- 19-30 Simple Pattern Detection: Query

- 19-31 Pattern Detection With PARTITION BY: Query

- 19-32 Pattern Detection With Aggregates: Query

- 19-33 Pattern Detection With Aggregates: Stream Input

- 19-34 Pattern Detection With Aggregates: Stream Output

- 19-35 PATTERN Clause and WITHIN Clause

- 19-36 PATTERN Clause and WITHIN INCLUSIVE Clause

- 19-37 Pattern Detection With the WITHIN Clause: Stream Input

- 19-38 Fixed Duration Non-Event Detection: Query

- 20-1 Query in a <query></query> Element

- 20-2 Specifying the usinglist in a DIFFERENCE USING Clause

- 20-3 REGISTER QUERY

- 20-4 HAVING Query

- 20-5 HAVING Stream Input

- 20-6 HAVING Relation Output

- 20-7 Set Operators: UNION Query

- 20-8 Set Operators: UNION Relation Input R1

- 20-9 Set Operators: UNION Relation Input R2

- 20-10 Set Operators: UNION Relation Output

- 20-11 Set Operators: UNION ALL Relation Output

- 20-12 Set Operators: INTERSECT Query

- 20-13 Set Operators: INTERSECT Relation Input R1

- 20-14 Set Operators: INTERSECT Relation Input R2

- 20-15 Set Operators: INTERSECT Relation Output

- 20-16 Set Operators: MINUS Query

- 20-17 Set Operators: MINUS Relation Input R1

- 20-18 Set Operators: MINUS Relation Input R2

- 20-19 Set Operators: MINUS Relation Output

- 20-20 IN and NOT IN as a Set Operation: Query

- 20-21 IN and NOT IN as a Set Operation: Stream S3 Input

- 20-22 IN and NOT IN as a Set Operation: Stream S4 Input

- 20-23 IN and NOT IN as a Set Operation: Relation Output

- 20-24 Select DISTINCT Query

- 20-25 Select DISTINCT Stream Input

- 20-26 Select DISTINCT Stream Output

- 20-27 XMLTABLE Query

- 20-28 XMLTABLE Stream Input

- 20-29 XMLTABLE Relation Output

- 20-30 XMLTABLE With XML Namespaces Query

- 20-31 XMLTABLE With XML Namespaces Stream Input

- 20-32 XMLTABLE With XML Namespaces Relation Output

- 20-33 MyCartridge Method getIterator

- 20-34 TABLE Query: Iterator

- 20-35 TABLE Query Stream Input: Iterator

- 20-36 TABLE Query Output: Iterator

- 20-37 MyCartridge Method getArray

- 20-38 TABLE Query: Array

- 20-39 TABLE Query Stream Input: Array

- 20-40 TABLE Query Output: Array

- 20-41 MyCartridge Method getCollection

- 20-42 TABLE Query: Collection

- 20-43 TABLE Query Stream Input: Collection

- 20-44 TABLE Query Output: Collection

- 20-45 ORDER BY ROWS Query

- 20-46 ORDER BY ROWS Stream Input

- 20-47 ORDER BY ROWS Output

- 20-48 View in a <view></view> Element

- 20-49 REGISTER VIEW

List of Figures

- 1-1 Oracle CEP Architecture

- 1-2 Stream in the Event Processing Network

- 1-3 Range and Slide: Equal (Steady-State Condition)

- 1-4 Range and Slide: Different (Steady-State Condition)

- 1-5 Event Sources and Event Sinks in the Event Processing Network

- 1-6 Oracle CEP IDE for Eclipse

- 1-7 Oracle CEP Visualizer

- 10-1 cern.jet.stat.Gamma beta

- 10-2 cern.jet.stat.Probability beta1

- 10-3 Definition of binomial coefficient

- 10-4 cern.jet.stat.Probability binomial2

- 10-5 cern.jet.stat.Probability binomialComplemented

- 10-6 cern.jet.stat.Probability chiSquare

- 10-7 cern.jet.stat.Probability errorFunction

- 10-8 cern.jet.stat.Probability errorfunctioncompelemented

- 10-9 cern.jet.stat.Probability gamma1

- 10-10 cern.jet.stat.Probability gammaComplemented

- 10-11 cern.jet.math.Arithmetic log

- 10-12 cern.jet.stat.Probability negativeBinomial

- 10-13 cern.jet.stat.Probability negativeBinomialComplemented

- 10-14 cern.jet.stat.Probability normal

- 10-15 cern.jet.stat.Probability normal1

- 10-16 cern.jet.stat.Probability poisson

- 10-17 cern.jet.stat.Probability poissonComplemented

- 10-18 cern.jet.math.Arithmetic stirlingCorrection

- 10-19 cern.jet.stat.Probability studentT

- 11-1 cern.jet.stat.Descriptive.covariance

- 11-2 cern.jet.stat.Descriptive.geometricMean(DoubleArrayList data)

- 11-3 cern.jet.stat.Descriptive.geometricMean1(int size, double sumOfLogarithms)

- 11-4 cern.jet.stat.Descriptive.kurtosis(DoubleArrayList data, double mean, double standardDeviation)

- 11-5 cern.jet.stat.Descriptive.mean(DoubleArrayList data)

- 11-6 cern.jet.stat.Descriptive.meanDeviation(DoubleArrayList data, double mean)

- 11-7 cern.jet.stat.Descriptive.moment(DoubleArrayList data, int k, double c)

- 11-8 cern.jet.stat.Descriptive.pooledMean(int size1, double mean1, int size2, double mean2)

- 11-9 cern.jet.stat.Descriptive.pooledVariance(int size1, double variance1, int size2, double variance2)

- 11-10 cern.jet.stat.Descriptive.product(DoubleArrayList data)

- 11-11 cern.jet.stat.Descriptive.rms(int size, double sumOfSquares)

- 11-12 cern.jet.stat.Descriptive.sampleVariance(DoubleArrayList data, double mean)

- 11-13 cern.jet.stat.Descriptive.skew(DoubleArrayList data, double mean, double standardDeviation)

- 11-14 cern.jet.stat.Descriptive.cern.jet.stat.Descriptive.standardError(int size, double variance)

- 11-15 cern.jet.stat.Descriptive.sumOfInversions(DoubleArrayList data, int from, int to)

- 11-16 cern.jet.stat.Descriptive.sumOfLogarithms(DoubleArrayList data, int from, int to)

- 11-17 cern.jet.stat.Descriptive.sumOfPowerDeviations(DoubleArrayList data, int k, double c)

- 11-18 cern.jet.stat.Descriptive.sumOfPowers(DoubleArrayList data, int k)

- 11-19 cern.jet.stat.Descriptive.sumOfSquaredDeviations(int size, double variance)

- 11-20 cern.jet.stat.Descriptive.sumOfSquares(DoubleArrayList data)

- 11-21 cern.jet.stat.Descriptive.variance(int size, double sum, double sumOfSquares)

- 11-22 cern.jet.stat.Descriptive.weightedMean(DoubleArrayList data, DoubleArrayList weights)

- 12-1 java.lang.Math Expm1

- 12-2 java.lang.Math hypot

- 15-1 Example Oracle CEP Applications

- 18-1 Navigating to the Configuration Source of a Processor from the EPN Editor

- 18-2 Editing the Configuration Source for a Processor

- 19-1 Pattern Detection: Double Bottom Stock Fluctuations

- 19-2 Pattern Detection With Partition By: Stock Fluctuations

- 19-3 Fixed Duration Non-Event Detection

List of Tables

- 1-1 Default Range and Tuple-Based Stream-to-Relation Operators

- 1-2 Range, Rows, and Slide at Query Start-Up and Empty Relations

- 2-1 Oracle CQL Built-in Datatype Summary

- 2-2 Implicit Type Conversion Matrix

- 2-3 Explicit Type Conversion Matrix

- 2-4 Datetime Format Models

- 2-5 Conditions Containing Nulls

- 4-1 Oracle CQL Operator Precedence

- 4-2 Arithmetic Operators

- 4-3 Concatenation Operator

- 4-4 Alternation Operator

- 4-5 DStream Example Output

- 5-1 Return Values for COUNT Aggregate Function

- 6-1 Oracle CQL Condition Precedence

- 6-2 Comparison Conditions

- 6-3 Logical Conditions

- 6-4 NOT Truth Table

- 6-5 AND Truth Table

- 6-6 OR Truth Table

- 6-7 XOR Truth Table

- 6-8 LIKE Conditions

- 6-9 Range Conditions

- 6-10 Null Conditions

- 8-1 Oracle CQL Built-in Single-Row Functions

- 9-1 Oracle CQL Built-in Aggregate Functions

- 9-2 Return Values for COUNT Aggregate Function

- 10-1 Oracle CQL Built-in Single-Row Colt-Based Functions

- 10-2 cern.jet.math.Arithmetic binomial Return Values

- 10-3 cern.jet.math.Arithmetic Binomial1 Return Values

- 11-1 Oracle CQL Built-in Aggregate Colt-Based Functions

- 12-1 Oracle CQL Built-in java.lang.Math Functions

- 13-1 User-Defined Function Datatypes

- 15-1 Class Accessibility by Class Loading Policy

- 15-2 Oracle Java Data Cartridge: Oracle CQL to Java Datatype Mapping

- 15-3 Oracle Java Data Cartridge: Java Datatype to Oracle CQL Mapping

- 16-1 Oracle Spatial Scope

- 16-2 Oracle Spatial Geometry Types

- 16-3 Oracle Spatial Coordinate Systems

- 16-4 Oracle Spatial Geometry Methods

- 16-5 SDO_ETYPE and SDO_INTERPRETATION

- 16-6 SDO_ETYPE and SDO_INTERPRETATION

- 17-1 function Element Attributes

- 17-2 param Element Attributes

- 17-3 SQL Column Types and Oracle CEP Type Equivalents

- 18-1 DIFFERENCE USING Clause Affect on IStream

- 18-2 Parameterized Query Parameter Value Lexical Conventions

- 19-1 Return Values for count Aggregate Function

- 19-2 MATCH_RECOGNIZE Pattern Quantifiers

- 19-3 WITHIN and WITHIN INCLUSIVE Query Output

Oracle® Fusion Middleware

CQL Language Reference for Oracle Complex Event Processing

11g Release 1 (11.1.1.6.0)

E12048-06

November 2011

Documentation for developers that provides a reference to Oracle Continuous Query Language (Oracle CQL), an SQL-like language for querying streaming data in Oracle Complex Event Processing (Oracle CEP) applications.

Oracle Fusion Middleware CQL Language Reference for Oracle Complex Event Processing, 11g Release 1 (11.1.1.6.0)

E12048-06

Copyright © 2006, 2011, Oracle and/or its affiliates. All rights reserved.

Primary Author: Steve Traut, Peter Purich

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Preface

This reference contains a complete description of the Oracle Continuous Query Language (Oracle CQL), a query language based on SQL with added constructs that support streaming data. Using Oracle CQL, you can express queries on data streams to perform complex event processing (CEP) using Oracle CEP. Oracle CQL is a new technology but it is based on a subset of SQL99.

Oracle CEP (formally known as the WebLogic Event Server) is a Java server for the development of high-performance event driven applications. It is a lightweight Java application container based on Equinox OSGi, with shared services, including the Oracle CEP Service Engine, which provides a rich, declarative environment based on Oracle Continuous Query Language (Oracle CQL) - a query language based on SQL with added constructs that support streaming data - to improve the efficiency and effectiveness of managing business operations. Oracle CEP supports ultra-high throughput and microsecond latency using JRockit Real Time and provides Oracle CEP Visualizer and Oracle CEP IDE for Eclipse developer tooling for a complete real time end-to-end Java Event-Driven Architecture (EDA) development platform.

Audience

This document is intended for all users of Oracle CQL.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Related Documents

For more information, see the following:

- Oracle Fusion Middleware Getting Started Guide for Oracle Complex Event Processing

- Oracle Fusion Middleware Administrator's Guide for Oracle Complex Event Processing

- Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse

- Oracle Fusion Middleware Visualizer User's Guide for Oracle Complex Event Processing

- Oracle Complex Event Processing Java API Reference

- Oracle Fusion Middleware EPL Language Reference for Oracle Complex Event Processing

- Oracle Database SQL Language Reference at http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/toc.htm

- SQL99 Specifications (ISO/IEC 9075-1:1999, ISO/IEC 9075-2:1999, ISO/IEC 9075-3:1999, and ISO/IEC 9075-4:1999)

- Oracle CEP Forum: http://forums.oracle.com/forums/forum.jspa?forumID=820

- Oracle CEP Samples: http://www.oracle.com/technologies/soa/complex-event-processing.html

- Oracle Event Driven Architecture Suite sample code: http://www.oracle.com/technology/sample_code/products/event-driven-architecture

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

Syntax Diagrams

Syntax descriptions are provided in this book for various Oracle CQL, SQL, PL/SQL, or other command-line constructs in graphic form or Backus Naur Form (BNF). See "How to Read Syntax Diagrams" in the Oracle Database SQL Language Reference for information about how to interpret these descriptions.

Part I

Understanding Oracle CQL

This part contains the following chapters:

- Chapter 1, "Introduction to Oracle CQL"

- Chapter 2, "Basic Elements of Oracle CQL"

- Chapter 3, "Pseudocolumns"

- Chapter 4, "Operators"

- Chapter 5, "Expressions"

- Chapter 6, "Conditions"

- Chapter 7, "Common Oracle CQL DDL Clauses"

1 Introduction to Oracle CQL

This chapter introduces Oracle Continuous Query Language (Oracle CQL), a query language based on SQL with added constructs that support streaming data. Using Oracle CQL, you can express queries on data streams with Oracle Complex Event Processing (Oracle CEP).

Oracle CEP (formally known as the WebLogic Event Server) is a Java server for the development of high-performance event driven applications. It is a lightweight Java application container based on Equinox OSGi, with shared services, including the Oracle CEP Service Engine, which provides a rich, declarative environment based on Oracle CQL to improve the efficiency and effectiveness of managing business operations. Oracle CEP supports ultra-high throughput and microsecond latency using JRockit Real Time and provides Oracle CEP Visualizer and Oracle CEP IDE for Eclipse developer tooling for a complete real time end-to-end Java Event-Driven Architecture (EDA) development platform.

- Section 1.1, "Fundamentals of Oracle CQL"

- Section 1.2, "Oracle CQL Statements"

- Section 1.3, "Oracle CQL and SQL Standards"

- Section 1.4, "Oracle CEP Server and Tools Support"

1.1 Fundamentals of Oracle CQL

Databases are best equipped to run queries over finite stored data sets. However, many modern applications require long-running queries over continuous unbounded sets of data. By design, a stored data set is appropriate when significant portions of the data are queried repeatedly and updates are relatively infrequent. In contrast, data streams represent data that is changing constantly, often exclusively through insertions of new elements. It is either unnecessary or impractical to operate on large portions of the data multiple times.

Many types of applications generate data streams as opposed to data sets, including sensor data applications, financial tickers, network performance measuring tools, network monitoring and traffic management applications, and clickstream analysis tools. Managing and processing data for these types of applications involves building data management and querying capabilities with a strong temporal focus.

To address this requirement, Oracle introduces Oracle CEP, a data management infrastructure that supports the notion of streams of structured data records together with stored relations.

To provide a uniform declarative framework, Oracle offers Oracle Continuous Query Language (Oracle CQL), a query language based on SQL with added constructs that support streaming data.

Oracle CQL is designed to be:

- Scalable with support for a large number of queries over continuous streams of data and traditional stored data sets.

- Comprehensive to deal with complex scenarios. For example, through composability, you can create various intermediate views for querying.

Figure 1-1 shows a simplified view of the Oracle CEP architecture. Oracle CEP server provides the light-weight Spring container for Oracle CEP applications. The Oracle CEP application shown is composed of an event adapter that provides event data to an input channel. The input channel is connected to an Oracle CQL processor associated with one or more Oracle CQL queries that operate on the events offered by the input channel. The Oracle CQL processor is connected to an output channel to which query results are written. The output channel is connected to an event Bean: a user-written Plain Old Java Object (POJO) that takes action based on the events it receives from the output channel.

Figure 1-1 Oracle CEP Architecture

Using Oracle CEP, you can define event adapters for a variety of data sources including JMS, relational database tables, and files in the local filesystem. You can connect multiple input channels to an Oracle CQL processor and you can connect an Oracle CQL processor to multiple output channels. You can connect an output channel to another Oracle CQL processor, to an adapter, to a cache, or an event Bean.

Using Oracle CEP IDE for Eclipse and Oracle CEP Visualizer, you:

- Create an Event Processing Network (EPN) as Figure 1-1 shows.

- Associate one more Oracle CQL queries with the Oracle CQL processors in your EPN.

- Package your Oracle CEP application and deploy it to Oracle CEP server for execution.

Consider the typical Oracle CQL statements that Example 1-1 shows.

Example 1-1 Typical Oracle CQL Statements

<?xml version="1.0" encoding="UTF-8"?>
<n1:config xsi:schemaLocation="http://www.bea.com/ns/wlevs/config/application wlevs_application_config.xsd"
xmlns:n1="http://www.bea.com/ns/wlevs/config/application"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<processor>
<name>cqlProcessor</name>
<rules>

The script content on this page is for navigation purposes only and does not alter the content in any way.

2 Basic Elements of Oracle CQL

This chapter provides a reference for fundamental parts of Oracle Continuous Query Language (Oracle CQL), including datatypes, literals, nulls, and more. Oracle CQL is the query language used in Oracle Complex Event Processing (Oracle CEP) applications.

The basic elements of Oracle CQL include:

- Section 2.1, "Datatypes"

- Section 2.2, "Datatype Comparison Rules"

- Section 2.3, "Literals"

- Section 2.4, "Format Models"

- Section 2.5, "Nulls"

- Section 2.6, "Comments"

- Section 2.7, "Aliases"

- Section 2.8, "Schema Object Names and Qualifiers"

Before using the statements described in Part IV, "Using Oracle CQL", you should familiarize yourself with the concepts covered in this chapter.

2.1 Datatypes

Each value manipulated by Oracle CEP has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle CEP to treat values of one datatype differently from values of another. For example, you can add values of INTEGER datatype, but not values of CHAR datatype.When you create a stream, you must specify a datatype for each of its elements. When you create a user-defined function, you must specify a datatype for each of its arguments. These datatypes define the domain of values that each element can contain or each argument can have. For example, attributes with TIMESTAMP as datatype cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'.Oracle CQL provides a number of built-in datatypes that you can use. The syntax of Oracle CQL datatypes appears in the diagrams that follow.

If Oracle CQL does not support a datatype that your events use, you can use an Oracle CQL data cartridge or a user-defined function to evaluate that datatype in an Oracle CQL query.

For more information, see:

- Section 2.1.1, "Oracle CQL Built-in Datatypes"

- Section 2.1.2, "Handling Other Datatypes Using Oracle CQL Data Cartridges"

- Section 2.1.3, "Handling Other Datatypes Using a User-Defined Function"

- Section 2.2, "Datatype Comparison Rules"

- Section 2.3, "Literals"

- Section 2.4, "Format Models"

- Section 2.7.2.1, "How to Define a Data Type Alias Using the Aliases Element"

datatype::=

variable_length_datatype::=

fixed_length_datatype::=

2.1.1 Oracle CQL Built-in Datatypes

Table 2-1 summarizes Oracle CQL built-in datatypes. Refer to the syntax in the preceding sections for the syntactic elements.

Consider these datatype and datatype literal restrictions when defining event types. For more information, see "Creating Oracle CEP Event Types" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.

Table 2-1 Oracle CQL Built-in Datatype Summary

Oracle CQL Datatype

Description

BIGINT

Fixed-length number equivalent to a Java Long type.

For more information, see Section 2.3.2, "Numeric Literals".

BOOLEAN

Fixed-length boolean equivalent to a Java Boolean type. Valid values are true or false.

BYTE[(size)]Foot 1

Variable-length character data of length size bytes. Maximum size is 4096 bytes. Default and minimum size is 1 byte.

For more information, see Section 2.3.2, "Numeric Literals".

CHAR[(size)]Footref 1

Variable-length character data of length size characters. Maximum size is 4096 characters. Default and minimum size is 1 character.

For more information, see Section 2.3.1, "Text Literals".

DOUBLE

Fixed-length number equivalent to a Java double type.

For more information, see Section 2.3.2, "Numeric Literals".

FLOAT

Fixed-length number equivalent to a Java float type.

For more information, see Section 2.3.2, "Numeric Literals".

INTEGER

Fixed-length number equivalent to a Java int type.

For more information, see Section 2.3.2, "Numeric Literals".

INTERVAL

Fixed-length INTERVAL datatype specifies a period of time. Oracle CEP supports DAY TO SECOND. Maximum length is 64 bytes.

For more information, see Section 2.3.4, "Interval Literals".

TIMESTAMP

Fixed-length TIMESTAMP datatype stores a datetime literal that conforms to one of the java.text.SimpleDateFormat format models that Oracle CQL supports. Maximum length is 64 bytes.

For more information, see Section 2.3.3, "Datetime Literals".

XMLTYPE

Use this datatype for stream elements that contain XML data. Maximum length is 4096 characters.

XMLTYPE is a system-defined type, so you can use it as an argument of a function or as the datatype of a stream attribute.

For more information, see "SQL/XML (SQLX)".

OBJECT

This stands for any Java object (that is, any subclass of java.lang.Object).

We refer to this as opaque type support in Oracle CEP since the Oracle CEP engine does not understand the contents of an OBJECT field.

You typically use this type to pass values, from an adapter to its destination, as-is; these values need not be interpreted by the Oracle CEP engine (such as Collection types or any other user-specific Java type) but that are associated with the event whose other fields are referenced in a query.

Footnote 1 Oracle CQL supports single-dimension arrays only.

2.1.2 Handling Other Datatypes Using Oracle CQL Data Cartridges

If your event uses a datatype that Oracle CQL does not support, you can use an Oracle CQL data cartridge to evaluate that datatype in an Oracle CQL query.

Oracle CQL includes the following data cartridges:

- Chapter 15, "Oracle Java Data Cartridge"

- Chapter 16, "Oracle Spatial"

- Chapter 17, "Oracle CEP JDBC Data Cartridge"

For more information, see Chapter 14, "Introduction to Data Cartridges".

2.1.3 Handling Other Datatypes Using a User-Defined Function

If your event uses a datatype that Oracle CQL does not support, you can create a user-defined function to evaluate that datatype in an Oracle CQL query.

Consider the enum datatype that Example 2-1 shows. The event that Example 2-2 shows uses this enum datatype. Oracle CQL does not support enum datatypes.

Example 2-1 Enum Datatype ProcessStatus

package com.oracle.app;

public enum ProcessStatus {
OPEN(1),
CLOSED(0)}
}

Example 2-2 Event Using Enum Datatype ProcessStatus

package com.oracle.app;

import com.oracle.capp.ProcessStatus;

public class ServiceOrder {
private String serviceOrderId;
private String electronicSerialNumber;
private ProcessStatus status;
...
}

By creating the user-defined function that Example 2-3 shows and registering the function in your application assembly file as Example 2-4 shows, you can evaluate this enum datatype in an Oracle CQL query as Example 2-5 shows.

Example 2-3 User-Defined Function to Evaluate Enum Datatype

package com.oracle.app;

import com.oracle.capp.ProcessStatus;
public class CheckIfStatusClosed {
public boolean execute(Object[] args) {
ProcessStatus arg0 = (ProcessStatus)args[0];
if (arg0 == ProcessStatus.OPEN)
return Boolean.FALSE;
else
return Boolean.TRUE;
}
}

Example 2-4 Registering the User-Defined Function in Application Assembly File

<wlevs:processor id="testProcessor">
<wlevs:listener ref="providerCache"/>
<wlevs:listener ref="outputCache"/>
<wlevs:cache-source ref="testCache"/>
<wlevs:function function-name="statusClosed" exec-method=”execute” />
<bean class="com.oracle.app.CheckIfStatusClosed"/>
</wlevs:function>
</wlevs:processor>

Example 2-5 Using the User-Defined Function to Evaluate Enum Datatype in an Oracle CQL Query

3 Pseudocolumns

This chapter provides a reference for Oracle Continuous Query Language (Oracle CQL) pseudocolumns, which you can query for but which are not part of the data from which an event was created.

- Section 3.1, "Introduction to Pseudocolumns"

- Section 3.2, "ELEMENT_TIME Pseudocolumn"

3.1 Introduction to Pseudocolumns

You can select from pseudocolumns, but you cannot modify their values. A pseudocolumn is also similar to a function without arguments (see Section 1.1.11, "Functions").

Oracle CQL supports the following pseudocolumns:

- Section 3.2, "ELEMENT_TIME Pseudocolumn"

3.2 ELEMENT_TIME Pseudocolumn

Every stream element of a base stream or derived stream (a view that evaluates to a stream) has an associated element time.The ELEMENT_TIME pseudo column returns this time as an Oracle CQL native type bigint.

Note:

ELEMENT_TIME is not supported on members of an Oracle CQL relation. For more information, see Section 1.1.1, "Streams and Relations".

This section describes:

- Section 3.2.1, "Understanding the Value of the ELEMENT_TIME Pseudocolumn"

- Section 3.2.2, "Using the ELEMENT_TIME Pseudocolumn in Oracle CQL Queries"

For more information, see:

- pseudo_column::=

- "to_timestamp"

3.2.1 Understanding the Value of the ELEMENT_TIME Pseudocolumn

The value of the ELEMENT_TIME pseudocolumn depends on whether or not you configure the stream element's channel as system- or application-timestamped.

3.2.1.1 ELEMENT_TIME for a System-Timestamped Stream

In this case, the element time for a stream element is assigned by the Oracle CEP system in such a way that subtracting two values of system-assigned time will give a duration that roughly matches the elapsed wall clock time.

For more information, see "System-Timestamped Channels" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.

3.2.1.2 ELEMENT_TIME for an Application-Timestamped Stream

In this case, the associated element time is assigned by the application using the application assembly file wlevs:expression element to specify a derived timestamp expression.

Oracle CEP processes the result of this expression as follows:

- Section 3.2.1.2.1, "Dervied Timestamp Expression Evalutes to int or bigint"

- Section 3.2.1.2.2, "Dervied Timestamp Expression Evalutes to timestamp"

For more information, see "Application-Timestamped Channels" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.

3.2.1.2.1 Dervied Timestamp Expression Evalutes to int or bigint

If the dervied timestamp expression evaluates to an Oracle CQL native type of int, then it is cast to and returned as a corresponding bigint value. If the expression evaluates to an Oracle CQL native type of bigint, that value is returned as is.

3.2.1.2.2 Dervied Timestamp Expression Evalutes to timestamp

If the derived timestamp expression evaluates to an Oracle CQL native type of timestamp, it is converted to a long value by expressing this time value as the number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT.

3.2.2 Using the ELEMENT_TIME Pseudocolumn in Oracle CQL Queries

This section describes how to use ELEMENT_TIME in various queries, including:

- Section 3.2.2.1, "Using ELEMENT_TIME With SELECT"

- Section 3.2.2.2, "Using ELEMENT_TIME With GROUP BY"

- Section 3.2.2.3, "Using ELEMENT_TIME With PATTERN"

3.2.2.1 Using ELEMENT_TIME With SELECT

Example 3-1 shows how you can use the ELEMENT_TIME pseudocolumn in a select statement. Stream S1 has schema (c1 integer). Given the input stream that Example 3-2 shows, this query returns the results that Example 3-3 shows. Note that the function to_timestamp is used to convert the Long values to timestamp values.

Example 3-1 ELEMENT_TIME Pseudocolumn in a Select Statement

4 Operators

This chapter provides a reference for operators in Oracle Continuous Query Language (Oracle CQL). An operator manipulates data items and returns a result. Syntactically, an operator appears before or after an operand or between two operands.

- Section 4.1, "Introduction to Operators"

4.1 Introduction to Operators

Operators manipulate individual data items called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*).

Oracle CQL provides the following operators:

- "Arithmetic Operators"

- "Concatenation Operator"

- "Alternation Operator"

- "Range-Based Stream-to-Relation Window Operators"

- "Tuple-Based Stream-to-Relation Window Operators"

- "Partitioned Stream-to-Relation Window Operators"

- "IStream Relation-to-Stream Operator"

- "DStream Relation-to-Stream Operator"

- "RStream Relation-to-Stream Operator"

4.1.1 What You May Need to Know About Unary and Binary Operators

The two general classes of operators are:

- unary: A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:

operator operand

- binary: A binary operator operates on two operands. A binary operator appears with its operands in this format:

operand1 operator operand2

Other operators with special formats accept more than two operands. If an operator is given a null operand, then the result is always null. The only operator that does not follow this rule is concatenation (||).

4.1.2 What You May Need to Know About Operator Precedence

Precedence is the order in which Oracle CEP evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle CEP evaluates operators with higher precedence before evaluating those with lower precedence. Oracle CEP evaluates operators with equal precedence from left to right within an expression.

Table 4-1 lists the levels of precedence among Oracle CQL operators from high to low. Operators listed on the same line have the same precedence.

Table 4-1 Oracle CQL Operator Precedence

Operator

Operation

+, - (as unary operators)

Identity, negation

*, /

Multiplication, division

+, - (as binary operators), ||

Addition, subtraction, concatenation

Oracle CQL conditions are evaluated after Oracle CQL operators

See Chapter 6, "Conditions"

Precedence Example In the following expression, multiplication has a higher precedence than addition, so Oracle first multiplies 2 by 3 and then adds the result to 1.

1+2*3

You can use parentheses in an expression to override operator precedence. Oracle evaluates expressions inside parentheses before evaluating those outside.

Arithmetic Operators

Table 4-2 lists arithmetic operators that Oracle CEP supports. You can use an arithmetic operator with one or two arguments to negate, add, subtract, multiply, and divide numeric values. Some of these operators are also used in datetime and interval arithmetic. The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype.

In certain cases, Oracle CEP converts the arguments to the datatype as required by the operation. For example, when an integer and a float are added, the integer argument is converted to a float. The datatype of the resulting expression is a float. For more information, see "Implicit Datatype Conversion".

Table 4-2 Arithmetic Operators

Operator

Purpose

Example

+ -

When these denote a positive or negative expression, they are unary operators.

5 Expressions

This chapter provides a reference to expressions in Oracle Continuous Query Language (Oracle CQL). An expression is a combination of one or more values and one or more operations, including a constant having a definite value, a function that evaluates to a value, or an attribute containing a value.

Every expression maps to a datatype. This simple expression evaluates to 4 and has datatype NUMBER (the same datatype as its components):

2*2

The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR datatype:

TO_CHAR(TRUNC(SYSDATE+7))

5.1 Introduction to Expressions

Oracle CEP provides the following expressions:

- Aggregate distinct expressions: "aggr_distinct_expr".

- Aggregate expressions: "aggr_expr".

- Arithmetic expressions: "arith_expr".

- Arithmetic expression list: "arith_expr_list".

- Case expressions: "case_expr".

- Decode expressions: "decode".

- Function expressions: "func_expr".

- Object expressions: "object_expr"

- Order expressions: "order_expr".

- XML aggregate expressions: "xml_agg_expr"

- XML column attribute value expressions: "xmlcolattval_expr".

- XML element expressions: "xmlelement_expr".

- XML forest expressions: "xmlforest_expr".

- XML parse expressions: "xml_parse_expr"

You can use expressions in:

- The select list of the SELECT statement

- A condition of the WHERE clause and HAVING clause

Oracle CEP does not accept all forms of expressions in all parts of all Oracle CQL statements. Refer to the individual Oracle CQL statements in Chapter 20, "Oracle CQL Statements" for information on restrictions on the expressions in that statement.

You must use appropriate expression notation whenever expr appears in conditions, Oracle CQL functions, or Oracle CQL statements in other parts of this reference. The sections that follow describe and provide examples of the various forms of expressions.

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

aggr_distinct_expr

Use an aggr_distinct_expr aggregate expression when you want to use an aggregate built-in function with distinct. When you want to use an aggregate built-in function without distinct, see "aggr_expr".

aggr_distinct_expr::=

(arith_expr::=)

You can specify an arith_distinct_expr as the argument of an aggregate expression.

You can use an aggr_distinct_expr in the following Oracle CQL statements:

- arith_expr::=

For more information, see Chapter 9, "Built-In Aggregate Functions".

Examples

Example 5-2 shows how to use a COUNT aggregate distinct expression.

Example 5-1 aggr_distinct_expr for COUNT

create view viewq2Cond1(ACCT_INTRL_ID, sumForeign, countForeign) as
select ACCT_INTRL_ID, sum(TRXN_BASE_AM), count(distinct ADDR_CNTRY_CD)
from ValidCashForeignTxn[range 48 hours]
group by ACCT_INTRL_ID
having ((sum(TRXN_BASE_AM) * 100) >= (1000 * 60) and
(count(distinct ADDR_CNTRY_CD >= 2)))

aggr_expr

Use an aggr_expr aggregate expression when you want to use aggregate built-in functions. When you want to use an aggregate built-in function with distinct, see "aggr_distinct_expr"

aggr_expr::=

(arith_expr::=, identifier::=, attr::=, xml_agg_expr::=)

You can specify an arith_expr as the argument of an aggregate expression.

The count aggregate built-in function takes a single argument made up of any of the values that Table 5-1 lists and returns the int value indicated.

Table 5-1 Return Values for COUNT Aggregate Function

Input Argument

Return Value

arith_expr

The number of tuples where arith_expr is not null.

*

The number of all tuples, including duplicates and nulls.

identifier.*

The number of all tuples that match the correlation variable identifier, including duplicates and nulls.

identifier.attr

The number of tuples that match correlation variable identifier, where attr is not null.

The first and last aggregate built-in functions take a single argument made up of the following period separated values:

- identifier1: the name of a pattern as specified in a DEFINE clause.

- identifier2: the name of a stream element as specified in a CREATE STREAM statement.

You can use an aggr_expr in the following Oracle CQL statements:

- arith_expr::=

For more information, see:

- Chapter 9, "Built-In Aggregate Functions"

- Section 19.1.3.5, "Using count With *, identifier.*, and identifier.attr"

- "first"

- "last"

Examples

Example 5-2 shows how to use a COUNT aggregate expression.

Example 5-2 aggr_expr for COUNT

6 Conditions

This chapter provides a reference to conditions in Oracle Continuous Query Language (Oracle CQL). A condition specifies a combination of one or more expressions and logical operators and returns a value of TRUE, FALSE, or UNKNOWN.

- Section 6.1, "Introduction to Conditions"

- Section 6.2, "Comparison Conditions"

- Section 6.3, "Logical Conditions"

- Section 6.4, "LIKE Condition"

- Section 6.5, "Range Conditions"

- Section 6.6, "Null Conditions"

- Section 6.7, "Compound Conditions"

- Section 6.8, "IN Condition"

6.1 Introduction to Conditions

You must use appropriate condition syntax whenever condition appears in Oracle CQL statements.

You can use a condition in the WHERE clause of these statements:

- SELECT

You can use a condition in any of these clauses of the SELECT statement:

- WHERE

- HAVING

See Also:

"Query"

A condition could be said to be of a logical datatype.

The following simple condition always evaluates to TRUE:

1 = 1

The following more complex condition adds the salary value to the commission_pct value (substituting the value 0 for null using the nvl function) and determines whether the sum is greater than the number constant 25000:

NVL(salary, 0) + NVL(salary + (salary*commission_pct, 0) > 25000)

Logical conditions can combine multiple conditions into a single condition. For example, you can use the AND condition to combine two conditions:

(1 = 1) AND (5 < 7)

Here are some valid conditions:

name = 'SMITH'
S0.department_id = S2.department_id
hire_date > '01-JAN-88'
commission_pct IS NULL AND salary = 2100

6.1.1 Condition Precedence

Precedence is the order in which Oracle CEP evaluates different conditions in the same expression. When evaluating an expression containing multiple conditions, Oracle CEP evaluates conditions with higher precedence before evaluating those with lower precedence. Oracle CEP evaluates conditions with equal precedence from left to right within an expression.

Table 6-1 lists the levels of precedence among Oracle CQL condition from high to low. Conditions listed on the same line have the same precedence. As the table indicates, Oracle evaluates operators before conditions.

Table 6-1 Oracle CQL Condition Precedence

Type of Condition

Purpose

Oracle CQL operators are evaluated before Oracle CQL conditions

See Section 4.1.2, "What You May Need to Know About Operator Precedence".

=, <>, <, >, <=, >=

comparison

IS NULL, IS NOT NULL, LIKE, BETWEEN, IN, NOT IN

comparison

NOT

exponentiation, logical negation

AND

conjunction

OR

disjunction

XOR

disjunction

6.2 Comparison Conditions

Comparison conditions compare one expression with another. The result of such a comparison can be TRUE, FALSE, or NULL.

When comparing numeric expressions, Oracle CEP uses numeric precedence to determine whether the condition compares INTEGER, FLOAT, or BIGINT values.

Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements.

A comparison condition specifies a comparison with expressions or view results.

Table 6-2 lists comparison conditions.

Table 6-2 Comparison Conditions

Type of Condition

Purpose

Example

=

Equality test.

7 Common Oracle CQL DDL Clauses

This chapter provides a reference to clauses in the data definition language (DDL) in Oracle Continuous Query Language (Oracle CQL).

- Section 7.1, "Introduction to Common Oracle CQL DDL Clauses"

7.1 Introduction to Common Oracle CQL DDL Clauses

Oracle CQL supports the following common DDL clauses:

- "array_type"

- "attr"

- "attrspec"

- "complex_type"

- "const_int"

- "const_string"

- "const_value"

- "identifier"

- "l-value"

- "methodname"

- "non_mt_arg_list"

- "non_mt_attr_list"

- "non_mt_attrname_list"

- "non_mt_attrspec_list"

- "non_mt_cond_list"

- "param_list"

- "qualified_type_name"

- "query_ref"

- "time_spec"

- "xml_attribute_list"

- "xml_attr_list"

- "xqryargs_list"

For more information on Oracle CQL statements, see Chapter 20, "Oracle CQL Statements".

array_type

Purpose

Use the array_type clause to specify an Oracle CQL data cartridge type composed of a sequence of complex_type components, all of the same type.

Note:

Oracle CQL supports single-dimension arrays only. That is, you can use java.lang.String[] but not java.lang.String[][].

Prerequisites

None.

Syntax

array_type::=

(l-value::=, qualified_type_name::=)

Symantics

Array Declaration

You declare an array type using the qualified_type_name of the Oracle CQL data cartridge complex_type. Only arrays of complextype are supported: you cannot declare an array of Oracle CQL simple types unless there is an equivalent type defined in the Oracle CQL Java data cartridge.

For examples, see:

- "Array Declaration Example: complex_type"

- "Array Declaration Example: Oracle CQL Simple Type"

Array Access

You access a complex_type array element by integer index. The index begins at 0 or 1 depending on the data cartridge implementation.

There is no support for the instantiation of new array type instances directly in Oracle CQL at the time you access an array. For example, the following is not allowed:

SELECT java.lang.String[10] ...

For examples, see "Array Access Examples".

Examples

The following examples illustrate the various semantics that this statement supports:

- "Array Declaration Example: complex_type"

- "Array Declaration Example: Oracle CQL Simple Type"

- "Array Access Examples"

Array Declaration Example: complex_type

Example 7-1 shows how to create an event type as a Java class that specifies an event property as an array of Oracle CQL data cartridge complex type MyClass defined in package com.mypackage.

Example 7-1 Declaring an Oracle CQL Data Cartridge Array in an Event Type

package com.myapplication.event;

import java.util.Date;
import // Oracle CQL Java data cartridge package?

public final class MarketEvent {
private final String symbol;
private final Double price;
private final com.mypackage.MyClass[] a1;

public MarketEvent(...) {
...
}
...
}

Array Declaration Example: Oracle CQL Simple Type

Only arrays of Oracle CQL data cartridge types are supported: you cannot declare an array of Oracle CQL simple types.

int[] a1

However, you can work around this by using the Oracle CQL Java data cartridge and referencing the Java equivalent of the simple type, if one exists:

int@java[] a1

For more information on the @ syntax, see link::=.

Array Access Examples

Example 7-2 shows how to register the following queries that use Oracle CQL data cartridge complex type array access:

- View v1 accesses the third element of the array a1. This array contains instances of Oracle CQL data cartridge complex type com.mypackage.MyClass as defined in Example 7-1.

- Query q1 accesses the first element of the array field1. This array is defined on Oracle CQL data cartridge complex type a1.

Example 7-2 Accessing an Oracle CQL Data Cartridge Array in an Oracle CQL Query

Part II

Functions

This part contains the following chapters:

- Chapter 8, "Built-In Single-Row Functions"

- Chapter 9, "Built-In Aggregate Functions"

- Chapter 10, "Colt Single-Row Functions"

- Chapter 11, "Colt Aggregate Functions"

- Chapter 12, "java.lang.Math Functions"

- Chapter 13, "User-Defined Functions"

8 Built-In Single-Row Functions

This chapter provides a reference to single-row functions in Oracle Continuous Query Language (Oracle CQL). Single-row functions return a single result row for every row of a queried stream or view.

For more information, see Section 1.1.11, "Functions".

8.1 Introduction to Oracle CQL Built-In Single-Row Functions

Table 8-1 lists the built-in single-row functions that Oracle CQL provides.

Table 8-1 Oracle CQL Built-in Single-Row Functions

Type

Function

Character (returning character values)

- concat

Character (returning numeric values)

- length

Datetime

- systimestamp

Conversion

- to_bigint

- to_boolean

- to_char

- to_double

- to_float

- to_timestamp

XML and SQLX

- xmlcomment

- xmlconcat

- xmlexists

- xmlquery

Encoding and Decoding

- hextoraw

- rawtohex

Null-related

- nvl

Pattern Matching

- lk

- prev

Note:

Built-in function names are case sensitive and you must use them in the case shown (in lower case).

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

For more information, see:

- Section 1.1.11, "Functions"

- Section 2.1, "Datatypes"

concat

Syntax

Purpose

concat returns char1 concatenated with char2 as a char[] or byte1 concatenated with byte2 as a byte[]. The char returned is in the same character set as char1. Its datatype depends on the datatypes of the arguments.

Using concat, you can concatenate any combination of character, byte, and numeric datatypes. The concat performs automatic numeric to string conversion.

This function is equivalent to the concatenation operator (||). For more information, see "Concatenation Operator".

To concatenate xmltype arguments, use xmlconcat. For more information, see "xmlconcat".

Examples

concat Function

Consider the query chr_concat in Example 8-1 and data stream S4 in Example 8-2. Stream S4 has schema (c1 char(10)). The query returns the relation in Example 8-3.

Example 8-1 concat Function Query

9 Built-In Aggregate Functions

This chapter provides a reference to built-in aggregate functions included in Oracle Continuous Query Language (Oracle CQL). Built-in aggregate functions perform a summary operation on all the values that a query returns.

For more information, see Section 1.1.11, "Functions".

9.1 Introduction to Oracle CQL Built-In Aggregate Functions

Table 9-1 lists the built-in aggregate functions that Oracle CQL provides:

Table 9-1 Oracle CQL Built-in Aggregate Functions

Type

Function

Aggregate

- max

- min

- xmlagg

Aggregate (incremental computation)

- avg

- count

- sum

Extended aggregate

- first

- last

builtin_aggr::=

builtin_aggr_incr::=

extended_builtin_aggr::=

Specify distinct if you want Oracle CEP to return only one copy of each set of duplicate tuples selected. Duplicate tuples are those with matching values for each expression in the select list. For more information, see aggr_distinct_expr.

Oracle CEP does not support nested aggregations.

Note:

Built-in function names are case sensitive and you must use them in the case shown (in lower case).

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

For more information, see:

- Section 9.1.1, "Built-In Aggregate Functions and the Where, Group By, and Having Clauses"

- Section 1.1.2, "Relation-to-Relation Operators"

- Section 1.1.11, "Functions"

- Section 2.1, "Datatypes"

- select_clause::=

9.1.1 Built-In Aggregate Functions and the Where, Group By, and Having Clauses

In Oracle CQL, the where clause is applied before the group by and having clauses. This means the Oracle CQL statement in Example 9-1 is invalid:

Example 9-1 Invalid Use of count

10 Colt Single-Row Functions

This chapter provides a reference to Colt single-row functions included in Oracle Continuous Query Language (Oracle CQL). Colt single-row functions are based on the Colt open source libraries for high performance scientific and technical computing.

For more information, see Section 1.1.11, "Functions".

10.1 Introduction to Oracle CQLBuilt-In Single-Row Colt Functions

Table 10-1 lists the built-in single-row Colt functions that Oracle CQL provides.

Table 10-1 Oracle CQL Built-in Single-Row Colt-Based Functions

Colt Package

Function

cern.jet.math.Arithmetic

A set of basic polynomials, rounding, and calculus functions.

- binomial

- binomial1

- ceil

- factorial

- floor

- log

- log2

- log10

- logFactorial

- longFactorial

- stirlingCorrection

cern.jet.math.Bessel

A set of Bessel functions.

- i0

- i0e

- i1

- i1e

- j0

- j1

- jn

- k0

- k0e

- k1

- k1e

- kn

- y0

- y1

- yn

cern.jet.random.engine.RandomSeedTable

A table with good seeds for pseudo-random number generators. Each sequence in this table has a period of 10**9 numbers.

- getSeedAtRowColumn

cern.jet.stat.Gamma

A set of Gamma and Beta functions.

- beta

- gamma

- incompleteBeta

- incompleteGamma

- incompleteGammaComplement

- logGamma

cern.jet.stat.Probability

A set of probability distributions.

- beta1

- betaComplemented

- binomial2

- binomialComplemented

- chiSquare

- chiSquareComplemented

- errorFunction

- errorFunctionComplemented

- gamma1

- gammaComplemented

- negativeBinomial

- negativeBinomialComplemented

- normal

- normal1

- normalInverse

- poisson

- poissonComplemented

- studentT

- studentTInverse

cern.colt.bitvector.QuickBitVector

A set of non polymorphic, non bounds checking, low level bit-vector functions.

- bitMaskWithBitsSetFromTo

- leastSignificantBit

- mostSignificantBit

cern.colt.map.HashFunctions

A set of hash functions.

- hash

- hash1

- hash2

- hash3

Note:

Built-in function names are case sensitive and you must use them in the case shown (in lower case).

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

For more information, see:

- Section 1.1.11, "Functions"

- Section 2.1, "Datatypes"

- http://dsd.lbl.gov/~hoschek/colt/

beta

Syntax

Purpose

beta is based on cern.jet.stat.Gamma. It returns the beta function (see Figure 10-1) of the input arguments as a double.

Figure 10-1 cern.jet.stat.Gamma beta

This function takes the following arguments:

- double1: the x value.

- double2: the y value.

For more information, see http://dsd.lbl.gov/~hoschek/colt/api/cern/jet/stat/Gamma.html#beta(double,%20double).

Examples

Consider the query qColt28 in Example 10-1. Given the data stream SColtFunc with schema (c1 integer, c2 double, c3 bigint) in Example 10-2, the query returns the relation in Example 10-3.

Example 10-1 beta Function Query

11 Colt Aggregate Functions

This chapter provides a reference to Colt aggregate functions provided in Oracle Continuous Query Language (Oracle CQL). Colt aggregate functions are based on the Colt open source libraries for high performance scientific and technical computing.

For more information, see Section 1.1.11, "Functions".

11.1 Introduction to Oracle CQL Built-In Aggregate Colt Functions

Table 11-1 lists the built-in aggregate Colt functions that Oracle CQL provides.

Table 11-1 Oracle CQL Built-in Aggregate Colt-Based Functions

Colt Package

Function

cern.jet.stat.Descriptive

A set of basic descriptive statistics functions.

- autoCorrelation

- correlation

- covariance

- geometricMean

- geometricMean1

- harmonicMean

- kurtosis

- lag1

- mean

- meanDeviation

- median

- moment

- pooledMean

- pooledVariance

- product

- quantile

- quantileInverse

- rankInterpolated

- rms

- sampleKurtosis

- sampleKurtosisStandardError

- sampleSkew

- sampleSkewStandardError

- sampleVariance

- skew

- standardDeviation

- standardError

- sumOfInversions

- sumOfLogarithms

- sumOfPowerDeviations

- sumOfPowers

- sumOfSquaredDeviations

- sumOfSquares

- trimmedMean

- variance

- weightedMean

- winsorizedMean

Note:

Built-in function names are case sensitive and you must use them in the case shown (in lower case).

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

In relation output examples, the first tuple output is:

-9223372036854775808:+

This value is -Long.MIN_VALUE() and represents the largest negative timestamp possible.

For more information, see:

- Section 11.1.1, "Oracle CQL Colt Aggregate Function Signatures and Tuple Arguments"

- Section 11.1.2, "Colt Aggregate Functions and the Where, Group By, and Having Clauses"

- Section 1.1.11, "Functions"

- Section 2.1, "Datatypes"

- http://dsd.lbl.gov/~hoschek/colt/

11.1.1 Oracle CQL Colt Aggregate Function Signatures and Tuple Arguments

Note that the signatures of the Oracle CQL Colt aggregate functions do not match the signatures of the corresponding Colt aggregate functions.

Consider the following Colt aggregate function:

double autoCorrelation(DoubleArrayList data, int lag, double mean, double variance)

In this signature, data is the Collection over which aggregates will be calculated and mean and variance are the other two parameter aggregates which are required to calculate autoCorrelation (where mean and variance aggregates are calculated on data).

In Oracle CEP, data will never come in the form of a Collection. The Oracle CQL function receives input data in a stream of tuples.

So suppose our stream is defined as S:(double val, integer lag). On each input tuple, the Oracle CQL autoCorrelation function will compute two intermediate aggregates, mean and variance, and one final aggregate, autoCorrelation.

Since the function expects a stream of tuples having a double data value and an integer lag value only, the signature of the Oracle CQL autoCorrelation function is:

double autoCorrelation (double data, int lag)

11.1.2 Colt Aggregate Functions and the Where, Group By, and Having Clauses

In Oracle CQL, the where clause is applied before the group by and having clauses. This means the Oracle CQL statement in Example 11-1 is invalid:

Example 11-1 Invalid Use of count

12 java.lang.Math Functions

This chapter provides a reference to the java.lang.Math functions provided in Oracle Continuous Query Language (Oracle CQL).

For more information, see Section 1.1.11, "Functions".

12.1 Introduction to Oracle CQL Built-In java.lang.Math Functions

Table 12-1 lists the built-in java.lang.Math functions that Oracle CQL provides.

Table 12-1 Oracle CQL Built-in java.lang.Math Functions

Type

Function

Trigonometric

- sin

- cos

- tan

- asin

- acos

- atan

- atan2

- cosh

- sinh

- tanh

Logarithmic

- log1

- log101

- log1p

Euler's Number

- exp

- expm1

Roots

- cbrt

- sqrt

- hypot

Signum Function

- signum

- signum1

Unit of Least Precision

- ulp

- ulp1

Other

- abs

- abs1

- abs2

- abs3

- ceil1

- floor1

- IEEERemainder

- pow

- rint

- round

- round1

- todegrees

- toradians

Note:

Built-in function names are case sensitive and you must use them in the case shown (in lower case).

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

For more information, see:

- Section 1.1.11, "Functions"

- http://java.sun.com/javase/6/docs/api/java/lang/Math.html

abs

Syntax

Purpose

abs returns the absolute value of the input integer argument as an integer.

For more information, see http://java.sun.com/javase/6/docs/api/java/lang/Math.html#abs(int).

Examples

Consider the query q66 in Example 12-1. Given the data stream SFunc with schema (c1 integer, c2 double, c3 bigint) in Example 12-2, the query returns the stream in Example 12-3.

Example 12-1 abs Function Query

13 User-Defined Functions

This chapter describes how you can write user-defined functions for use in Oracle Continuous Query Language (Oracle CQL) to perform more advanced or application-specific operations on stream data than is possible using built-in functions.

For more information, see Section 1.1.11, "Functions".

13.1 Introduction to Oracle CQL User-Defined Functions

You can write user-defined functions in Java to provide functionality that is not available in Oracle CQL or Oracle CQL built-in functions. You can create a user-defined function that returns an aggregate value or a single (non-aggregate) value.

For example, you can use user-defined functions in the following:

- The select list of a SELECT statement

- The condition of a WHERE clause

To make your user-defined function available for use in Oracle CQL queries, the JAR file that contains the user-defined function implementation class must be in the Oracle CEP server classpath or the Oracle CEP server classpath must be modified to include the JAR file.

For more information, see:

- Section 13.1.1, "Types of User-Defined Functions"

- Section 13.1.2, "User-Defined Function Datatypes"

- Section 13.1.3, "User-Defined Functions and the Oracle CEP Server Cache"

- Section 13.2, "Implementing a User-Defined Function"

- Section 1.1.11, "Functions"

13.1.1 Types of User-Defined Functions

Using the classes in the oracle.cep.extensibility.functions package you can create the following types of user-defined functions:

- Section 13.1.1.1, "User-Defined Single-Row Functions"

- Section 13.1.1.2, "User-Defined Aggregate Functions"

You can create overloaded functions and you can override built-in functions.

13.1.1.1 User-Defined Single-Row Functions

A user-defined single-row function is a function that returns a single result row for every row of a queried stream or view (for example, like the concat built-in function does).

For more information, see "How to Implement a User-Defined Single-Row Function".

13.1.1.2 User-Defined Aggregate Functions

A user-defined aggregate is a function that implements com.bea.wlevs.processor.AggregationFunctionFactory and returns a single aggregate result based on group of tuples, rather than on a single tuple (for example, like the sum built-in function does).

Consider implementing your aggregate function so that it performs incremental processing, if possible. This will improve scalability and performance because the cost of (re)computation on arrival of new events will be proportional to the number of new events as opposed to the total number of events seen thus far.

For more information, see "How to Implement a User-Defined Aggregate Function".

13.1.2 User-Defined Function Datatypes

Table 13-1 lists the datatypes you can specify when you implement and register a user-defined function.

Table 13-1 User-Defined Function Datatypes

Oracle CQL Datatype

Equivalent Java Datatype

bigint

java.lang.Long

char

java.lang.String

double

java.lang.Double

float

java.lang.Float

int

java.lang.Integer

Object

java.lang.Object

The Oracle CQL Datatype column lists the datatypes you can specify in the Oracle CQL statement you use to register your user-defined function and the Equivalent Java Datatype column lists the Java datatype equivalents you can use in your user-defined function implementation.

At run time, Oracle CEP maps between the Oracle CQL datatype and the Java datatype. If your user-defined function returns a datatype that is not in this list, Oracle CEP will throw a ClassCastException.

For more information about data conversion, see Section 2.2.4, "Datatype Conversion".

13.1.3 User-Defined Functions and the Oracle CEP Server Cache

You can access an Oracle CEP cache from an Oracle CQL statement or user-defined function.

For more information, see:

- "Configuring Oracle CEP Caching" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse

- "Accessing a Cache From an Oracle CQL Statement" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse

- "Accessing a Cache From an Oracle CQL User-Defined Function" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse

13.2 Implementing a User-Defined Function

This section describes:

- Section 13.2.1, "How to Implement a User-Defined Single-Row Function"

- Section 13.2.2, "How to Implement a User-Defined Aggregate Function"

For more information, see Section 13.1, "Introduction to Oracle CQL User-Defined Functions".

13.2.1 How to Implement a User-Defined Single-Row Function

You implement a user-defined single-row function by implementing a Java class that provides a public constructor and a public method that is invoked to execute the function.

To implement a user-defined single-row function:

1. Implement a Java class as Example 13-1 shows.

Ensure that the data type of the return value corresponds to a supported data type as Section 13.1.2, "User-Defined Function Datatypes" describes.

For more information on accessing the Oracle CEP cache from a user-defined function, see Section 13.1.3, "User-Defined Functions and the Oracle CEP Server Cache".

Example 13-1 MyMod.java User-Defined Single-Row Function

package com.bea.wlevs.example.function;

public class MyMod {
public Object execute(int arg0, int arg1) {
return new Integer(arg0 % arg1);
}
}

2. Compile the user-defined function Java implementation class and register the class in your Oracle CEP application assembly file as Example 13-2 shows.

Example 13-2 Single-Row User Defined Function for an Oracle CQL Processor

<wlevs:processor id="testProcessor">
<wlevs:listener ref="providerCache"/>
<wlevs:listener ref="outputCache"/>
<wlevs:cache-source ref="testCache"/>
<wlevs:function function-name="mymod" exec-method=”execute” />
<bean class="com.bea.wlevs.example.function.MyMod"/>
</wlevs:function>
</wlevs:processor>

Specify the method that is invoked to execute the function using the wlevs:function element exec-method attribute. This method must be public and must be uniquely identifiable by its name (that is, the method cannot have been overridden).

For more information, see "wlevs:function" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.

3. Invoke your user-defined function in the select list of a SELECT statement or the condition of a WHERE clause as Example 13-3 shows.

Example 13-3 Accessing a User-Defined Single-Row Function in Oracle CQL

...

Part III

Data Cartridges

This part contains the following chapters:

- Chapter 14, "Introduction to Data Cartridges"

- Chapter 15, "Oracle Java Data Cartridge"

- Chapter 16, "Oracle Spatial"

- Chapter 17, "Oracle CEP JDBC Data Cartridge"

14 Introduction to Data Cartridges

This chapter introduces data cartridges in Oracle Complex Event Processing (Oracle CEP). Data cartridges extend Oracle Continuous Query Language (Oracle CQL) to support domain-specific abstract data types of the following forms: simple types, complex types, array types, and domain-specific functions.

This chapter describes:

- Section 14.1, "Understanding Data Cartridges"

- Section 14.2, "Oracle CQL Data Cartridge Types"

14.1 Understanding Data Cartridges

The Oracle CQL data cartridge framework allows you to tightly integrate arbitrary domain data types and functions with the Oracle CQL language, allowing the usage of these extensions within Oracle CQL queries in the same way you use Oracle CQL native types and built-in functions.

With regards to data types, the framework supports both simple and complex types, the latter allowing the usage of object-oriented programming.

Using Oracle CQL data cartridges, you can extend the Oracle CQL engine with domain-specific types that augment and interoperate with native Oracle CQL built-in types.

14.1.1 Data Cartridge Name

Each data cartridge is identified by a unique data cartridge name that defines a name space for the data cartridge implementation. You use the data cartridge name to disambiguate references to types, methods, fields, and constructors, if necessary (see link::=).

14.1.2 Data Cartridge Application Context

Depending on the data cartridge implementation, you may be able to define an application context that the Oracle CEP server propagates to the functions and types that an instance of the data cartridge provides.

For example, you might be able to configure an Oracle CEP server resource or a default data cartridge option and associate this application context information with a particular data cartridge instance.

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

14.2 Oracle CQL Data Cartridge Types

How you access data cartridge types, methods, fields, and constructors using Oracle CQL is the same for all data cartridge implementations.

You may reference a data-cartridge function by using the func_expr, which may optionally take a link name.

To reference the members of a complex type, Oracle CQL provides the object_expr production.

For more information, see:

- func_expr::=

- object_expr::=

What you access is, of course, unique to each data cartridge implementation. For more information, see:

- Chapter 15, "Oracle Java Data Cartridge"

- Chapter 16, "Oracle Spatial"

- Chapter 17, "Oracle CEP JDBC Data Cartridge"

Note:

To simplify Oracle data cartridge type names, you can use aliases as Section 2.7.2, "Defining Aliases Using the Aliases Element" describes.

The script content on this page is for navigation purposes only and does not alter the content in any way.

15 Oracle Java Data Cartridge

This chapter describes how to use the Oracle Java Data Cartridge, an extension of Oracle Continuous Query Language (Oracle CQL) with which you can write CQL code that seamlessly interacts with Java classes in your Oracle CEP application.

This chapter describes the types, methods, fields, and constructors that the Oracle Java data cartridge exposes. You can use these types, methods, fields, and constructors in Oracle CQL queries and views as you would Oracle CQL native types.

This chapter describes:

- Section 15.1, "Understanding the Oracle Java Data Cartridge"

- Section 15.2, "Using the Oracle Java Data Cartridge"

For more information, see:

- Section 14.1, "Understanding Data Cartridges"

- Section 14.2, "Oracle CQL Data Cartridge Types"

15.1 Understanding the Oracle Java Data Cartridge

The Oracle Java data cartridge is a built-in Java cartridge which allows you to write Oracle CQL queries and views that seamlessly interact with the Java classes in your Oracle CEP application.

This section describes:

- Section 15.1.1, "Data Cartridge Name"

- Section 15.1.2, "Class Loading"

- Section 15.1.3, "Method Resolution"

- Section 15.1.4, "Datatype Mapping"

- Section 15.1.5, "Oracle CQL Query Support for the Oracle Java Data Cartridge"

15.1.1 Data Cartridge Name

The Oracle Java data cartridge uses the cartridge ID com.oracle.cep.cartrdiges.java.

The Oracle Java data cartridge is the default Oracle CEP data cartridge.

For types under the default Java package name or types under the system package of java.lang, you may reference the Java type in an Oracle CQL query unqualified by package or data cartridge name:

16 Oracle Spatial

This chapter provides a reference and guide to using the Oracle Spatial cartridge, which extends Oracle Continuous Query Language (Oracle CQL) to provide advanced spatial features for location-enabled applications.

You can use Oracle Spatial types, methods, fields, and constructors in Oracle CQL queries and views as you would Oracle CQL native types when building Oracle CEP applications.

This chapter describes:

- Section 16.1, "Understanding Oracle Spatial"

- Section 16.2, "Using Oracle Spatial"

For more information, see:

- Section 14.1, "Understanding Data Cartridges"

- Section 14.2, "Oracle CQL Data Cartridge Types"

16.1 Understanding Oracle Spatial

Oracle Spatial is an option for Oracle Database that provides advanced spatial features to support high-end geographic information systems (GIS) and location-enabled business intelligence solutions (LBS).

Oracle Spatial is an optional data cartridge which allows you to write Oracle CQL queries and views that seamlessly interact with Oracle Spatial classes in your Oracle CEP application.

Using Oracle Spatial, you can configure Oracle CQL queries that perform the most important geographic domain operations such as storing spatial data, performing proximity and overlap comparisons on spatial data, and integrating spatial data with the Oracle CEP server by providing the ability to index on spatial data.

To use Oracle Spatial, you require a working knowledge of the Oracle Spatial API. For more information about Oracle Spatial, see:

- Product overview: http://www.oracle.com/technology/products/spatial/index.html

- Oracle Spatial documentation: http://www.oracle.com/pls/db112/portal.portal_db?selected=7&frame=#oracle_spatial_and_location_information

- Oracle Spatial Java API reference: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11829/toc.htm

This section describes:

- Section 16.1.1, "Data Cartridge Name"

- Section 16.1.2, "Scope"

- Section 16.1.3, "Datatype Mapping"

- Section 16.1.4, "Oracle Spatial Application Context"

16.1.1 Data Cartridge Name

Oracle Spatial uses the cartridge ID com.oracle.cep.cartrdiges.spatial and registers the server-scoped reserved link name spatial.

Use the spatial link name to associate an Oracle Spatial method call with the Oracle Spatial application context.

For more information, see:

- Section 16.1.4, "Oracle Spatial Application Context"

- Section 16.1.2.7, "Geometry API"

16.1.2 Scope

Oracle Spatial is based on the Oracle Spatial Java API. Oracle Spatial exposes Oracle Spatial functionality in the com.oracle.cep.cartridge.spatial.Geometry class. Oracle Spatial functionality that is not in the Oracle Spatial Java API is not accessible from Oracle Spatial.

Using Oracle Spatial, your Oracle CQL queries may access the Oracle Spatial functionality that Table 16-1 describes.

Table 16-1 Oracle Spatial Scope

Oracle Spatial Feature

Scope

Geometry Types

The following geometry types from the Oracle Spatial Java API:

- 2D points

- 2D simple polygons

- 2D rectangles

The following geometry operations:

- Creating geometry types

- Accessing geometry type public member functions and public fields

For more information, see:

- Section 16.1.2.1, "Geometry Types"

- Section 16.1.2.2, "Element Info Array"

Coordinate Systems

- Cartesian and WGS84 geodetic coordinates (default)

- Specifying the default coordinate system through SRID

- Using other geodetic coordinates

For more information, see Section 16.1.2.3, "Ordinates and Coordinate Systems and the SDO_SRID".

Geometric Index

- R-Tree

For more information, see Section 16.1.2.4, "Geometric Index".

Geometric Relation Operators

- ANYINTERACT

- CONTAIN

- INSIDE

- WITHINDISTANCE

For more information, see Section 16.1.2.5, "Geometric Relation Operators".

Geometric Filter Operators

- FILTER

- NN

For more information, see Section 16.1.2.6, "Geometric Filter Operators".

Geometry API

For a complete list of the methods that com.oracle.cep.cartridge.spatial.Geometry provides, see Section 16.1.2.7, "Geometry API".

For more information on how to access these Oracle Spatial features using Oracle Spatial, see Section 16.2, "Using Oracle Spatial".

16.1.2.1 Geometry Types

The Oracle Spatial data model consists of geometries. A geometry is an ordered sequence of vertices. The semantics of the geometry are determined by its type.

Oracle Spatial allows you to access the following Oracle Spatial types directly in Oracle CQL queries and views:

- SDO_GTYPES: Oracle Spatial supports the following geometry types:

- 2D points

- 2D simple polygons

- 2D rectangles

Table 16-2 describes the geometry types from the com.oracle.cep.cartridge.spatial.Geometry class that you can use.

Table 16-2 Oracle Spatial Geometry Types

Geometry Type

Description

GTYPE_POINT

Point geometry type that contains one point.

GTYPE_POLYGON

Polygon geometry type that contains one polygon.

- SDO_ELEMENT_INFO: You can create the Element Info array using:

- com.oracle.cep.cartridge.spatial.Geometry.createElemInfo static method

- einfogenerator function

For more information, see Section 16.1.2.2, "Element Info Array".

- ORDINATES: You can create the ordinates using the Oracle Spatial ordsgenerator function.

For more information, see Section 16.1.2.3, "Ordinates and Coordinate Systems and the SDO_SRID".

For more information, see:

- Section 16.2.1, "How to Access the Geometry Types That the Oracle Spatial Java API Supports"

- Section 16.2.2, "How to Create a Geometry"

- Section 16.2.3, "How to Access Geometry Type Public Methods and Fields"

16.1.2.2 Element Info Array

The Element Info attribute is defined using a varying length array of numbers. This attribute specifies how to interpret the ordinates stored in the Ordinates attribute.

Oracle Spatial provides the following helper function for generating Element Info attribute values:

com.oracle.cep.cartridge.spatial.Geometry.createElemInfo(int SDO_STARTING_OFFSET, int SDO_ETYPE , int SDO_INTERPRETATION)

You can also use the einfogenerator function.

For more information, see:

- "createElemInfo"

- "einfogenerator"

- "SDO_ELEM_INFO" in the Oracle Spatial Developer's Guide.

16.1.2.3 Ordinates and Coordinate Systems and the SDO_SRID

Table 16-3 lists the coordinate systems that Oracle Spatial supports by default and the SDO_SRID value that identifies each coordinate system.

Table 16-3 Oracle Spatial Coordinate Systems

Coordinate System

SDO_SRID

Description

Cartesian

0

Cartesian coordinates are coordinates that measure the position of a point from a defined origin along axes that are perpendicular in the represented space.

Geodetic (WGS84)

8307

Geodetic coordinates (sometimes called geographic coordinates) are angular coordinates (longitude and latitude), closely related to spherical polar coordinates, and are defined relative to a particular Earth geodetic datum.

This is the default coordinate system in Oracle Spatial.

You can specify the SDO_SRID value as an argument to each Oracle Spatial method and constructor you call or you can configure the SDO_SRID in the Oracle Spatial application context once and use com.oracle.cep.cartridge.spatial.Geometry methods without having to set the SDO_SRID as an argument each time. Using the application context, you can also specify any coordinate system that Oracle Spatial supports.

Note:

If you use a com.oracle.cep.cartridge.spatial.Geometry method that does not take an SDO_SRID value, then you must use the Oracle Spatial application context. For example, the following method call will cause a runtime exception:

com.oracle.cep.cartridge.spatial.Geometry.createPoint(lng, lat)

Instead, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(lng, lat)

If you use a Geometry method that takes an SDO_SRID value, then the use of the spatial link name is optional. For example, both the following method calls are valid:

com.oracle.cep.cartridge.spatial.Geometry.createPoint(8307, lng, lat)
com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(lng, lat)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

Ordinates define the array of coordinates for a geometry using a double array. Oracle Spatial provides the ordsgenerator helper function for generating the array of coordinates. For syntax, see "ordsgenerator".

For more information, see:

- "SDO_SRID" in the Oracle Spatial Developer's Guide

- "Coordinate Systems (Spatial Reference Systems)" in the Oracle Spatial Developer's Guide

- Section 16.2.6, "How to Use the Default Geodetic Coordinates"

- Section 16.2.7, "How to Use Other Geodetic Coordinates"

16.1.2.4 Geometric Index

Oracle Spatial uses a spatial index to implement the primary filter. The purpose of the spatial index is to quickly create a subset of the data and reduce the processing burden on the secondary filter.

A spatial index, like any other index, provides a mechanism to limit searches, but in this case the mechanism is based on spatial criteria such as intersection and containment.

Oracle Spatial uses R-Tree indexing for the default indexing mechanism. A spatial R-tree index can index spatial data of up to four dimensions. An R-tree index approximates each geometry by a single rectangle that minimally encloses the geometry (called the Minimum Bounding Rectangle, or MBR)

For more information, see:

- "Indexing of Spatial Data" in the Oracle Spatial Developer's Guide

- Section 16.1.2.6, "Geometric Filter Operators"

16.1.2.5 Geometric Relation Operators

Oracle Spatial supports the following Oracle Spatial geometric relation operators:

- ANYINTERACT

- CONTAIN

- INSIDE

- WITHINDISTANCE

You can use any of these operators in either the Oracle CQL query projection clause or where clause.

When you use a geometric relation operator in the where clause of an Oracle CQL query, Oracle Spatial enables Rtree indexing on the relation specified in the where clause.

Oracle Spatial supports only geometric relations between point and other geometry types.

For more information, see Section 16.2.4, "How to Use Geometry Relation Operators".

16.1.2.6 Geometric Filter Operators

Oracle Spatial supports the following Oracle Spatial geometric filter operators:

- FILTER

- NN

These filter operators perform primary filtering and so they may only appear in an Oracle CQL query where clause.

These filter operators use the spatial index to identify the set of spatial objects that are likely to interact spatially with the given object.

For more information, see:

- Section 16.1.2.4, "Geometric Index"

- Section 16.2.5, "How to Use Geometry Filter Operators".

16.1.2.7 Geometry API

Oracle Spatial is based on the Oracle Spatial Java API. Oracle Spatial exposes Oracle Spatial functionality in the com.oracle.cep.cartridge.spatial.Geometry class. This Geometry class also extends oracle.spatial.geometry.J3D_Geometry.

Although Oracle Spatial supports only 2D geometries, for efficiency, the Geometry class uses some J3D_Geometry methods. The Geometry class automatically zero-pads the Z coordinates for J3D_Geometry methods.

Oracle Spatial functionality inaccessible from the Geometry class (or not conforming to the scope and geometry types that Oracle Spatial supports) is inaccessible from Oracle Spatial.

This section describes:

- Section 16.1.2.7.1, "com.oracle.cep.cartridge.spatial.Geometry Methods"

- Section 16.1.2.7.2, "oracle.spatial.geometry.JGeometry Methods"

For more information, see:

- Section 16.1.2, "Scope"

- "ordsgenerator"

Note:

To simplify Oracle Spatial type names, you can use aliases as Section 2.7.2, "Defining Aliases Using the Aliases Element" describes.

16.1.2.7.1 com.oracle.cep.cartridge.spatial.Geometry Methods

Table 16-4 lists the public methods that the Geometry class provides.

Table 16-4 Oracle Spatial Geometry Methods

Type

Method

Buffers

- bufferPolygon

Distance

- distance

Element information

- createElemInfo

Geometries

- createGeometry

Linear polygons

- createLinearPolygon

Minimum Bounding Rectangle (MBR)

- get2dMbr

Points

- createPoint

Rectangles

- createRectangle

Type and type conversion

- to_Geometry

- to_JGeometry

Note:

Geometry class methods are case sensitive and you must use them in the case shown.

Note:

If you use a com.oracle.cep.cartridge.spatial.Geometry method that does not take an SDO_SRID value, then you must use the Oracle Spatial application context. For example, the following method call will cause a runtime exception:

com.oracle.cep.cartridge.spatial.Geometry.createPoint(lng, lat)

Instead, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(lng, lat)

If you use a Geometry method that takes an SDO_SRID value, then the use of the spatial link name is optional. For example, both the following method calls are valid:

com.oracle.cep.cartridge.spatial.Geometry.createPoint(8307, lng, lat)
com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(lng, lat)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

16.1.2.7.2 oracle.spatial.geometry.JGeometry Methods

The following JGeometry public methods are applicable to Oracle Spatial:

- double area(double tolerance): returns the total planar surface area of a 2D geometry.

- double length(double tolerance): returns the perimeter of a 2D geometry. All edge lengths are added.

- double[] getMBR(): returns the Minimum Bounding Rectangle (MBR) of this geometry. It returns a double array containing the minX, minY, maxX, and maxY value of the MBR for 2D.

For more information, see:

- http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28401/oracle/spatial/geometry/JGeometry.html

16.1.3 Datatype Mapping

The Oracle Spatial cartridge supports one data type: com.oracle.cep.cartridge.spatial.Geometry.

For a complete list of the methods that com.oracle.cep.cartridge.spatial.Geometry provides, see Section 16.1.2.7, "Geometry API".

16.1.4 Oracle Spatial Application Context

You can define an application context for an instance of Oracle Spatial and propagate this application context at runtime. This allows you to associate specific Oracle Spatial application defaults (such as an SDO_SRID) with a particular Oracle Spatial instance.

Before you can define an Oracle Spatial application context, edit your Oracle CEP application EPN assembly file to add the required namespace and schema location entries as Example 16-1 shows:

Example 16-1 EPN Assembly File: Oracle Spatial 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: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/spring-wlevs-v11_1_1_6.xsd"
http://www.oracle.com/ns/ocep/spatial
http://www.oracle.com/ns/ocep/spatial/ocep-spatial.xsd">

Example 16-2 shows how to create a spatial context named SpatialGRS80 in an EPN assembly file using the Geodetic Reference System 1980 (GRS80) coordinate system.

Example 16-2 spatial:context Element in EPN Assembly File

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

Example 16-3 shows how to reference a spatial:context in an Oracle CQL query. In this case, the query uses link name SpatialGRS80 (defined in Example 16-2) to propagate this application context to Oracle Spatial. The spatial:context attribute settings of SpatialGRS80 are applied to the createPoint method call.

Example 16-3 Referencing spatial:context in an Oracle CQL Query

<view  id="createPoint">
select com.oracle.cep.cartridge.spatial.Geometry.createPoint@SpatialGRS80(
lng, lat)
from CustomerPos[NOW]
</view>

For more information (including a complete list of all spatial:context attributes), see "How to Configure Oracle Spatial Application Context" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.

Note:

If you use a com.oracle.cep.cartridge.spatial.Geometry method that does not take an SDO_SRID value, then you must use the Oracle Spatial application context. For example, the following method call will cause a runtime exception:

com.oracle.cep.cartridge.spatial.Geometry.createPoint(lng, lat)

Instead, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(lng, lat)

If you use a Geometry method that takes an SDO_SRID value, then the use of the spatial link name is optional. For example, both the following method calls are valid:

com.oracle.cep.cartridge.spatial.Geometry.createPoint(8307, lng, lat)
com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(lng, lat)

For more information, see Section 16.1.2.7, "Geometry API".

16.2 Using Oracle Spatial

This section describes common use-cases that highlight how you can use Oracle Spatial in your Oracle CEP applications, including:

- Section 16.2.1, "How to Access the Geometry Types That the Oracle Spatial Java API Supports"

- Section 16.2.2, "How to Create a Geometry"

- Section 16.2.3, "How to Access Geometry Type Public Methods and Fields"

- Section 16.2.4, "How to Use Geometry Relation Operators"

- Section 16.2.5, "How to Use Geometry Filter Operators"

- Section 16.2.6, "How to Use the Default Geodetic Coordinates"

- Section 16.2.7, "How to Use Other Geodetic Coordinates"

For more information, see Section 16.1.2.7, "Geometry API".

16.2.1 How to Access the Geometry Types That the Oracle Spatial Java API Supports

This procedure describes how to access Oracle Spatial geometry types SDO_GTYPE, SDO_ELEMENT_INFO, and ORDINATES using Oracle Spatial in an Oracle CQL query.

To access the geometry types that the Oracle Spatial Java API supports:

1. Import the package com.oracle.cep.cartridge.spatial into your Oracle CEP application's MANIFEST.MF file.

For more information, see "How to Import a Package" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.

2. Define your Oracle CEP application event type using the appropriate Oracle Spatial data types.

Example 16-4 shows how to define event type MySpatialEvent with two event properties x and y of type com.oracle.cep.cartridge.spatial.Geometry.

Example 16-4 Oracle CEP Event Using Oracle Spatial Types

<wlevs:event-type-repository>
<wlevs:event-type type-name="MySpatialEvent">
<wlevs:properties>
<wlevs:property name="x" type="com.oracle.cep.cartridge.spatial.Geometry"/>
<wlevs:property name="y" type="com.oracle.cep.cartridge.spatial.Geometry"/>
</wlevs:properties>
</wlevs:event-type>
</wlevs:event-type-repository>

You can use these event properties in an Oracle CQL query like this:

CONTAIN@spatial(x, y, 20.0d)

For more information, see "Overview of Oracle CEP Events" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.

3. Choose an SDO_GTYPE, for example, GTYPE_POLYGON.

For more information, see Section 16.1.2.1, "Geometry Types".

4. Choose the Element Info appropriate for your ordinates.

For more information, see Section 16.1.2.2, "Element Info Array"

5. Define your coordinate values.

For more information, see Section 16.1.2.3, "Ordinates and Coordinate Systems and the SDO_SRID".

6. Create your Oracle CQL query as Example 16-5 shows.

Example 16-5 Oracle CQL Query Using Oracle Spatial Geometry Types

view id="ShopGeom">
select  com.oracle.cep.cartridge.spatial.Geometry.createGeometry@spatial(
com.oracle.cep.cartridge.spatial.Geometry.GTYPE_POLYGON,
com.oracle.cep.cartridge.spatial.Geometry.createElemInfo(1, 1003, 1),
ordsgenerator@spatial(
lng1, lat1, lng2, lat2, lng3, lat3,
lng4, lat4, lng5, lat5, lng6, lat6
)
) as geom
from ShopDesc
</view>

16.2.2 How to Create a Geometry

You can use Oracle Spatial to create a geometry in an Oracle CQL query by invoking:

- static methods in com.oracle.cartridge.spatial.Geometry

- methods in oracle.spatial.geometry.JGeometry that conform to the scope and geometry types that Oracle Spatial supports.

For more information, see Section 16.1.2.7, "Geometry API".

Using a Static Method in the Oracle Spatial Geometry Class

Example 16-6 shows how to create a point geometry using a static method in com.oracle.cartridge.spatial.Geometry. In this case, you must use a link (@spatial) to identify the data cartridge that provides this class. The advantage of using this approach is that the Oracle Spatial application context is applied to set the SRID and other Oracle Spatial options, either by default or based on an application context you configure (see Section 16.1.4, "Oracle Spatial Application Context").

Example 16-6 Creating a Point Geometry Using a Geometry Static Method

<view  id="CustomerPosGeom">
select com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(
lng, lat) as geom
from CustomerPos[NOW]
</view>

For more information, see Section 16.1.2.1, "Geometry Types".

16.2.3 How to Access Geometry Type Public Methods and Fields

Using Oracle Spatial, you can access the public member functions and public member fields of Oracle Spatial classes directly in Oracle CQL.

Oracle Spatial functionality inaccessible from the Geometry class (or not conforming to the scope and geometry types that Oracle Spatial supports) is inaccessible from Oracle Spatial.

In Example 16-7, the view ShopGeom creates an Oracle Spatial geometry called geom. The view shopMBR calls JGeometry static method getMBR which returns a double[] as stream element mbr. The query qshopMBR accesses this double[] using regular Java API.

Example 16-7 Accessing Geometry Type Public Methods and Fields

<view id="ShopGeom">
select  com.oracle.cep.cartridge.spatial.Geometry.createGeometry@spatial(
com.oracle.cep.cartridge.spatial.Geometry.GTYPE_POLYGON,
com.oracle.cep.cartridge.spatial.Geometry.createElemInfo(1, 1003, 1),
ordsgenerator@spatial(
lng1, lat1, lng2, lat2, lng3, lat3,
lng4, lat4, lng5, lat5, lng6, lat6
)
) as geom
from ShopDesc
</view>
<view  id=”shopMBR”>
select geom.getMBR() as mbr
from ShopGeom
</view>
<query  id=”qshopMBR”>
select mbr[0], mbr[1], mbr[2], mbr[3]
from shopMBR
</query>

For more information, see:

- Section 16.1.2.1, "Geometry Types"

- Chapter 15, "Oracle Java Data Cartridge".

16.2.4 How to Use Geometry Relation Operators

Using Oracle Spatial, you can access the following Oracle Spatial geometry relation operators in either the WHERE or SELECT clause of an Oracle CQL query:

- ANYINTERACT

- CONTAIN

- INSIDE

- WITHINDISTANCE

In Example 16-8, the view op_in_where uses the CONTAIN geometry relation operator in the WHERE clause: in this case, Oracle Spatial uses R-Tree indexing. The view op_in_proj uses CONTAIN in the SELECT clause.

Example 16-8 Using Geometry Relation Operators

<view id="op_in_where">
RStream(
select
loc.customerId,
shop.shopId
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
where
CONTAIN@spatial(shop.geom, loc.curLoc, 5.0d) = true
)
</view>
<view id="op_in_proj">
RStream(
select
loc.customerId,
shop.shopId,
CONTAIN@spatial(shop.geom, loc.curLoc, 5.0d)
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
)
</view>

For more information, see Section 16.1.2.5, "Geometric Relation Operators".

16.2.5 How to Use Geometry Filter Operators

Using Oracle Spatial, you can access the following Oracle Spatial geometry filter operators in the WHERE clause of an Oracle CQL query:

- FILTER

- NN

In Example 16-9, the view filter uses the FILTER geometry filter operator in the WHERE clause.

Example 16-9 Using Geometry Filter Operators

<view id="filter">
RStream(
select loc.customerId, shop.shopId
from LocGeomStream[NOW] as loc, ShopGeomRelation as shop
where FILTER@spatial(shop.geom, loc.curLoc, 5.0d) = true
)
</view>

For more information, see Section 16.1.2.6, "Geometric Filter Operators".

16.2.6 How to Use the Default Geodetic Coordinates

When you create an Oracle CQL query using the default Oracle Spatial application context, the default SRID will be set to CARTESIAN.

As Example 16-10 shows, the createPoint method call uses the default link (@spatial). This guarantees that the default Oracle Spatial application context is applied.

Example 16-10 Using the Default Geodetic Coordinates in an Oracle CQL Query

<view  id="createPoint">
select com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(
lng, lat)
from CustomerPos[NOW]
</view>

For more information, see:

- Section 16.1.4, "Oracle Spatial Application Context"

- Section 16.1.2.3, "Ordinates and Coordinate Systems and the SDO_SRID"

16.2.7 How to Use Other Geodetic Coordinates

This procedure describes how to use the Oracle Spatial application context to specify a geodetic coordinate system other than the default Cartesian geodetic coordinate system in an Oracle CQL query:

For more information, see:

- Section 16.1.4, "Oracle Spatial Application Context"

- Section 16.1.2.3, "Ordinates and Coordinate Systems and the SDO_SRID"

To use other geodetic coordinates:

1. Create an Oracle Spatial application context and define the srid attribute for the geodetic coordinate system you want to use.

Example 16-11 shows how to create a spatial context named SpatialGRS80 in an EPN assembly file using the Geodetic Reference System 1980 (GRS80) coordinate system.

Example 16-11 spatial:context Element in EPN Assembly File

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

2. In your Oracle CQL query, use the id of this spatial:context in your links.

Example 16-12 shows how to reference a spatial:context in an Oracle CQL query. In this case, the query uses link name SpatialGRS80 (defined in Example 16-11) to propagate this application context to Oracle Spatial. The spatial:context attribute settings of SpatialGRS80 are applied to the createPoint method call.

Example 16-12 Referencing spatial:context in an Oracle CQL Query

<view  id=”createPoint”>
select com.oracle.cep.cartridge.spatial.Geometry.createPoint@SpatialGRS80(
lng, lat)
from CustomerPos[NOW]
</view>

ANYINTERACT

Syntax

Note:

This is an Oracle Spatial geometric relation operator and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this operator as Example 16-13 shows, without a package prefix:

ANYINTERACT@spatial

Purpose

This operator returns true if the GTYPE_POINT interacts with the geometry, and false otherwise.

This operator takes the following arguments:

- geom: any supported geometry type.

- key: a GTYPE_POINT geometry type.

The geometry type of this geometry must be GTYPE_POINT or a RUNTIME_EXCEPTION will be thrown.

- tol: the tolerance as a double value.

For more information, see "SDO_ANYINTERACT" in the Oracle Spatial Developer's Guide.

Examples

Example 16-27 shows how to use the ANYINTERACT Oracle Spatial geometric relation operator in an Oracle CQL query.

Example 16-13 Oracle CQL Query Using Geometric Relation Operator ANYINTERACT

<view id="op_in_where">
RStream(
select
loc.customerId,
shop.shopId
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
where
ANYINTERACT@spatial(shop.geom, loc.curLoc, 5.0d) = true
)
</view>
<view id="op_in_proj">
RStream(
select
loc.customerId,
shop.shopId,
ANYINTERACT@spatial(shop.geom, loc.curLoc, 5.0d)
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
)
</view>

bufferPolygon

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method returns a new com.oracle.cep.cartridge.spatial.Geometry object which is the buffered version of the input oracle.spatial.geometry.JGeometry polygon.

This method takes the following arguments:

- polygon: an oracle.spatial.geometry.JGeometry polygon.

- distance: the distance value used for this buffer as a double.

This value is assumed to be in the same unit as the Unit of Projection for projected geometry. If the geometry is geodetic, this buffer width should be in meters.

This method obtains parameters from the Oracle Spatial application context. Consequently, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.bufferPolygon@spatial(geom, 1300)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

Examples

Example 16-14 shows how to use the bufferPolygon method. Because this bufferPolygon call depends on the Oracle Spatial application context, it uses the spatial link name.

Example 16-14 Oracle CQL Query Using Geometry.bufferPolygon

<view id="LocGeomStream" schema="customerId curLoc">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.bufferPolygon@spatial(geom, 13)
from
CustomerLocStream
</view>

CONTAIN

Syntax

Note:

This is an Oracle Spatial geometric relation operator and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this operator as Example 16-13 shows, without a package prefix:

CONTAIN@spatial

Purpose

This operator returns true if the GTYPE_POINT is contained by the geometry, and false otherwise.

This operator takes the following arguments:

- geom: any supported geometry type.

- key: a GTYPE_POINT geometry type.

The geometry type of this geometry must be GTYPE_POINT or a RUNTIME_EXCEPTION will be thrown.

- tol: the tolerance as a double value.

For more information, see "SDO_CONTAINS" in the Oracle Spatial Developer's Guide.

Examples

Example 16-27 shows how to use the CONTAIN Oracle Spatial geometric relation operator in an Oracle CQL query.

Example 16-15 Oracle CQL Query Using Geometric Relation Operator CONTAIN

<view id="op_in_where">
RStream(
select
loc.customerId,
shop.shopId
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
where
CONTAIN@spatial(shop.geom, loc.curLoc, 5.0d) = true
)
</view>
<view id="op_in_proj">
RStream(
select
loc.customerId,
shop.shopId,
CONTAIN@spatial(shop.geom, loc.curLoc, 5.0d)
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
)
</view>

createElemInfo

Syntax

Note:

Alternatively, you can use the function einfogenerator. For more information, see "einfogenerator".

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method returns a single element info value as an int[] from the given arguments.

This method takes the following arguments:

- soffset: the offset, as an int, within the ordinates array where the first ordinate for this element is stored.

SDO_STARTING_OFFSET values start at 1 and not at 0. Thus, the first ordinate for the first element will be at SDO_GEOMETRY.Ordinates(1). If there is a second element, its first ordinate will be at SDO_GEOMETRY.Ordinates(n * 3 + 2), where n reflects the position within the SDO_ORDINATE_ARRAY definition.

- etype: the type of the element as an int.

Oracle Spatial supports SDO_ETYPE values 1, 1003, and 2003 are considered simple elements (not compound types). They are defined by a single triplet entry in the element info array. These types are:

- 1: point.

- 1003: exterior polygon ring (must be specified in counterclockwise order).

- 2003: interior polygon ring (must be specified in clockwise order).

These types are further qualified by the SDO_INTERPRETATION.

Note:

You cannot mix 1-digit and 4-digit SDO_ETYPE values in a single geometry.

- interp: the interpretation as an int.

For an SDO_ETYPE that is a simple element (1, 1003, or 2003) the SDO_INTERPRETATION attribute determines how the sequence of ordinates for this element is interpreted. For example, a polygon boundary may be made up of a sequence of connected straight line segments.

If a geometry consists of more than one element, then the last ordinate for an element is always one less than the starting offset for the next element. The last element in the geometry is described by the ordinates from its starting offset to the end of the ordinates varying length array.

Table 16-5 describes the relationship between SDO_ETYPE and SDO_INTERPREATION.

Table 16-5 SDO_ETYPE and SDO_INTERPRETATION

SDO_ETYPE

SDO_INTERPRETATION

Description

0

Any numeric value

Used to model geometry types not supported by Oracle Spatial.

1

1

Point type.

1

0

Orientation for an oriented point.

1003 or 2003

1

Simple polygon whose vertices are connected by straight line segments. You must specify a point for each vertex; and the last point specified must be exactly the same point as the first (within the tolerance value), to close the polygon.

For example, for a 4-sided polygon, specify 5 points, with point 5 the same as point 1.

Examples

Example 16-16 shows how to use the createElemInfo method.

Example 16-16 Oracle CQL Query Using Geometry.createElemInfo

<view id="ShopGeom">
select  com.oracle.cep.cartridge.spatial.Geometry.createGeometry@spatial(
com.oracle.cep.cartridge.spatial.Geometry.GTYPE_POLYGON,
com.oracle.cep.cartridge.spatial.Geometry.createElemInfo(1, 1003, 1),
ordsgenerator@spatial(
lng1, lat1, lng2, lat2, lng3, lat3,
lng4, lat4, lng5, lat5, lng6, lat6
)
) as geom
from ShopDesc
</view>

createGeometry

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method returns a new 2D oracle.cep.cartridge.spatial.Geometry object.

This method takes the following arguments:

- gtype: the geometry type as an int.

For more information, see Table 16-2.

- eleminfo: the geometry element info as an int[].

For more information, see "createElemInfo".

- ordinates: the geometry ordinates as a double[].

- srid: the optional SDO_SRID of the geometry as an int.

If you omit the srid parameter, then this method obtains parameters from the Oracle Spatial application context. Consequently, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.createGeometry@spatial(gtype, eleminfo, ordinates)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

Examples

Example 16-17 shows how to use the createGeometry method. Because this createGeometry call does not include the srid argument, it uses the spatial link name to associate the method call with the Oracle Spatial application context.

Example 16-17 Oracle CQL Query Using Geometry.createGeometry

<view id="ShopGeom">
select  com.oracle.cep.cartridge.spatial.Geometry.createGeometry@spatial(
com.oracle.cep.cartridge.spatial.Geometry.GTYPE_POLYGON,
com.oracle.cep.cartridge.spatial.Geometry.createElemInfo(1, 1003, 1),
ordsgenerator@spatial(
lng1, lat1, lng2, lat2, lng3, lat3,
lng4, lat4, lng5, lat5, lng6, lat6
)
) as geom
from ShopDesc
</view>

createLinearPolygon

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method returns a new com.oracle.cep.cartridge.spatial.Geometry object which is a 2D simple linear polygon without holes. If the coordinate array does not close itself (the last coordinate is not the same as the first) then this method copies the first coordinate and appends this coordinate value to the end of the input coordinates array.

To create a simple linear polygon without holes, use the following arguments:

- coords: the coordinates of the linear polygon as a double[].

- srid: the optional SRID of the geometry as an int.

If you omit the srid parameter, then this method obtains parameters from the Oracle Spatial application context. Consequently, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.createLinearPolygon@spatial(coords)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

Examples

Example 16-18 shows how to use the createLinearPolygon method. Because this createLinearPolygon method call does not include the srid argument, it must use the spatial link name to associate the method call with the Oracle Spatial application context.

Example 16-18 Oracle CQL Query Using Geometry.createLinearPolygon

<view id="LocGeomStream" schema="customerId curLoc">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.createLinearPolygon@spatial(coords)
from
CustomerLocStream
</view>

createPoint

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method returns a new com.oracle.cep.cartridge.spatial.Geometry object which is a 3D point.

This method takes the following arguments:

- x: the x coordinate of the lower left as a double.

- y: the y coordinate of the lower left as a double.

- srid: the optional SRID of the geometry as an int.

If you omit the srid parameter, then this method obtains parameters from the Oracle Spatial application context. Consequently, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(x, y)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

Examples

Example 16-19 shows how to use the createPoint method. Because this createPoint call includes the srid argument, it does not need to use the spatial link name.

Example 16-19 Oracle CQL Query Using Geometry.createPoint

<view id="LocGeomStream" schema="customerId curLoc">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.createPoint@spatial(lng, lat)
from
CustomerLocStream
</view>

createRectangle

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method returns a new com.oracle.cep.cartridge.spatial.Geometry object which is a 2D rectangle.

This method takes the following arguments:

- x1: the x coordinate of the lower left as a double.

- y1: the y coordinate of the lower left as a double.

- x2: the x coordinate of the upper right as a double.

- y2: the y coordinate of the upper right as a double.

- srid: the optional SRID of the geometry as an int.

If you omit the srid parameter, then this method obtains parameters from the Oracle Spatial application context. Consequently, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.createRectangle@spatial(x1, y1, x2, y2)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

Examples

Example 16-20 shows how to use the createRectangle method. Because this createRectangle method call does not include the srid argument, it must use the spatial link name to associate the method call with the Oracle Spatial application context.

Example 16-20 Oracle CQL Query Using Geometry.createRectangle

<view id="LocGeomStream" schema="customerId curLoc">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.createRectangle@spatial(x1, y1, x2, y2)
from
CustomerLocStream
</view>

distance

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method calculates the distance between two geometries as a double.

To calculate the distance between a given com.oracle.cep.cartridge.spatial.Geometry object and another, use the non-static distance method of the current Geometry object with the following arguments:

- g: the other com.oracle.cep.cartridge.spatial.Geometry object.

To calculate the distance between two com.oracle.cep.cartridge.spatial.Geometry objects, use the static distance method with the following arguments:

- g1: the first com.oracle.cep.cartridge.spatial.Geometry object.

- g2: the second com.oracle.cep.cartridge.spatial.Geometry object.

In both cases, this method obtains parameters from the Oracle Spatial application context. Consequently, you must use the spatial link name to associate the method call with the Oracle Spatial application context:

com.oracle.cep.cartridge.spatial.Geometry.distance@spatial(geom)
com.oracle.cep.cartridge.spatial.Geometry.distance@spatial(geom1, geom2)

For more information, see Section 16.1.4, "Oracle Spatial Application Context".

Examples

Example 16-21 shows how to use the distance method. Because the distance method depends on the Oracle Spatial application context, it must use the spatial link name.

Example 16-21 Oracle CQL Query Using Geometry.distance

<view id="LocGeomStream" schema="customerId curLoc">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.createRectangle(x1, y1, x2, y2, 8307)
from
CustomerLocStream
where
com.oracle.cep.cartridge.spatial.Geometry.distance@spatial(geom1, geom2) < 5
</view>

einfogenerator

Syntax

Note:

This is an Oracle CQL function and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this function as Example 16-22 shows, without a package prefix:

einfogenerator@spatial

Alternatively, you can use the Geometry method createElemInfo. For more information, see "createElemInfo".

Purpose

This function returns a single element info value as an int[] from the given arguments.

This function takes the following arguments:

- offset: the offset, as an int, within the ordinates array where the first ordinate for this element is stored.

SDO_STARTING_OFFSET values start at 1 and not at 0. Thus, the first ordinate for the first element will be at SDO_GEOMETRY.Ordinates(1). If there is a second element, its first ordinate will be at SDO_GEOMETRY.Ordinates(n * 3 + 2), where n reflects the position within the SDO_ORDINATE_ARRAY definition.

- etype: the type of the element as an int.

Oracle Spatial supports SDO_ETYPE values 1, 1003, and 2003 are considered simple elements (not compound types). They are defined by a single triplet entry in the element info array. These types are:

- 1: point.

- 1003: exterior polygon ring (must be specified in counterclockwise order).

- 2003: interior polygon ring (must be specified in clockwise order).

These types are further qualified by the SDO_INTERPRETATION.

Note:

You cannot mix 1-digit and 4-digit SDO_ETYPE values in a single geometry.

- interp: the interpretation as an int.

For an SDO_ETYPE that is a simple element (1, 1003, or 2003) the SDO_INTERPRETATION attribute determines how the sequence of ordinates for this element is interpreted. For example, a polygon boundary may be made up of a sequence of connected straight line segments.

If a geometry consists of more than one element, then the last ordinate for an element is always one less than the starting offset for the next element. The last element in the geometry is described by the ordinates from its starting offset to the end of the ordinates varying length array.

Table 16-6 describes the relationship between SDO_ETYPE and SDO_INTERPREATION.

Table 16-6 SDO_ETYPE and SDO_INTERPRETATION

SDO_ETYPE

SDO_INTERPRETATION

Description

0

Any numeric value

Used to model geometry types not supported by Oracle Spatial.

1

1

Point type.

1

0

Orientation for an oriented point.

1003 or 2003

1

Simple polygon whose vertices are connected by straight line segments. You must specify a point for each vertex; and the last point specified must be exactly the same point as the first (within the tolerance value), to close the polygon.

For example, for a 4-sided polygon, specify 5 points, with point 5 the same as point 1.

Examples

Example 16-22 shows how to use the oeinfogenerator function to create the element information for a geometry.

Example 16-22 Oracle CQL Query Using Oracle Spatial Geometry Types

view id="ShopGeom">
select  com.oracle.cep.cartridge.spatial.Geometry.createGeometry@spatial(
com.oracle.cep.cartridge.spatial.Geometry.GTYPE_POLYGON,
einfogenerator@spatial(1, 1003, 1),
ordsgenerator@spatial(
lng1, lat1, lng2, lat2, lng3, lat3,
lng4, lat4, lng5, lat5, lng6, lat6
)
) as geom
from ShopDesc
</view>

FILTER

Syntax

Note:

This is an Oracle Spatial geometric filter operator and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this operator as Example 16-13 shows, without a package prefix:

FILTER@spatial

Purpose

This operator returns true for object pairs that are non-disjoint, and false otherwise.

This operator takes the following arguments:

- key: a GTYPE_POINT geometry type.

The geometry type of this geometry must be GTYPE_POINT or a RUNTIME_EXCEPTION will be thrown.

- tol: the tolerance as a double value.

For more information, see "SDO_FILTER" in the Oracle Spatial Developer's Guide.

Examples

Example 16-27 shows how to use the FILTER Oracle Spatial geometric filter operator in an Oracle CQL query.

Example 16-23 Oracle CQL Query Using Geometric Relation Operator FILTER

<view id="filter">
RStream(
select loc.customerId, shop.shopId
from LocGeomStream[NOW] as loc, ShopGeomRelation as shop
where FILTER@spatial(loc.curLoc, 5.0d) = true
)
</view>

get2dMbr

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method returns the Minimum Bounding Rectangle (MBR) of a given Geometry as a double[][] that contains the following values:

- [0][0]: minX

- [0][1]: maxX

- [1][0]: minY

- [1][1]: maxY

This method takes the following arguments:

- geom: the com.oracle.cep.cartridge.spatial.Geometry object.

Examples

Example 16-24 shows how to use the get2dMbr method.

Example 16-24 Oracle CQL Query Using Geometry.get2dMbr

<view id="LocGeomStream" schema="customerId mbr">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.get2dMbr(geom)
from
CustomerLocStream
where
com.oracle.cep.cartridge.spatial.Geometry.distance@spatial(geom1, geom2) < 5
</view>

INSIDE

Syntax

Note:

This is an Oracle Spatial geometric relation operator and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this operator as Example 16-13 shows, without a package prefix:

INSIDE@spatial

Purpose

This operator returns true if the GTYPE_POINT is inside the geometry, and false otherwise.

This operator takes the following arguments:

- geom: any supported geometry type.

- key: a GTYPE_POINT geometry type.

The geometry type of this geometry must be GTYPE_POINT or a RUNTIME_EXCEPTION will be thrown.

- tol: the tolerance as a double value.

For more information, see "SDO_INSIDE" in the Oracle Spatial Developer's Guide.

Examples

Example 16-27 shows how to use the INSIDE Oracle Spatial geometric relation operator in an Oracle CQL query.

Example 16-25 Oracle CQL Query Using Geometric Relation Operator INSIDE

<view id="op_in_where">
RStream(
select
loc.customerId,
shop.shopId
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
where
INSIDE@spatial(shop.geom, loc.curLoc, 5.0d) = true
)
</view>
<view id="op_in_proj">
RStream(
select
loc.customerId,
shop.shopId,
INSIDE@spatial(shop.geom, loc.curLoc, 5.0d)
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
)
</view>

NN

Syntax

Note:

This is an Oracle Spatial geometric filter operator and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this operator as Example 16-13 shows, without a package prefix:

NN@spatial

Purpose

This operator returns the objects (nearest neighbors) from geom that are nearest to key. In determining how near two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.

This function takes the following arguments:

- geom: any supported geometry type.

- key: a GTYPE_POINT geometry type.

The geometry type of this geometry must be GTYPE_POINT or a RUNTIME_EXCEPTION will be thrown.

- tol: the tolerance as a double value.

For more information, see "SDO_NN" in the Oracle Spatial Developer's Guide.

Examples

Example 16-27 shows how to use the NN Oracle Spatial geometric filter operator in an Oracle CQL query.

Example 16-26 Oracle CQL Query Using Geometric Relation Operator NN

<view id="filter">
RStream(
select loc.customerId, shop.shopId
from LocGeomStream[NOW] as loc, ShopGeomRelation as shop
where NN@spatial(shop.geom, loc.curLoc, 5.0d) = true
)
</view>

ordsgenerator

Syntax

Note:

This is an Oracle CQL function and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this function as Example 16-27 shows, without a package prefix:

ordsgenerator@spatial

Purpose

This function returns the double array of 2D coordinates that Oracle Spatial requires.

This function takes the following arguments:

- x1, y1, ... xN, yN: a comma-separated list of double coordinate values.

Examples

Example 16-27 shows how to use the ordsgenerator function to create an Oracle Spatial double array out of six double coordinate values.

Example 16-27 Oracle CQL Query Using Oracle Spatial Geometry Types

view id="ShopGeom">
select  com.oracle.cep.cartridge.spatial.Geometry.createGeometry@spatial(
com.oracle.cep.cartridge.spatial.Geometry.GTYPE_POLYGON,
com.oracle.cep.cartridge.spatial.Geometry.createElemInfo(1, 1003, 1),
ordsgenerator@spatial(
lng1, lat1, lng2, lat2, lng3, lat3,
lng4, lat4, lng5, lat5, lng6, lat6
)
) as geom
from ShopDesc
</view>

to_Geometry

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method converts an oracle.spatial.geometry.JGeometry type to a 3D com.oracle.cep.cartridge.spatial.Geometry type. If the given geometry is already a Geometry type and a 3D geometry, then no conversion is done. If the given geometry is a 2D geometry, then the given geometry is converted to 3D by padding z coordinates.

This method takes the following arguments:

- geom: the oracle.spatial.geometry.JGeometry object to convert.

Examples

Example 16-28 shows how to use the to_Geometry method.

Example 16-28 Oracle CQL Query Using Geometry.to_Geometry

<view id="LocStream" schema="customerId loc">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.to_Geometry(geom)
from
CustomerLocStream
</view>

to_JGeometry

Syntax

Purpose

This com.oracle.cep.cartridge.spatial.Geometry method converts a com.oracle.cep.cartridge.spatial.Geometry object to an oracle.spatial.geometry.JGeometry 2D type.

This method takes the following arguments:

- g: the com.oracle.cep.cartridge.spatial.Geometry object to convert.

Examples

Example 16-29 shows how to use the to_JGeometry method.

Example 16-29 Oracle CQL Query Using Geometry.to_JGeometry

<view id="LocStream" schema="customerId loc">
select
customerId,
com.oracle.cep.cartridge.spatial.Geometry.to_JGeometry(geom)
from
CustomerLocStream
</view>

WITHINDISTANCE

Syntax

Note:

This is an Oracle Spatial geometric relation operator and not a method of the com.oracle.cep.cartridge.spatial.Geometry class so you invoke this operator as Example 16-13 shows, without a package prefix:

WITHINDISTANCE@spatial

Purpose

This operator returns true if the GTYPE_POINT is within the given distance of the geometry, and false otherwise.

This operator takes the following arguments:

- geom: any supported geometry type.

- key: a GTYPE_POINT geometry type.

The geometry type of this geometry must be GTYPE_POINT or a RUNTIME_EXCEPTION will be thrown.

- dist: the distance as a double value.

For more information, see "SDO_WITHIN_DISTANCE" in the Oracle Spatial Developer's Guide.

Examples

Example 16-27 shows how to use the WITHINDISTANCE Oracle Spatial geometric relation operator in an Oracle CQL query.

Example 16-30 Oracle CQL Query Using Geometric Relation Operator WITHINDISTANCE

<view id="op_in_where">
RStream(
select
loc.customerId,
shop.shopId
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
where
WITHINDISTANCE@spatial(shop.geom, loc.curLoc, 5.0d) = true
)
</view>
<view id="op_in_proj">
RStream(
select
loc.customerId,
shop.shopId,
WITHINDISTANCE@spatial(shop.geom, loc.curLoc, 5.0d)
from
LocGeomStream[NOW] as loc,
ShopGeomRelation as shop
)
</view>

17 Oracle CEP JDBC Data Cartridge

This chapter describes the Oracle CEP 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 describes:

- Section 17.1, "Understanding the Oracle CEP JDBC Data Cartridge"

- Section 17.2, "Using the Oracle CEP JDBC Data Cartridge"

For more information, see:

- Section 14.1, "Understanding Data Cartridges"

- Section 14.2, "Oracle CQL Data Cartridge Types"

17.1 Understanding the Oracle CEP JDBC Data Cartridge

Oracle CEP streams contain streaming data, and a database typically stores historical data. Use the Oracle CEP JDBC data cartridge to associate historical data (stored in one or more tables) with the streaming data coming from Oracle CEP streams. The Oracle CEP 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 CEP JDBC data cartridge.

This section describes:

- Section 17.1.1, "Data Cartridge Name"

- Section 17.1.2, "Scope"

- Section 17.1.3, "Datatype Mapping"

- Section 17.1.4, "Oracle CEP JDBC Data Cartridge Application Context"

17.1.1 Data Cartridge Name

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

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

17.1.2 Scope

The Oracle CEP 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 CEP 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 Complex Event Processing

17.1.3 Datatype Mapping

This section describes Oracle CEP JDBC data cartridge datatype mapping.

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

Example 17-1 Oracle CEP 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>

Part IV

Using Oracle CQL

This part contains the following chapters:

- Chapter 18, "Oracle CQL Queries, Views, and Joins"

- Chapter 19, "Pattern Recognition With MATCH_RECOGNIZE"

- Chapter 20, "Oracle CQL Statements"

18 Oracle CQL Queries, Views, and Joins

This chapter provides reference and usage guidelines for queries, views, and joins in Oracle Continuous Query Language (Oracle CQL). You select, process, and filter element data from streams and relations using Oracle CQL queries and views.

A top-level SELECT statement that you create using the QUERY statement is called a query.

A top-level VIEW statement that you create using the VIEW statement is called a view (the Oracle CQL equivalent of a subquery).

A join is a query that combines rows from two or more streams, views, or relations.

This chapter describes:

- Section 18.1, "Introduction to Oracle CQL Queries, Views, and Joins"

- Section 18.2, "Queries"

- Section 18.3, "Views"

- Section 18.4, "Joins"

- Section 18.5, "Oracle CQL Queries and the Oracle CEP Server Cache"

- Section 18.6, "Oracle CQL Queries and Relational Database Tables"

- Section 18.7, "Oracle CQL Queries and Oracle Data Cartridges"

For more information, see:

- Section 1.2.1, "Lexical Conventions"

- Section 1.2.3, "Documentation Conventions"

- Chapter 2, "Basic Elements of Oracle CQL"

- Chapter 7, "Common Oracle CQL DDL Clauses"

- Chapter 20, "Oracle CQL Statements"

18.1 Introduction to Oracle CQL Queries, Views, and Joins

An Oracle CQL query is an operation that you express in Oracle CQL syntax and execute on an Oracle CEP CQL Processor to process data from one or more streams or views. For more information, see Section 18.2, "Queries".

An Oracle CQL view represents an alternative selection on a stream or relation. In Oracle CQL, you use a view instead of a subquery. For more information, see Section 18.3, "Views".

Oracle CEP performs a join whenever multiple streams appear in the FROM clause of the query. For more information, see Section 18.4, "Joins".

Example 18-1 shows typical Oracle CQL queries defined in an Oracle CQL processor component configuration file for the processor named proc.

Example 18-1 Typical Oracle CQL Query

<?xml version="1.0" encoding="UTF-8"?>
<n1:config
xsi:schemaLocation="http://www.bea.com/ns/wlevs/config/application wlevs_application_config.xsd"
xmlns:n1="http://www.bea.com/ns/wlevs/config/application"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<processor>
<name>proc</name>
<rules>

19 Pattern Recognition With MATCH_RECOGNIZE

This chapter provides reference and usage information about the MATCH_RECOGNIZE clause in Oracle Continuous Query Language (Oracle CQL). This clause and its sub-clauses perform pattern recognition in Oracle CQL queries.

- Section 19.1, "Understanding Pattern Recognition With MATCH_RECOGNIZE"

- Section 19.2, "MEASURES Clause"

- Section 19.3, "PATTERN Clause"

- Section 19.4, "DEFINE Clause"

- Section 19.5, "PARTITION BY Clause"

- Section 19.6, "ORDER BY Clause"

- Section 19.7, "ALL MATCHES Clause"

- Section 19.8, "WITHIN Clause"

- Section 19.9, "DURATION Clause"

- Section 19.10, "INCLUDE TIMER EVENTS Clause"

- Section 19.11, "SUBSET Clause"

- Section 19.12, "MATCH_RECOGNIZE Examples"

19.1 Understanding Pattern Recognition With MATCH_RECOGNIZE

The MATCH_RECOGNIZE clause performs pattern recognition in an Oracle CQL query as Example 19-1 shows. This query will export (make available for inclusion in the SELECT) the MEASURES clause values for events (tuples) that satisfy the PATTERN clause regular expression over the DEFINE clause conditions.

Example 19-1 Pattern Matching With MATCH_RECOGNIZE

20 Oracle CQL Statements

This chapter describes data definition language (DDL) statements in Oracle Continuous Query Language (Oracle CQL).

- Section 20.1, "Introduction to Oracle CQL Statements"

20.1 Introduction to Oracle CQL Statements

Oracle CQL supports the following DDL statements:

- Query

- View

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

For more information, see:

- Section 1.2.1, "Lexical Conventions"

- Section 1.2.2, "Syntactic Shortcuts and Defaults"

- Section 1.2.3, "Documentation Conventions"

- Chapter 2, "Basic Elements of Oracle CQL"

- Chapter 7, "Common Oracle CQL DDL Clauses"

- Chapter 18, "Oracle CQL Queries, Views, and Joins"

Query

Purpose

Use the query statement to define a Oracle CQL query that you reference by identifier in subsequent Oracle CQL statements.

Prerequisites

If your query references a stream or view, then the stream or view must already exist.

If the query already exists, Oracle CEP server throws an exception.

For more information, see:

- "View"

- Chapter 18, "Oracle CQL Queries, Views, and Joins"

Syntax

You express a query in a <query></query> element as Example 20-1 shows.

The query element has one attribute:

- id: Specify the identifier as the query element id attribute.

The id value must conform with the specification given by identifier::=.

Example 20-1 Query in a <query></query> Element

Index

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y

Symbols

-

arithmetic operator negative, 4.1.2

arithmetic operator subtraction, 4.1.2

relation deletion, 1.1.1.3

()

grouping pattern operator, 19.3.2

*

arithmetic operator multiply, 4.1.2

maximal pattern quantifier (0 or more times), 19.3.1

used with count function, 5.1, 5.1, 9.1.1, 9.1.1, 19.1.3.5, 19.1.3.5

used with select clause, 20.1

*?

minimal pattern quantifier (0 or more times), 19.3.1

+

arithmetic operator addition, 4.1.2

arithmetic operator positive, 4.1.2

maximal pattern quantifier (1 or more times), 19.3.1

relation insertion, 1.1.1.3

+?

minimal pattern quantifier (1 or more times), 19.3.1

:n

parameterized query placeholder, 18.2.11

?

maximal pattern quantifier (0 or 1 time), 19.3.1

??

minimal pattern quantifier (0 or 1 time), 19.3.1

|

alternation pattern operator, 19.3.2

||

concatenation operator, 4.1.2, 4.1.2

A

abs function, 12.1

abs1 function, 12.1

abs2 function, 12.1

abs3 function, 12.1

acos function, 12.1

aggr_distinct_expr syntax, 5.1

aggr_expr syntax, 5.1

aggregates

correlation variable restrictions, 19.1.3.2, 19.1.3.3, 19.1.3.4, 19.1.3.5

count in MATCH_RECOGNIZE clause, 19.1.3.5

expressions, 5.1, 5.1

final, 19.1.3.1

first in MATCH_RECOGNIZE clause, 19.1.3.6

functions, built-in, 9

functions, Colt built-in, 11

last in MATCH_RECOGNIZE clause, 19.1.3.6

prev in MATCH_RECOGNIZE clause, 19.1.4

referencing in MATCH_RECOGNIZE clause, 19.1.3

running, 19.1.3.1

XML aggregate expressions, 5.1

AggregationFunctionFactory, 13.2.2

aliases

about, 2.7

ALIASES element, 2.7.2

AS operator, 2.7.1

columns, 2.7.1.1

stream elements, 2.7.1.1

type aliases, 2.7.2.1

window operators, 2.7.1.2

ALL MATCHES clause

about, 19.7

partial overlapping patterns, 19.7

total overlapping patterns, 19.7

ALL operator, 6.2

alternation operator, 4.1.2

AND condition, 6.3, 6.3

ANY operator, 6.2

ANYINTERACT geometric relation operator, 16.2.7

API

AggregationFunctionFactory, 13.2.2

application context

data cartridges, 14.1.2

Oracle JDBC data cartridge, 17.1.4

Oracle Spatial, 16.1.4

application time, 1.1.13

application timestamped, 1.1.13

APPLICATION_NO_AUTO_IMPORT_CLASS_SPACE, 15.1.2.2

arguments of operators, 4

arith_expr syntax, 5.1

arith_expr_list syntax, 5.1

array_type

clause, 7.1

AS

alias operator, 2.7.1, 2.7.1.2

correlation_name_definition syntax, 19.4

measure_column syntax, 19.2

asc_desc syntax, 20.1

asin function, 12.1

atan function, 12.1

atan2 function, 12.1

attr

clause, 7.1

syntax, 7.1

attrspec

clause, 7.1

syntax, 7.1

autoCorrelation function, 11.1.2

avg function, 9.1.1

B

base relation, 1.1.1.2

base stream, 1.1.1.2

beta function, 10.1

beta1 function, 10.1

betaComplemented function, 10.1

BETWEEN conditions, 6.5

between_condition syntax, 6.5

BIGINT datatype, 2.1.1

binary

clause, 20.1

syntax, 20.1

binary operators, 4.1.1

bindings element, 18.2.11.2

binomial function, 10.1

binomial1 function, 10.1

binomial2 function, 10.1

binomialComplemented function, 10.1

bitMaskWithBitsSetFromTo function, 10.1

BOOLEAN datatype, 2.1.1

bufferPolygon Geometry method, 16.2.7

built-in datatypes, 2.1.1

built-in functions

about, 1.1.11

aggregate

about, 9

distinct, 9.1

nested, 9.1

where, group by, and having clause, 9.1.1, 9.1.1

Colt, 1.1.11, 1.1.11, 10, 11

where, group by, and having clause, 11.1.2

java.lang.Math, 1.1.11, 12

single-row

about, 8

built-in windows

about, 1.1.3

queries, 18.2.3

builtin_aggr syntax, 9.1

builtin_aggr_incr syntax, 9.1

BYTE datatype, 2.1.1

C

cache, 1.1.10

Oracle CQL queries, 18.5

Oracle CQL user-defined functions, 13.1.3

cache queries, 18.2.8

case_expr syntax, 5.1

cbrt function, 12.1

CDATA, 1.2.1

ceil function, 10.1

ceil1 function, 12.1

CEP DDL

use, 1.2.1

channels

query selector, 1.1.1.1.4

stream, 1.1.1.1.1

CHAR datatype, 2.1.1

character sets and multibyte characters, 2.8.1

chiSquare function, 10.1

chiSquareComplemented function, 10.1

class_name

clause, 7.1, 7.1

classpath

Oracle CEP Service Engine, 13.1, 13.2.1, 13.2.2

single-row user-defined functions, 13.2.1, 13.2.2

user-defined functions, 13.1

Colt built-in functions, 1.1.11, 1.1.11, 10, 11

where, group by, and having clause, 11.1.2

column aliases, 2.7.1.1

comments

CQL, 1.2.1, 2.6

XML, 8.1

comparison conditions

about, 6.2

simple, 6.2

comparison rules

character values, 2.2.3

datatypes, 2.2

date value, 2.2.2

complex_type

clause, 7.1

compound conditions, 6.7

compound_conditions syntax, 6.7

concat function, 8.1

concatenation operator, 4.1.2, 8.1

condition syntax, 6.2

conditions

about, 6

BETWEEN, 6.5

comparison

about, 6.2

simple, 6.2

compound, 6.7

compound_conditions syntax, 6.7

in, 6.8

logical

about, 6.3

AND, 6.3

NOT, 6.3

OR, 6.3

XOR, 6.3

nulls, 2.5.2, 6.6

pattern-matching

LIKE, 6.4

lk function, 8.1

precedence, 6.1.1

range, 6.5

const_bigint

clause, 7.1

syntax, 7.1

const_int

clause, 7.1

syntax, 7.1

const_string

clause, 7.1

quotation marks, 2.3.1

syntax, 7.1

text literals, 2.3.1

const_value

clause, 7.1

syntax, 7.1

CONTAIN geometric relation operator, 16.2.7

content, 5.1

Continuous Query Language. See CQL

conversion, 2.2.4

correlation function, 11.1.2

correlation variables, 19.1

correlation_name syntax, 19.3.1

correlation_name_definition syntax, 19.4

cos function, 12.1

cosh function, 12.1

count function, 9.1.1

covariance function, 11.1.2

CQL, 1.2.1, 1.2.1, 1.2.1, 1.2.1, 1.2.1

aliases

about, 2.7

ALIASES element, 2.7.2

AS operator, 2.7.1

type aliases, 2.7.2.1

comments, 1.2.1, 2.6

conditions, 6

.cqlx files, 1.2.1

datatype comparison rules, 2.2

datatypes, 2.1

expressions, 5

format models, 2.4

joins, 18

lexical conventions, 1.2.1

literals, 2.3

MATCH_RECOGNIZE, 19

naming rules, 2.8.1

nulls

about, 2.5

in comparison conditions, 2.5.2

in functions, 2.5.1

operators, 4

Oracle tools support of, 1.4

pattern recognition

about, 1.1.7, 19

pseudocolumns

about, 3

attr clause, 7.1

ELEMENT_TIME, 3.2

queries, 18

syntax, 1.2.3

views, 18

CQL data cartridge statements

array_type clause, 7.1

class_name clause, 7.1, 7.1

complex_type clause, 7.1

data_cartridge_name clause, 5.1

fieldname clause, 7.1, 7.1

link clause, 5.1

l-value clause, 7.1

methodname clause, 7.1

package_name clause, 7.1, 7.1

param_list clause, 7.1

qualified_type_name clause, 7.1

CQL statements

about, 1.2

array_type clause, 7.1

attr clause, 7.1

attrspec clause, 7.1

binary clause, 20.1

class_name clause, 7.1, 7.1

const_bigint clause, 7.1

const_int clause, 7.1

const_string clause, 7.1

const_value clause, 7.1

data_cartridge_name clause, 5.1

fieldname clause, 7.1, 7.1

identifier clause, 7.1

IN and NOT IN, 20.1

INTERSECT, 20.1

lexical conventions, 1.2.1

link clause, 5.1

l-value clause, 7.1

methodname clause, 7.1

MINUS, 20.1

non_mt_arg_list clause, 7.1

non_mt_attr_list clause, 7.1

non_mt_attrname_list clause, 7.1

non_mt_attrspec_list clause, 7.1

non_mt_cond_list clause, 7.1

package_name clause, 7.1, 7.1

param_list clause, 7.1

qualified_type_name clause, 7.1

query, 20.1

query_ref clause, 7.1

relation_variable, 20.1

set, 20.1

syntax conventions, 1.2.3

time_spec clause, 7.1

UNION and UNION ALL, 20.1

view, 20.1

xml_attr_list clause, 7.1

xml_attribute_list clause, 7.1

xqryargs_list clause, 7.1

.cqlx

case, 1.2.1

CDATA, 1.2.1

CEP DDL use, 1.2.1

comments, 1.2.1

lexical conventions, 1.2.1

rules, 1.2.1

statement terminator, 1.2.1

whitespace, 1.2.1

createElemInfo Geometry method, 16.2.7, 16.2.7

createGeometry Geometry method, 16.2.7

createLinearPolygon Geometry method, 16.2.7

createPoint Geometry method, 16.2.7

createRectangle Geometry method, 16.2.7

D

data cartridges

about, 1.1.12, 14.1

application context, 14.1.2

complex_type

constructor invocation, 7.1

field access, 7.1

instantiation, 7.1

method access, 7.1

data cartridge name

java, 15.1.1

jdbc, 17.1.1

spatial, 16.1.1

datatypes, and, 2.1.2

Oracle CQL queries, 18.7

Oracle JDBC data cartridge

application context, 17.1.4

Oracle Spatial

application context, 16.1.4

TABLE query, 18.2.7, 20.1

types

Java, 15.1

Oracle JDBC, 17.1

Oracle Spatial, 16.1

view schema, 20.1

data destinations

about, 1.1.8, 1.1.8.2

data sources

cache, 1.1.10

function table, 1.1.9.3

relational database table, 1.1.9.1

table, 1.1.9

XML table, 1.1.9.2

data_cartridge_name

clause, 5.1

datatype syntax, 2.1

datatypes

about, 2.1

aliases, 2.7.2.1

BIGINT, 2.1.1

BOOLEAN, 2.1.1

built-in, 2.1.1

BYTE, 2.1.1

CHAR, 2.1.1

comparison rules, 2.2.1

about, 2.2

character values, 2.2.3

conversion

about, 2.2.4

explicit, 2.2.4.2

implicit, 2.2.4.1

JDBC, 2.2.4.3

SQL, 2.2.4.3

user-defined functions, 2.2.4.4, 2.2.4.5, 13.1.2

data cartridges, 2.1.2

date value comparison rules, 2.2.2

DOUBLE, 2.1.1

enum, 2.1.3

evaluating with functions, 2.1.3

FLOAT, 2.1.1

format models

about, 2.4

datetime, 2.4.2, 8.1

number, 2.4.1

INTEGER, 2.1.1

INTERVAL, 2.1.1

literals

about, 2.3

float, 2.3.2.2

integer, 2.3.2.1

interval, 2.3.4

interval day to second, 2.3.4.1

text, 2.3.1

timestamp, 2.3.3

mapping in user-defined functions, 13.1.2

OBJECT, 2.1.1

opaque, 2.1.1

Oracle JDBC data cartridge, 17.1.2

Oracle Spatial, 16.1.2.1

other, 2.1.2, 2.1.3

TIMESTAMP, 2.1.1

type aliases, 2.7.2.1

unsupported, 2.1.2, 2.1.3

user-defined functions, 13.1.2

XMLTYPE, 2.1.1

datetime literals

about, 2.3.3

xsd

dateTime, 2.3.3

day, 7.1

days, 7.1

DDL

query, 20.1

view, 20.1

decimal characters, 2.3.2.2

decode

in arithmetic expressions, 5.1

nulls, 2.5.2

decode syntax, 5.1

DEFINE clause

about, 19.4

correlation names, 19.4.2, 19.4.3

derived stream, 1.1.1.2, 1.1.1.2

distance Geometry method, 16.2.7

distinct

aggregate expressions, 5.1

built-in aggregate functions, 9.1

function expressions, 5.1

relations, 1.1.2

select_clause, 20.1

document, 5.1

DOUBLE datatype, 2.1.1

double quotes, 2.3.1

Dstream relation-to-stream operator, 4.1.2

DURATION clause, 19.9

DURATION MULTIPLES OF clause, 19.9

duration_clause syntax, 19.9

E

Eclipse, 1.4.2

enum datatypes, 2.1.3

equality test, 6.2

errorFunction function, 10.1

errorFunctionComplemented function, 10.1

event sinks

about, 1.1.8, 1.1.8.2

event sources

about, 1.1.8, 1.1.8.1

cache, 1.1.10

function table, 1.1.9.3

pull, 1.1.8.1, 18.2.8, 18.6

push, 1.1.8.1

relational database table, 1.1.9.1

table, 1.1.9

XML table, 1.1.9.2

exp function, 12.1

expm1 function, 12.1

expressions

about, 5

aggr_distinct_expr, 5.1

aggr_expr, 5.1

aggregate, 5.1, 5.1

aggregate distinct, 5.1

arith_expr, 5.1

arith_expr_list, 5.1

arithmetic, 5.1, 5.1

case, 5.1

case_expr, 5.1

decode, 5.1, 5.1

func_expr, 5.1

function, 5.1, 5.1, 5.1, 5.1, 5.1

object, 5.1

object_expr, 5.1

order, 5.1

order_expr, 5.1

xml_agg_expr, 5.1

xml_parse_expr, 5.1

xmlcolattval_expr, 5.1

xmlelement_expr, 5.1

xmlforest_expr, 5.1

extended_builtin_aggr syntax, 9.1

F

factorial function, 10.1

fieldname

clause, 7.1, 7.1

FILTER geometric filter operator, 16.2.7

first function, 9.1.1

fixed duration non-event detection, 19.9.1

fixed_length_datatype syntax, 2.1

FLOAT datatype, 2.1.1

floor function, 10.1

floor1 function, 12.1

format models

about, 2.4

datetime, 2.4.2, 8.1

number, 2.4.1

from_clause syntax, 18.2.1.3, 18.4.2, 20.1

func_expr syntax, 5.1

func_name syntax, 5.1

functions

abs, 12.1

abs1, 12.1

abs2, 12.1

abs3, 12.1

acos, 12.1

asin, 12.1

atan, 12.1

atan2, 12.1

autoCorrelation, 11.1.2

avg, 9.1.1

beta, 10.1

beta1, 10.1

betaComplemented, 10.1

binomial, 10.1

binomial1, 10.1

binomial2, 10.1

binomialComplemented, 10.1

bitMaskWithBitsSetFromTo, 10.1

cbrt, 12.1

ceil, 10.1

ceil1, 12.1

chiSquare, 10.1

chiSquareComplemented, 10.1

concat, 8.1

correlation, 11.1.2

cos, 12.1

cosh, 12.1

count, 9.1.1

covariance, 11.1.2

errorFunction, 10.1

errorFunctionComplemented, 10.1

exp, 12.1

expm1, 12.1

factorial, 10.1

first, 9.1.1

floor, 10.1

floor1, 12.1

gamma, 10.1

gamma1, 10.1

gammaComplemented, 10.1

geometricMean, 11.1.2

geometricMean1, 11.1.2

getseedatrowcolumn, 10.1

harmonicMean, 11.1.2

hash, 10.1

hash1, 10.1

hash2, 10.1

hash3, 10.1

hextoraw, 8.1

hypot, 12.1

i0, 10.1

i0e, 10.1

i1, 10.1

i1e, 10.1

IEEERemainder, 12.1

incompleteBeta, 10.1

incompleteGamma, 10.1

incompleteGammaComplement, 10.1

j0, 10.1

j1, 10.1

jn, 10.1

k0, 10.1

k0e, 10.1

k1, 10.1

k1e, 10.1

kn, 10.1

kurtosis, 11.1.2

lag1, 11.1.2

last, 9.1.1

leastSignificantBit, 10.1

length, 8.1

lk, 8.1

log, 10.1

log1, 12.1

log10, 10.1

log101, 12.1

log1p, 12.1

log2, 10.1

logFactorial, 10.1

logGamma, 10.1

longFactorial, 10.1

max, 9.1.1

mean, 11.1.2

meanDeviation, 11.1.2

median, 11.1.2

min, 9.1.1

moment, 11.1.2

mostSignificantBit, 10.1

negativeBinomial, 10.1

negativeBinomialComplemented, 10.1

normal, 10.1

normal1, 10.1

normalInverse, 10.1

nvl, 8.1

ordsgenerator, 16.2.7

poisson, 10.1

poissonComplemented, 10.1

pooledMean, 11.1.2

pooledVariance, 11.1.2

pow, 12.1

prev, 8.1

product, 11.1.2

quantile, 11.1.2

quantileInverse, 11.1.2

rankInterpolated, 11.1.2

rawtohex, 8.1

rint, 12.1

rms, 11.1.2

round, 12.1

round1, 12.1

sampleKurtosis, 11.1.2

sampleKurtosisStandardError, 11.1.2

sampleSkew, 11.1.2

sampleSkewStandardError, 11.1.2

sampleVariance, 11.1.2

signum, 12.1

signum1, 12.1

sin, 12.1

sinh, 12.1

skew, 11.1.2

sqrt, 12.1

standardDeviation, 11.1.2

standardError, 11.1.2

stirlingCorrection, 10.1

studentT, 10.1

studentTInverse, 10.1

sum, 9.1.1

sumOfInversions, 11.1.2

sumOfLogarithms, 11.1.2

sumOfPowerDeviations, 11.1.2

sumOfPowers, 11.1.2

sumOfSquaredDeviations, 11.1.2

sumOfSquares, 11.1.2

systemtimestamp, 8.1

tables, 1.1.9.3

tan, 12.1

tanh, 12.1

to_bigint, 8.1

to_boolean, 8.1

to_char, 8.1

to_double, 8.1

to_float, 8.1

to_timestamp, 8.1

todegrees, 12.1

toradians, 12.1

trimmedMean, 11.1.2

ulp, 12.1

ulp1, 12.1

variance, 11.1.2

weightedMean, 11.1.2

winsorizedMean, 11.1.2

xmlagg, 9.1.1

xmlcomment, 8.1

xmlconcat, 8.1

xmlexists, 8.1

xmlquery, 8.1

y0, 10.1

y1, 10.1

yn, 10.1

functions about

AggregationFunctionFactory, 13.2.2

categories, 1.1.11

datatype mapping, 13.1.2

distinct and aggregate built-in functions, 9.1

java.lang.Math, 1.1.11

naming rules, 1.1.11, 2.8.1, 13.1.1

nested aggregates, 9.1

nulls, 2.5.1

overloading, 1.1.11, 2.8.1, 13.1.1

overriding, 1.1.11, 2.8.1, 13.1.1

single-row built-in, 1.1.11

user-defined, 1.1.11

G

gamma function, 10.1

gamma1 function, 10.1

gammacomplemented function, 10.1

geometricMean function, 11.1.2

geometricMean1 function, 11.1.2

Geometry class, 16.1.3

get2dMbr Geometry method, 16.2.7

getseedatrowcolumn function, 10.1

greater than or equal to tests, 6.2

greater than tests, 6.2

group by

ELEMENT_TIME, 3.2.2.2

GROUP BY clause, 20.1

ORDER BY clause, 19.6

PARTITION BY clause, 19.5

partitioned window, 4.1.2, 4.1.2, 4.1.2

group match, 19.1.2

H

harmonicMean function, 11.1.2

hash function, 10.1

hash1 function, 10.1

hash2 function, 10.1

hash3 function, 10.1

HAVING clause, 20.1

heartbeat

system timestamped relations, 1.1.13

system timestamped streams, 1.1.13

hextoraw function, 8.1

hour, 7.1

hours, 7.1

hypot function, 12.1

I

i0 function, 10.1

i0e function, 10.1

i1 function, 10.1

i1e function, 10.1

identifier

clause, 7.1

syntax, 7.1

IEEERemainder function, 12.1

IN clause, 20.1

in conditions, 6.8

in_condition_membership syntax, 6.8.2

in_condition_set syntax, 20.1

INCLUDE TIMER EVENTS clause, 19.10

incompleteBeta function, 10.1

incompleteGamma function, 10.1

incompleteGammaComplement function, 10.1

incremental computation

about, 13.1.1.2

implementing, 13.2.2

run-time behavior, 13.2.2

inequality test, 6.2

inner joins, 18.4.1

INSIDE geometric relation operator, 16.2.7

INTEGER datatype, 2.1.1

integer syntax, 2.3.2.1

integers

in CQL syntax, 2.3.2.1

precision of, 2.3.2.1

INTERSECT clause, 20.1

INTERVAL datatype, 2.1.1

interval_value syntax, 2.3.4.1, 7.1

IS NOT NULL condition, 6.6

IS NULL condition, 6.6

is-total-order, 1.1.13

Istream relation-to-stream operator, 4.1.2

J

j0 function, 10.1

j1 function, 10.1

Java data cartridge

about, 15.1

class loading

about, 15.1.2

application class space policy, 15.1.2.1

example, 15.1.2.4

method resolution, 15.1.3

no automatic import class space policy, 15.1.2.2

server class space policy, 15.1.2.3

data cartridge name, 15.1.1

datatype mapping, 15.1.4

default package name, 15.1.1

java.lang package, 15.1.1

limitations, 15.1.5

method resolution, 15.1.3

Oracle CQL query support, 15.1.5

queries

limitations, 15.1.5

using exported Java classes, 15.2.2

using the Java API, 15.2.1

unqualified types, 15.1.1

java.lang.Math built-in functions, 12

jc:jdbc-ctx, 17.1.4.2

jdbc:jdbc-context, 17.1.4.1

jn function, 10.1

joins

about, 18, 18.4

inner, 18.4.1

left outer, 18.4.2, 18.4.2.1

outer, 18.4.2, 18.4.2.1, 18.4.2.2, 18.4.2.3

outer join look-back, 18.4.2.3

right outer, 18.4.2, 18.4.2.2

simple, 18.4.1

views, 18.3.1

K

k0 function, 10.1

k0e function, 10.1

k1 function, 10.1

k1e function, 10.1

keywords in object names, 2.8.1, 7.1, 7.1

kn function, 10.1

kurtosis function, 11.1.2

L

lag1 function, 11.1.2

last function, 9.1.1

leastSignificantBit function, 10.1

left outer joins, 18.4.2, 18.4.2.1

length function, 8.1

less than tests, 6.2

lexical conventions

about, 1.2.1

case, 1.2.1, 1.2.1

CDATA, 1.2.1, 1.2.1

CEP DDL use, 1.2.1, 1.2.1

comments, 1.2.1

.cqlx, 1.2.1

query, 1.2.1

statement terminator, 1.2.1, 1.2.1

view, 1.2.1

whitespace, 1.2.1, 1.2.1

LIKE condition, 6.4, 8.1

like_condition syntax, 6.4

link

clause, 5.1

literals, 2.3

lk function, 8.1

locale

decimal characters, 2.3.2.2

nonquoted identifiers, 2.8.1

ORDER BY, 18.2.9

RAWTOHEX, 8.1

text literals, 2.3.1

log function, 10.1

log1 function, 12.1

log10 function, 10.1

log101 function, 12.1

log1p function, 12.1

log2 function, 10.1

logFactorial function, 10.1

logGamma function, 10.1

logical conditions, 6.3

AND, 6.3

NOT, 6.3

OR, 6.3

XOR, 6.3

longFactorial function, 10.1

l-value

clause, 7.1

M

MATCH_RECOGNIZE clause

about, 19

aggregates

about, 19.1.3

correlation variable restrictions, 19.1.3.2, 19.1.3.3, 19.1.3.4, 19.1.3.5

count, 19.1.3.5

count(*), 19.1.3.5

count(identifier.*), 19.1.3.5

count(identifier.attr), 19.1.3.5

final, 19.1.3.1

first, 19.1.3.6

last, 19.1.3.6

prev, 19.1.4

running, 19.1.3.1

ALL MATCHES clause

about, 19.7

partial overlapping patterns, 19.7

total overlapping patterns, 19.7

correlation variables, 19.1

DEFINE clause, 19.4

DURATION clause, 19.9

DURATION MULTIPLES OF clause, 19.9

examples

non-event detection, fixed duration, 19.12.5

pattern detection, 19.12.1

pattern detection with aggregates, 19.12.3

pattern detection with partition by, 19.12.2

WITHIN clause, 19.12.4

group match, 19.1.2

INCLUDE TIMER EVENTS clause, 19.10

MEASURES clause, 19.2

ORDER BY clause, 19.6

PARTITION BY clause, 19.5

PATTERN clause

about, 19.3

alternation operator, 19.3.2

default, 19.3.1

group matches, 19.1.2

grouping operator, 19.3.2

quantifiers, 19.3.1

regular expressions, 19.3.1

singleton matches, 19.1.2

queries, 18.2.4

singleton match, 19.1.2

sub-clauses

optional, 19.1

principle, 19.1

SUBSET clause, 19.11

WHERE clause, 19.1.1

WITHIN clause, 19.8

WITHIN INCLUSIVE clause, 19.8

max function, 9.1.1

MBR

geometric index, 16.1.2.4

get2dMbr method, 16.2.7

getMBR method, 16.1.2.7.2

mean function, 11.1.2

meanDeviation function, 11.1.2

measure_column syntax, 19.2

MEASURES clause, 19.2

median function, 11.1.2

methodname

clause, 7.1

millisecond, 7.1

milliseconds, 7.1

min function, 9.1.1

Minimum Bounding Rectangle. See MBR

MINUS clause, 20.1

minute, 7.1

minutes, 7.1

moment function, 11.1.2

mostSignificantBit function, 10.1

multibyte characters, 2.8.1

N

naming rules

about, 2.8.1

NaN, 9.1.1

nanosecond, 7.1

nanoseconds, 7.1

negativeBinomial function, 10.1

negativeBinomialComplemented function, 10.1

NN geometric relation operator, 16.2.7

non_mt_arg_list

clause, 7.1

syntax, 7.1

non_mt_arg_list_set syntax, 6.8.2

non_mt_attr_list

clause, 7.1

syntax, 7.1

non_mt_attrname_list

clause, 7.1

syntax, 7.1

non_mt_attrspec_list

clause, 7.1

syntax, 7.1

non_mt_cond_list

clause, 7.1

syntax, 7.1

non_mt_corr_list syntax, 19.11

non_mt_corrname_definition_list syntax, 19.4

non_mt_measure_list syntax, 19.2

non_mt_projterm_list syntax, 20.1

non_mt_relation_list syntax, 20.1

non_mt_subset_definition_list syntax, 19.11

non-event detection

about, 19.12.5

fixed duration, 19.9.1, 19.12.5

recurring, 19.9.2

non-events, 19.12.5

normal function, 10.1

normal1 function, 10.1

normalInverse function, 10.1

NOT condition, 6.3, 6.3

NOT IN clause, 20.1

null_conditions syntax, 6.6

null_spec syntax, 20.1

nulls

about, 2.5

conditions, 6.6

decode, 2.5.2

in conditions, 2.5.3, 2.5.3

in functions, 2.5.1

IS NOT NULL condition, 6.6

IS NULL condition, 6.6

nvl function, 8.1

value conversion, 8.1

with comparison conditions, 2.5.2

number precision, 2.3.2.2

number syntax, 2.3.2.2

numeric literals

NaN, 9.1.1

numeric values

about, 2.2.1

comparison rules, 2.2.1

nvl function, 8.1

O

OBJECT datatype, 2.1.1

object names and keywords, 2.8.1, 7.1, 7.1

object_expr syntax, 5.1

OCEP_JAVA_CARTRIDGE_CLASS_SPACE, 15.1.2.2, 15.1.2.3

ON clause, 18.4.2

opaque data type, 2.1.1

operands, 4

operations

relation-to-stream, 1.1.4

stream-to-stream, 1.1.5

operators, 4

arithmetic, 4.1.2

binary, 4.1.1

concatenation, 4.1.2, 4.1.2, 8.1, 8.1

precedence, 4.1.2

relation-to-stream

Dstream, 4.1.2

Istream, 4.1.2

Rstream, 4.1.2

stream-to-relation

about, 1.1.3

default, 1.1.3.3

partitioned S[Partition By A1 ... Ak Rows N Range T], 4.1.2

partitioned S[Partition By A1 ... Ak Rows N Range T1 Slide T2], 4.1.2

partitioned S[Partition By A1 ... Ak Rows N], 4.1.2

S[Now] time-based, 4.1.2

S[Range C on E] constant value-based, 4.1.2

S[Range T] time-based, 4.1.2

S[Range T1 Slide T2] time-based, 4.1.2

S[Range Unbounded] time-based, 4.1.2

S[Rows N] tuple-based, 4.1.2

S[Rows N1 slide N2] tuple-based, 4.1.2

unary, 4.1.1

opt_group_by_clause syntax, 20.1

opt_having_clause syntax, 20.1

opt_where_clause syntax, 20.1

OR condition, 6.3, 6.3

Oracle JDBC data cartridge

about, 17.1

application context

about, 17.1.4

jc:jdbc-ctx, 17.1.4.2

jdbc:jdbc-context, 17.1.4.1

data cartridge name, 17.1.1

datatype mapping

about, 17.1.3

param element, 17.2.1.2.1

return-component-type element, 17.2.1.2.2

sql element, 17.2.1.2.3

Oracle CQL queries, 17.2.2

scope, 17.1.2

SQL statements, 17.2.1

Oracle Spatial

about, 16.1

application context, 16.1.4

coordinate systems, 16.1.2.3

data cartridge name, 16.1.1

datatype mapping, 16.1.3

functions

ordsgenerator, 16.2.7

Geometry class, 16.1.3

Geometry methods

about, 16.1.2.7

bufferPolygon, 16.2.7

createElemInfo, 16.2.7, 16.2.7

createGeometry, 16.2.7

createLinearPolygon, 16.2.7

createPoint, 16.2.7

createRectangle, 16.2.7

distance, 16.2.7

get2dMbr, 16.2.7

to_Geometry, 16.2.7

to_JGeometry, 16.2.7

indexing, 16.1.2.4

MBR, 16.1.2.4, 16.1.2.7.2, 16.2.7

operators

ANYINTERACT, 16.2.7

CONTAIN, 16.2.7

FILTER, 16.2.7

filter, 16.1.2.6

INSIDE, 16.2.7

NN, 16.1.2.6, 16.2.7

relation, 16.1.2.5

WITHINDISTANCE, 16.2.7

ordinate systems, 16.1.2.3

scope

about, 16.1.2

coordinate systems, 16.1.2.3

datatypes, 16.1.2.1

element info array, 16.1.2.2

filter operators, 16.1.2.6

geometrix index, 16.1.2.4

Geometry methods, 16.1.2.7

MBR, 16.1.2.4

ordinate systems, 16.1.2.3

relation operators, 16.1.2.5

Oracle tools

about, 1.4

support of CQL, 1.4

ORDER BY clause, 19.6

about, 20.1, 20.1

data cartridge

field access, 7.1

of SELECT, 18.2.9

order by top, 20.1

rows, 20.1

ORDER BY ROWS clause, 20.1

order_by_clause syntax, 18.2.9, 20.1

order_by_list syntax, 20.1

order_by_top_clause syntax, 20.1

order_expr syntax, 5.1

orderterm syntax, 20.1

ordsgenerator function, 16.2.7

outer join look-back, 18.4.2.3

outer joins, 18.4.2, 18.4.2.1, 18.4.2.2, 18.4.2.3

ON clause, 18.4.2

overlapping patterns

partial, 19.7

total, 19.7

overloading functions, 1.1.11, 2.8.1, 13.1.1

overriding

functions, 1.1.11, 2.8.1, 13.1.1

P

package_name

clause, 7.1, 7.1

param_list

clause, 7.1

parameterized queries

about, 18.2.11

bindings element, 18.2.11.2

lexical conventions, 18.2.11.4

Oracle CQL statements, 18.2.11.1

parameter values, 18.2.11.4

runtime, 18.2.11.5

views, 18.2.11

partial overlapping patterns, 19.7

partition by

rows, 4.1.2

rows and range, 4.1.2

rows, range, and slide, 4.1.2

window specification, 1.1.3.2

PARTITION BY clause, 19.5

PATTERN clause

about, 19.3

alternation operator, 19.3.2

default, 19.3.1

group matches, 19.1.2

grouping operator, 19.3.2

permutation, 19.3.2

quantifiers, 19.3.1

regular expressions, 19.3.1

singleton matches, 19.1.2

pattern quantifier

(), 19.3.2

*, 19.3.1

*?, 19.3.1

+, 19.3.1

+?, 19.3.1

?, 19.3.1

??, 19.3.1

|, 19.3.2

0 or 1 times (maximal), 19.3.1

0 or 1 times (minimal), 19.3.1

0 or more times (maximal), 19.3.1

0 or more times (minimal), 19.3.1

1 or more times (maximal), 19.3.1

1 or more times (minimal), 19.3.1

alternation, 19.3.2

exactly 1 time, 19.3.1

grouping, 19.3.2

None, 19.3.1

pattern recognition. See MATCH_RECOGNIZE clause

pattern_clause syntax, 19.3

pattern_def_dur_clause syntax, 19.1

pattern_definition_clause syntax, 19.4

pattern_inc_timer_evs_clause syntax, 19.10

pattern_measures_clause syntax, 19.2

pattern_partition_clause syntax, 19.5, 19.6

pattern_quantifier syntax, 19.3.1

pattern_recognition_clause syntax, 19.1

pattern_skip_match_clause syntax, 19.7

pattern-matching conditions

LIKE, 6.4

lk function, 8.1

poisson function, 10.1

poissonComplemented function, 10.1

pooledMean function, 11.1.2

pooledVariance function, 11.1.2

pow function, 12.1

precedence

operators, 4.1.2

precision and number of digits of, 2.3.2.2

prev function, 8.1

product function, 11.1.2

projterm syntax, 20.1

pseudo_column syntax, 7.1

pseudocoloumns

about, 3

attr clause, 7.1

ELEMENT_TIME

about, 3.2

application-timestamped value, 3.2.1.2

bigint application-timestamped value, 3.2.1.2.1

int application-timestamped value, 3.2.1.2.1

system-timestamped value, 3.2.1.1

timestamp application-timestamped value, 3.2.1.2.2

with GROUP BY, 3.2.2.2

with PATTERN, 3.2.2.3

with SELECT, 3.2.2.1

pull event sources, 1.1.8.1, 18.2.8, 18.6

push event sources, 1.1.8.1

Q

qualified_type_name

clause, 7.1

quantile function, 11.1.2

quantileInverse function, 11.1.2

queries

about, 18, 18.2

built-in windows, 18.2.3

cache, 18.2.8, 18.5

data cartridges, 18.7

detecting differences, 18.2.10

DIFFERENCE USING, 18.2.10

MATCH_RECOGNIZE clause, 18.2.4

ORDER_BY, 18.2.9

parameterized, 18.2.11

simple, 18.2.2

sorting, 18.2.9

subqueries, 1.1.6, 18.1

TABLE, 18.2.7, 20.1

table

data cartridge, 18.2.7, 20.1

relational database, 18.2.5

relational database datatypes, 2.2.4.3

XML, 18.2.6

views, 18.3

XMLTABLE, 18.2.6

query

attributes

id, 20.1

statement, 20.1

query syntax, 18.2, 20.1

query_ref

clause, 7.1

syntax, 7.1

quotation marks

about, 2.3.1

locale, 2.8.1

text literals, 2.3.1

quoted_string_double_quotes syntax, 7.1

quoted_string_single_quotes syntax, 7.1

R

range

conditions, 6.5

window specification

about, 1.1.3.1

default, 1.1.3.1

rankInterpolated function, 11.1.2

rawtohex function, 8.1

recurring non-event detection, 19.9.2

regexp syntax, 19.3.1

regexp_grp_alt syntax, 19.3.2

relation_variable syntax, 18.2.1.3, 20.1

relational database tables, 1.1.9.1

relations

about, 1.1.1.2

base, 1.1.1.2

distinct, 1.1.2

heartbeat, 1.1.13

operation indicator

deletion (-), 1.1.1.3

insertion (+), 1.1.1.3

update (U), 1.1.1.3

schema, 1.1.1.2

tuple kind indicator, 1.1.1.3

relation-to-stream operators, 1.1.4

Dstream, 4.1.2

Istream, 4.1.2

Rstream, 4.1.2

reserved words, 2.8.1, 2.8.1, 7.1

right outer joins, 18.4.2, 18.4.2.2

rint function, 12.1

rms function, 11.1.2

round function, 12.1

round1 function, 12.1

Rstream relation-to-stream operator, 4.1.2

S

sampleKurtosis function, 11.1.2

sampleKurtosisStandardError function, 11.1.2

sampleSkew function, 11.1.2

sampleSkewStandardError function, 11.1.2

sampleVariance function, 11.1.2

scheduler

time, 1.1.13

schema

relation, 1.1.1.2

stream, 1.1.1.1, 1.1.1.1.2

schema objects

naming

examples, 2.8.3

guidelines, 2.8.2

searched_case syntax, 5.1

searched_case_list syntax, 5.1

second, 7.1

seconds, 7.1

select_clause

distinct, 20.1

syntax, 18.2.1.2, 20.1

SERVER_CLASS_SPACE, 15.1.2.3

set statements

about, 20.1

IN and NOT IN, 20.1

INTERSECT, 20.1

MINUS, 20.1

UNION and UNION ALL, 20.1

sfw_block syntax, 18.2.1.1, 20.1

signum function, 12.1

signum1 function, 12.1

simple comparison conditions, 6.2

simple joins, 18.4.1

simple queries, 18.2.2

simple_case syntax, 5.1

simple_case_list syntax, 5.1

sin function, 12.1

single quotes, 2.3.1

singleton match, 19.1.2

sinh function, 12.1

skew function, 11.1.2

slide

window specification

about, 1.1.3.1

default, 1.1.3.1

SOME operator, 6.2

sorting query results, 18.2.9

SQL99, 1.3

SQLX

about, 5.1

content, 5.1

datatype conversion, 2.2.4.3

document, 5.1

expressions, 5.1

func_expr, 5.1

functions, 8.1

wellformed, 5.1

xml_agg_expr, 5.1

xml_parse_expr, 5.1

xmlagg function, 9.1.1

xmlcolattval_expr, 5.1

xmlcomment function, 8.1

xmlconcat function, 8.1

xmlelement_expr, 5.1

xmlexists function, 8.1

xmlforest_expr, 5.1

xmlquery function, 8.1

SQL/XML. See SQLX

sqrt function, 12.1

standardDeviation function, 11.1.2

standardError function, 11.1.2

standards, 1.3

statement terminator, 1.2.1

stirlingCorrection function, 10.1

streams

about, 1.1.1.1

base, 1.1.1.2

channel, 1.1.1.1.1

derived, 1.1.1.2, 1.1.1.2

heartbeat, 1.1.13

query selector, 1.1.1.1.4

relation, 1.1.1.2, 1.1.1.2

schema, 1.1.1.1, 1.1.1.1.2

tuple, 1.1.1.1

stream-to-relation operators

about, 1.1.3

default, 1.1.3.3

group by, 3.2.2.2, 4.1.2, 4.1.2, 4.1.2

partitioned S[Partition By A1 ... Ak Rows N Range T], 4.1.2

partitioned S[Partition By A1 ... Ak Rows N Range T1 Slide T2], 4.1.2

partitioned S[Partition By A1 ... Ak Rows N], 4.1.2

S[Now] time-based, 4.1.2

S[Range C on E] constant value-based, 4.1.2

S[Range T] time-based, 4.1.2

S[Range T1 Slide T2] time-based, 4.1.2

S[Range Unbounded] time-based, 4.1.2

S[Rows N] tuple-based, 4.1.2

S[Rows N1 slide N2] tuple-based, 4.1.2

stream-to-stream operators, 1.1.5

filter, 1.1.5

MATCH_RECOGNIZE, 1.1.5

project, 1.1.5

XMLTABLE, 1.1.5

studentT function, 10.1

studentTInverse function, 10.1

subqueries, 1.1.6, 1.1.6, 18.1

SUBSET clause, 19.11

subset_clause

about, 19.11

syntax, 19.11

subset_definition syntax, 19.11

subset_name syntax, 19.11

sum function, 9.1.1

sumOfInversions function, 11.1.2

sumOfLogarithms function, 11.1.2

sumOfPowerDeviations function, 11.1.2

sumOfPowers function, 11.1.2

sumOfSquaredDeviations function, 11.1.2

sumOfSquares function, 11.1.2

synonyms. See aliases

Syntax, 10.1

system time, 1.1.13

system timestamped, 1.1.13

systemtimestamp function, 8.1

T

TABLE queries, 18.2.7, 20.1

table_clause syntax, 18.2.7, 20.1

tables, 1.1.9

queries

data cartridge, 18.2.7, 20.1

relational database, 18.2.5

relational database datatypes, 2.2.4.3

XML, 18.2.6

tan function, 12.1

tanh function, 12.1

terminator, 1.2.1

text literals

about, 2.3.1

quotation marks, 2.3.1

time

about, 1.1.13

application, 1.1.13

application timestamped, 1.1.13

heartbeat, 1.1.13

is-total-order, 1.1.13

scheduler, 1.1.13

system, 1.1.13

system timestamped, 1.1.13

time system timestamped, 1.1.13

time_spec

clause, 7.1

syntax, 7.1

time_unit

syntax, 7.1

TIMESTAMP

about, 2.3.3

xsd

dateTime, 2.3.3

TIMESTAMP datatype, 2.1.1

timestamps

application, 1.1.13

system, 1.1.13

to_bigint function, 8.1

to_boolean function, 8.1

to_char function, 8.1

to_double function, 8.1

to_float function, 8.1

to_Geometry Geometry method, 16.2.7

to_JGeometry Geometry method, 16.2.7

to_timestamp function, 8.1

todegrees function, 12.1

tools support

development, 1.4.2

Oracle CEP IDE for Eclipse, 1.4.2

Oracle CEP Server, 1.4.1

Oracle CEP Visualizer, 1.4.1

runtime, 1.4.1

toradians function, 12.1

total overlapping patterns, 19.7

trimmedMean function, 11.1.2

tuple

about, 1.1.1.1

kind indicator, 1.1.1.3

streams, 1.1.1.1

U

U

relation update, 1.1.1.3

ulp function, 12.1

ulp1 function, 12.1

unary operators, 4.1.1

UNION ALL clause, 20.1

UNION clause, 20.1

unreserved words, 7.1

unreserved_keyword syntax, 7.1

user-defined functions

about, 1.1.11, 13

aggregate, 13.1.1.2

cache, 13.1.3

classpath, 13.2.1, 13.2.2

datatype mapping, 13.1.2

implementing, 13.1, 13.2

aggregate, 13.2.2

AggrFunctionImpl, 13.2.2

SingleElementFunction, 13.2.1

single-row, 13.2.1

incremental computation

about, 13.1.1.2

implementing, 13.2.2

run-time behavior, 13.2.2

single-row, 13.1.1.1

types of, 13.1.1

using_clause syntax, 18.2.10, 20.1

usingexpr syntax, 20.1

usinglist syntax, 20.1

usingterm syntax, 20.1

V

variable_length_datatype syntax, 2.1

variance function, 11.1.2

view

attributes

id, 20.1

schema, 20.1

statement, 20.1

views

about, 18

joins, 18.3.1

queries, 18.3

schema, 18.3.2

visibility, 18.3

W

weightedMean function, 11.1.2

wellformed, 5.1

WHERE clause

built-in aggregate functions, 9.1.1

Colt aggregate functions, 11.1.2

group by and having clause, 9.1.1, 11.1.2

MATCH_RECOGNIZE clause, 19.1.1

window_type syntax, 1.1.3, 20.1

window_type_partition syntax, 4.1.2

window_type_range syntax, 4.1.2

window_type_tuple syntax, 4.1.2

windows, 1.1.3

about, 1.1.3

Now, 4.1.2

partitioned, 1.1.3.2, 4.1.2, 4.1.2, 4.1.2

range specification

about, 1.1.3.1

default, 1.1.3.1

S[Now] time-based, 4.1.2

S[Partition By A1 ... Ak Rows N Range T], 4.1.2

S[Partition By A1 ... Ak Rows N Range T1 Slide T2], 4.1.2

S[Partition By A1 ... Ak Rows N], 4.1.2

S[Range C on E] constant value-based, 4.1.2

S[Range T] time-based, 4.1.2

S[Range T1 Slide T2] time-based, 4.1.2

S[Range Unbounded] time-based, 4.1.2

S[Rows N] tuple-based, 4.1.2

S[Rows N1 slide N2] tuple-based, 4.1.2

slide specification

about, 1.1.3.1

default, 1.1.3.1

Unbounded, 4.1.2

winsorizedMean function, 11.1.2

WITHIN clause, 19.8

WITHIN INCLUSIVE clause, 19.8

within_clause syntax, 19.8

WITHINDISTANCE geometric relation operator, 16.2.7

X

XML tables, 1.1.9.2

xml_agg_expr syntax, 5.1

xml_attr syntax, 7.1

xml_attr_list

clause, 7.1

syntax, 7.1

xml_attribute_list

clause, 7.1

syntax, 7.1

xml_namespace syntax, 20.1

xmlagg function, 9.1.1

xmlcolattval_expr syntax, 5.1, 5.1

xmlcomment function, 8.1

xmlconcat function, 8.1

xmlexists function, 8.1

xmlforest_expr syntax, 5.1, 5.1

xmlnamespace_clause syntax, 20.1

xmlnamespaces_list syntax, 20.1

xmlquery function, 8.1

XMLTABLE queries, 18.2.6

xmltable_clause syntax, 20.1

XMLTYPE datatype, 2.1.1

XOR condition, 6.3

xqryarg syntax, 7.1

xqryargs_list

clause, 7.1

syntax, 7.1

xsd

dateTime, 2.3.3

xstream_clause syntax, 20.1

xtbl_col syntax, 20.1

xtbl_cols_list syntax, 20.1

Y

y0 function, 10.1

y1 function, 10.1

yn function, 10.1

Copyright © 2006, 2011, Oracle and/or its affiliates. All rights reserved.