20 Oracle CQL Statements

This chapter describes the various Oracle CQL data definition language (DDL) and data modification language (DML) statements that Oracle CEP supports.

20.1 Introduction to Oracle CQL Statements

Oracle CQL supports the following DDL statements:

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.

For more information, see:


Query

Purpose

Use the query statement to define a Oracle CQL query that you reference by identifier in subsequent Oracle CQL statements.

Prerequisites

If your query references a stream or view, then the stream or view must already exist.

If the query already exists, Oracle CEP server throws an exception.

For more information, see:

Syntax

You express a query in a <query></query> element as Example 20-1 shows.

The query element has one attribute:

  • id: Specify the identifier as the query element id attribute.

    The id value must conform with the specification given by identifier::=.

Example 20-1 Query in a <query></query> Element

<query id="q0"><![CDATA[ 
    select * from OrderStream where orderAmount > 10000.0 
]]></query>

query::=

Surrounding text describes query.png.

(sfw_block::=, idstream_clause::=, binary::=, using_clause::=)

sfw_block::=

Surrounding text describes sfw_block.png.

(select_clause::=, from_clause::=, opt_where_clause::=, opt_group_by_clause::=, order_by_clause::=, order_by_top_clause::=, opt_having_clause::=)

select_clause::=

Surrounding text describes select_clause.png.

(non_mt_projterm_list::=)

non_mt_projterm_list::=

Surrounding text describes non_mt_projterm_list.png.

(projterm::=)

projterm::=

Surrounding text describes projterm.png.

(arith_expr, identifier::=)

from_clause::=

Surrounding text describes from_clause.png.

(non_mt_relation_list::=, relation_variable::=, non_mt_cond_list::=)

non_mt_relation_list::=

Surrounding text describes non_mt_relation_list.png.

(relation_variable::=)

relation_variable::=

Surrounding text describes relation_variable.png.

(identifier::=, window_type::=, pattern_recognition_clause::=, xmltable_clause::=, object_expr::=, datatype::=, table_clause::=)

window_type::=

Surrounding text describes window_type.png.

(identifier::=, non_mt_attr_list::=, time_spec::=)

table_clause::=

Surrounding text describes table_clause.png.

(object_expr::=, identifier::=, datatype::=)

opt_where_clause::=

Surrounding text describes opt_where_clause.png.

(non_mt_cond_list::=)

opt_group_by_clause::=

Surrounding text describes opt_group_by_clause.png.

(non_mt_attr_list::=)

order_by_clause::=

Surrounding text describes order_by_clause.png.

(order_by_list::=)

order_by_top_clause::=

Surrounding text describes order_by_top_clause.png.

(pattern_partition_clause::=, order_by_list::=)

order_by_list::=

Surrounding text describes order_by_list.png.

(orderterm::=)

orderterm::=

Surrounding text describes orderterm.png.

(order_expr::=, null_spec::=, asc_desc::=)

null_spec::=

Surrounding text describes null_spec.png.

asc_desc::=

Surrounding text describes asc_desc.png.

opt_having_clause::=

Surrounding text describes opt_having_clause.png.

(non_mt_cond_list::=)

binary::=

Surrounding text describes binary.png.

idstream_clause::=

Surrounding text describes idstream_clause.png.

using_clause::=

Surrounding text describes using_clause.png.

(usinglist::=)

usinglist::=

Surrounding text describes usinglist.png.

(usingterm::=)

usingterm::=

Surrounding text describes usingterm.png.

(usingexpr::=)

usingexpr::=

Surrounding text describes usingexpr.png.

(attr::=, const_int::=)

xmltable_clause::=

Surrounding text describes xmltable_clause.png.

(xmlnamespace_clause::=, const_string::=, xqryargs_list::=, xtbl_cols_list::=)

xmlnamespace_clause::=

Surrounding text describes xmlnamespace_clause.png.

(xmlnamespaces_list::=)

xmlnamespaces_list::=

Surrounding text describes xmlnamespaces_list.png.

(xml_namespace::=)

xml_namespace::=

Surrounding text describes xml_namespace.png.

(const_string::=)

xtbl_cols_list::=

Surrounding text describes xtbl_cols_list.png.

(xtbl_col::=)

xtbl_col::=

Surrounding text describes xtbl_col.png.

Semantics

named_query

