This chapter provides a reference to single-row functions in Oracle Continuous Query Language (Oracle CQL). Single-row functions return a single result row for every row of a queried stream or view.
This chapter includes the following section:
Table 8-1 lists the built-in single-row functions that Oracle CQL provides.
Table 8-1 Oracle CQL Built-in Single-Row Functions
Type | Function |
---|---|
Character (returning character values) |
|
Character (returning numeric values) |
|
Datetime |
|
Conversion |
|
XML and SQLX |
|
Encoding and Decoding |
|
Null-related |
|
Pattern Matching |
Note:
Built-in function names are case sensitive and you must use them in the case shown (in lower case).Note:
In stream input examples, lines beginning withh
(such as h 3800
) are heartbeat input tuples. These inform Oracle Event Processing that no further input will have a timestamp lesser than the heartbeat value.For more information, see:
concat
returns char1
concatenated with char2
as a char[]
or byte1
concatenated with byte2
as a byte[]
. The char
returned is in the same character set as char1
. Its data type depends on the data types of the arguments.
Using concat
, you can concatenate any combination of character, byte, and numeric data types. The concat
performs automatic numeric to string conversion.
This function is equivalent to the concatenation operator (||).
To concatenate xmltype
arguments, use xmlconcat
. For more information, see "xmlconcat".
Consider the query chr_concat
and data stream S4
. Stream S4
has schema (c1 char(10))
. The query returns the relation.
<query id="chr_concat"><![CDATA[ select concat(c1,c1), concat("abc",c1), concat(c1,"abc") from S4[range 5] ]]></query>
Timestamp Tuple 1000 2000 hi 8000 hi1 9000 15000 xyz h 200000000
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
Consider the query q264
and the data stream S10
. Stream S10
has schema (c1 integer, c2 char(10))
. The query returns the relation.
<query id="q264"><![CDATA[ select c2 || "xyz" from S10 ]]></query>
Timestamp Tuple 1 1,abc 2 2,ab 3 3,abc 4 4,a h 200000000
Timestamp Tuple Kind Tuple 1: + abcxyz 2: + abxyz 3: + abcxyz 4: + axyz
hextoraw
converts char
containing hexadecimal digits in the char
character set to a raw value.
See Also:
"rawtohex"Consider the query q6
and the data stream SinpByte1
. Stream SinpByte1
has schema (c1 byte(10), c2 integer)
. The query returns the relation.
<query id="q6"><![CDATA[ select * from SByt[range 2] where c2 between hextoraw("5200") and hextoraw("5600") ]]></query>
Timestamp Tuple 1000 1,"51c1" 2000 2,"52" 3000 3,"53aa" 4000 4,"5" 5000 ,"55ef" 6000 6, h 8000 h 200000000
Timestamp Tuple Kind Tuple 2000 + 2,"52" 3000 + 3,"53aa" 4000 - 2,"52" 5000 - 3,"53aa" 5000 + ,"55ef" 7000 - ,"55ef"
The length
function returns the length of its char
or byte
expression as an int
. length
calculates length using characters as defined by the input character set.
For a char
expression, the length includes all trailing blanks. If the expression is null, this function returns null.
Consider the query chr_len
and the data stream S2
. Stream S2
has schema (c1 integer, c2 integer)
. The query returns the relation.
<query id="chr_len"><![CDATA[ select length(c1) from S4[range 5] ]]></query>
Timestamp Tuple 1000 2000 hi 8000 hi1 9000 15000 xyz h 200000000
Timestamp Tuple Kind Tuple 1000: + 0 2000: + 2 6000: - 0 7000: - 2 8000: + 3 9000: + 0 13000: - 3 14000: - 0 15000: + 3 20000: - 3
lk
boolean true
if char1
matches the regular expression char2
, otherwise it returns false
.
This function is equivalent to the LIKE
condition. For more information, see .
Consider the query q291
and the data stream SLk1
. Stream SLk1
has schema (first1 char(20), last1 char(20))
. The query returns the relation.
<query id="q291"><![CDATA[ select * from SLk1 where lk(first1,"^Ste(v|ph)en$") = true ]]></query>
Timestamp Tuple 1 Steven,King 2 Sten,Harley 3 Stephen,Stiles 4 Steven,Markles h 200000000
Timestamp Tuple Kind Tuple 1: + Steven,King 3: + Stephen,Stiles 4: + Steven,Markles
nvl
lets you replace null (returned as a blank) with a string in the results of a query. If expr1
is null, then NVL
returns expr2
. If expr1
is not null, then NVL
returns expr1
.
The arguments expr1
and expr2
can have any data type. If their data types are different, then Oracle Event Processing implicitly converts one to the other. If they cannot be converted implicitly, Oracle Event Processing returns an error. The implicit conversion is implemented as follows:
If expr1
is character data, then Oracle Event Processing converts expr2
to character data before comparing them and returns VARCHAR2
in the character set of expr1
.
If expr1
is numeric, then Oracle Event Processing determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
Consider the query q281
and the data stream SNVL
. Stream SNVL
has schema (c1 char(20), c2 integer)
. The query returns the relation.
<query id="q281"><![CDATA[ select nvl(c1,"abcd") from SNVL ]]></query>
Timestamp Tuple 1 ,1 2 ab,2 3 abc,3 4 ,4 h 200000000
Timestamp Tuple Kind Tuple 1: + abcd 2: + ab 3: + abc 4: + abcd
prev
returns the value of the stream attribute (function argument identifier2
) of the event that occurred previous to the current event and which belongs to the partition to which the current event belongs. It evaluates to NULL
if there is no such previous event.
The type of the specified stream element may be any of:
integer
bigint
float
double
byte
char
interval
timestamp
The return type of this function depends on the type of the specified stream attribute (function argument identifier2
).
This function takes the following arguments:
Where:
identifier1.identifier2
: identifier1
is the name of a correlation variable used in the PATTERN
clause and defined in the DEFINE
clause and identifier2
is the name of a stream attribute whose value in the previous event should be returned by prev
.
const_int
: if this argument has a value n, then it specifies the nth previous event in the partition to which the current event belongs. The value of the attribute (specified in argument identifier2
) in the nth previous event will be returned if such an event exists, NULL
otherwise.
const_bigint
: specifies a time range duration in nanoseconds and should be used if you are interested in previous events that occurred only within a certain range of time before the current event.
If the query uses PARTITION BY
with the prev
function and input data will include many different partition key values (meaning many partitions), then total memory consumed for storing the previous event(s) per partition could be large. In such cases, consider using the time range duration (the third argument, possibly with a large range value) so that this memory can be reclaimed wherever possible.
See Also:
Consider query q2
and the data stream S1
. Stream S1
has schema (c1 integer)
. This example defines pattern A
as A.c1 = prev(A.c1)
. In other words, pattern A
matches when the value of c1
in the current stream element matches the value of c1
in the stream element immediately before the current stream element. The query returns the stream.
<query id="q2"><![CDATA[ select T.Ac1, T.Cc1 from S1 MATCH_RECOGNIZE ( MEASURES A.c1 as Ac1, C.c1 as Cc1 PATTERN(A B+ C) DEFINE A as A.c1 = prev(A.c1), B as B.c1 = 10, C as C.c1 = 7 ) as T ]]></query>
Timestamp Tuple 1000 35 3000 35 4000 10 5000 7
Timestamp Tuple Kind Tuple 5000: + 35,7
prev(identifier1.identifier2, const_int)
Consider query q35
and the data stream S15
. Stream S15
has schema (c1 integer, c2 integer)
. This example defines pattern A
as A.c1 = prev(A.c1,3)
. In other words, pattern A
matches when the value of c1
in the current stream element matches the value of c1
in the third stream element before the current stream element. The query returns the stream.
<query id="q35"><![CDATA[ select T.Ac1 from S15 MATCH_RECOGNIZE ( MEASURES A.c1 as Ac1 PATTERN(A) DEFINE A as (A.c1 = prev(A.c1,3) ) ) as T ]]></query>
Timestamp Tuple 1000 45,20 2000 45,30 3000 45,30 4000 45,30 5000 45,30 6000 45,20 7000 45,20 8000 45,20 9000 43,40 10000 52,10 11000 52,30 12000 43,40 13000 52,50 14000 43,40 15000 43,40
Timestamp Tuple Kind Tuple 4000: + 45 5000: + 45 6000: + 45 7000: + 45 8000: + 45 12000: + 43 13000: + 52 15000: + 43
prev(identifier1.identifier2, const_int, const_bigint)
Consider query q36
and the data stream S15
. Stream S15
has schema (c1 integer, c2 integer)
. This example defines pattern A
as A.c1 = prev(A.c1,3,5000000000L)
. In other words, pattern A
matches when:
the value of c1
in the current event equals the value of c1
in the third previous event of the partition to which the current event belongs, and
the difference between the timestamp of the current event and that third previous event is less than or equal to 5000000000L
nanoseconds.
The query returns the output stream. Notice that in the output stream, there is no output at 8000
. The following example shows the contents of the partition (partitioned by the value of the c2
attribute) to which the event at 8000
belongs.
Timestamp Tuple 1000 45,20 6000 45,20 7000 45,20 8000 45,20
As the following example shows, even though the value of c1
in the third previous event (the event at 1000
) is the same as the value c1
in the current event (the event at 8000
), the range condition is not satisfied. This is because the difference in the timestamps of these two events is more than 5000000000
nanoseconds. So it is treated as if there is no previous tuple and prev
returns NULL
so the condition fails to match.
<query id="q36"><![CDATA[ select T.Ac1 from S15 MATCH_RECOGNIZE ( PARTITION BY c2 MEASURES A.c1 as Ac1 PATTERN(A) DEFINE A as (A.c1 = prev(A.c1,3,5000000000L) ) ) as T ]]></query>
Timestamp Tuple 1000 45,20 2000 45,30 3000 45,30 4000 45,30 5000 45,30 6000 45,20 7000 45,20 8000 45,20 9000 43,40 10000 52,10 11000 52,30 12000 43,40 13000 52,50 14000 43,40 15000 43,40
Timestamp Tuple Kind Tuple 5000: + 45
rawtohex
converts byte
containing a raw value to hexadecimal digits in the CHAR
character set.
See Also:
"hextoraw"Consider the query byte_to_hex
and the data stream S5
. Stream S5
has schema (c1 integer, c2 byte(10))
. This query uses the rawtohex
function to convert a ten byte raw value to the equivalent ten hexadecimal digits in the character set of your current locale. The query returns the relation.
<query id="byte_to_hex"><![CDATA[ select rawtohex(c2) from S5[range 4] ]]></query>
Timestamp Tuple 1000 1,"51c1" 2000 2,"52" 2500 7,"axc" 3000 3,"53aa" 4000 4,"5" 5000 ,"55ef" 6000 6, h 8000 h 200000000
Timestamp Tuple Kind Tuple 1000: + 51c1 2000: + 52 3000: + 53aa 4000: + 05 5000: - 51c1 5000: + 55ef 6000: - 52 6000: + 7000: - 53aa 8000: - 05 9000: - 55ef 10000: -
systimestamp
returns the system date, including fractional seconds and time zone, of the system on which the Oracle Event Processing server resides. The return type is TIMESTAMP
WITH
TIME
ZONE
.
Consider the query q106
and the data stream S0
. Stream S0
has schema (c1 float, c2 integer)
. The query returns the relation.
<query id="q106"><![CDATA[ select * from S0 where case c2 when 10 then null when 20 then null else systimestamp() end > "07/06/2007 14:13:33" ]]></query>
Timestamp Tuple 1000 0.1 ,10 1002 0.14,15 200000 0.2 ,20 400000 0.3 ,30 500000 0.3 ,35 600000 ,35 h 800000 100000000 4.04,40 h 200000000
Timestamp Tuple Kind Tuple 1002: + 0.14,15 400000: + 0.3 ,30 500000: + 0.3 ,35 600000: + ,35 100000000: + 4.04,40
The input/output types for this function are as follows:
Input Type | Output Type |
---|---|
INTEGER | BIGINT |
TIMESTAMP | BIGINT |
CHAR | BIGINT |
Consider the query q282
and the data stream S11
. Stream S11
has schema (c1 integer, name char(10))
. The query returns the relation.
<query id="q282"><![CDATA[ select nvl(to_bigint(c1), 5.2) from S11 ]]></query>
Timestamp Tuple 10 1,abc 2000 ,ab 3400 3,abc 4700 ,a h 8000 h 200000000
Timestamp Tuple Kind Tuple 10: + 1 2000: + 5.2 3400: + 3 4700: + 5.2
to_boolean
returns a value of true
or false
for its bigint
or integer
expression argument.
For more information, see:
Consider the query q282
and the data stream S11
. Stream S11
has schema (c1 integer, name char(10))
. The query returns the relation.
<view id="v2" schema="c1 c2" ><![CDATA[ select to_boolean(c1), c1 from tkboolean_S3 [now] where c2 = 0.1 ]]></view><query id="q1"><![CDATA[ select * from v2 ]]></query>
Timestamp Tuple 1000 -2147483648, 0.1 2000 2147483647, 0.2 3000 12345678901, 0.3 4000 -12345678901, 0.1 5000 9223372036854775799, 0.2 6000 -9223372036854775799, 0.3 7000 , 0.1 8000 10000000000, 0.2 9000 60000000000, 0.3 h 200000000
Timestamp Tuple Kind Tuple 1000 + true,-2147483648 1000 - true,-2147483648 4000 + true,-12345678901 4000 - true,-12345678901 7000 + , 7000 - ,
to_char
returns a char
value for its integer
, double
, bigint
, float
, timestamp
, or interval
expression argument. If the bigint
argument exceeds the char
precision, Oracle Event Processing returns an error.
For more information, see:
Consider the query q282
and the data stream S11
. Stream S11
has schema (c1 integer, name char(10))
. The query returns the relation.
<query id="q1"><![CDATA[ select to_char(c1), to_char(c2), to_char(c3), to_char(c4), to_char(c5), to_char(c6) from S1 ]]></query>
Timestamp Tuple 1000 99,99999, 99.9, 99.9999, "4 1:13:48.10", "08/07/2004 11:13:48", cep
Timestamp Tuple Kind Tuple 1000: + 99,99999,99.9,99.9999,4 1:13:48.10,08/07/2004 11:13:48
to_double
returns a double
value for its bigint
, integer
, or float
expression argument. If the bigint
argument exceeds the double
precision, Oracle Event Processing returns an error.
Consider the query q282
and the data stream S11
. Stream S11
has schema (c1 integer, name char(10))
. The query returns the relation.
<query id="q282"><![CDATA[ select nvl(to_double(c1), 5.2) from S11 ]]></query>
Timestamp Tuple 10 1,abc 2000 ,ab 3400 3,abc 4700 ,a h 8000 h 200000000
Timestamp Tuple Kind Tuple 10: + 1 2000: + 5.2 3400: + 3 4700: + 5.2
to_float
returns a float
number equivalent of its bigint
or integer
argument. If the bigint
argument exceeds the float
precision, Oracle Event Processing returns an error.
Consider the query q1
and the data stream S11
. Stream S1
has schema (c1 integer, name char(10))
. The query returns the relation.
<query id="q1"><![CDATA[ select nvl(to_float(c1), 5.2) from S11 ]]></query>
Timestamp Tuple 10 1, abc 2000 , ab 3400 3, abc 4700 , a h 8000 h 200000000
Timestamp Tuple Kind Tuple 10:+ 1.02000:+ 5.23400:+ 3.04700:+ 5.2
to_timestamp
converts char
literals that conform to java.text.SimpleDateFormat
format models to timestamp
data types. There are two forms of the to_timestamp
function distinguished by the number of arguments:
char
: this form of the to_timestamp
function converts a single char
argument that contains a char
literal that conforms to the default java.text.SimpleDateFormat
format model (MM/dd/yyyy HH:mm:ss
) into the corresponding timestamp
data type.
char1, char2
: this form of the to_timestamp
function converts the char1
argument that contains a char
literal that conforms to the java.text.SimpleDateFormat
format model specified in the second char2
argument into the corresponding timestamp
data type.
long
: this form of the to_timestamp
function converts a single long
argument that represents the number of nanoseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT, into the corresponding timestamp
data type represented as a number in milliseconds since "the epoch" with a date format that conforms to the default java.text.SimpleDateFormat
format model (MM/dd/yyyy HH:mm:ss
).
For more information, see:
Consider the query q277
and the data stream STs2
. Stream STs2
has schema (c1 integer, c2 char(20))
. The query returns the relation.
<query id="q277"><![CDATA[ select * from STs2 where to_timestamp(c2,"yyMMddHHmmss") = to_timestamp("09/07/2005 10:13:48") ]]></query>
Timestamp Tuple 1 1,"040807111348" 2 2,"050907101348" 3 3,"041007111348" 4 4,"060806111248" h 200000000
Timestamp Tuple Kind Tuple 2: + 2,050907101348
xmlcomment
returns its double-quote delimited constant String
argument as an xmltype
.
Using xmlcomment
, you can add a well-formed XML comment to your query results.
This function takes the following arguments:
quoted_string_double_quotes
: a double-quote delimited String
constant.
The return type of this function is xmltype
. The exact schema depends on that of the input stream of XML data.
Consider the query tkdata64_q1
and data stream tkdata64_S
. Stream tkdata64_S
has schema (c1 char(30))
. The query returns the relation.
<query id="tkdata64_q1"><![CDATA[ xmlconcat(xmlelement("parent", c1), xmlcomment("this is a comment")) from tkdata64_S ]]></query>
Timestamp Tuple c 30 1000 "san jose" 1000 "mountain view" 1000 1000 "sunnyvale" 1003 1004 "belmont"
Timestamp Tuple Kind Tuple 1000: + <parent>san jose</parent> <!--this is a comment--> 1000: + <parent>mountain view</parent> <!--this is a comment--> 1000: + <parent/> <!--this is a comment--> 1000: + <parent>sunnyvale</parent> <!--this is a comment--> 1003: + <parent/> <!--this is a comment--> 1004: + <parent>belmont</parent> <!--this is a comment-->
xmlconcat
returns the concatenation of its comma-delimited xmltype
arguments as an xmltype
.
Using xmlconcat
, you can concatenate any combination of xmltype
arguments.
This function takes the following arguments:
non_mt_arg_list
: a comma-delimited list of xmltype
arguments. For more information, see .
The return type of this function is xmltype
. The exact schema depends on that of the input stream of XML data.
This function is especially useful when processing SQLX streams. For more information, see .
To concatenate data types other than xmltype
, use CONCAT
. For more information, see "concat".
Consider the query tkdata64_q1
and the data stream tkdata64_S
. Stream tkdata64_S
has schema (c1 char(30))
. The query returns the relation.
<query id="tkdata64_q1"><![CDATA[ select xmlconcat(xmlelement("parent", c1), xmlcomment("this is a comment")) from tkdata64_S ]]></query>
Timestamp Tuple c 30 1000 "san jose" 1000 "mountain view" 1000 1000 "sunnyvale" 1003 1004 "belmont"
Timestamp Tuple Kind Tuple 1000: + <parent>san jose</parent> <!--this is a comment--> 1000: + <parent>mountain view</parent> <!--this is a comment--> 1000: + <parent/> <!--this is a comment--> 1000: + <parent>sunnyvale</parent> <!--this is a comment--> 1003: + <parent/> <!--this is a comment--> 1004: + <parent>belmont</parent> <!--this is a comment-->
xmlexists
creates a continuous query against a stream of XML data to return a boolean
that indicates whether or not the XML data satisfies the XQuery you specify.
This function takes the following arguments:
const_string
: An XQuery that Oracle Event Processing applies to the XML stream element data that you bind in xqryargs_list
. For more information, see .
xqryargs_list
: A list of one or more bindings between stream elements and XQuery variables or XPath operators. For more information, see .
The return type of this function is boolean
: true
if the XQuery is satisfied; false
otherwise.
This function is especially useful when processing SQLX streams. For more information, see .
See Also:
Consider the query q1
and the XML data stream S
. Stream S
has schema (c1 integer, c2 xmltype)
. In this example, the value of stream element c2
is bound to the current node ("."
) and the value of stream element c1 + 1
is bound to XQuery variable x
. The query returns the relation.
<query id="q1"><![CDATA[ SELECT xmlexists( "for $i in /PDRecord where $i/PDId <= $x return $i/PDName" PASSING BY VALUE c2 as ".", (c1+1) AS "x" RETURNING CONTENT ) XMLData FROM S ]]></query>
Timestamp Tuple 3 1, "<PDRecord><PDName>hello</PDName></PDRecord>" 4 2, "<PDRecord><PDName>hello</PDName><PDName>hello1</PDName></PDRecord>" 5 3, "<PDRecord><PDId>6</PDId><PDName>hello1</PDName></PDRecord>" 6 4, "<PDRecord><PDId>46</PDId><PDName>hello2</PDName></PDRecord>"
Timestamp Tuple Kind Tuple 3: + false 4: + false 5: + true 6: + false
xmlquery
creates a continuous query against a stream of XML data to return the XML data that satisfies the XQuery you specify.
This function takes the following arguments:
const_string
: An XQuery that Oracle Event Processing applies to the XML stream element data that you bind in xqryargs_list
. For more information, see .
xqryargs_list
: A list of one or more bindings between stream elements and XQuery variables or XPath operators. For more information, see .
The return type of this function is xmltype
. The exact schema depends on that of the input stream of XML data.
This function is especially useful when processing SQLX streams. For more information, see .
See Also:
Consider the query and the XML data stream S
. Stream S
has schema (c1 integer, c2 xmltype)
. In this example, the value of stream element c2 is bound to the current node ("."
) and the value of stream element c1 + 1
is bound to XQuery variable x
. The query returns the relation.
<query id="q1"><![CDATA[ SELECT xmlquery( "for $i in /PDRecord where $i/PDId <= $x return $i/PDName" PASSING BY VALUE c2 as ".", (c1+1) AS "x" RETURNING CONTENT ) XMLData FROM S ]]></query>
Timestamp Tuple 3 1, "<PDRecord><PDName>hello</PDName></PDRecord>" 4 2, "<PDRecord><PDName>hello</PDName><PDName>hello1</PDName></PDRecord>" 5 3, "<PDRecord><PDId>6</PDId><PDName>hello1</PDName></PDRecord>" 6 4, "<PDRecord><PDId>46</PDId><PDName>hello2</PDName></PDRecord>"
Timestamp Tuple Kind Tuple 3: + 4: + 5: + "<PDName>hello1</PDName>" 6: + "<PDName>hello2</PDName>"