Skip Headers
Oracle® Fusion Middleware CQL Language Reference for Oracle Event Processing
11g Release 1 (11.1.1.7)

Part Number E12048-09
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

2 Basic Elements of Oracle CQL

This chapter provides a reference for fundamental parts of Oracle Continuous Query Language (Oracle CQL), including datatypes, literals, nulls, and more. Oracle CQL is the query language used in Oracle Event Processing applications.

This chapter includes the following sections:

Before using the statements described in Part IV, "Using Oracle CQL", you should familiarize yourself with the concepts covered in this chapter.

2.1 Datatypes

Each value manipulated by Oracle Event Processing has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle Event Processing to treat values of one datatype differently from values of another. For example, you can add values of INTEGER datatype, but not values of CHAR datatype.When you create a stream, you must specify a datatype for each of its elements. When you create a user-defined function, you must specify a datatype for each of its arguments. These datatypes define the domain of values that each element can contain or each argument can have. For example, attributes with TIMESTAMP as datatype cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'.Oracle CQL provides a number of built-in datatypes that you can use. The syntax of Oracle CQL datatypes appears in the diagrams that follow.

If Oracle CQL does not support a datatype that your events use, you can use an Oracle CQL data cartridge or a user-defined function to evaluate that datatype in an Oracle CQL query.

For more information, see:

datatype::=

Surrounding text describes datatype.png.

variable_length_datatype::=

Surrounding text describes variable_length_datatype.png.

fixed_length_datatype::=

Surrounding text describes fixed_length_datatype.png.

2.1.1 Oracle CQL Built-in Datatypes

Table 2-1 summarizes Oracle CQL built-in datatypes. Refer to the syntax in the preceding sections for the syntactic elements.

Consider these datatype and datatype literal restrictions when defining event types. For more information, see "Creating Oracle Event Processing Event Types" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

Table 2-1 Oracle CQL Built-in Datatype Summary

Oracle CQL Datatype Description

BIGINT

Fixed-length number equivalent to a Java Long type.

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

BOOLEAN

Fixed-length boolean equivalent to a Java Boolean type. Valid values are true or false.

BYTE[(size)]Foot 1 

Variable-length character data of length size bytes. Maximum size is 4096 bytes. Default and minimum size is 1 byte.

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

CHAR[(size)]Footref 1

Variable-length character data of length size characters. Maximum size is 4096 characters. Default and minimum size is 1 character.

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

DOUBLE

Fixed-length number equivalent to a Java double type.

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

FLOAT

Fixed-length number equivalent to a Java float type.

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

INTEGER

Fixed-length number equivalent to a Java int type.

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

INTERVAL

Fixed-length INTERVAL datatype specifies a period of time. Oracle Event Processing supports DAY TO SECOND. Maximum length is 64 bytes. This corresponds to a Java long type.

For more information, see Section 2.3.4, "Interval Literals".

TIMESTAMP

Fixed-length TIMESTAMP datatype stores a datetime literal that conforms to one of the java.text.SimpleDateFormat format models that Oracle CQL supports. Maximum length is 64 bytes.

For more information, see Section 2.3.3, "Datetime Literals".

XMLTYPE

Use this datatype for stream elements that contain XML data. Maximum length is 4096 characters.

XMLTYPE is a system-defined type, so you can use it as an argument of a function or as the datatype of a stream attribute. This corresponds to a Java java.lang.String type.

For more information, see "SQL/XML (SQLX)".

OBJECT

This stands for any Java object (that is, any subclass of java.lang.Object).

We refer to this as opaque type support in Oracle Event Processing since the Oracle Event Processing engine does not understand the contents of an OBJECT field.

You typically use this type to pass values, from an adapter to its destination, as-is; these values need not be interpreted by the Oracle Event Processing engine (such as Collection types or any other user-specific Java type) but that are associated with the event whose other fields are referenced in a query.