Specify the Oracle CQL query statement itself (see "query").

For syntax, see "Query".

query

You can create an Oracle CQL query from any of the following clauses:

  • sfw_block: a select, from, and other optional clauses (see "sfw_block").

  • binary: an optional set operation clause (see "binary").

  • xstream_clause: apply an optional relation-to-stream operator to your sfw_block or binary clause to control how the query returns its results (see "idstream_clause").

For syntax, see query::=.

sfw_block

Specify the select, from, and other optional clauses of the Oracle CQL query. You can specify any of the following clauses:

  • select_clause: the stream elements to select from the stream or view you specify (see "select_clause").

  • from_clause: the stream or view from which your query selects (see "from_clause").

  • opt_where_clause: optional conditions your query applies to its selection (see "opt_where_clause")

  • opt_group_by_clause: optional grouping conditions your query applies to its results (see "opt_group_by_clause")

  • order_by_clause: optional ordering conditions your query applies to its results (see "order_by_clause")

  • order_by_top_clause: optional ordering conditions your query applies to the top-n elements in its results (see "order_by_top_clause")

  • opt_having_clause: optional clause your query uses to restrict the groups of returned stream elements to those groups for which the specified condition is TRUE (see "opt_having_clause")

For syntax, see sfw_block::= (parent: query::=).

select_clause

Specify the select clause of the Oracle CQL query statement.

If you specify the asterisk (*), then this clause returns all tuples, including duplicates and nulls.

Otherwise, specify the individual stream elements you want (see "non_mt_projterm_list").

Optionally, specify distinct if you want Oracle CEP to return only one copy of each set of duplicate tuples selected. Duplicate tuples are those with matching values for each expression in the select list. For an example, see "Select and Distinct Examples".

For syntax, see select_clause::= (parent: sfw_block::=).

non_mt_projterm_list

Specify the projection term ("projterm") or comma separated list of projection terms in the select clause of the Oracle CQL query statement.

For syntax, see non_mt_projterm_list::= (parent: select_clause::=).

projterm

Specify a projection term in the select clause of the Oracle CQL query statement. You can select any element from any of stream or view in the from_clause (see "from_clause") using the identifier of the element.

Optionally, you can specify an arithmetic expression on the projection term.

Optionally, use the AS keyword to specify an alias for the projection term instead of using the stream element name as is.

For syntax, see projterm::= (parent: non_mt_projterm_list::=).

from_clause

Specify the from clause of the Oracle CQL query statement by specifying the individual streams or views from which your query selects (see "non_mt_relation_list").

To perform an outer join, use the LEFT or RIGHT OUTER JOIN ... ON syntax. To perform an inner join, use the WHERE clause.

For more information, see:

For syntax, see from_clause::= (parent: sfw_block::=).

non_mt_relation_list

Specify the stream or view ("relation_variable") or comma separated list of streams or views in the from clause of the Oracle CQL query statement.

For syntax, see non_mt_relation_list::= (parent: from_clause::=).

relation_variable

Use the relation_variable statement to specify a stream or view from which the Oracle CQL query statement selects.

You can specify a previously registered or created stream or view directly by its identifier you used when you registered or created the stream or view. Optionally, use the AS keyword to specify an alias for the stream or view instead of using its name as is.

To specify a built-in stream-to-relation operator, use a window_type clause (see "window_type"). Optionally, use the AS keyword to specify an alias for the stream or view instead of using its name as is.

To apply advanced comparisons optimized for data streams to the stream or view, use a pattern_recognition_clause (see "pattern_recognition_clause"). Optionally, use the AS keyword to specify an alias for the stream or view instead of using its name as is.

To process xmltype stream elements using XPath and XQuery, use an xmltable_clause (see "xmltable_clause"). Optionally, use the AS keyword to specify an alias for the stream or view instead of using its name as is.

To access, as a relation, the multiple rows returned by a data cartridge function in the FROM clause of an Oracle CQL query, use a table_clause (see "table_clause").

For more information, see:

For syntax, see relation_variable::= (parent: non_mt_relation_list::=).

window_type

Specify a built-in stream-to-relation operator.

For more information, see Section 1.1.3, "Stream-to-Relation Operators (Windows)".

For syntax, see window_type::= (parent: relation_variable::=).

table_clause

Use the data cartridge TABLE clause to access the multiple rows returned by a data cartridge function in the FROM clause of an Oracle CQL query.

