1/31
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
Introduction to Basic Elements of Oracle CQL
2.2
Datatypes
2.2.1
Oracle CQL Built-in Datatypes
2.2.2
Handling Other Datatypes Using Oracle CQL Data Cartridges
2.2.3
Handling Other Datatypes Using a User-Defined Function
2.3
Datatype Comparison Rules
2.3.1
Numeric Values
2.3.2
Date Values
2.3.3
Character Values
2.3.4
Datatype Conversion
2.3.4.1
Implicit Datatype Conversion
2.3.4.2
Explicit Datatype Conversion
2.3.4.3
SQL Datatype Conversion
2.3.4.4
Oracle Data Cartridge Datatype Conversion
2.3.4.5
User-Defined Function Datatype Conversion
2.4
Literals
2.4.1
Text Literals
2.4.2
Numeric Literals
2.4.2.1
Integer Literals
2.4.2.2
Floating-Point Literals
2.4.3
Datetime Literals
2.4.4
Interval Literals
2.4.4.1
INTERVAL DAY TO SECOND
2.5
Format Models
2.5.1
Number Format Models
2.5.2
Datetime Format Models
2.6
Nulls
2.6.1
Nulls in Oracle CQL Functions
2.6.2
Nulls with Comparison Conditions
2.6.3
Nulls in Conditions
2.7
Comments
2.8
Aliases
2.8.1
Defining Aliases Using the AS Operator
2.8.1.1
Aliases in the relation_variable Clause
2.8.1.2
Aliases in Window Operators
2.8.2
Defining Aliases Using the Aliases Element
2.8.2.1
How to Define a Data Type Alias Using the Aliases Element
2.9
Schema Object Names and Qualifiers
2.9.1
Schema Object Naming Rules
2.9.2
Schema Object Naming Guidelines
2.9.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
Scripting on this page enhances content navigation, but does not change the content in any way.