1/33
Contents
List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Syntax Diagrams
What's New in This Guide
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 Event Processing 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 Event Processing Server and Tools Support
1.4.1
Oracle Event Processing Server
1.4.2
Oracle Event Processing Tools
1.4.2.1
Oracle Event Processing IDE for Eclipse
1.4.2.2
Oracle Event Processing 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
listagg
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 Event Processing 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 Event Processing JDBC Data Cartridge
17.1
Understanding the Oracle Event Processing JDBC Data Cartridge
17.1.1
Data Cartridge Name
17.1.2
Scope
17.1.3
Datatype Mapping
17.1.4
Oracle Event Processing 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 Event Processing 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 Event Processing 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
18
Oracle Event Processing Hadoop Data Cartridge
18.1
Understanding the Oracle Event Processing Hadoop Data Cartridge
18.1.1
Usage Scenario: Using Purchase Data to Develop Buying Incentives
18.1.2
Data Cartridge Name
18.2
Using Hadoop Data Sources in Oracle CQL
18.2.1
Configuring Integration of Oracle Event Processing and Hadoop
18.2.2
Integrating a File from a Hadoop System Into an EPN
18.2.3
Using Hadoop Data in Oracle CQL
19
Oracle Event Processing NoSQL Database Data Cartridge
19.1
Understanding the Oracle Event Processing NoSQL Database Data Cartridge
19.1.1
Data Cartridge Name
19.2
Using a NoSQL Database in Oracle CQL
19.2.1
Integrating a NoSQL Database Into an EPN
19.2.2
Using NoSQL Data in Oracle CQL
19.2.2.1
Formatting the Key Used to Obtain Entries from the NoSQL Store
Part IV Using Oracle CQL
20
Oracle CQL Queries, Views, and Joins
20.1
Introduction to Oracle CQL Queries, Views, and Joins
20.1.1
How to Create an Oracle CQL Query
20.2
Queries
20.2.1
Query Building Blocks
20.2.1.1
Select, From, Where Block
20.2.1.2
Select Clause
20.2.1.3
From Clause
20.2.1.4
Where Clause
20.2.1.5
Group By Clause
20.2.1.6
Order By Clause
20.2.1.7
Having Clause
20.2.1.8
Binary Clause
20.2.1.9
IDStream Clause
20.2.2
Simple Query
20.2.3
Built-In Window Query
20.2.4
MATCH_RECOGNIZE Query
20.2.5
Relational Database Table Query
20.2.6
XMLTABLE Query
20.2.7
Function TABLE Query
20.2.8
Cache Query
20.2.9
Sorting Query Results
20.2.10
Detecting Differences in Query Results
20.2.11
Parameterized Queries
20.2.11.1
Parameterized Queries in Oracle CQL Statements
20.2.11.2
The bindings Element
20.2.11.3
Run-Time Query Naming
20.2.11.4
Lexical Conventions for Parameter Values
20.2.11.5
Parameterized Queries at Runtime
20.2.11.6
Replacing Parameters Programmatically
20.3
Views
20.3.1
Views and Joins
20.3.2
Views and Schemas
20.4
Joins
20.4.1
Inner Joins
20.4.2
Outer Joins
20.4.2.1
Left Outer Join
20.4.2.2
Right Outer Join
20.4.2.3
Outer Join Look-Back
20.5
Oracle CQL Queries and the Oracle Event Processing Server Cache
20.5.1
Creating Joins Against the Cache
20.5.1.1
Cache Key First and Simple Equality
20.5.1.2
No Arithmetic Operations on Cache Keys
20.5.1.3
No Full Scans
20.5.1.4
Multiple Conditions and Inequality
20.6
Oracle CQL Queries and Relational Database Tables
20.7
Oracle CQL Queries and Oracle Data Cartridges
21
Pattern Recognition With MATCH_RECOGNIZE
21.1
Understanding Pattern Recognition With MATCH_RECOGNIZE
21.1.1
MATCH_RECOGNIZE and the WHERE Clause
21.1.2
Referencing Singleton and Group Matches
21.1.3
Referencing Aggregates
21.1.3.1
Running Aggregates and Final Aggregates
21.1.3.2
Operating on the Same Correlation Variable
21.1.3.3
Referencing Variables That Have not Been Matched Yet
21.1.3.4
Referencing Attributes not Qualified by Correlation Variable
21.1.3.5
Using count With *,
identifier
.*, and
identifier
.
attr
21.1.3.6
Using first and last
21.1.4
Using prev
21.2
MEASURES Clause
21.2.1
Functions Over Correlation Variables in the MEASURES Clause
21.3
PATTERN Clause
21.3.1
Pattern Quantifiers and Regular Expressions
21.3.2
Grouping and Alternation in the PATTERN Clause
21.4
DEFINE Clause
21.4.1
Functions Over Correlation Variables in the DEFINE Clause
21.4.2
Referencing Attributes in the DEFINE Clause
21.4.3
Referencing One Correlation Variable From Another in the DEFINE Clause
21.5
PARTITION BY Clause
21.6
ORDER BY Clause
21.7
ALL MATCHES Clause
21.8
WITHIN Clause
21.9
DURATION Clause
21.9.1
Fixed Duration Non-Event Detection
21.9.2
Recurring Non-Event Detection
21.10
INCLUDE TIMER EVENTS Clause
21.11
SUBSET Clause
21.12
MATCH_RECOGNIZE Examples
21.12.1
Pattern Detection
21.12.2
Pattern Detection With PARTITION BY
21.12.3
Pattern Detection With Aggregates
21.12.4
Pattern Detection With the WITHIN Clause
21.12.5
Fixed Duration Non-Event Detection
22
Oracle CQL Statements
22.1
Introduction to Oracle CQL Statements
Query
View
Scripting on this page enhances content navigation, but does not change the content in any way.