The TABLE clause converts the set of returned rows into an Oracle CQL relation. Because this is an external relation, you must join the TABLE function clause with a stream. Oracle CEP invokes the data cartridge method only on the arrival of elements on the joined stream.

Use the optional OF keyword to specify the type contained by the returned array type or Collection type.

Use the AS keyword to specify an alias for the object_expr and for the returned relation.

Note the following:

  • The data cartridge method must return an array type or Collection type.

  • You must join the TABLE function clause with a stream.

For examples, see:

For more information, see:

For syntax, see table_clause::= (parent: relation_variable::=).

time_spec

Specify the time over which a range or partitioned range sliding window should slide.

Default: if units are not specified, Oracle CEP assumes [second|seconds].

For more information, see "Range-Based Stream-to-Relation Window Operators" and "Partitioned Stream-to-Relation Window Operators".

For syntax, see time_spec::= (parent: window_type::=).

opt_where_clause

Specify the (optional) where clause of the Oracle CQL query statement.

Because Oracle CQL applies the WHERE clause before GROUP BY or HAVING, if you specify an aggregate function in the SELECT clause, you must test the aggregate function result in a HAVING clause, not the WHERE clause.

In Oracle CQL (as in SQL), the FROM clause is evaluated before the WHERE clause. Consider the following Oracle CQL query:

SELECT ... FROM S MATCH_RECOGNIZE ( .... ) as T WHERE ...

In this query, the S MATCH_RECOGNIZE ( .... ) as T is like a subquery in the FROM clause and is evaluated first, before the WHERE clause. Consequently, you rarely use both a MATCH_RECOGNIZE clause and a WHERE clause in the same Oracle CQL query. Instead, you typically use views to apply the required WHERE clause to a stream and then select from the views in a query that applies the MATCH_RECOGNIZE clause.

For more information, see:

For syntax, see opt_where_clause::= (parent: sfw_block::=).

opt_group_by_clause

Specify the (optional) GROUP BY clause of the Oracle CQL query statement. Use the GROUP BY clause if you want Oracle CEP to group the selected stream elements based on the value of expr(s) and return a single (aggregate) summary result for each group.

Expressions in the GROUP BY clause can contain any stream elements or views in the FROM clause, regardless of whether the stream elements appear in the select list.

The GROUP BY clause groups stream elements but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.

Because Oracle CQL applies the WHERE clause before GROUP BY or HAVING, if you specify an aggregate function in the SELECT clause, you must test the aggregate function result in a HAVING clause, not the WHERE clause.

For more information, see:

For syntax, see opt_group_by_clause::= (parent: sfw_block::=).

order_by_clause

Specify the ORDER BY clause of the Oracle CQL query statement as a comma-delimited list ("order_by_list") of one or more order terms (see "orderterm"). Use the ORDER BY clause to specify the order in which stream elements on the left-hand side of the rule are to be evaluated. The expr must resolve to a dimension or measure column.

For more information, see Section 18.2.9, "Sorting Query Results".

For syntax, see order_by_clause::= (parent: sfw_block::=).

order_by_top_clause

Specify the ORDER BY clause of the Oracle CQL query statement as a comma-delimited list ("order_by_list") of one or more order terms (see "orderterm") followed by a ROWS keyword and integer number (n) of elements. Use this form of the ORDER BY clause to select the top-n elements over a stream or relation. This clause always returns a relation.

Consider the following example queries:

  • At any point of time, the output of the following example query will be a relation having top 10 stock symbols throughout the stream.

    select stock_symbols from StockQuotes order by stock_price rows 10
    
  • At any point of time, the output of the following example query will be a relation having top 10 stock symbols from last 1 hour of data.

    select stock_symbols from StockQuotes[range 1 hour] order by stock_price rows 10
    

For more information, see

For syntax, see order_by_top_clause::= (parent: sfw_block::=).

order_by_list

Specify a comma-delimited list of one ore more order terms (see "orderterm") in an (optional) ORDER BY clause.

For syntax, see order_by_list::= (parent: order_by_clause::=).

orderterm

A stream element (attr::=) or positional index (constant int) to a stream element. Optionally, you can configure whether or not nulls are ordered first or last using the NULLS keyword (see "null_spec").

