7 Common Oracle CQL DDL Clauses

This chapter describes the Oracle CQL data definition language (DDL) clauses that appear in multiple Oracle CQL statements.

7.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 20, "Oracle CQL Statements".

array_type

Purpose

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 use java.lang.String[] but not java.lang.String[][].

Prerequisites

None.

Syntax

array_type::=

Surrounding text describes array.png.

(l-value::=, qualified_type_name::=)

Symantics

Array Declaration

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:

Array Access

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".

Examples

The following examples illustrate the various semantics that this statement supports:

Array Declaration Example: complex_type

Example 7-1 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.

Example 7-1 Declaring an Oracle CQL Data Cartridge Array in an Event Type

package com.myapplication.event;

import java.util.Date;
import // Oracle CQL Java data cartridge package?

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

Array Access Examples

Example 7-2 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 as defined in Example 7-1.

  • Query q1 accesses the first element of the array field1. This array is defined on Oracle CQL data cartridge complex type a1.

Example 7-2 Accessing an Oracle CQL Data Cartridge Array in an Oracle CQL Query

<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>

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:

Prerequisites

None.

Syntax

attr::=

Surrounding text describes attr.png.

(identifier::=, pseudo_column::=)

pseudo_column::=

Surrounding text describes pseudo_column.png.

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:

For more information, see Chapter 3, "Pseudocolumns".

For syntax, see pseudo_column::= (parent: attr::=).

Examples

Given the stream that Example 7-3 shows, valid attribute clauses are:

  • ItemTempStream.temp

  • temp

  • B.element_time

  • element_time

Example 7-3 attr Clause

<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:

Prerequisites

None.

Syntax

attrspec::=

Surrounding text describes attrspec.png.

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

complex_type

Purpose

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 CEP assumes that the complex type is a Java class (that is, Oracle CEP assumes that the complex type belongs to the Java data cartridge).

Prerequisites

The Oracle CQL data cartridge that provides the complextype must be loaded by Oracle CEP server at runtime.

Syntax

complex_type::=

Surrounding text describes complextype.png.

(attr::=, fieldname::=, methodname::=, param_list::=, qualified_type_name::=)

fieldname::=

Surrounding text describes fieldname.png.

(identifier::=, link::=)

Semantics

fieldname

Use the fiel_dname clause to specify a static field of an Oracle CQL data cartridge complex type.

Syntax: fieldname::= (parent: complex_type::=).

Field Access

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".

Method Access

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".

Constructor Invocation

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".

Examples

The following examples illustrate the various semantics that this statement supports:

Field Access Examples: complex_type

Example 7-4 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.

Example 7-4 Data Cartridge Field Access

<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

Example 7-5 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.

Example 7-5 Data Cartridge Method Access

<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

Example 7-6 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.

Example 7-6 Data Cartridge Constructor Invocation

<query id="q1"><![CDATA[ 
    SELECT java.lang.String() …
]]></query>
<query id="q2"><![CDATA[ 
    SELECT java.lang.String(“food”).substring(0,1) …
]]></query>

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:

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

Prerequisites

None.

Syntax

const_bigint::=

Surrounding text describes const_bigint.png.

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:

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

Prerequisites

None.

Syntax

const_int::=

Surrounding text describes const_int.png.

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:

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

Prerequisites

None.

Syntax

const_string::=

Surrounding text describes const_string.png.

const_value

Purpose

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".

Prerequisites

None.

Syntax

const_value::=

Surrounding text describes const_value.png.

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

interval_value::=

Surrounding text describes interval_value.png.

(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 use the identifier clause in the following Oracle CQL statements:

Prerequisites

The schema object must already exist.

Syntax

identifier::=

Surrounding text describes identifier.png.

(const_string::=, unreserved_keyword::=)

unreserved_keyword::=

Surrounding text describes unreserved_keyword.png.

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:

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.

l-value

Purpose

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:

Prerequisites

None.

Syntax

l-value::=

Surrounding text describes l_value.png.

(integer::=)

methodname

Purpose

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:

Prerequisites

None.

Syntax

methodname::=

Surrounding text describes methodname.png.

(identifier::=, link::=)

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:

Prerequisites

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

Syntax

non_mt_arg_list::=

Surrounding text describes non_mt_arg_list.png.

(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:

Prerequisites

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

Syntax

non_mt_attr_list::=

Surrounding text describes non_mt_attr_list.png.

(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:

Prerequisites

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

Syntax

non_mt_attrname_list::=

Surrounding text describes non_mt_attrname_list.png.

(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:

Prerequisites

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

Syntax

non_mt_attrspec_list::=

Surrounding text describes non_mt_attrspec_list.png.

(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:

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

Prerequisites

None.

Syntax

non_mt_cond_list::=

Surrounding text describes non_mt_cond_list.png.

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

Semantics

condition

Specify a comparison condition.

For more information, see Section 6.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 6.5, "Range Conditions".

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

param_list

Purpose

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:

Prerequisites

None.

Syntax

param_list::=

Surrounding text describes param_list.png.

(arith_expr::=)

qualified_type_name

Purpose

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:

Prerequisites

None.

Syntax

qualified_type_name::=

Surrounding text describes qualified_type_name.png.

(arith_expr::=, package_name::=, class_name::=, link::=)

package_name::=

Surrounding text describes package_name.png.

(identifier::=)

class_name::=

Surrounding text describes package_name.png.

(arith_expr::=)

Symantics

package_name

Use the package_name clause to specify the name of an Oracle CQL data cartridge package.

Syntax: package_name::= (parent: qualified_type_name::=)

class_name

Use the class_name clause to specify the name of an Oracle CQL data cartridge Class.

Syntax: class_name::= (parent: qualified_type_name::=)

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:

Prerequisites

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

Syntax

query_ref::=

Surrounding text describes query_ref.png.

(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:

Prerequisites

None.

Syntax

time_spec::=

Surrounding text describes time_spec.png.

(time_unit::=)

time_unit::=

Surrounding text describes time_unit.png.

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:

Prerequisites

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

Syntax

xml_attribute_list::=

Surrounding text describes xml_attribute_list.png.

(xml_attr_list::=)

Semantics

xml_attr_list

Specify one or more XML attributes as Example 7-7 shows.

Example 7-7 xml_attr_list

<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:

Prerequisites

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

Syntax

xml_attr_list::=

Surrounding text describes xml_attr_list.png.

(xml_attr::=)

xml_attr::=

Surrounding text describes xml_attr.png.

(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:

Prerequisites

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

Syntax

xqryargs_list::=

Surrounding text describes xqryargs_list.png.

(xqryarg::=)

xqryarg::=

Surrounding text describes xqryarg.png.

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