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