order_expr (order_expr::=) can be an attr or constant_int. The attr (attr::=) can be any stream element or pseudo column.

For syntax, see orderterm::= (parent: order_by_list::=).

null_spec

Specify whether or not nulls are ordered first (NULLS FIRST) or last (NULLS LAST) for a given order term (see "orderterm").

For syntax, see null_spec::= (parent: orderterm::=).

asc_desc

Specify whether an order term is ordered in ascending (ASC) or descending (DESC) order.

For syntax, see asc_desc::= (parent: orderterm::=).

opt_having_clause

Use the HAVING clause to restrict the groups of returned stream elements to those groups for which the specified condition is TRUE. If you omit this clause, then Oracle CEP returns summary results for all groups.

Specify GROUP BY and HAVING after the opt_where_clause. If you specify both GROUP BY and HAVING, then they can appear in either order.

Because Oracle CQL applies the WHERE clause before GROUP BY or HAVING, if you specify an aggregate function in the SELECT clause, you must test the aggregate function result in a HAVING clause, not the WHERE clause.

For more information, see:

For an example, see "HAVING Example".

For syntax, see opt_having_clause::= (parent: sfw_block::=).

binary

Use the binary clause to perform set operations on the tuples that two streams or views return.

For examples, see:

For syntax, see binary::= (parent: query::=).

idstream_clause

Use an idstream_clause to specify an IStream or DStream relation-to-stream operator that applies to the query.

For more information, see Section 1.1.4, "Relation-to-Stream Operators".

For syntax, see idstream_clause::= (parent: query::=).

using_clause

Use a DIFFERENCE USING clause to succinctly detect differences in the IStream or DStream of a query.

For more information, see Section 18.2.10, "Detecting Differences in Query Results".

For syntax, see using_clause::= (parent: query::=).

usinglist

Use a usinglist clause to specify the columns to use to detect differences in the IStream or DStream of a query. You may specify columns by:

  • attribute name: use this option when you are selecting by attribute name.

    Example 20-2 shows attribute name c1 in the DIFFERENCE USING clause usinglist.

  • alias: use this option when you want to include the results of an expression where an alias is specified.

    Example 20-2 shows alias logval in the DIFFERENCE USING clause usinglist.

  • position: use this option when you want to include the results of an expression where no alias is specified.

    Specify position as a constant, positive integer starting at 1, reading from left to right.

    Example 20-2 specifies the result of expression funct(c2, c3) by its position (3) in the DIFFERENCE USING clause usinglist.

Example 20-2 Specifying the usinglist in a DIFFERENCE USING Clause

<query id="q1">
    ISTREAM (
        SELECT c1, log(c4) as logval, funct(c2, c3) FROM S [RANGE 1 NANOSECONDS]
    )    DIFFERENCE USING (c1, logval, 3)
</query>

For more information, see Section 18.2.10, "Detecting Differences in Query Results".

For syntax, see usinglist::= (parent: using_clause::=).

xmltable_clause

Use an xmltable_clause to process xmltype stream elements using XPath and XQuery. You can specify a comma separated list (see xtbl_cols_list::=) of one or more XML table columns (see xtbl_col::=), with or without an XML namespace.

For examples, see:

For syntax, see xmltable_clause::= (parent: relation_variable::=).

pattern_recognition_clause

Use a pattern_recognition_clause to perform advanced comparisons optimized for data streams.

For more information and examples, see Chapter 19, "Pattern Recognition With MATCH_RECOGNIZE".

For syntax, see pattern_recognition_clause::= (parent: relation_variable::=).

Examples

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

For more examples, see Chapter 18, "Oracle CQL Queries, Views, and Joins".

Simple Query Example

Example 20-3 shows how to register a simple query q0 that selects all (*) tuples from stream OrderStream where stream element orderAmount is greater than 10000.

Example 20-3 REGISTER QUERY

<query id="q0"><![CDATA[ 
    select * from OrderStream where orderAmount > 10000.0 
]]></query>

HAVING Example

Consider the query q4 in Example 20-4 and the data stream S2 in Example 20-5. Stream S2 has schema (c1 integer, c2 integer). The query returns the relation in Example 20-6.

Example 20-4 HAVING Query

<query id="q4"><![CDATA[ 
    select 
        c1,
        sum(c1) 
    from 
        S2[range 10] 
    group by 
        c1 
    having 
        c1 > 0 and sum(c1) > 1
]]></query>

