1 Introduction to Oracle CQL

This chapter introduces Oracle Continuous Query Language (Oracle CQL), a query language based on SQL with added constructs that support streaming data. Using Oracle CQL, you can express queries on data streams with Oracle Event Processing.

Oracle Event Processing (formally known as the WebLogic Event Server) is a Java server for the development of high-performance event driven applications. It is a lightweight Java application container based on Equinox OSGi, with shared services, including the Oracle Event Processing Service Engine, which provides a rich, declarative environment based on Oracle CQL to improve the efficiency and effectiveness of managing business operations. Oracle Event Processing supports ultra-high throughput and microsecond latency using JRockit Real Time and provides Oracle Event Processing Visualizer and Oracle Event Processing IDE for Eclipse developer tooling for a complete real time end-to-end Java Event-Driven Architecture (EDA) development platform.

This chapter includes the following sections:

Fundamentals of Oracle CQL
Oracle CQL Statements
Oracle CQL and SQL Standards
Oracle Event Processing Server and Tools Support

1.1 Fundamentals of Oracle CQL

Databases are best equipped to run queries over finite stored data sets. However, many modern applications require long-running queries over continuous unbounded sets of data. By design, a stored data set is appropriate when significant portions of the data are queried repeatedly and updates are relatively infrequent. In contrast, data streams represent data that is changing constantly, often exclusively through insertions of new elements. It is either unnecessary or impractical to operate on large portions of the data multiple times.

Many types of applications generate data streams as opposed to data sets, including sensor data applications, financial tickers, network performance measuring tools, network monitoring and traffic management applications, and clickstream analysis tools. Managing and processing data for these types of applications involves building data management and querying capabilities with a strong temporal focus.

To address this requirement, Oracle introduces Oracle Event Processing, a data management infrastructure that supports the notion of streams of structured data records together with stored relations.

To provide a uniform declarative framework, Oracle offers Oracle Continuous Query Language (Oracle CQL), a query language based on SQL with added constructs that support streaming data.

Oracle CQL is designed to be:

Scalable with support for a large number of queries over continuous streams of data and traditional stored data sets.
Comprehensive to deal with complex scenarios. For example, through composability, you can create various intermediate views for querying.

Figure 1-1 shows a simplified view of the Oracle Event Processing architecture. Oracle Event Processing server provides the light-weight Spring container for Oracle Event Processing applications. The Oracle Event Processing application shown is composed of an event adapter that provides event data to an input channel. The input channel is connected to an Oracle CQL processor associated with one or more Oracle CQL queries that operate on the events offered by the input channel. The Oracle CQL processor is connected to an output channel to which query results are written. The output channel is connected to an event Bean: a user-written Plain Old Java Object (POJO) that takes action based on the events it receives from the output channel.

Figure 1-1 Oracle Event Processing Architecture

Description of "Figure 1-1 Oracle Event Processing Architecture"

Using Oracle Event Processing, you can define event adapters for a variety of data sources including JMS, relational database tables, and files in the local filesystem. You can connect multiple input channels to an Oracle CQL processor and you can connect an Oracle CQL processor to multiple output channels. You can connect an output channel to another Oracle CQL processor, to an adapter, to a cache, or an event Bean.

Using Oracle Event Processing IDE for Eclipse and Oracle Event Processing Visualizer, you:

Create an Event Processing Network (EPN) as Figure 1-1 shows.
Associate one more Oracle CQL queries with the Oracle CQL processors in your EPN.
Package your Oracle Event Processing application and deploy it to Oracle Event Processing server for execution.

Consider the typical Oracle CQL statements that Example 1-1 shows.

Example 1-1 Typical Oracle CQL Statements

<?xml version="1.0" encoding="UTF-8"?>
<n1:config xsi:schemaLocation="http://www.bea.com/ns/wlevs/config/application wlevs_application_config.xsd" 
xmlns:n1="http://www.bea.com/ns/wlevs/config/application" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<processor>
    <name>cqlProcessor</name>
    <rules>
        <view id="lastEvents" schema="cusip bid ask"><![CDATA[ 
            select cusip, bid, srcId, bidQty, ask, askQty, seq 
            from inputChannel[partition by srcId, cusip rows 1]
        ]]></view>
        <view id="bidask" schema="cusip bid ask"><![CDATA[ 
            select cusip, max(bid), min(ask) 
            from lastEvents
            group by cusip
        ]]></view>
            <view ...><![CDATA[
                ...
        ]]></view>
        ...
        <view id="MAXBIDMINASK" schema="cusip bidseq bidSrcId bid askseq askSrcId ask bidQty askQty"><![CDATA[ 
            select bid.cusip, bid.seq, bid.srcId as bidSrcId, bid.bid, ask.seq, ask.srcId as askSrcId, ask.ask, bid.bidQty, ask.askQty 
            from BIDMAX as bid, ASKMIN as ask 
            where bid.cusip = ask.cusip
        ]]></view>
        <query id="BBAQuery"><![CDATA[ 
            ISTREAM(select bba.cusip, bba.bidseq, bba.bidSrcId, bba.bid, bba.askseq, bba.askSrcId, bba.ask, 
                bba.bidQty, bba.askQty, "BBAStrategy" as intermediateStrategy, p.seq as correlationId, 1 as priority 
            from MAXBIDMINASK as bba, inputChannel[rows 1] as p where bba.cusip = p.cusip)
        ]]></query>
    </rules>
</processor>