Footnote 1 Oracle CQL supports single-dimension arrays only.

2.1.2 Handling Other Datatypes Using Oracle CQL Data Cartridges

If your event uses a datatype that Oracle CQL does not support, you can use an Oracle CQL data cartridge to evaluate that datatype in an Oracle CQL query.

Oracle CQL includes the following data cartridges:

For more information, see Chapter 14, "Introduction to Data Cartridges".

2.1.3 Handling Other Datatypes Using a User-Defined Function

If your event uses a datatype that Oracle CQL does not support, you can create a user-defined function to evaluate that datatype in an Oracle CQL query.

Consider the enum datatype that Example 2-1 shows. The event that Example 2-2 shows uses this enum datatype. Oracle CQL does not support enum datatypes.

Example 2-1 Enum Datatype ProcessStatus

package com.oracle.app;

public enum ProcessStatus {
    OPEN(1), 
    CLOSED(0)}
}

Example 2-2 Event Using Enum Datatype ProcessStatus

package com.oracle.app;

import com.oracle.capp.ProcessStatus;

public class ServiceOrder {
    private String serviceOrderId;
    private String electronicSerialNumber;
    private ProcessStatus status;
... 
}

By creating the user-defined function that Example 2-3 shows and registering the function in your application assembly file as Example 2-4 shows, you can evaluate this enum datatype in an Oracle CQL query as Example 2-5 shows.

Example 2-3 User-Defined Function to Evaluate Enum Datatype

package com.oracle.app;

import com.oracle.capp.ProcessStatus;
public class CheckIfStatusClosed {
    public boolean execute(Object[] args) {
        ProcessStatus arg0 = (ProcessStatus)args[0];
        if (arg0 == ProcessStatus.OPEN)
            return Boolean.FALSE;
        else
            return Boolean.TRUE;
    }
}

Example 2-4 Registering the User-Defined Function in Application Assembly File

<wlevs:processor id="testProcessor">
    <wlevs:listener ref="providerCache"/>
    <wlevs:listener ref="outputCache"/>
    <wlevs:cache-source ref="testCache"/>
    <wlevs:function function-name="statusClosed" exec-method=”execute” />
        <bean class="com.oracle.app.CheckIfStatusClosed"/>
    </wlevs:function>
</wlevs:processor>

Example 2-5 Using the User-Defined Function to Evaluate Enum Datatype in an Oracle CQL Query

<query id="rule-04"><![CDATA[
    SELECT
        meter.electronicSerialNumber, 
        meter.exceptionKind
    FROM 
        MeterLogEvent AS meter, 
        ServiceOrder AS svco
    WHERE 
        meter.electronicSerialNumber = svco.electronicSerialNumber and
        svco.serviceOrderId IS NULL OR statusClosed(svco.status)
]]></query>

For more information, see Chapter 13, "User-Defined Functions".

2.2 Datatype Comparison Rules

This section describes how Oracle Event Processing compares values of each datatype.

2.2.1 Numeric Values

A larger value is considered greater than a smaller one. All negative numbers are less than zero and all positive numbers. Thus, -1 is less than 100; -100 is less than -1.

2.2.2 Date Values

A later date is considered greater than an earlier one. For example, the date equivalent of '29-MAR-2005' is less than that of '05-JAN-2006' and '05-JAN-2006 1:35pm' is greater than '05-JAN-2005 10:09am'.

2.2.3 Character Values

Oracle CQL supports Lexicographic sort based on dictionary order.

Internally, Oracle CQL compares the numeric value of the char. Depending on the encoding used, the numeric values will differ, but in general, the comparison will remain the same. For example:

'a' < 'b'
'aa' < 'ab'
'aaaa' < 'aaaab'

2.2.4 Datatype Conversion