Example 20-5 HAVING Stream Input

Timestamp   Tuple
1000        ,2
2000        ,4
3000        1,4
5000        1,
6000        1,6
7000        ,9
8000        ,

Example 20-6 HAVING Relation Output

Timestamp   Tuple Kind  Tuple
5000:       +           1,2
6000:       -           1,2
6000:       +           1,3

BINARY Example: UNION and UNION ALL

Given the relations R1 and R2 in Example 20-8 and Example 20-9, respectively, the UNION query q1 in Example 20-7 returns the relation in Example 20-10 and the UNION ALL query q2 in Example 20-7 returns the relation in Example 20-11.

Example 20-7 Set Operators: UNION Query

<query id="q1"><![CDATA[ 
    R1 UNION R2 
]]></query>
<query id="q2"><![CDATA[ 
    R1 UNION ALL R2 
]]></query>

Example 20-8 Set Operators: UNION Relation Input R1

Timestamp   Tuple Kind  Tuple
   200000:  +           20,0.2
   201000:  -           20,0.2
   400000:  +           30,0.3
   401000:  -           30,0.3
100000000:  +           40,4.04
100001000:  -           40,4.04

Example 20-9 Set Operators: UNION Relation Input R2

Timestamp   Tuple Kind  Tuple
     1002:  +           15,0.14
     2002:  -           15,0.14
   200000:  +           20,0.2
   201000:  -           20,0.2
   400000:  +           30,0.3
   401000:  -           30,0.3
100000000:  +           40,4.04
100001000:  -           40,4.04

Example 20-10 Set Operators: UNION Relation Output

Timestamp   Tuple Kind  Tuple
     1002:  +           15,0.14
     2002:  -           15,0.14
   200000:  +           20,0.2
   201000:  -           20,0.2
   400000:  +           30,0.3
   401000:  -           30,0.3
100000000:  +           40,4.04
100001000:  -           40,4.04

Example 20-11 Set Operators: UNION ALL Relation Output

Timestamp   Tuple Kind  Tuple
     1002:  +           15,0.14
     2002:  -           15,0.14
   200000:  +           20,0.2
   200000:  +           20,0.2
    20100:  -           20,0.2
   201000:  -           20,0.2
   400000:  +           30,0.3
   400000:  +           30,0.3
   401000:  -           30,0.3
   401000:  -           30,0.3
100000000:  +           40,4.04
100000000:  +           40,4.04
 10001000:  -           40,4.04
100001000:  -           40,4.04

BINARY Example: INTERSECT

Given the relations R1 and R2 in Example 20-13 and Example 20-14, respectively, the INTERSECT query q1 in Example 20-12 returns the relation in Example 20-15.

Example 20-12 Set Operators: INTERSECT Query

<query id="q1"><![CDATA[ 
    R1 INTERSECT R2 
]]></query>

Example 20-13 Set Operators: INTERSECT Relation Input R1

Timestamp   Tuple Kind  Tuple
1000:        +          10,30
1000:        +          10,40
2000:        +          11,20
3000:        -          10,30
3000:        -          10,40

Example 20-14 Set Operators: INTERSECT Relation Input R2

Timestamp   Tuple Kind  Tuple
1000:       +           10,40
2000:       +           10,30
2000:       -           10,40
3000:       -           10,30

Example 20-15 Set Operators: INTERSECT Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           10,30
1000:       +           10,40
1000:       -           10,30
1000:       -           10,40
1000:       +           10,40
2000:       +           11,20
2000:       -           11,20
2000:       +           10,30
2000:       -           10,40
3000:       -           10,30

BINARY Example: MINUS

Given the relations R1 and R2 in Example 20-17 and Example 20-18, respectively, the MINUS query q1 in Example 20-16 returns the relation in Example 20-19.

Example 20-16 Set Operators: MINUS Query

<query id="q1BBAQuery"><![CDATA[ 
    R1 MINUS R2 
]]></query>

Example 20-17 Set Operators: MINUS Relation Input R1

Timestamp   Tuple Kind  Tuple
1500:       +           10,40
2000:       +           10,30
2000:       -           10,40
3000:       -           10,30

Example 20-18 Set Operators: MINUS Relation Input R2

Timestamp   Tuple Kind  Tuple
1000:       +           11,20
2000:       +           10,40
3000:       -           10,30