This example defines multiples views (the Oracle CQL-equivalent of subqueries) to create multiple relations, each building on previous views. Views always act on an inbound channel such as inputChannel. The first view, named lastEvents, selects directly from inputChannel. Subsequent views may select from inputChannel directly or select from previously defined views. The results returned by a view's select statement remain in the view's relation: they are not forwarded to any outbound channel. That is the responsibility of a query. This example defines query BBAQuery that selects from both the inputChannel directly and from previously defined views. The results returned from a query's select clause are forwarded to the outbound channel associated with it: in this example, to outputChannel. The BBAQuery uses a tuple-based stream-to-relation operator (or sliding window).

For more information on these elements, see:

Section 1.1.1, "Streams and Relations"
Section 1.1.2, "Relation-to-Relation Operators"
Section 1.1.3, "Stream-to-Relation Operators (Windows)"
Section 1.1.4, "Relation-to-Stream Operators"
Section 1.1.5, "Stream-to-Stream Operators"
Section 1.1.6, "Queries, Views, and Joins"
Section 1.1.7, "Pattern Recognition"
Section 1.1.8, "Event Sources and Event Sinks"
Section 1.1.11, "Functions"
Section 1.1.12, "Data Cartridges"
Section 1.1.13, "Time"
Section 1.2, "Oracle CQL Statements"
Section 1.2.1, "Lexical Conventions"
Section 1.2.3, "Documentation Conventions"

For more information on Oracle Event Processing server and tools, see:

1.1.1 Streams and Relations

This section introduces the two fundamental Oracle Event Processing objects that you manipulate using Oracle CQL:

Streams
Relations

Using Oracle CQL, you can perform the following operations with streams and relations:

Relation-to-Relation Operators: to produce a relation from one or more other relations
Stream-to-Relation Operators (Windows): to produce a a relation from a stream
Relation-to-Stream Operators: to produce a stream from a relation
Stream-to-Stream Operators: to produce a stream from one or more other streams

1.1.1.1 Streams

A stream is the principle source of data that Oracle CQL queries act on.

Stream S is a bag (or multi-set) of elements (s,T) where s is in the schema of S and T is in the time domain.

Stream elements are tuple-timestamp pairs, which can be represented as a sequence of timestamped tuple insertions. In other words, a stream is a sequence of timestamped tuples. There could be more than one tuple with the same timestamp. The tuples of an input stream are required to arrive at the system in the order of increasing timestamps. For more information, see Section 1.1.13, "Time".

A stream has an associated schema consisting of a set of named attributes, and all tuples of the stream conform to the schema.

The term "tuple of a stream" denotes the ordered list of data portion of a stream element, excluding timestamp data (the s of <s,t>). Example 1-2 shows how a stock ticker data stream might appear, where each stream element is made up of <timestamp value>, <stock symbol>, and <stock price>:

Example 1-2 Stock Ticker Data Stream

...
<timestampN>    NVDA,4
<timestampN+1>  ORCL,62
<timestampN+2>  PCAR,38
<timestampN+3>  SPOT,53
<timestampN+4>  PDCO,44
<timestampN+5>  PTEN,50
...

In the stream element <timestampN+1> ORCL,62, the tuple is ORCL,62.

By definition, a stream is unbounded.

This section describes:

Section 1.1.1.1.1, "Streams and Channels"
Section 1.1.1.1.2, "Channel Schema"
Section 1.1.1.1.3, "Querying a Channel"
Section 1.1.1.1.4, "Controlling Which Queries Output to a Downstream Channel"

For more information, see:

Section 1.1.8, "Event Sources and Event Sinks"
Section 20.1, "Introduction to Oracle CQL Queries, Views, and Joins"
"Channels Representing Streams and Relations" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse

1.1.1.1.1 Streams and Channels

Oracle Event Processing represents a stream as a channel as Figure 1-2 shows. Using Oracle Event Processing IDE for Eclipse, you connect the stream event source (PriceAdapter) to a channel (priceStream) and the channel to an Oracle CQL processor (filterFanoutProcessor) to supply the processor with events. You connect the Oracle CQL processor to a channel (filteredStream) to output Oracle CQL query results to down-stream components (not shown in Figure 1-2).

Figure 1-2 Stream in the Event Processing Network

Description of "Figure 1-2 Stream in the Event Processing Network"

Note:

In Oracle Event Processing, you must use a channel to connect an event source to an Oracle CQL processor and to connect an Oracle CQL processor to an event sink. A channel is optional with other Oracle Event Processing processor types.

1.1.1.1.2 Channel Schema

The event source you connect to a stream determines the stream's schema. In Figure 1-2, the PriceAdapter adapter determines the priceStream stream's schema. Example 1-3 shows the PriceAdapter Event Processing Network (EPN) assembly file: the eventTypeName property specifies event type PriceEvent. The event-type-repository defines the property names and types for this event.

Example 1-3 Channel Schema Definition

...
<wlevs:event-type-repository>
    <wlevs:event-type type-name="PriceEvent">
        <wlevs:properties>
            <wlevs:property name="cusip" type="char" />
            <wlevs:property name="bid" type="double" />
            <wlevs:property name="srcId" type="char" />
            <wlevs:property name="bidQty" type="int" />
            <wlevs:property name="ask" type="double" />
            <wlevs:property name="askQty" type="int" />
            <wlevs:property name="seq" type="bigint" />
            <wlevs:property name="sector" type="char" />
        </wlevs:properties>
    </wlevs:event-type>
</wlevs:event-type-repository>

<wlevs:adapter id="PriceAdapter" provider="loadgen">
    <wlevs:instance-property name="port" value="9011"/>
    <wlevs:listener ref="priceStream"/>