Generally an expression cannot contain values of different datatypes. For example, an arithmetic expression cannot multiply 5 by 10 and then add 'JAMES'. However, Oracle Event Processing supports both implicit and explicit conversion of values from one datatype to another.

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

  • Oracle CQL statements are easier to understand when you use explicit datatype conversion functions.

  • Implicit datatype conversion can have a negative impact on performance.

  • Implicit conversion depends on the context in which it occurs and may not work the same way in every case.

  • Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

This section describes:

2.2.4.1 Implicit Datatype Conversion

Oracle Event Processing automatically converts a value from one datatype to another when such a conversion makes sense.

Table 2-2 is a matrix of Oracle implicit conversions. The table shows all possible conversions (marked with an X). Unsupported conversions are marked with a --.

Table 2-2 Implicit Type Conversion Matrix


to CHAR to BYTE to BOOLEAN to INTEGER to DOUBLE to BIGINT to FLOAT to TIMESTAMP to INTERVAL

from CHAR

--

--

--

--

--

--

--

X

--

from BYTE

X

--

--

--

--

--

--

--

--

from BOOLEAN

--

--

X

--

--

--

--

--

--

from INTEGER

X

--

--

--

X

X

X

--

--

from DOUBLE

X

--

--

--

X

--

--

--

--

from BIGINT

X

--

--

--

X

--

X

--

--

from FLOAT

X

--

--

--

X

--

--

--

--

from TIMESTAMP

X

--

--

--

--

--

--

--

--

from INTERVAL

X

--

--

--

--

--

--

--

--


The following rules govern the direction in which Oracle Event Processing makes implicit datatype conversions:

  • During SELECT FROM operations, Oracle Event Processing converts the data from the stream to the type of the target variable if the select clause contains arithmetic expressions or condition evaluations.

    For example, implicit conversions occurs in the context of expression evaluation, such as c1+2.0, or condition evaluation, such as c1 < 2.0, where c1 is of type INTEGER.

  • Conversions from FLOAT to BIGINT are exact.

  • Conversions from BIGINT to FLOAT are inexact if the BIGINT value uses more bits of precision that supported by the FLOAT.

  • When comparing a character value with a TIMESTAMP value, Oracle Event Processing converts the character data to TIMESTAMP.

  • When you use a Oracle CQL function or operator with an argument of a datatype other than the one it accepts, Oracle Event Processing converts the argument to the accepted datatype wherever supported.

  • When making assignments, Oracle Event Processing converts the value on the right side of the equal sign (=) to the datatype of the target of the assignment on the left side.

  • During concatenation operations, Oracle Event Processing converts from noncharacter datatypes to CHAR.

  • During arithmetic operations on and comparisons between character and noncharacter datatypes, Oracle Event Processing converts from numeric types to CHAR as Table 2-2 shows.

2.2.4.2 Explicit Datatype Conversion

You can explicitly specify datatype conversions using Oracle CQL conversion functions. Table 2-3 shows Oracle CQL functions that explicitly convert a value from one datatype to another. Unsupported conversions are marked with a --.

Table 2-3 Explicit Type Conversion Matrix


to CHAR to BYTE to BOOLEAN to INTEGER to DOUBLE to BIGINT to FLOAT to TIMESTAMP to INTERVAL

from CHAR

--

hextoraw

--

--

--

--

--

to_timestamp

--

from BYTE

--

rawtohex

--

--

--

--

--

--

--

from BOOLEAN

                 

from INTEGER

to_char

--

to_boolean

--

to_double

to_bigint

to_float

--

--

from DOUBLE

to_char

--

--

--

--

--

--

--

--

from LONG

--

--

--

--

--

--

--

to_timestamp

--

from BIGINT

to_char

--

to_boolean

--

to_double

--

to_float

--

--

from FLOAT

to_char

--

--

--

to_double

--

--

--

--

from TIMESTAMP

to_char

--

--

--

--

--

--

--

--

from INTERVAL

to_char

--

--

--

--

--

--

--

--


