Home
/
Middleware
/
Oracle Stream Explorer and Oracle Event Processing
1/25
Contents
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
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
2
Basic Elements of Oracle CQL
2.1
Data Types
2.1.1
Oracle CQL Built-in Data Types
2.1.2
Handling Other Data Types Using Oracle CQL Data Cartridges
2.1.3
Handling Other Data Types Using a User-Defined Function
2.2
Data Type Comparison Rules
2.2.1
Numeric Values
2.2.2
Date Values
2.2.3
Character Values
2.2.4
Data Type Conversion
2.2.4.1
Implicit Data Type Conversion
2.2.4.2
Explicit Data Type Conversion
2.2.4.3
SQL Data Type Conversion
2.2.4.4
Oracle Data Cartridge Data Type Conversion
2.2.4.5
User-Defined Function Data Type 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
4.2
Arithmetic Operators
4.3
Concatenation Operator
4.4
Alternation Operator
4.5
Range-Based Stream-to-Relation Window Operators
4.5.1
S[now]
4.5.1.1
Examples
4.5.2
S[range T]
4.5.2.1
Examples
4.5.3
S[range T1 slide T2]
4.5.3.1
Examples
4.5.4
S[range unbounded]
4.5.4.1
Examples
4.5.5
S[range C on E]
4.5.5.1
Examples
4.6
Tuple-Based Stream-to-Relation Window Operators
4.6.1
S [rows N]
4.6.1.1
Examples
4.6.2
S [rows N1 slide N2]
4.6.2.1
Examples
4.7
Partitioned Stream-to-Relation Window Operators
4.7.1
S [partition by A1,..., Ak rows N]
4.7.1.1
Examples
4.7.2
S [partition by A1,..., Ak rows N range T]
4.7.2.1
Examples
4.7.3
S [partition by A1,..., Ak rows N range T1 slide T2]
4.7.3.1
Examples
4.8
User-Defined Stream-to-Relation Window Operators
4.8.1
Implementing a User-Defined Window
4.8.1.1
How to Implement a User-Defined Generic Time Window
4.9
IStream Relation-to-Stream Operator
4.10
DStream Relation-to-Stream Operator
4.11
RStream Relation-to-Stream Operator
5
Expressions
5.1
Introduction to Expressions
5.2
aggr_distinct_expr
5.2.1
Examples
5.3
aggr_expr
5.3.1
Examples
5.4
arith_expr
5.4.1
Examples
5.5
arith_expr_list
5.5.1
Examples
5.6
case_expr
5.6.1
Examples
5.7
decode
5.7.1
Examples
5.8
func_expr
5.8.1
Examples
5.9
object_expr
5.9.1
Examples
5.10
order_expr
5.10.1
Examples
5.11
xml_agg_expr
5.11.1
Examples
5.12
xmlcolattval_expr
5.12.1
Examples
5.13
xmlelement_expr
5.13.1
Examples
5.14
xmlforest_expr
5.14.1
Examples
5.15
xml_parse_expr
5.15.1
Examples
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 Data Types
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 Using Oracle CQL
14
Oracle CQL Queries, Views, and Joins
14.1
Introduction to Oracle CQL Queries, Subqueries, Views, and Joins
14.2
Queries
14.2.1
Query Building Blocks
14.2.1.1
Select, From, Where Block
14.2.1.2
Select Clause
14.2.1.3
From Clause
14.2.1.4
Where Clause
14.2.1.5
Group By Clause
14.2.1.6
Order By Clause
14.2.1.7
Having Clause
14.2.1.8
Binary Clause
14.2.1.9
IDStream Clause
14.2.2
Simple Query
14.2.3
Built-In Window Query
14.2.4
User-Defined Window Query
14.2.5
MATCH_RECOGNIZE Query
14.2.6
Relational Database Table Query
14.2.7
XMLTABLE Query
14.2.8
Function TABLE Query
14.2.9
Cache Query
14.2.10
Sorting Query Results
14.2.11
Detecting Differences in Query Results
14.2.12
Parameterized Queries
14.2.12.1
Parameterized Queries in Oracle CQL Statements
14.2.12.2
The bindings Element
14.2.12.3
Run-Time Query Naming
14.2.12.4
Lexical Conventions for Parameter Values
14.2.12.5
Parameterized Queries at Runtime
14.2.12.6
Replacing Parameters Programmatically
14.2.13
Subqueries
14.3
Views
14.3.1
Views and Joins
14.3.2
Views and Schemas
14.3.3
Views and Subqueries
14.4
Joins
14.4.1
Inner Joins
14.4.2
Outer Joins
14.4.2.1
Left Outer Join
14.4.2.2
Right Outer Join
14.4.2.3
Outer Join Look-Back
14.5
Oracle CQL Queries and the Oracle Event Processing Server Cache
14.5.1
Creating Joins Against the Cache
14.5.1.1
Cache Key First and Simple Equality
14.5.1.2
No Arithmetic Operations on Cache Keys
14.5.1.3
No Full Scans
14.5.1.4
Multiple Conditions and Inequality
14.6
Oracle CQL Queries and Relational Database Tables
14.7
Oracle CQL Queries and Oracle Data Cartridges
15
Pattern Recognition With MATCH_RECOGNIZE
15.1
Understanding Pattern Recognition With MATCH_RECOGNIZE
15.1.1
MATCH_RECOGNIZE and the WHERE Clause
15.1.2
Referencing Singleton and Group Matches
15.1.3
Referencing Aggregates
15.1.3.1
Running Aggregates and Final Aggregates
15.1.3.2
Operating on the Same Correlation Variable
15.1.3.3
Referencing Variables That Have not Been Matched Yet
15.1.3.4
Referencing Attributes not Qualified by Correlation Variable
15.1.3.5
Using count With *,
identifier
.*, and
identifier
.
attr
15.1.3.6
Using first and last
15.1.4
Using prev
15.2
MEASURES Clause
15.2.1
Functions Over Correlation Variables in the MEASURES Clause
15.3
PATTERN Clause
15.3.1
Pattern Quantifiers and Regular Expressions
15.3.2
Grouping and Alternation in the PATTERN Clause
15.4
DEFINE Clause
15.4.1
Functions Over Correlation Variables in the DEFINE Clause
15.4.2
Referencing Attributes in the DEFINE Clause
15.4.3
Referencing One Correlation Variable From Another in the DEFINE Clause
15.5
PARTITION BY Clause
15.6
ALL MATCHES Clause
15.7
WITHIN Clause
15.8
DURATION Clause
15.8.1
Fixed Duration Non-Event Detection
15.8.2
Recurring Non-Event Detection
15.9
INCLUDE TIMER EVENTS Clause
15.10
SUBSET Clause
15.11
MATCH_RECOGNIZE Examples
15.11.1
Pattern Detection
15.11.2
Pattern Detection With PARTITION BY
15.11.3
Pattern Detection With Aggregates
15.11.4
Pattern Detection With the WITHIN Clause
15.11.5
Fixed Duration Non-Event Detection
16
Oracle CQL Statements
16.1
Introduction to Oracle CQL Statements
Query
View
Scripting on this page enhances content navigation, but does not change the content in any way.