</wlevs:adapter>

<wlevs:channel id="priceStream" event-type="PriceEvent">
    <wlevs:listener ref="filterFanoutProcessor"/>
</wlevs:channel>

<wlevs:processor id="filterFanoutProcessor" provider="cql">
    <wlevs:listener ref="filteredStream"/>
</wlevs:processor>

...

1.1.1.1.3 Querying a Channel

Once the event source, channel, and processor are connected as Figure 1-2 shows, you can write Oracle CQL statements that make use of the stream. Example 1-4 shows the component configuration file that defines the Oracle CQL statements for the filterFanoutProcessr.

Example 1-4 filterFanoutProcessor Oracle CQL Query Using priceStream

<processor>
    <name>filterFanoutProcessor</name>
    <rules>
        <query id="Yr3Sector"><![CDATA[ 
            select cusip, bid, srcId, bidQty, ask, askQty, seq 
            from priceStream where sector="3_YEAR"
        ]]></query>
        <query id="Yr2Sector"><![CDATA[ 
            select cusip, bid, srcId, bidQty, ask, askQty, seq 
            from priceStream where sector="2_YEAR"
        ]]></query>
        <query id="Yr1Sector"><![CDATA[ 
            select cusip, bid, srcId, bidQty, ask, askQty, seq 
            from priceStream where sector="1_YEAR"
        ]]></query>
    </rules>
</processor>

1.1.1.1.4 Controlling Which Queries Output to a Downstream Channel

If you specify more than one query for a processor as Example 1-4 shows, then all query results are output to the processor's out-bound channel (filteredStream in Figure 1-2).

Optionally, in the component configuration file, you can use the channel element selector attribute to control which query's results are output as Example 1-5 shows. In this example, query results for query Yr3Sector and Yr2Sector are output to filteredStream but not query results for query Yr1Sector. For more information, see "Connecting EPN Stages Using Channels" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

Example 1-5 Using channel Element selector Child Element to Control Which Query Results are Output to a Channel

<channel>
    <name>filteredStream</name>
    <selector>Yr3Sector Yr2Sector</selector>
</channel>

You may configure a channel element with a selector before creating the queries in the upstream processor. In this case, you must specify query names that match the names in the selector.

For more information, see "Controlling Which Queries Output to a Downstream Channel" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

1.1.1.2 Relations

Time varying relation R is a mapping from the time domain to an unbounded bag of tuples to the schema of R.

A relation is an unordered, time-varying bag of tuples: in other words, an instantaneous relation. At every instant of time, a relation is a bounded set. It can also be represented as a sequence of timestamped tuples that includes insertions, deletions, and updates to capture the changing state of the relation.

Like streams, relations have a fixed schema to which all tuples conform.

Oracle Event Processing supports both base and derived streams and relations. The external sources supply data to the base streams and relations.

A base (explicit) stream is a source data stream that arrives at an Oracle Event Processing adapter so that time is non-decreasing. That is, there could be events that carry same value of time.

A derived (implicit) stream/relation is an intermediate stream/relation that query operators produce. Note that these intermediate operators can be named (through views) and can therefore be specified in further queries.

A base relation is an input relation.

A derived relation is an intermediate relation that query operators produce. Note that these intermediate operators can be named (through views) and can therefore be specified in further queries.

In Oracle Event Processing, you do not create base relations yourself. The Oracle Event Processing server creates base relations for you as required.

When we say that a relation is a time-varying bag of tuples, time refers to an instant in the time domain. Input relations are presented to the system as a sequence of timestamped updates which capture how the relation changes over time. An update is either a tuple insertion or deletion. The updates are required to arrive at the system in the order of increasing timestamps.

For more information, see:

"Channels Representing Streams and Relations" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse
Section 1.1.13, "Time"

1.1.1.3 Relations and Oracle Event Processing Tuple Kind Indicator

By default, Oracle Event Processing includes time stamp and an Oracle Event Processing tuple kind indicator in the relations it generates as Example 1-6 shows.

Example 1-6 Oracle Event Processing Tuple Kind Indicator in Relation Output

Timestamp   Tuple Kind  Tuple
 1000:      +           ,abc,abc
 2000:      +           hihi,abchi,hiabc
 6000:      -           ,abc,abc
 7000:      -           hihi,abchi,hiabc
 8000:      +           hi1hi1,abchi1,hi1abc
 9000:      +           ,abc,abc
13000:      -           hi1hi1,abchi1,hi1abc
14000:      -           ,abc,abc
15000:      +           xyzxyz,abcxyz,xyzabc
20000:      -           xyzxyz,abcxyz,xyzabc

The Oracle Event Processing tuple kind indicators are:

+ for inserted tuple
- for deleted tuple
U for updated tuple indicated when invoking com.bea.wlevs.ede.api.RealtionSink method onUpdateEvent (for more information, see Oracle Fusion Middleware Java API Reference for Oracle Event Processing).

1.1.2 Relation-to-Relation Operators

The relation-to-relation operators in Oracle CQL are derived from traditional relational queries expressed in SQL.

Anywhere a traditional relation is referenced in a SQL query, a relation can be referenced in Oracle CQL.

Consider the following examples for a stream CarSegStr with schema: car_id integer, speed integer, exp_way integer, lane integer, dir integer, and seg integer.

In Example 1-7, at any time instant, the output relation of this query contains the set of vehicles having transmitted a position-speed measurement within the last 30 seconds.

Example 1-7 Relation-to-Relation Operation