2.2.4.3 SQL Datatype Conversion

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. When doing so, you must observe the SQL and Oracle Event Processing data type equivalents that Oracle Event Processing supports.

For more information, see:

2.2.4.4 Oracle Data Cartridge Datatype Conversion

At run time, Oracle Event Processing maps between Oracle CQL and data cartridge datatypes according to the data cartridge's implementation.

For more information, see:

2.2.4.5 User-Defined Function Datatype Conversion

At run time, Oracle Event Processing maps between the Oracle CQL datatype you specify for a user-defined function's return type and its Java datatype equivalent.

For more information, see Section 13.1.2, "User-Defined Function Datatypes".

2.3 Literals

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all text literals; 5001 is a numeric literal.

Oracle Event Processing supports the following types of literals in Oracle CQL statements:

2.3.1 Text Literals

Use the text literal notation to specify values whenever const_string, quoted_string_double_quotes, or quoted_string_single_quotes appears in the syntax of expressions, conditions, Oracle CQL functions, and Oracle CQL statements in other parts of this reference. This reference uses the terms text literal, character literal, and string interchangeably.

Text literals are enclosed in single or double quotation marks so that Oracle Event Processing can distinguish them from schema object names.

You may use single quotation marks (') or double quotation marks ("). Typically, you use double quotation marks. However, for certain expressions, conditions, functions, and statements, you must use the quotation marks as specified in the syntax given in other parts of this reference: either quoted_string_double_quotes or quoted_string_single_quotes.

If the syntax uses simply const_string, then you can use either single or double quotation marks.

If the syntax uses the term char, then you can specify either a text literal or another expression that resolves to character data. When char appears in the syntax, the single quotation marks are not used.

Oracle Event Processing supports Java localization. You can specify text literals in the character set specified by your Java locale.

For more information, see:

2.3.2 Numeric Literals

Use numeric literal notation to specify fixed and floating-point numbers.

2.3.2.1 Integer Literals

You must use the integer notation to specify an integer whenever integer appears in expressions, conditions, Oracle CQL functions, and Oracle CQL statements described in other parts of this reference.

The syntax of integer follows:

integer::=

Surrounding text describes integer.png.

where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer can store a maximum of 32 digits of precision.

Here are some valid integers:

7
+255

2.3.2.2 Floating-Point Literals

You must use the number or floating-point notation to specify values whenever number or n appears in expressions, conditions, Oracle CQL functions, and Oracle CQL statements in other parts of this reference.

The syntax of number follows:

number::=

Surrounding text describes number.png.

where

  • + or - indicates a positive or negative value. If you omit the sign, then a positive value is the default.

  • digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.

  • f or F indicates that the number is a 64-bit binary floating point number of type FLOAT.

  • d or D indicates that the number is a 64-bit binary floating point number of type DOUBLE.

    If you omit f or F and d or D, then the number is of type INTEGER.

    The suffixes f or F and d or D are supported only in floating-point number literals, not in character strings that are to be converted to INTEGER. For example, if Oracle Event Processing is expecting an INTEGER and it encounters the string '9', then it converts the string to the Java Integer 9. However, if Oracle Event Processing encounters the string '9f', then conversion fails and an error is returned.

A number of type INTEGER can store a maximum of 32 digits of precision. If the literal requires more precision than provided by BIGINT or FLOAT, then Oracle Event Processing truncates the value. If the range of the literal exceeds the range supported by BIGINT or FLOAT, then Oracle Event Processing raises an error.

If your Java locale uses a decimal character other than a period (.), then you must specify numeric literals with 'text' notation. In these cases, Oracle Event Processing automatically converts the text literal to a numeric value.

Note:

You cannot use this notation for floating-point number literals.

For example, if your Java locale specifies a decimal character of comma (,), specify the number 5.123 as follows:

'5,123'

Here are some valid NUMBER literals:

25
+6.34
0.5
-1

Here are some valid floating-point number literals:

25f
+6.34F
0.5d
-1D

2.3.3 Datetime Literals

Oracle Event Processing supports datetime datatype TIMESTAMP.

Datetime literals must not exceed 64 bytes.

All datetime literals must conform to one of the java.text.SimpleDateFormat format models that Oracle CQL supports. For more information, see Section 2.4.2, "Datetime Format Models".

Currently, the SimpleDateFormat class does not support xsd:dateTime. As a result, Oracle CQL does not support XML elements or attributes that use this type.

For example, if your XML event uses an XSD like Example 2-6, Oracle CQL cannot parse the MyTimestamp element.

Example 2-6 Invalid Event XSD: xsd:dateTime is Not Supported

<xsd:element name="ComplexTypeBody">
    <xsd:complexType>
        <xsd:sequence>
            <xsd:element name="MyTimestamp" type="xsd:dateTime"/>
            <xsd:element name="ElementKind" type="xsd:string"/>
            <xsd:element name="name" type="xsd:string"/>
            <xsd:element name="node" type="SimpleType"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:element> 

Oracle recommends that you define your XSD to replace xsd:dateTime with xsd:string as Example 2-7 shows.

Example 2-7 Valid Event XSD: Using xsd:string Instead of xsd:dateTime

<xsd:element name="ComplexTypeBody">
    <xsd:complexType>
        <xsd:sequence>
            <xsd:element name="MyTimestamp" type="xsd:string"/>
            <xsd:element name="ElementKind" type="xsd:string"/>
            <xsd:element name="name" type="xsd:string"/>
            <xsd:element name="node" type="SimpleType"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:element> 

Using the XSD from Example 2-7, Oracle CQL can process events such as that shown in Example 2-8 as long as the Timestamp element's String value conforms to the java.text.SimpleDateFormat format models that Oracle CQL supports. For more information, see Section 2.4.2, "Datetime Format Models".

Example 2-8 XML Event Payload

<ComplexTypeBody xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ...>
  <MyTimestamp>2000-01-15T00:00:00</MyTimestamp>
  <ElementKind>plus</ElementKind>
  <name>complexEvent</name>
  <node>
    <type>complexNode</type>
    <number>1</number>
  </node>
</ComplexTypeBody>

For more information on using XML with Oracle CQL, see "SQL/XML (SQLX)".

2.3.4 Interval Literals

An interval literal specifies a period of time. Oracle Event Processing supports interval literal DAY TO SECOND. This literal contains a leading field and may contain a trailing field. The leading field defines the basic unit of date or time being measured. The trailing field defines the smallest increment of the basic unit being considered. Part ranges (such as only SECOND or MINUTE to SECOND) are not supported.

Interval literals must not exceed 64 bytes.

2.3.4.1 INTERVAL DAY TO SECOND

Specify DAY TO SECOND interval literals using the following syntax:

interval_value::=

Surrounding text describes interval_value.png.

where const_string is a TIMESTAMP value that conforms to the appropriate datetime format model (see Section 2.4.2, "Datetime Format Models").

Examples of the various forms of INTERVAL DAY TO SECOND literals follow:

Form of Interval Literal Interpretation

INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.


You can add or subtract one DAY TO SECOND interval literal from another DAY TO SECOND literal and compare one interval literal to another as Example 2-9 shows. In this example, stream tkdata2_SIn1 has schema (c1 integer, c2 interval).

Example 2-9 Comparing Intervals

<query id="tkdata2_q295"><![CDATA
select * from tkdata2_SIn1 where (c2 + INTERVAL "2 1:03:45.10" DAY TO SECOND) > INTERVAL "6 12:23:45.10" DAY TO SECOND
]]></query>

