This chapter provides a reference to clauses in the data definition language (DDL) in Oracle Continuous Query Language (Oracle CQL).
This chapter includes the following section:
Oracle CQL supports the following common DDL clauses:
For more information on Oracle CQL statements, see Chapter 16, "Oracle CQL Statements".
Use the array_type
clause to specify an Oracle CQL data cartridge type composed of a sequence of complex_type
components, all of the same type.
Note:
Oracle CQL supports single-dimension arrays only. That is, you can usejava.lang.String[]
but not java.lang.String[][]
.You declare an array type using the qualified_type_name
of the Oracle CQL data cartridge complex_type
. Only arrays of complextype
are supported: you cannot declare an array of Oracle CQL simple types unless there is an equivalent type defined in the Oracle CQL Java data cartridge.
For examples, see:
You access a complex_type
array element by integer index. The index begins at 0 or 1 depending on the data cartridge implementation.
There is no support for the instantiation of new array type instances directly in Oracle CQL at the time you access an array. For example, the following is not allowed:
SELECT java.lang.String[10] ...
For examples, see "Array Access Examples".
The following examples illustrate the various semantics that this statement supports:
Array Declaration Example: complex_type
The following example shows how to create an event type as a Java class that specifies an event property as an array of Oracle CQL data cartridge complex type MyClass
defined in package com.mypackage
.
package com.myapplication.event; import java.util.Date; public final class MarketEvent { private final String symbol; private final Double price; private final com.mypackage.MyClass[] a1; public MarketEvent(...) { ... } ... }
Array Declaration Example: Oracle CQL Simple Type
Only arrays of Oracle CQL data cartridge types are supported: you cannot declare an array of Oracle CQL simple types.
int[] a1
However, you can work around this by using the Oracle CQL Java data cartridge and referencing the Java equivalent of the simple type, if one exists:
int@java[] a1
For more information on the @
syntax, see link::=.
The following example shows how to register the following queries that use Oracle CQL data cartridge complex type array access:
View v1
accesses the third element of the array a1
. This array contains instances of Oracle CQL data cartridge complex type com.mypackage.MyClass
.
Query q1
accesses the first element of the array field1
. This array is defined on Oracle CQL data cartridge complex type a1
.
<view id="v1" schema="symbol price a1"><![CDATA[ IStream(select symbol, price, a1[3] from S1[range 10 slide 10]) ]]></view> <query id="q1"><![CDATA[ SELECT a1.field1[1] … ]]></query>
Use the attr
clause to specify a stream element or pseudocolumn.
You can use the attr
clause in the following Oracle CQL statements:
Specify the identifier of the stream element.
You can specify
StreamOrViewName
.
ElementName
ElementName
CorrelationName
.
PseudoColumn
PseudoColumn
For examples, see "Examples".
For syntax, see identifier::= (parent: attr::=).
Specify the timestamp associated with a specific stream element, all stream elements, or the stream element associated with a correlation name in a MATCH_RECOGNIZE
clause.
For examples, see:
For more information, see Chapter 3, "Pseudocolumns".
For syntax, see pseudo_column::= (parent: attr::=).
Given the stream, valid attribute clauses are:
ItemTempStream
.temp
temp
B.element_time
element_time
<view id="ItemTempStream" schema="itemId temp"><![CDATA[ IStream(select * from ItemTemp) ]]></view> <query id="detectPerish"><![CDATA[ select its.itemId from ItemTempStream MATCH_RECOGNIZE ( PARTITION BY itemId MEASURES A.itemId as itemId PATTERN (A B* C) DEFINE A AS (A.temp >= 25), B AS ((B.temp >= 25) and (to_timestamp(B.element_time) - to_timestamp(A.element_time) < INTERVAL "0 00:00:05.00" DAY TO SECOND)), C AS (to_timestamp(C.element_time) - to_timestamp(A.element_time) >= INTERVAL "0 00:00:05.00" DAY TO SECOND) ) as its ]]></query>
Use the attrspec
clause to define the identifier and data type of a stream element.
You can use the attrspec
clause in the following Oracle CQL statements:
Specify the identifier of the stream element.
For syntax, see identifier::= (parent: attrspec::=).
Specify the stream element data type as a fixed-length data type.
For syntax, see fixed_length_datatype::= (parent: attrspec::=).
Specify the stream element data type as a variable-length data type.
For syntax, see variable_length_datatype::= (parent: attrspec::=).
Specify the length of the variable-length data type.
For syntax, see attrspec::=.
Use the complex_type
clause to specify an Oracle CQL data cartridge type that defines:
member fields (static or instance)
member methods (static or instance)
constructors
The type of a field, and the return type and parameter list of a method may be complex types or simple types.
A complex type is identified by its qualified type name (set of identifiers separated by a period ".
") and the optional name of the data cartridge to which it belongs (see link::=). If you do not specify a link name, then Oracle Event Processing assumes that the complex type is a Java class (that is, Oracle Event Processing assumes that the complex type belongs to the Java data cartridge).
The Oracle CQL data cartridge that provides the complextype must be loaded by Oracle Event Processing server at runtime.
Use the fieldname
clause to specify a static field of an Oracle CQL data cartridge complex type.
Syntax: fieldname::= (parent: complex_type::=).
You cannot use a complex type l-value
generated in expressions within an ORDER BY
clause. Currently, only expressions within a SELECT
clause and a WHERE
clause may generate a complex type l-value
.
You may access only a static field using qualified_type_name
. To access a non-static field, you must first instantiate the complex type (see "Constructor Invocation").
For examples, see "Field Access Examples: complex_type".
Accessing complex type setter methods may cause side effects. Side effects decrease the opportunities for concurrency and sharing. For example, if you invoke a setter method and change the value of a view attribute (such as an event property) shared by different queries that depend on the view, then the query results may change as a side effect of your method invocation.
You may access only a static method using qualified_type_name
. To access a non-static field, you must first instantiate the complex type (see "Constructor Invocation").
For examples, see "Method Access Examples: complex_type".
You may access only a static fields and static methods using qualified_type_name
. To access a non-static field or non-static method, you must first instantiate the complex type by invoking one of its constructors.
For examples, see "Constructor Invocation Examples: complex_type".
The following examples illustrate the various semantics that this statement supports:
Field Access Examples: complex_type
The following example shows how to register the following queries that use Oracle CQL data cartridge complex type field access:
Query q1
accesses field myField
from Oracle CQL data cartridge complex type a1
.
Query q2
accesses field myField
defined on the Oracle CQL data cartridge complex type returned by the method function-returning-object
. For more information on method access, see "Method Access".
Query q3
accesses field myNestedField
defined on the Oracle CQL data cartridge complex type myField
which is defined on Oracle CQL data cartridge complex type a1
.
Query q4
accesses the static field myStaticField
defined in the class MyType
in package com.myPackage
. Note that a link (@myCartridge
) is necessary in the case of a static field.
<query id="q1"><![CDATA[ SELECT a1.myField … ]]></query> <query id="q2"><![CDATA[ SELECT function-returning-object().myField … ]]></query> <query id="q3"><![CDATA[ SELECT a1.myField.myNestedField … ]]></query> <query id="q4"><![CDATA[ SELECT com.myPackage.MyType.myStaticField@myCartridge … ]]></query>
Method Access Examples: complex_type
The following example shows how to register the following queries that use Oracle CQL data cartridge complex type method access:
Query q1
accesses method myMethod
defined on Oracle CQL data cartridge complex type a1
. This query accesses the method with an empty parameter list.
Query q2
accesses method myMethod
defined on Oracle CQL data cartridge complex type a1
with a different signature than in query q1
. In this case, the query accesses the method with a three-argument parameter list.
Query q3
accesses static method myStaticMethod
defined on Oracle CQL data cartridge complex type MyType
. This query accesses the method with a single parameter. Note that a link (@myCartridge
) is necessary in the case of a static method.
<query id="q1"><![CDATA[ SELECT a1.myMethod() … ]]></query> <query id="q2"><![CDATA[ SELECT a1.myMethod(a2, ”foo”, 10) … ]]></query> <query id="q3"><![CDATA[ SELECT myPackage.MyType.myStaticMethod@myCartridge("foo") … ]]></query>
Constructor Invocation Examples: complex_type
The following example shows how to register the following queries that use Oracle CQL data cartridge complex type constructor invocation:
Query q1
invokes the constructor String
defined in package java.lang
. In this case, the query invokes the constructor with an empty argument list.
Query q2
invokes the constructor String
defined in package java.lang
. In this case, the query invokes the constructor with a single argument parameter list and invokes the non-static method substring
defined on the returned String
instance.
<query id="q1"><![CDATA[ SELECT java.lang.String() … ]]></query> <query id="q2"><![CDATA[ SELECT java.lang.String(”food”).substring(0,1) … ]]></query>
Use the const_bigint
clause to specify a big integer numeric literal.
You can use the const_bigint
clause in the following Oracle CQL statements:
For more information, see Section 2.3.2, "Numeric Literals".
Use the const_int
clause to specify an integer numeric literal.
You can use the const_int
clause in the following Oracle CQL statements:
For more information, see Section 2.3.2, "Numeric Literals".
Use the const_string
clause to specify a constant String
text literal.
You can use the const_string
clause in the following Oracle CQL statements:
For more information, see Section 2.3.1, "Text Literals".
Use the const_value
clause to specify a literal value.
You can use the const_value
clause in the following Oracle CQL statements:
For more information, see Section 2.3, "Literals".
Specify an interval constant value as a quoted string. For example:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
For more information, see Section 2.3.4, "Interval Literals".
For syntax, see interval_value::= (parent: const_value::=).
Specify a quoted String
constant value.
For more information, see Section 2.3.1, "Text Literals".
For syntax, see const_string::= (parent: interval_value::= and const_value::=).
Specify a null constant value.
For more information, see Section 2.5, "Nulls".
Specify an int
constant value.
For more information, see Section 2.3.2, "Numeric Literals".
Specify a bigint
constant value.
For more information, see Section 2.3.2, "Numeric Literals".
Specify a float constant value.
For more information, see Section 2.3.2, "Numeric Literals".
Use the identifier
clause to reference an existing Oracle CQL schema object.
You can use the identifier
clause in the following Oracle CQL statements:
Specify the identifier as a String.
For more information, see Section 2.8.1, "Schema Object Naming Rules".
For syntax, see identifier::=.
Specify the identifier as a single uppercase letter.
For syntax, see identifier::=.
These are names that you may use as identifiers.
For more information, see:
For syntax, see unreserved_keyword::= (parent: identifier::=).
These are names that you may not use as identifiers, because they are reserved keywords: add
, aggregate
, all
, alter
, and
, application
, as
, asc
, avg
, between
, bigint
, binding
, binjoin
, binstreamjoin
, boolean
, by
, byte
, callout
, case
, char
, clear
, columns
, constraint
, content
, count
, create
, day
, days
, decode
, define
, derived
, desc
, destination
, disable
, distinct
, document
, double
, drop
, dstream
, dump
, duration
, duration
, element_time
, else
, enable
, end
, evalname
, event
, events
, except
, external
, false
, first
, float
, from
, function
, group
, groupaggr
, having
, heartbeat
, hour
, hours
, identified
, implement
, in
, include
, index
, instance
, int
, integer
, intersect
, interval
, is
, istream
, java
, key
, language
, last
, level
, like
, lineage
, logging
, match_recognize
, matches
, max
, measures
, metadata_query
, metadata_system
, metadata_table
, metadata_userfunc
, metadata_view
, metadata_window
, microsecond
, microseconds
, millisecond
, milliseconds
, min
, minus
, minute
, minutes
, monitoring
, multiples
, nanosecond
, nanoseconds
, not
, now
, null
, nulls
, object
, of
, on
, operator
, or
, order
, orderbytop
, output
, partition
, partitionwin
, partnwin
, passing
, path
, pattern
, patternstrm
, patternstrmb
, prev
, primary
, project
, push
, query
, queue
, range
, rangewin
, real
, register
, relation
, relsrc
, remove
, return
, returning
, rows
, rowwin
, rstream
, run
, run_time
, sched_name
, sched_threaded
, schema
, second
, seconds
, select
, semantics
, set
, silent
, sink
, slide
, source
, spill
, start
, stop
, storage
, store
, stream
, strmsrc
, subset
, sum
, synopsis
, system
, systemstate
, then
, time
, time_slice
, timeout
, timer
, timestamp
, timestamped
, to
, true
, trusted
, type
, unbounded
, union
, update
, using
, value
, view
, viewrelnsrc
, viewstrmsrc
, wellformed
, when
, where
, window
, xmlagg
, xmlattributes
, xmlcolattval
, xmlconcat
, xmldata
, xmlelement
, xmlexists
, xmlforest
, xmlparse
, xmlquery
, xmltable
, xmltype
, or xor
.
Use the l-value
clause to specify an integer literal.
You can use the l-value
clause in the following Oracle CQL data cartridge statements:
Use the methodname
clause to specify a method of an Oracle CQL data cartridge complex type.
You can use the methodname
clause in the following Oracle CQL data cartridge statements:
Use the non_mt_arg_list
clause to specify one or more arguments as arithmetic expressions involving stream elements. To specify one or more arguments as stream elements directly, see non_mt_attr_list::=.
You can use the non_mt_arg_list
clause in the following Oracle CQL statements:
Use the non_mt_attr_list
clause to specify one or more arguments as stream elements directly. To specify one or more arguments as arithmetic expressions involving stream elements, see non_mt_arg_list::=.
You can use the non_mt_attr_list
clause in the following Oracle CQL statements:
Use the non_mt_attrname_list
clause to one or more stream elements by name.
You can use the non_mt_attrname_list
clause in the following Oracle CQL statements:
Use the non_mt_attrspec_list
clause to specify one or more attribute specifications that define the identifier and data type of stream elements.
You can use the non_mt_attrspec_list
clause in the following Oracle CQL statements:
Use the non_mt_cond_list
clause to specify one or more conditions using any combination of logical operators AND
, OR
, XOR
and NOT
.
You can use the non_mt_cond_list
clause in the following Oracle CQL statements:
For more information, see Chapter 6, "Conditions".
Specify a comparison condition.
For more information, see Section 6.2, "Comparison Conditions".
For syntax, see condition::= (parent: non_mt_cond_list::=).
Specify a condition that tests for inclusion in a range.
For more information, see Section 6.5, "Range Conditions".
For syntax, see between_condition::= (parent: non_mt_cond_list::=).
Use the param_list
clause to specify a comma-separated list of zero or more parameters, similar to a function parameter list, for an Oracle CQL data cartridge complex type method or constructor.
You can use the param_list
clause in the following Oracle CQL data cartridge statements:
Use the qualified_type_name
clause to specify a fully specified type name of an Oracle CQL data cartridge complex type, for example java.lang.String
. Use the qualified_type_name
when invoking Oracle CQL data cartridge static fields, static methods, or constructors.
There is no default package. For example, using the Java data cartridge, you must specify java.lang
when referencing the class String
. To be able to distinguish a reserved word from a qualified type, all qualified types must have at least two identifiers, that is, there must be at least one period (.
) in a qualified name.
You can use the qualified_type_name
clause in the following Oracle CQL data cartridge statements:
Use the package_name
clause to specify the name of an Oracle CQL data cartridge package.
Syntax: package_name::= (parent: qualified_type_name::=)
Use the class_name
clause to specify the name of an Oracle CQL data cartridge Class
.
Syntax: class_name::= (parent: qualified_type_name::=)
Use the query_ref
clause to reference an existing Oracle CQL query by name.
You can reference a Oracle CQL query in the following Oracle CQL statements:
The query must already exist (see "Query").
Use the time_spec
clause to define a time duration in days, hours, minutes, seconds, milliseconds, or nanoseconds.
Default: if units are not specified, Oracle Event Processing assumes [second|seconds]
.
You can use the time_spec
clause in the following Oracle CQL statements:
Use the xml_attribute_list
clause to specify one or more XML attributes.
You can use the xml_attribute_list
clause in the following Oracle CQL statements:
Specify one or more XML attributes.
<query id="tkdata51_q1"><![CDATA[ select XMLELEMENT(NAME "S0", XMLATTRIBUTES(tkdata51_S0.c1 as "C1", tkdata51_S0.c2 as "C2"), XMLELEMENT(NAME "c1_plus_c2", c1+c2), XMLELEMENT(NAME "c2_plus_10", c2+10.0)) from tkdata51_S0 [range 1] ]]> </query>
For syntax, see xml_attr_list::= (parent: xml_attribute_list::=).
Use the xml_attr_list
clause to specify one or more XML attributes..
You can use the xml_attr_list
clause in the following Oracle CQL statements:
Specify an XML attribute.
For syntax, see xml_attr::= (parent: xml_attr_list::=).
Use the xqryargs_list
clause to specify one or more arguments to an XML query.
You can use the non_mt_arg_list
clause in the following Oracle CQL statements:
A clause that binds a stream element value to an XQuery variable or XPath operator.
You can bind any arithmetic expression that involves one or more stream elements (see arith_expr::=) to either a variable in a given XQuery or an XPath operator such as "."
as a quoted string.
For syntax, see xqryarg::= (parent: xqryargs_list::=).