13 Common Oracle CQL DDL Clauses

attr

Purpose

Use the attr clause to specify a stream element or pseudocolumn.

You can use the attr clause in the following Oracle CQL statements:

• arith_expr::=

• order_expr::=

• non_mt_attr_list::=

Prerequisites

None.

Syntax

attr::=

(identifier::=, pseudo_column::=)

pseudo_column::=

Semantics

identifier

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::=).

pseudo_column

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::=).

Examples

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

Purpose

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:

• non_mt_attrspec_list::=

Prerequisites

None.

Syntax

attrspec::=

(identifier::=, fixed_length_datatype::=, variable_length_datatype::=)

Semantics

identifier

Specify the identifier of the stream element.

For syntax, see identifier::= (parent: attrspec::=).

fixed_length_datatype

Specify the stream element datatype as a fixed-length datatype.

For syntax, see fixed_length_datatype::= (parent: attrspec::=).

variable_length_datatype

Specify the stream element datatype as a variable-length datatype.

For syntax, see variable_length_datatype::= (parent: attrspec::=).

integer

Specify the length of the variable-length datatype.

For syntax, see attrspec::=.

const_bigint

Purpose

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:

• func_expr::=

• const_value::=

For more information, see Section 2.4.2, "Numeric Literals".

Prerequisites

None.

Syntax

const_bigint::=

const_int

Purpose

Use the const_int clause to specify an integer numeric literal.

You can use the const_int clause in the following Oracle CQL statements:

• func_expr::=

• order_expr::=

• const_value::=

For more information, see Section 2.4.2, "Numeric Literals".

Prerequisites

None.

Syntax

const_int::=

const_string

Purpose

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:

• func_expr::=

• order_expr::=

• condition::=

• const_value::=

• interval_value::=

• xmltable_clause::=

• xtbl_col::=

For more information, see Section 2.4.1, "Text Literals".

Prerequisites

None.

Syntax

const_string::=

const_value

Purpose

const_int::=Use the const_value clause to specify a literal value.

You can use the const_value clause in the following Oracle CQL statements:

• arith_expr::=

• condition::=

For more information, see Section 2.4, "Literals".

Prerequisites

None.

Syntax

const_value::=

(interval_value::=, const_string::=, const_int::=, const_bigint::=)

interval_value::=

(const_string::=)

Semantics

interval_value

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::=).

const_string

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::=).

null

Specify a null constant value.

For more information, see Section 2.6, "Nulls".

const_int

Specify an int constant value.

For more information, see Section 2.4.2, "Numeric Literals".

bigint

Specify a bigint constant value.

For more information, see Section 2.4.2, "Numeric Literals".

float

Specify a float constant value.

For more information, see Section 2.4.2, "Numeric Literals".

identifier

Purpose

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:

• binary::=

• aggr_expr::=

• func_expr::=

• attr::=

• attrspec::=

• query_ref::=

• non_mt_attrname_list::=

• relation_variable::=

• measure_column::=

• "Query"

• projterm::=

• "View"

Prerequisites

The schema object must already exist.

Syntax

identifier::=

(const_string::=, unreserved_keyword::=)

unreserved_keyword::=

Symantics

const_string

Specify the identifier as a String.

For more information, see Section 2.9.1, "Schema Object Naming Rules".

For syntax, see identifier::=.

[A-Z]

Specify the identifier as a single uppercase letter.

For syntax, see identifier::=.

unreserved_keyword

These are names that you may use as identifiers.

For more information, see:

• "reserved_keyword"

• Section 2.9.1, "Schema Object Naming Rules"

For syntax, see unreserved_keyword::= (parent: identifier::=).

reserved_keyword

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

Purpose

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:

• decode::=

• func_expr::=

• condition::=

• non_mt_arg_list_set::=

Prerequisites

If any stream elements are referenced, the stream must already exist.

Syntax

non_mt_arg_list::=

(arith_expr::=)

Semantics

arith_expr

Specify the arithmetic expression that resolves to the argument value.

non_mt_attr_list

Purpose

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:

• pattern_partition_clause::=

• window_type::=

• opt_group_by_clause::=

Prerequisites

If any stream elements are referenced, the stream must already exist.

Syntax

non_mt_attr_list::=

(attr::=)

Semantics

attr

Specify the argument as a stream element directly.

non_mt_attrname_list

Purpose

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:

• out_of_line_constraint::=

• "View"

Prerequisites

If any stream elements are referenced, the stream must already exist.

Syntax

non_mt_attrname_list::=

(identifier::=)

Semantics

identifier

Specify the stream element by name.

non_mt_attrspec_list

Purpose

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:

• "View"

Prerequisites

If any stream elements are referenced, the stream must already exist.

Syntax

non_mt_attrspec_list::=

(attrspec::=)

Semantics

attrspec

Specify the attribute identifier and datatype.

non_mt_cond_list

Purpose

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:

• correlation_name_definition::=

• searched_case::=

• opt_where_clause::=

• opt_having_clause::=

For more information, see Chapter 12, "Conditions".

Prerequisites

None.

Syntax

non_mt_cond_list::=

(non_mt_cond_list::=, condition::=, between_condition::=)

non_mt_arg_list_set::=

(non_mt_arg_list::=)

Semantics

condition

Specify a comparison condition.

For more information, see Section 12.2, "Comparison Conditions".

For syntax, see condition::= (parent: non_mt_cond_list::=).

between_condition

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::=).

non_mt_arg_list_set

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

Purpose

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:

• "Query"

For more information, see:

• Section 2.6, "Nulls"

Prerequisites

A tuple that you specify with an out_of_line_constraint may not contain a null value.

Syntax

out_of_line_constraint::=

(non_mt_attrname_list::=)

Semantics

non_mt_attrname_list

Specify one or more tuples to restrict by a primary key integrity constraint.

query_ref

Purpose

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:

• "View"

Prerequisites

The query must already exist (see "Query").

Syntax

query_ref::=

(identifier::=)

Symantics

identifier

Specify the name of the query. This is the name you use to reference the query in subsequent Oracle CQL statements.

time_spec

Purpose

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:

• window_type::=

• duration_clause::=

Prerequisites

None.

Syntax

time_spec::=

(time_unit::=)

time_unit::=

Semantics

integer

Specify the number of time units.

time_unit

Specify the unit of time.

xml_attribute_list

Purpose

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:

• "xmlelement_expr"

Prerequisites

If any stream elements are referenced, the stream must already exist.

Syntax

xml_attribute_list::=

(xml_attr_list::=)

Semantics

xml_attr_list

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

Purpose

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:

• "xml_attribute_list"

• "xmlforest_expr"

• "xml_agg_expr"

Prerequisites

If any stream elements are referenced, the stream must already exist.

Syntax

xml_attr_list::=

(xml_attr::=)

xml_attr::=

(const_string::=, arith_expr::=, attr::=)

Semantics

xml_attr

Specify an XML attribute.

For syntax, see xml_attr::= (parent: xml_attr_list::=).

xqryargs_list

Purpose

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:

• "XMLEXISTS"

• "XMLQUERY"

• func_expr::=

• xmltable_clause::=

Prerequisites

If any stream elements are referenced, the stream must already exist.

Syntax

xqryargs_list::=

(xqryarg::=)

xqryarg::=

(const_string::=, arith_expr::=)

Semantics

xqryarg

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::=).