2.4 Format Models

A format model is a character literal that describes the format of datetime or numeric data stored in a character string. When you convert a character string into a date or number, a format model determines how Oracle Event Processing interprets the string. The following format models are relevant to Oracle CQL queries:

2.4.1 Number Format Models

You can use number format models in the following functions:

  • In the to_bigint function to translate a value of int datatype to bigint datatype.

  • In the to_float function to translate a value of int or bigint datatype to float datatype

2.4.2 Datetime Format Models

Oracle CQL supports the format models that the java.text.SimpleDateFormat specifies.

Table 2-4 lists the java.text.SimpleDateFormat models that Oracle CQL uses to interpret TIMESTAMP literals. For more information, see Section 2.3.3, "Datetime Literals".

Table 2-4 Datetime Format Models

Format Model Example

MM/dd/yyyy HH:mm:ss Z

11/21/2005 11:14:23 -0800

MM/dd/yyyy HH:mm:ss z

11/21/2005 11:14:23 PST

MM/dd/yyyy HH:mm:ss

11/21/2005 11:14:23

MM-dd-yyyy HH:mm:ss

11-21-2005 11:14:23

dd-MMM-yy

15-DEC-01

yyyy-MM-dd'T'HH:mm:ss

2005-01-01T08:12:12


You can use a datetime format model in the following functions:

  • to_timestamp: to translate the value of a char datatype to a TIMESTAMP datatype.