Example 20-19 Set Operators: MINUS Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           10,40.0
2000:       -           10,40.0

BINARY Example: IN and NOT IN

In this usage, the query will be a binary query.

in_condition_set::=

Surrounding text describes in_condition_set.png.

Note:

You cannot combine this usage with in_condition_membership as Section 6.8.2, "Using IN and NOT IN as a Membership Condition" describes.

Consider the views V3 and V4 and the query Q1 in Example 20-20 and the data streams S3 in Example 20-21 (with schema (c1 integer, c2 integer)) and S4 in Example 20-22 (with schema (c1 integer, c2 integer)). In this condition test, the numbers and data types of attributes in left relation should be same as number and types of attributes of the right relation. Example 20-23 shows the relation that the query returns.

Example 20-20 IN and NOT IN as a Set Operation: Query

<view id="V3" schema="c1 c2"><![CDATA[ 
    select * from S3[range 2] 
]]></query>
<view id="V4" schema="c1 d1"><![CDATA[ 
    select * from S4[range 1]
]]></query>
<query id="Q1"><![CDATA[ 
     v3 not in v4
]]></query>

Example 20-21 IN and NOT IN as a Set Operation: Stream S3 Input

Timestamp   Tuple
1000        10, 30
1000        10, 40
2000        11, 20
3000        12, 40
3000        12, 30
3000        15, 50
h 2000000

Example 20-22 IN and NOT IN as a Set Operation: Stream S4 Input

Timestamp   Tuple
1000        10, 40
2000        10, 30
2000        12, 40
h 2000000

Example 20-23 IN and NOT IN as a Set Operation: Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           10,30
1000:       +           10,40
1000:       -           10,30
1000:       -           10,40
2000:       +           11,20
2000:       +           10,30
2000:       +           10,40
2000:       -           10,30
2000:       -           10,40
3000:       +           15,50
3000:       +           12,40
3000:       +           12,30
4000:       -           11,20
5000:       -           12,40
5000:       -           12,30
5000:       -           15,50

Select and Distinct Examples

Consider the query q1 in Example 20-24. Given the data stream S in Example 20-25, the query returns the relation in Example 20-26.

Example 20-24 Select DISTINCT Query

<query id="q1"><![CDATA[ 
    SELECT DISTINCT FROM S WHERE c1 > 10 
]]></query>

Example 20-25 Select DISTINCT Stream Input

Timestamp   Tuple
 1000       23
 2000       14
 3000       13
 5000       22
 6000       11
 7000       10
 8000        9
10000        8
11000        7
12000       13
13000       14

Example 20-26 Select DISTINCT Stream Output

Timestamp   Tuple
1000        23
2000        14
3000        13
5000        22
6000        11

XMLTABLE Query Example

Consider the query q1 in Example 20-27 and the data stream S in Example 20-28. Stream S has schema (c1 xmltype). The query returns the relation in Example 20-29. For more information, see Section 18.2.6, "XMLTable Query".

Example 20-27 XMLTABLE Query

<query id="q1"><![CDATA[ 
    SELECT
        X.Name, 
        X.Quantity 
    from 
        S1 
    XMLTable (
        "//item" PASSING BY VALUE S1.c2 as "." 
        COLUMNS 
            Name CHAR(16) PATH "/item/productName", 
            Quantity INTEGER PATH "/item/quantity"
    ) AS X
]]></query>

Example 20-28 XMLTABLE Stream Input

Timestamp   Tuple
3000        "<purchaseOrder><shipTo><name>Alice Smith</name><street>123 Maple Street</street><city>Mill Valley</city><state>CA</state><zip>90952</zip> </shipTo><billTo><name>Robert Smith</name><street>8 Oak Avenue</street><city>Old Town</city><state>PA</state> <zip>95819</zip> </billTo><comment>Hurry, my lawn is going wild!</comment><items> <item><productName>Lawnmower </productName><quantity>1</quantity><USPrice>148.95</USPrice><comment>Confirm this is electric</comment></item><item> <productName>Baby Monitor</productName><quantity>1</quantity> <USPrice>39.98</USPrice> <shipDate>1999-05-21</shipDate></item></items> </purchaseOrder>"
4000        "<a>hello</a>"

Example 20-29 XMLTABLE Relation Output