<processor>
    <name>cqlProcessor</name>
    <rules>
        <view id="CurCarSeg" schema="car_id exp_way lane dir seg"><![CDATA[ 
            select distinct
                car_id, exp_way, lane, dir, seg 
            from 
                CarSegStr [range 30 seconds]
        ]]></query>
    </rules>
</processor>

The distinct operator is the relation-to-relation operator. Using distinct, Oracle Event Processing returns only one copy of each set of duplicate tuples selected. Duplicate tuples are those with matching values for each expression in the select list. You can use distinct in a select_clause and with aggregate functions.

For more information on distinct, see:

1.1.3 Stream-to-Relation Operators (Windows)

Oracle CQL supports stream-to-relation operations based on a sliding window. In general, S[W] is a relation. At time T the relation contains all tuples in window W applied to stream S up to T.

window_type::=

Oracle CQL supports the following built-in window types:

Range: time-based

S[Range T], or, optionally,

S[Range T1 Slide T2]
Range: time-based unbounded

S[Range Unbounded]
Range: time-based now

S[Now]
Range: constant value

S[Range C on ID]
Tuple-based:

S[Rows N], or, optionally,

S[Rows N1 Slide N2]
Partitioned:

S[Partition By A1 ... Ak Rows N] or, optionally,

S[Partition By A1 ... Ak Rows N Range T], or

S[Partition By A1 ... Ak Rows N Range T1 Slide T2]

This section describes the following stream-to-relation operator properties:

Section 1.1.3.1, "Range, Rows, and Slide"
Section 1.1.3.2, "Partition"
Section 1.1.3.3, "Default Stream-to-Relation Operator"

For more information, see:

1.1.3.1 Range, Rows, and Slide

The keywords Range and Rows specify how much data you want to query:

Range specifies as many tuples as arrive in a given time period
Rows specifies a number of tuples

The Slide keyword specifies how frequently you want to see output from the query, while the Range keyword specifies the time range from which to query events. Using Range and Slide together results in a set of events from which to query, with that set changing based on where the query window slides to. So the set time is the time from which events get drawn for the query.So the time interval is the actual amount of time (as measured by event timestamps) divided by the amount of time specified for sliding. If the remainder from this is 0, then the set time is the time interval multiplied by the amount of time specified for the slide. If the remainder is greater than 0, then the set time is the time interval + 1 multiplied by the amount of time specified for the slide.Another way to express this is the following formula:timeInterval = actualTime / slideSpecificationif((actualTime % slideSpecification) == 0) // No remainder setTime = timeInterval * slideSpecificationelse setTime = (timeInterval + 1) * slideSpecification

In Figure 1-3, the Range specification indicates "I want to look at 5 seconds worth of data" and the Slide specification indicates "I want a result every 5 seconds". In this case, the query returns a result at the end of each Range specification (except for certain conditions, as "Range, Rows, and Slide at Query Start-Up and for Empty Relations" describes).

Figure 1-3 Range and Slide: Equal (Steady-State Condition)

Description of "Figure 1-3 Range and Slide: Equal (Steady-State Condition)"

In Figure 1-4, the Range specification indicates "I want to look at 10 seconds worth of data" and the Slide specification indicates "I want a result every 5 seconds". In this case, the query returns a result twice during each Range specification (except for certain conditions, as Section 1.1.3.1.1, "Range, Rows, and Slide at Query Start-Up and for Empty Relations" describes)

Figure 1-4 Range and Slide: Different (Steady-State Condition)

Description of "Figure 1-4 Range and Slide: Different (Steady-State Condition)"

Table 1-1 lists the default Range, Range unit, and Slide (where applicable) for range-based and tuple-based stream-to-relation window operators:

Table 1-1 Default Range and Tuple-Based Stream-to-Relation Operators

Window Operator	Default Range	Default Range Unit	Default Slide
Range-Based Stream-to-Relation Window Operators	`Unbounded`	`seconds`	1 nanosecond
Tuple-Based Stream-to-Relation Window Operators	N/A	N/A	1 tuple

1.1.3.1.1 Range, Rows, and Slide at Query Start-Up and for Empty Relations

The descriptions for Figure 1-3 and Figure 1-4 assume a steady-state condition, after the query has been running for some time. Table 1-2 lists the behavior of Range, Rows, and Slide for special cases such as query start-up time and for an empty relation.

Table 1-2 Range, Rows, and Slide at Query Start-Up and Empty Relations

Operator or Function	Result
`COUNT(*)` or `COUNT(expression)`	Immediately returns 0 for an empty relation (when there is no `GROUP BY`), before `Range` or `Rows` worth of data has accumulated and before the first `Slide`.
`SUM(attribute)` and other aggregate functions	Immediately returns null for an empty relation, before `Range` or `Rows` worth of data has accumulated and before the first `Slide`.

For more information and detailed examples, see:

1.1.3.2 Partition

The keyword Partition By logically separates an event stream S into different substreams based on the equality of the attributes given in the Partition By specification. For example, the S[Partition By A,C Rows 2] partition specification creates a sub-stream for every unique combination of A and C value pairs and the Rows specification is applied on these sub-streams. The Rows specification indicates "I want to look at 2 tuples worth of data".

For more information, see Section 1.1.3.1, "Range, Rows, and Slide".

1.1.3.3 Default Stream-to-Relation Operator

When you reference a stream in an Oracle CQL query where a relation is expected (most commonly in the from clause), a Range Unbounded window is applied to the stream by default. For example, the queries in Example 1-8 and Example 1-9 are identical:

Example 1-8 Query Without Stream-to-Relation Operator