2.5 Nulls

If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in tuples of any datatype that are not restricted by primary key integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

Oracle Event Processing treats a character value with a length of zero as null. However, do not use null to represent a numeric value of zero, because they are not equivalent.

Note:

Oracle Event Processing currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

For more information, see:

2.5.1 Nulls in Oracle CQL Functions

All scalar functions (except nvl and concat) return null when given a null argument. You can use the nvl function to return a value when a null occurs. For example, the expression NVL(commission_pct,0) returns 0 if commission_pct is null or the value of commission_pct if it is not null.

Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

2.5.2 Nulls with Comparison Conditions

To test for nulls, use only the null comparison conditions (see null_conditions::=). If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle Event Processing considers two nulls to be equal when evaluating a decode expression. See decode::= for syntax and additional information.

2.5.3 Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no tuples. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2-5 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

Table 2-5 Conditions Containing Nulls

Condition Value of A Evaluation

a IS NULL

10

FALSE

a IS NOT NULL

10

TRUE

a IS NULL

NULL

TRUE

a IS NOT NULL

NULL

FALSE

a = NULL

10

FALSE

a != NULL

10

FALSE

a = NULL

NULL

FALSE

a != NULL

NULL

FALSE

a = 10

NULL

FALSE

a != 10

NULL

FALSE


For more information, see Section 6.6, "Null Conditions".

2.6 Comments

Oracle CQL does not support comments.

2.7 Aliases

Oracle CQL allows you to define aliases (or synonyms) to simplify and improve the clarity of your queries.

This section describes:

2.7.1 Defining Aliases Using the AS Operator

Using the AS operator, you can specify an alias in Oracle CQL for queries, relations, streams, and any items in the SELECT list of a query.

This section describes:

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

2.7.1.1 Aliases in the relation_variable Clause

You can use the relation_variable clause AS operator to define an alias to label the immediately preceding expression in the select list so that you can reference the result by that name. The alias effectively renames the select list item for the duration of the query. You can use an alias in the ORDER BY clause (see Section 20.2.9, "Sorting Query Results"), but not other clauses in the query.

Example 2-10 shows how to define alias badItem for a stream element its.itemId in a SELECT list and alias its for a MATCH_RECOGNIZE clause.

Example 2-10 Using the AS Operator in the SELECT Statement