Timestamp   Tuple Kind  Tuple
3000:       +           <productName>Lawnmower</productName>,<quantity>1</quantity>
3000:       +           <productName>Baby Monitor</productName>,<quantity>1</quantity>

XMLTABLE With XML Namespaces Query Example

Consider the query q1 in Example 20-30 and the data stream S1 in Example 20-31. Stream S1 has schema (c1 xmltype). The query returns the relation in Example 20-32. For more information, see Section 18.2.6, "XMLTable Query".

Example 20-30 XMLTABLE With XML Namespaces Query

<query id="q1"><![CDATA[ 
    SELECT * from S1 
    XMLTable ( 
        XMLNAMESPACES('http://example.com' as 'e'), 
        'for $i in //e:emps return $i/e:emp' PASSING BY VALUE S1.c1 as "." 
        COLUMNS 
            empName char(16) PATH 'fn:data(@ename)', 
            empId integer PATH 'fn:data(@empno)'
        ) AS X
]]></query>

Example 20-31 XMLTABLE With XML Namespaces Stream Input

Timestamp   Tuple
3000        "<emps xmlns=\"http://example.com\"><emp empno=\"1\" deptno=\"10\" ename=\"John\" salary=\"21000\"/><emp empno=\"2\" deptno=\"10\" ename=\"Jack\" salary=\"310000\"/><emp empno=\"3\" deptno=\"20\" ename=\"Jill\" salary=\"100001\"/></emps>"
h 4000

Example 20-32 XMLTABLE With XML Namespaces Relation Output

Timestamp   Tuple Kind  Tuple
3000:       +     John,1
3000:       +     Jack,2
3000:       +     Jill,3

Data Cartridge TABLE Query Example: Iterator

Consider a data cartridge (MyCartridge) with method getIterator as Example 20-33 shows.

Example 20-33 MyCartridge Method getIterator

...
    public static Iterator<Integer> getIterator() {
        ArrayList<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(2);
        return list.iterator();
    }
...

Consider the query q1 in Example 20-34. Given the data stream S0 in Example 20-35, the query returns the relation in Example 20-36.

Example 20-34 TABLE Query: Iterator

<query id="q1"><![CDATA[ 
    select S1.c1, S1.c2, S2.c1 
    from 
        S0[now] as S1, 
        table (com.acme.MyCartridge.getIterator() as c1) of integer as S2
]]></query>

Example 20-35 TABLE Query Stream Input: Iterator

Timestamp   Tuple
1           1, abc
2           2, ab
3           3, abc
4           4, a
h 200000000

Example 20-36 TABLE Query Output: Iterator

Timestamp   Tuple Kind  Tuple
1:          +           1,abc,1
1:          +           1,abc,2
1:          -           1,abc,1
1:          -           1,abc,2
2:          +           2,ab,1
2:          +           2,ab,2
2:          -           2,ab,1
2:          -           2,ab,2
3:          +           3,abc,1
3:          +           3,abc,2
3:          -           3,abc,1
3:          -           3,abc,2
4:          +           4,a,1
4:          +           4,a,2
4:          -           4,a,1
4:          -           4,a,2

Data Cartridge TABLE Query Example: Array

Consider a data cartridge (MyCartridge) with method getArray as Example 20-37 shows.

Example 20-37 MyCartridge Method getArray

...
    public static Integer[] getArray(int c1) {
        ArrayList<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(2);
        return list.toArray(new Integer[2]);;
    }
...

Consider the query q1 in Example 20-38. Given the data stream S0 in Example 20-39, the query returns the relation in Example 20-40.

Example 20-38 TABLE Query: Array

<query id="q1"><![CDATA[ 
    select S1.c1, S1.c2, S2.c1 
    from 
        S0[now] as S1, 
        table (com.acme.MyCartridge.getArrayS1.c1) as c1) of integer as S2
]]></query>

Example 20-39 TABLE Query Stream Input: Array

Timestamp   Tuple
1           1, abc
2           2, ab
3           3, abc
4           4, a
h 200000000

Example 20-40 TABLE Query Output: Array

Timestamp   Tuple Kind  Tuple
1:          +           1,abc,1
1:          +           1,abc,2
1:          -           1,abc,1
1:          -           1,abc,2
2:          +           2,ab,2
2:          +           2,ab,4
2:          -           2,ab,2
2:          -           2,ab,4
3:          +           3,abc,3
3:          +           3,abc,6
3:          -           3,abc,3
3:          -           3,abc,6
4:          +           4,a,4
4:          +           4,a,8
4:          -           4,a,4
4:          -           4,a,8