<query id="q1"><![CDATA[ 
    select * from InputChannel
]]></query>

Example 1-9 Equivalent Query

<query id="q1"><![CDATA[ 
    IStream(select * from InputChannel[RANGE UNBOUNDED])
]]></query>

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

1.1.4 Relation-to-Stream Operators

You can convert the result of a stream-to-relation operation back into a stream for further processing.

In Example 1-10, the select will output a stream of tuples satisfying the filter condition (viewq3.ACCT_INTRL_ID = ValidLoopCashForeignTxn.ACCT_INTRL_ID). The now window converts the viewq3 into a relation, which is kept as a relation by the filter condition. The IStream relation-to-stream operator converts the output of the filter back into a stream.

Example 1-10 Relation-to-Stream Operation

<processor>
    <name>cqlProcessor</name>
    <rules>
        <query id="q3Txns"><![CDATA[ 
            IStream(
                select 
                    TxnId, 
                    ValidLoopCashForeignTxn.ACCT_INTRL_ID, 
                    TRXN_BASE_AM, 
                    ADDR_CNTRY_CD, 
                    TRXN_LOC_ADDR_SEQ_ID 
                from 
                    viewq3[NOW], ValidLoopCashForeignTxn 
                where 
                    viewq3.ACCT_INTRL_ID = ValidLoopCashForeignTxn.ACCT_INTRL_ID
            )
        ]]></query>
    </rules>
</processor>

Oracle CQL supports the following relation-to-stream operators:

IStream: insert stream.

IStream(R) contains all (r,T) where r is in R at time T but r is not in R at time T-1.

For more information, see "IStream Relation-to-Stream Operator".
DSteam: delete stream.

DStream(R) contains all (r,T) where r is in R at time T-1 but r is not in R at time T.

For more information, see "DStream Relation-to-Stream Operator".
RStream: relation stream.

RStream(R) contains all (r,T) where r is in R at time T.

For more information, see "RStream Relation-to-Stream Operator".

By default, Oracle Event Processing includes an operation indicator in the relations it generates so you can identify insertions, deletions, and, when using UPDATE SEMANTICS, updates. For more information, see Section 1.1.1.3, "Relations and Oracle Event Processing Tuple Kind Indicator".

1.1.4.1 Default Relation-to-Stream Operator

Whenever an Oracle CQL query produces a relation that is monotonic, Oracle CQL adds an IStream operator by default.

A relation R is monotonic if and only if R(t1) is a subset of R(t2) whenever t1 <= t2.

Oracle CQL use a conservative static monotonicity test. For example, a base relation is monotonic if it is known to be append-only: S[Range Unbounded] is monotonic for any stream S; and the join of two monotonic relations is also monotonic.

If a relation is not monotonic (for example, it has a window like S[range 10 seconds]), it is impossible to determine what the query author intends (IStream, DStream, or RStream), so Oracle CQL does not add a relation-to-stream operator by default in this case.

1.1.5 Stream-to-Stream Operators

Typically, you perform stream to stream operations using the following:

A stream-to-relation operator to turn the stream into a relation. For more information, see Section 1.1.3, "Stream-to-Relation Operators (Windows)".
A relation-to-relation operator to perform a relational filter. For more information, see Section 1.1.2, "Relation-to-Relation Operators".
A relation-to-stream operator to turn the relation back into a stream. For more information, see Section 1.1.4, "Relation-to-Stream Operators".

However, some relation-relation operators (like filter and project) can also act as stream-stream operators. Consider the query that Example 1-11 shows: assuming that the input S is a stream, the query will produce a stream as an output where stream element c1 is greater than 50.

Example 1-11 Stream-to-Stream Operation

<processor>
    <name>cqlProcessor</name>
    <rules>
        <query id="q0"><![CDATA[ 
            select * from S where c1 > 50
        ]]></query>
    </rules>
</processor>

This is a consequence of the application of the default stream-to-relation and relation-to-stream operators. The stream S in Example 1-11 gets a default [Range Unbounded] window added to it. Since this query then evaluates to a relation that is monotonic, an IStream gets added to it.

For more information, see:

Section 1.1.3.3, "Default Stream-to-Relation Operator"
Section 1.1.4.1, "Default Relation-to-Stream Operator"

In addition, Oracle CQL supports the following direct stream-to-stream operators:

MATCH_RECOGNIZE: use this clause to write various types of pattern recognition queries on the input stream. For more information, see Section 1.1.7, "Pattern Recognition".
XMLTABLE: use this clause to parse data from the xmltype stream elements using XPath expressions. For more information, see Section 20.2.6, "XMLTABLE Query".

1.1.6 Queries, Views, and Joins

An Oracle CQL query is an operation that you express in Oracle CQL syntax and execute on an Oracle Event Processing CQL processor to retrieve data from one or more streams, relations, or views. A top-level SELECT statement that you create in a <query> element is called a query. For more information, see Section 20.2, "Queries".

An Oracle CQL view represents an alternative selection on a stream or relation. In Oracle CQL, you use a view instead of a subquery. A top-level SELECT statement that you create in a <view> element is called a view. For more information, see Section 20.3, "Views".

Each query and view must have an identifier unique to the processor that contains it. Example 1-12 shows a query with an id of q0. The id value must conform with the specification given by identifier::=.

Example 1-12 Query and View id Attribute

<processor>
    <name>cqlProcessor</name>
    <rules>
        <query id="q0"><![CDATA[ 
            select * from S where c1 > 50
        ]]></query>
    </rules>
</processor>

A join is a query that combines rows from two or more streams, views, or relations. For more information, see Section 20.4, "Joins".

