13 Common Oracle CQL DDL Clauses
This chapter describes the Oracle CQL data definition language (DDL) clauses that appear in multiple Oracle CQL statements.
13.1 Introduction to Common Oracle CQL DDL Clauses
Oracle CQL supports the following common DDL clauses:
For more information on Oracle CQL statements, see Chapter 16, "Oracle CQL Statements".
attr
Use the attr clause to specify a stream element or pseudocolumn.
You can use the attr clause in the following Oracle CQL statements:
None.
(identifier::=, pseudo_column::=)
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 "Examples".
For more information, see Chapter 3, "Pseudocolumns".
For syntax, see pseudo_column::= (parent: attr::=).
Given the stream that Example 13-1 shows, 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>
attrspec
Use the attrspec clause to define the identifier and datatype of a stream element.
You can use the attrspec clause in the following Oracle CQL statements:
None.
(identifier::=, fixed_length_datatype::=, variable_length_datatype::=)
Specify the identifier of the stream element.
For syntax, see identifier::= (parent: attrspec::=).
Specify the stream element datatype as a fixed-length datatype.
For syntax, see fixed_length_datatype::= (parent: attrspec::=).
Specify the stream element datatype as a variable-length datatype.
For syntax, see variable_length_datatype::= (parent: attrspec::=).
Specify the length of the variable-length datatype.
For syntax, see attrspec::=.
const_bigint
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.4.2, "Numeric Literals".
None.
const_int
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.4.2, "Numeric Literals".
None.
const_string
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.4.1, "Text Literals".
None.
const_value
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.4, "Literals".
None.
(interval_value::=, const_string::=, const_int::=, const_bigint::=)
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.4.4, "Interval Literals".
For syntax, see interval_value::= (parent: const_value::=).
Specify a quoted String constant value.
For more information, see Section 2.4.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.6, "Nulls".
Specify an int constant value.
For more information, see Section 2.4.2, "Numeric Literals".
Specify a bigint constant value.
For more information, see Section 2.4.2, "Numeric Literals".
Specify a float constant value.
For more information, see Section 2.4.2, "Numeric Literals".
identifier
Use the identifier clause to reference an existing Oracle CQL schema object.
You can reference a Oracle CQL query in the following Oracle CQL statements:
The schema object must already exist.
(const_string::=, unreserved_keyword::=)
Specify the identifier as a String.
For more information, see Section 2.9.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.
non_mt_arg_list
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:
If any stream elements are referenced, the stream must already exist.
Specify the arithmetic expression that resolves to the argument value.
non_mt_attr_list
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:
If any stream elements are referenced, the stream must already exist.
(attr::=)
Specify the argument as a stream element directly.
non_mt_attrname_list
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:
If any stream elements are referenced, the stream must already exist.
Specify the stream element by name.
non_mt_attrspec_list
Use the non_mt_attrspec_list clause to specify one or more attribute specifications that define the identifier and datatype of stream elements.
You can use the non_mt_attrspec_list clause in the following Oracle CQL statements:
If any stream elements are referenced, the stream must already exist.
Specify the attribute identifier and datatype.
non_mt_cond_list
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 12, "Conditions".
None.
(non_mt_cond_list::=, condition::=, between_condition::=)
Specify a comparison condition.
For more information, see Section 12.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 12.5, "Range Conditions".
For syntax, see between_condition::= (parent: non_mt_cond_list::=).
If you use the form of the IN condition with multiple expressions to the left of the operator, then you must use a non_mt_arg_list_set, and the expressions in each list of expressions must match in number and datatype.
For more information, see Section 12.8, "IN Condition".
For syntax, see non_mt_arg_list_set::= (parent: non_mt_cond_list::=).
out_of_line_constraint
Use this out_of_line_constraint clause to restrict a tuple of any datatype by a primary key integrity constraint.
If you plan to configure a query on a relation with USE UPDATE SEMANTICS, you must declare one or more stream elements as a primary key. Use this constraint to specify a compound primary key made up of one or more stream element values.
You can use the out_of_line_constraint clause in the following Oracle CQL statements:
For more information, see:
A tuple that you specify with an out_of_line_constraint may not contain a null value.
Specify one or more tuples to restrict by a primary key integrity constraint.
query_ref
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").
Specify the name of the query. This is the name you use to reference the query in subsequent Oracle CQL statements.
time_spec
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 CEP assumes [second|seconds].
You can use the time_spec clause in the following Oracle CQL statements:
None.
time_unit::=
Specify the number of time units.
Specify the unit of time.
xml_attribute_list
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:
If any stream elements are referenced, the stream must already exist.
Specify one or more XML attributes as Example 13-2 shows.
<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::=).
xml_attr_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:
If any stream elements are referenced, the stream must already exist.
xml_attr::=
(const_string::=, arith_expr::=, attr::=)
Specify an XML attribute.
For syntax, see xml_attr::= (parent: xml_attr_list::=).
xqryargs_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:
If any stream elements are referenced, the stream must already exist.
xqryarg::=
(const_string::=, arith_expr::=)
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::=).