1/30
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
Using ELEMENT_TIME With SELECT
3.2.2
Using ELEMENT_TIME With GROUP BY
3.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
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
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.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.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 Data Cartridge
16.1
Understanding the Oracle Spatial Data Cartridge
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
Geometry Relation Operators
16.1.2.4
Geometry Filter Operators
16.1.2.5
Geometric Index
16.1.2.6
Ordinates and Coordinate Systems and the SDO_SRID
16.1.2.7
Geometry API
16.1.3
Datatype Mapping
16.1.4
Oracle Spatial Data Cartridge Application Context
16.2
Using the Oracle Spatial Data Cartridge
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
bufferPolygon
createElemInfo
createGeometry
createLinearPolygon
createPoint
createRectangle
distance
einfogenerator
get2dMbr
ordsgenerator
to_Geometry
to_JGeometry
Part IV Using Oracle CQL
17
Oracle CQL Queries, Views, and Joins
17.1
Introduction to Oracle CQL Queries, Views, and Joins
17.1.1
How to Create an Oracle CQL Query
17.2
Queries
17.2.1
Query Building Blocks
17.2.1.1
Select, From, Where Block
17.2.1.2
Select Clause
17.2.1.3
From Clause
17.2.1.4
Where Clause
17.2.1.5
Group By Clause
17.2.1.6
Order By Clause
17.2.1.7
Having Clause
17.2.1.8
Binary Clause
17.2.1.9
IDStream Clause
17.2.2
Simple Query
17.2.3
Built-In Window Query
17.2.4
MATCH_RECOGNIZE Query
17.2.5
Relational Database Table Query
17.2.6
XMLTable Query
17.2.7
Function TABLE Query
17.2.8
Cache Query
17.2.9
Sorting Query Results
17.2.10
Detecting Differences in Query Results
17.3
Views
17.3.1
Views and Joins
17.3.2
Views and Schemas
17.4
Joins
17.4.1
Inner Joins
17.4.2
Outer Joins
17.4.2.1
Left Outer Join
17.4.2.2
Right Outer Join
17.4.2.3
Outer Join Look-Back
17.5
Oracle CQL Queries and the Oracle CEP Server Cache
17.5.1
Creating Joins Against the Cache
17.5.1.1
Cache Key First and Simple Equality
17.5.1.2
No Arithmetic Operations on Cache Keys
17.5.1.3
No Full Scans
17.5.1.4
Multiple Conditions and Inequality
17.6
Oracle CQL Queries and Relational Database Tables
17.7
Oracle CQL Queries and Oracle Data Cartridges
18
Pattern Recognition With MATCH_RECOGNIZE
18.1
Understanding Pattern Recognition With MATCH_RECOGNIZE
18.2
ALL MATCHES Clause
18.3
DEFINE Clause
18.4
DURATION Clause
18.4.1
Using the DURATION Clause for Fixed Duration Non-Event Detection
18.4.2
Using the DURATION Clause for Recurring Non-Event Detection
18.5
MEASURES Clause
18.6
PARTITION BY Clause
18.7
PATTERN Clause
18.7.1
Using the PATTERN Clause
18.7.2
Using the PATTERN Clause and WITHIN Clause
18.8
SUBSET Clause
18.9
Examples
18.9.1
Pattern Detection
18.9.2
Pattern Detection With Partition By
18.9.3
Pattern Detection With Aggregates
18.9.4
Pattern Detection With the WITHIN Clause
18.9.5
Fixed Duration Non-Event Detection
19
Oracle CQL Statements
19.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.