For more information, see Chapter 20, "Oracle CQL Queries, Views, and Joins".

1.1.7 Pattern Recognition

The Oracle CQL MATCH_RECOGNIZE construct is the principle means of performing pattern recognition.

A sequence of consecutive events or tuples in the input stream, each satisfying certain conditions constitutes a pattern. The pattern recognition functionality in Oracle CQL allows you to define conditions on the attributes of incoming events or tuples and to identify these conditions by using String names called correlation variables. The pattern to be matched is specified as a regular expression over these correlation variables and it determines the sequence or order in which conditions should be satisfied by different incoming tuples to be recognized as a valid match.

For more information, see Chapter 21, "Pattern Recognition With MATCH_RECOGNIZE".

1.1.8 Event Sources and Event Sinks

An Oracle Event Processing event source identifies a producer of data that your Oracle CQL queries operate on. An Oracle CQL event sink identifies a consumer of query results.

This section explains the types of event sources and sinks you can access in your Oracle CQL queries and how you connect event sources and event sinks.

1.1.8.1 Event Sources

An Oracle Event Processing event source identifies a producer of data that your Oracle CQL queries operate on.

In Oracle Event Processing, the following elements may be event sources:

adapter (JMS, HTTP, and file)
channel
processor
table
cache

Note:

Oracle Event Processing event sources are typically push data sources: that is, Oracle Event Processing expects the event source to notify it when the event source has data ready.

Oracle Event Processing relational database table and cache event sources are pull data sources: that is, Oracle Event Processing polls the event source on arrival of an event on the data stream.

For more information, see:

Section 1.1.9, "Table Event Sources"
Section 1.1.10, "Cache Event Sources"

1.1.8.2 Event Sinks

An Oracle CQL event sink connected to a CQL processor is a consumer of query results.

In Oracle Event Processing, the following elements may be event sinks:

adapter (JMS, HTTP, and file)
channel
processor
cache

You can associate the same query with more than one event sink and with different types of event sink.

1.1.8.3 Connecting Event Sources and Event Sinks

In Oracle Event Processing, you define event sources and event sinks using Oracle Event Processing IDE for Eclipse to create the Event Processing Network (EPN) as Figure 1-5 shows. In this EPN, adapter PriceAdapter is the event source for channel priceStream; channel priceStream is the event source for Oracle CQL processor filterFanoutProcessor. Similarly, Oracle CQL processor filterFanoutProcessor is the event sink for channel priceStream.

Figure 1-5 Event Sources and Event Sinks in the Event Processing Network

Description of "Figure 1-5 Event Sources and Event Sinks in the Event Processing Network"

For more information, see:

1.1.9 Table Event Sources

Using Oracle CQL, you can access tabular data, including:

Section 1.1.9.1, "Relational Database Table Event Sources"
Section 1.1.9.2, "XML Table Event Sources"
Section 1.1.9.3, "Function Table Event Sources"

For more information, see Section 1.1.8, "Event Sources and Event Sinks"

1.1.9.1 Relational Database Table Event Sources

Using an Oracle CQL processor, you can specify a relational database table as an event source. You can query this event source, join it with other event sources, and so on.

For more information, see Section 20.6, "Oracle CQL Queries and Relational Database Tables".

1.1.9.2 XML Table Event Sources

Using the Oracle CQL XMLTABLE clause, you can parse data from an xmltype stream into columns using XPath expressions and conveniently access the data by column name.

For more information, see Section 20.2.6, "XMLTABLE Query".

1.1.9.3 Function Table Event Sources

Use the TABLE clause to access, as a relation, the multiple rows returned by a built-in or user-defined function, as an array or Collection type, in the FROM clause of an Oracle CQL query.

For more information, see:

1.1.10 Cache Event Sources

Using an Oracle CQL processor, you can specify an Oracle Event Processing cache as an event source. You can query this event source and join it with other event sources using a now window only.

For more information, see:

1.1.11 Functions

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

function(argument, argument, ...)

A function without any arguments is similar to a pseudocolumn (refer to Chapter 3, "Pseudocolumns"). However, a pseudocolumn typically returns a different value for each tuple in a relation, whereas a function without any arguments typically returns the same value for each tuple.

Oracle CQL provides a wide variety of built-in functions to perform operations on stream data, including:

single-row functions that return a single result row for every row of a queried stream or view
aggregate functions that return a single aggregate result based on group of tuples, rather than on a single tuple
single-row statistical and advanced arithmetic operations based on the Colt open source libraries for high performance scientific and technical computing.
aggregate statistical and advanced arithmetic operations based on the Colt open source libraries for high performance scientific and technical computing.
statistical and advanced arithmetic operations based on the java.lang.Math class

If Oracle CQL built-in functions do not provide the capabilities your application requires, you can easily create user-defined functions in Java by using the classes in the oracle.cep.extensibility.functions package. You can create aggregate and single-row user-defined functions. You can create overloaded functions and you can override built-in functions.

If you call an Oracle CQL function with an argument of a datatype other than the datatype expected by the Oracle CQL function, then Oracle Event Processing attempts to convert the argument to the expected datatype before performing the Oracle CQL function.

Note:

Function names are case sensitive:

Built-in functions: lower case.
User-defined functions: welvs:function element function-name attribute determines the case you use.

For more information, see:

1.1.12 Data Cartridges

The Oracle CQL data cartridge framework allows you to tightly integrate arbitrary domain objects with the Oracle CQL language and use domain object fields, methods, and constructors within Oracle CQL queries in the same way you use Oracle CQL native types.