<query id="detectPerish"><![CDATA[ 
  select its.itemId as badItem
  from tkrfid_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>

For more information, see Section 20.2.1.3, "From Clause".

2.7.1.2 Aliases in Window Operators

You can use the AS operator to define an alias to label the immediately preceding window operator so that you can reference the result by that name.

You may not use the AS operator within a window operator but you may use the AS operator outside of the window operator.

Example 2-11 shows how to define aliases bid and ask after partitioned range window operators.

Example 2-11 Using the AS Operator After a Window Operator

<query id="Rule1"><![CDATA[
SELECT
    bid.id as correlationId
    bid.cusip as cusip
    max(bid.b0) as bid0
    bid.srcid as bidSrcId,
    bid.bq0 as bid0Qty,
    min(ask.a0) as ask0,
    ask.srcid as askSrcId,
    ask.aq0 as ask0Qty
FROM
    stream1[PARTITION by bid.cusip rows 100 range 4 hours] as bid,
    stream2[PARTITION by ask.cusip rows 100 range 4 hours] as ask
GROUP BY
    bid.id, bid.cusip, bid.srcid,bid.bq0, ask.srcid, ask.aq0
]]></query> 

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

2.7.2 Defining Aliases Using the Aliases Element

Aliases are required to provide location transparency. Using the aliases element, you can define an alias and then use it in an Oracle CQL query or view. You configure the aliases element in the component configuration file of a processor as Figure 2-0 shows.

Example 2-12 aliases Element in a Processor Component Configuration File

<?xml version="1.0" encoding="UTF-8"?>
<n1:config xmlns:n1="http://www.bea.com/ns/wlevs/config/application">
  <processor>
    <name>processor1</name>
    <rules>
      <query id="q1">
       <![CDATA[
         select str(msg) from cqlInStream [rows 2];
       ]]>
     </query>
    </rules>
    <aliases>
        <type-alias>
            <source>str</source>
            <target>java.lang.String </target>
       </type-alias>
    </aliases>
  </processor>
</n1:config>

The scope of the aliases element is the queries and views defined in the rules element of the processor to which the aliases element belongs.

Note the following:

  • If the alias already exists then, Oracle Event Processing will throw an exception.

  • If a query or view definition references an alias, then the alias must already exist.

This section describes:

2.7.2.1 How to Define a Data Type Alias Using the Aliases Element

Using the aliases element child element type-alias, you can define an alias for a data type. You can create an alias for any built-in or data cartridge data type.

For more information, see Section 2.1, "Datatypes".

To define a type alias using the aliases element:

  1. Edit the component configuration file of a processor.

  2. Add an aliases element as Example 2-13 shows.

    Example 2-13 Adding an aliases Element to a Processor

    <?xml version="1.0" encoding="UTF-8"?>
    <n1:config xmlns:n1="http://www.bea.com/ns/wlevs/config/application">
      <processor>
        <name>processor1</name>
        <rules>
          <query id="q1">
           <![CDATA[
             select str(msg) from cqlInStream [rows 2];
           ]]>
         </query>
        </rules>
        <aliases>
        </aliases>
      </processor>
    </n1:config>
    
  3. Add a type-alias child element to the aliases element as Example 2-14 shows.

    Example 2-14 Adding a type-alias Element to a Processor

    <?xml version="1.0" encoding="UTF-8"?>
    <n1:config xmlns:n1="http://www.bea.com/ns/wlevs/config/application">
      <processor>
        <name>processor1</name>
        <rules>
          <query id="q1">
           <![CDATA[
             select str(msg) from cqlInStream [rows 2];
           ]]>
         </query>
        </rules>
        <aliases>
            <type-alias>
           </type-alias>
        </aliases>
      </processor>
    </n1:config>
    
  4. Add a source and target child element to the type-alias element as Example 2-15 shows, where:

    Example 2-15 Adding the source and target Elements

    <?xml version="1.0" encoding="UTF-8"?>
    <n1:config xmlns:n1="http://www.bea.com/ns/wlevs/config/application">
      <processor>
        <name>processor1</name>
        <rules>
          <query id="q1">
           <![CDATA[
             select str(msg) from cqlInStream [rows 2];
           ]]>
         </query>
        </rules>
        <aliases>
            <type-alias>
                <source>str</source>
                <target>java.lang.String</target>
           </type-alias>
        </aliases>
      </processor>
    </n1:config>
    
  5. Use the alias in the queries and views you define for this processor.

    You can use the alias in exactly the same way you would use the data type it refers to. As Example 2-16 shows, you can access methods and fields of the aliased type.

    Example 2-16 Accessing the Methods and Fields of an Aliased Type

    <?xml version="1.0" encoding="UTF-8"?>
    <n1:config xmlns:n1="http://www.bea.com/ns/wlevs/config/application">
      <processor>
        <name>processor1</name>
        <rules>
          <query id="q1">
           <![CDATA[
             select str(msg).length() from cqlInStream [rows 2];
           ]]>
         </query>
        </rules>
        <aliases>
            <type-alias>
                <source>str</source>
                <target>java.lang.String</target>
           </type-alias>
        </aliases>
      </processor>
    </n1:config>
    

2.8 Schema Object Names and Qualifiers

Some schema objects are made up of parts that you can or must name, such as the stream elements in a stream or view, integrity constraints, streams, views, and user-defined functions. This section provides:

For more information, see Section 1.2.1, "Lexical Conventions".

2.8.1 Schema Object Naming Rules

Every Oracle Event Processing object has a name. In a Oracle CQL statement, you represent the name of an object with an nonquoted identifier, meaning an identifier that is not surrounded by any punctuation.

You must use nonquoted identifiers to name an Oracle Event Processing object.

The following list of rules applies to identifiers:

  • Identifiers cannot be Oracle Event Processing reserved words.

    Depending on the Oracle product you plan to use to access an Oracle Event Processing object, names might be further restricted by other product-specific reserved words.

    The Oracle CQL language contains other words that have special meanings. These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your Oracle CQL statements may be more difficult to read and may lead to unpredictable results.

    For more information, see

  • Oracle recommends that you use ASCII characters in schema object names because ASCII characters provide optimal compatibility across different platforms and operating systems.

  • Identifiers must begin with an alphabetic character (a letter) from your database character set.

  • Identifiers can contain only alphanumeric characters from your Java locale's character set and the underscore (_). In particular, space, dot and slash are not permitted.

    For more information, see:

  • In general, you should choose names that are unique across an application for the following objects:

    • Streams

    • Queries

    • Views

    • User-defined functions

    Specifically, a query and view cannot have the same name.

  • Identifier names are case sensitive.

  • Stream elements in the same stream or view cannot have the same name. However, stream elements in different streams or views can have the same name.

  • Functions can have the same name, if their arguments are not of the same number and datatypes (that is, if they have distinct signatures). Creating multiple functions with the same name with different arguments is called overloading the function.

    If you register or create a user-defined function with the same name and signature as a built-in function, your function replaces that signature of the built-in function. Creating a function with the same name and signature as that of a built-in function is called overriding the function.

    Built-in functions are public where as user-defined functions belong to a particular schema.

    For more information, see:

2.8.2 Schema Object Naming Guidelines

Here are several guidelines for naming objects and their parts:

  • Use full, descriptive, pronounceable names (or well-known abbreviations).

  • Use consistent naming rules.

  • Use the same name to describe the same entity or attribute across streams, views, and queries.

When naming objects, balance the goal of keeping names short and easy to use with the goal of making names as descriptive as possible. When in doubt, choose the more descriptive name, because the objects in Oracle Event Processing may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a stream element with a name like pmdd instead of payment_due_date.

Using consistent naming rules helps users understand the part that each stream plays in your application. One such rule might be to begin the names of all streams belonging to the FINANCE application with fin_.

Use the same names to describe the same things across streams. For example, the department number stream element of the employees and departments streams are both named department_id.

2.8.3 Schema Object Naming Examples

The following examples are valid schema object names:

last_name
horse
a_very_long_and_valid_name

All of these examples adhere to the rules listed in Section 2.8.1, "Schema Object Naming Rules".