Data Cartridge TABLE Query Example: Collection

Consider a data cartridge (MyCartridge) with method getCollection as Example 20-41 shows.

Example 20-41 MyCartridge Method getCollection

...
    public HashMap<Integer,String> developers;
    developers = new HashMap<Integer,String>();
    developers.put(2, "Mohit");
    developers.put(4, "Unmesh");
    developers.put(3, "Sandeep");
    developers.put(1, "Swagat");

    public HashMap<Integer,String> qaengineers;
    qaengineers = new HashMap<Integer,String>();
    qaengineers.put(4, "Terry");
    qaengineers.put(5, "Tony");
    qaengineers.put(3, "Junger");
    qaengineers.put(1, "Arthur");
...
    public Collection<String> getEmployees(int exp_yrs) {
        LinkedList<String> employees = new LinkedList<String>();
        employees.add(developers.get(exp_yrs));    
        employees.add(qaengineers.get(exp_yrs));
        return employees;
  }
...

Consider the query q1 in Example 20-42. Given the data stream S0 in Example 20-43, the query returns the relation in Example 20-44.

Example 20-42 TABLE Query: Collection

<query id="q1"><![CDATA[ 
    RStream(
        select S1.c1, S2.c1 
        from 
            S0[now] as S1, 
            table(S1.c2.getEmployees(S1.c1) as c1) of char as S2
    )
]]></query>

Example 20-43 TABLE Query Stream Input: Collection

Timestamp   Tuple
1           1, abc
2           2, ab
3           3, abc
4           4, a
h 200000000

Example 20-44 TABLE Query Output: Collection

Timestamp   Tuple Kind  Tuple
1:          +           1,Swagat
1:          +           1,Arthur
2:          +           2,Mohit
3:          +           3,Sandeep
3:          +           3,Junger
4:          +           4,Unmesh
4:          +           4,Terry

ORDER BY ROWS Query Example

Consider the query q1 in Example 20-45. Given the data stream S0 in Example 20-46, the query returns the relation in Example 20-47.

Example 20-45 ORDER BY ROWS Query

<query id="q1"><![CDATA[ 
    select c1 ,c2 from S0 order by c1,c2 rows 5
]]></query>

Example 20-46 ORDER BY ROWS Stream Input

Timestamp   Tuple
1000        7, 15
2000        7, 14
2000        5, 23
2000        5, 15
2000        5, 15
2000        5, 25
3000        2, 13
3000        3, 19
4000        4, 17
5000        1, 9
h 1000000000

Example 20-47 ORDER BY ROWS Output

Timestamp   Tuple Kind  Tuple
1000:       +           7,15
2000:       +           7,14
2000:       +           5,23
2000:       +           5,15
2000:       +           5,15
2000:       -           7,15
2000:       +           5,25
3000:       -           7,14
3000:       +           2,13
3000:       -           5,25
3000:       +           3,19
4000:       -           5,23
4000:       +           4,17
5000:       -           5,15
5000:       +           1,9

View

Purpose

Use view statement to create a view over a base stream or relation that you reference by identifier in subsequent Oracle CQL statements.

Prerequisites

For more information, see:

Syntax

You express the a view in a <view></view> element as Example 20-48 shows.

The view element has two attributes:

  • id: Specify the identifier as the view element id attribute.

    The id value must conform with the specification given by identifier::=.

  • schema: Optionally, specify the schema of the view as a space delimited list of attribute names.

    Oracle CEP server infers the types.

Example 20-48 View in a <view></view> Element

<view id="v2" schema="cusip bid ask"><![CDATA[ 
    IStream(select * from S1[range 10 slide 10]) 
]]></view>

The body of the view has the same syntax as a query. For more information, see "Query".

Examples

The following examples illustrate the various semantics that this statement supports. For more examples, see Chapter 18, "Oracle CQL Queries, Views, and Joins".

Registering a View Example

Example 20-49 shows how to register view v2.

Example 20-49 REGISTER VIEW

<view id="v2" schema="cusip bid ask"><![CDATA[ 
    IStream(select * from S1[range 10 slide 10]) 
]]></view>