Currently, Oracle Event Processing provides the following data cartridges:

Oracle Java data cartridge: this data cartridge exposes Java types, methods, fields, and constructors that you can use in Oracle CQL queries and views as you would Oracle CQL native types.

See Chapter 15, "Oracle Java Data Cartridge".
Oracle Spatial: this data cartridge exposes Oracle Spatial types, methods, fields, and constructors that you can use in Oracle CQL queries and views as you would Oracle CQL native types.

See Chapter 16, "Oracle Spatial".
Oracle JDBC data cartridge: this data cartridge allows you to incorporate arbitrary SQL functions against multiple tables and data sources in Oracle CQL queries and views as you would Oracle CQL native types.

See Chapter 17, "Oracle Event Processing JDBC Data Cartridge".

For more information, see:

1.1.13 Time

Timestamps are an integral part of an Oracle Event Processing stream. However, timestamps do not necessarily equate to clock time. For example, time may be defined in the application domain where it is represented by a sequence number. Timestamps need only guarantee that updates arrive at the system in the order of increasing timestamp values.

Note that the timestamp ordering requirement is specific to one stream or a relation. For example, tuples of different streams could be arbitrarily interleaved.

Oracle Event Processing can observe application time or system time.

To configure application timestamp or system timestamp operation, see child element application-timestamped in "wlevs:channel" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

For system timestamped relations or streams, time is dependent upon the arrival of data on the relation or stream data source. Oracle Event Processing generates a heartbeat on a system timestamped relation or stream if there is no activity (no data arriving on the stream or relation's source) for more than a specified time: for example, 1 minute. Either the relation or stream is populated by its specified source or Oracle Event Processing generates a heartbeat every minute. This way, the relation or stream can never be more than 1 minute behind.

To configure a heartbeat, see "heartbeat" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

For system timestamped streams and relations, the system assigns time in such a way that no two events will have the same value of time. However, for application timestamped streams and relations, events could have same value of time.

If you know that the application timestamp will be strictly increasing (as opposed to non-decreasing) you may set wlevs:channel attribute is-total-order to true. This enables the Oracle Event Processing engine to do certain optimizations and typically leads to reduction in processing latency.

To configure is-total-order, see "wlevs:application-timestamped" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

The Oracle Event Processing scheduler is responsible for continuously executing each Oracle CQL query according to its scheduling algorithm and frequency.

For more information on the scheduler, see "scheduler" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

1.2 Oracle CQL Statements

Oracle CQL provides statements for creating queries and views.

This section describes:

Section 1.2.1, "Lexical Conventions"
Section 1.2.2, "Syntactic Shortcuts and Defaults"
Section 1.2.3, "Documentation Conventions"

For more information, see:

1.2.1 Lexical Conventions

Using Oracle Event Processing IDE for Eclipse or Oracle Event Processing Visualizer, you write Oracle CQL statements in the XML configuration file associated with an Oracle Event Processing CQL processor. This XML file is called the configuration source.

The configuration source must conform with the wlevs_application_config.xsd schema and may contain only rule, view, or query elements as Example 1-13 shows.

Example 1-13 Typical Oracle CQL Processor Configuration Source File

<?xml version="1.0" encoding="UTF-8"?>
<n1:config xsi:schemaLocation="http://www.bea.com/ns/wlevs/config/application wlevs_application_config.xsd" 
    xmlns:n1="http://www.bea.com/ns/wlevs/config/application" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<processor>
    <name>cqlProcessor</name>
    <rules>
        <view id="lastEvents" schema="cusip bid srcId bidQty ask askQty seq"><![CDATA[ 
            select cusip, bid, srcId, bidQty, ask, askQty, seq 
            from inputChannel[partition by srcId, cusip rows 1]
        ]]></view>
        <view id="bidask" schema="cusip bid ask"><![CDATA[ 
            select cusip, max(bid), min(ask) 
            from lastEvents
            group by cusip
        ]]></view>
            <view ...><![CDATA[
                ...
        ]]></view>
        ...
        <view id="MAXBIDMINASK" schema="cusip bidseq bidSrcId bid askseq askSrcId ask bidQty askQty"><![CDATA[ 
            select bid.cusip, bid.seq, bid.srcId as bidSrcId, bid.bid, ask.seq, ask.srcId as askSrcId, ask.ask, bid.bidQty, ask.askQty 
            from BIDMAX as bid, ASKMIN as ask 
            where bid.cusip = ask.cusip
        ]]></view>
        <query id="BBAQuery"><![CDATA[ 
            ISTREAM(select bba.cusip, bba.bidseq, bba.bidSrcId, bba.bid, bba.askseq, 
                bba.askSrcId, bba.ask, bba.bidQty, bba.askQty, "BBAStrategy" as intermediateStrategy, 
                p.seq as correlationId, 1 as priority 
            from MAXBIDMINASK as bba, inputChannel[rows 1] as p where bba.cusip = p.cusip)
        ]]></query>
    </rules>
</processor>

When writing Oracle CQL queries in an Oracle CQL processor component configuration file, observe the following rules:

You may specify one Oracle CQL statement per view or query element.
You must not terminate Oracle CQL statements with a semicolon (;).
You must enclose each Oracle CQL statement in <![CDATA[ and ]]> as Example 1-13 shows.

When you issue an Oracle CQL statement, you can include one or more tabs, carriage returns, or spaces anywhere a space occurs within the definition of the statement. Thus, Oracle Event Processing evaluates the Oracle CQL statement in Example 1-14 and Example 1-15 in the same manner.

Example 1-14 Oracle CQL: Without Whitespace Formatting

<processor>
    <name>cqlProcessor</name>
    <rules>
        <query id="QTollStr"><![CDATA[ 
            RSTREAM(select cars.car_id, SegToll.toll from CarSegEntryStr[now] as cars, SegToll 
                where (cars.exp_way = SegToll.exp_way and cars.lane = SegToll.lane 
                    and cars.dir = SegToll.dir and cars.seg = SegToll.seg))
        ]]></query>
    </rules>
</processor>

Example 1-15 Oracle CQL: With Whitespace Formatting

<processor>
    <name>cqlProcessor</name>
    <rules>
        <query id="QTollStr"><![CDATA[ 
            RSTREAM(
                select
                    cars.car_id, 
                    SegToll.toll 
                from 
                    CarSegEntryStr[now]
                as
                    cars, SegToll 
                where (
                    cars.exp_way = SegToll.exp_way and 
                    cars.lane = SegToll.lane and 
                    cars.dir = SegToll.dir and 
                    cars.seg = SegToll.seg
                )
            )
        ]]></query>
    </rules>
</processor>

Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in function names, text literals, and quoted names.

For more information, see:
Comments are not permitted in Oracle CQL statements. For more information, see Section 2.6, "Comments".

Note:

Throughout the Oracle Fusion Middleware CQL Language Reference for Oracle Event Processing, Oracle CQL statements are shown only with their view or query element for clarity.

1.2.2 Syntactic Shortcuts and Defaults

When writing Oracle CQL queries, views, and joins, consider the syntactic shortcuts and defaults that Oracle CQL provides to simplify your queries.

For more information, see:

1.2.3 Documentation Conventions

All Oracle CQL statements in this reference (see Chapter 22, "Oracle CQL Statements") are organized into the following sections:

Syntax The syntax diagrams show the keywords and parameters that make up the statement.

Caution:

Not all keywords and parameters are valid in all circumstances. Be sure to refer to the "Semantics" section of each statement and clause to learn about any restrictions on the syntax.

Purpose The "Purpose" section describes the basic uses of the statement.

Prerequisites The "Prerequisites" section lists privileges you must have and steps that you must take before using the statement.

Semantics The "Semantics" section describes the purpose of the keywords, parameter, and clauses that make up the syntax, and restrictions and other usage notes that may apply to them. (The conventions for keywords and parameters used in this chapter are explained in the Preface of this reference.)

Examples The "Examples" section shows how to use the various clauses and parameters of the statement.

1.3 Oracle CQL and SQL Standards

Oracle CQL is a new technology but it is based on a subset of SQL99.

Oracle strives to comply with industry-accepted standards and participates actively in SQL standards committees. Oracle is actively pursuing Oracle CQL standardization.

1.4 Oracle Event Processing Server and Tools Support

Using the Oracle Event Processing server and tools, you can efficiently create, package, deploy, debug, and manage Oracle Event Processing applications that use Oracle CQL.

1.4.1 Oracle Event Processing Server

Oracle Event Processing server provides the light-weight Spring container for Oracle Event Processing applications and manages server and application lifecycle, provides a JRockit real-time JVM with deterministic garbage collection, and a wide variety of essential services such as security, Jetty, JMX, JDBC, HTTP publish-subscribe, and logging and debugging.

For more information on Oracle Event Processing server, see Oracle Fusion Middleware Administrator's Guide for Oracle Event Processing.

1.4.2 Oracle Event Processing Tools

Oracle Event Processing provides the following tools to facilitate your Oracle CQL development process:

Section 1.4.2.1, "Oracle Event Processing IDE for Eclipse"
Section 1.4.2.2, "Oracle Event Processing Visualizer"

1.4.2.1 Oracle Event Processing IDE for Eclipse

Oracle Event Processing IDE for Eclipse is targeted specifically to programmers that want to develop Oracle Event Processing applications as Figure 1-6 shows.

Figure 1-6 Oracle Event Processing IDE for Eclipse

Description of "Figure 1-6 Oracle Event Processing IDE for Eclipse"

The Oracle Event Processing IDE for Eclipse is a set of plugins for the Eclipse IDE designed to help develop, deploy, and debug Oracle Event Processing applications.

The key features of Oracle Event Processing IDE for Eclipse are:

Project creation wizards and templates to quickly get started building event driven applications.
Advanced editors for source files including Java and XML files common to Oracle Event Processing applications.
Integrated server management to seamlessly start, stop, and deploy to Oracle Event Processing server instances all from within the IDE.
Integrated debugging.
Event Processing Network (EPN) visual design views for orienting and navigating in event processing applications.
Integrated support for the Oracle Event Processing Visualizer so you can use the Oracle Event Processing Visualizer from within the IDE (see Section 1.4.2.2, "Oracle Event Processing Visualizer").

For details, see:

1.4.2.2 Oracle Event Processing Visualizer

Oracle provides an advanced run-time administration console called the Oracle Event Processing Visualizer as Figure 1-7 shows.

Figure 1-7 Oracle Event Processing Visualizer

Description of "Figure 1-7 Oracle Event Processing Visualizer"

Using Oracle Event Processing Visualizer, you can manage, tune, and monitor Oracle Event Processing server domains and the Oracle Event Processing applications you deploy to them all from a browser. Oracle Event Processing Visualizer provides a variety of sophisticated run-time administration tools, including support for Oracle CQL and EPL rule maintenance and creation.

For details, see Oracle Fusion Middleware Visualizer User's Guide for Oracle Event Processing