8 Built-In Single-Row Functions
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.
For more information, see Section 1.1.11, "Functions".
This chapter includes the following section:
8.1 Introduction to Oracle CQL Built-In Single-Row Functions
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 with h (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
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 datatype depends on the datatypes of the arguments.
Using concat, you can concatenate any combination of character, byte, and numeric datatypes. The concat performs automatic numeric to string conversion.
This function is equivalent to the concatenation operator (||). For more information, see "Concatenation Operator".
To concatenate xmltype arguments, use xmlconcat. For more information, see "xmlconcat".
Consider the query chr_concat in Example 8-1 and data stream S4 in Example 8-2. Stream S4 has schema (c1 char(10)). The query returns the relation in Example 8-3.
Example 8-1 concat Function Query
<query id="chr_concat"><![CDATA[
select
concat(c1,c1),
concat("abc",c1),
concat(c1,"abc")
from
S4[range 5]
]]></query>
Example 8-2 concat Function Stream Input
Timestamp Tuple 1000 2000 hi 8000 hi1 9000 15000 xyz h 200000000
Example 8-3 concat Function 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
Consider the query q264 in Example 8-4 and the data stream S10 in Example 8-5. Stream S10 has schema (c1 integer, c2 char(10)). The query returns the relation in Example 8-6.
Example 8-4 Concatenation Operator (||) Query
<query id="q264"><![CDATA[
select
c2 || "xyz"
from
S10
]]></query>
hextoraw
hextoraw converts char containing hexadecimal digits in the char character set to a raw value.
See Also:
Consider the query q6 in Example 8-7 and the data stream SinpByte1 in Example 8-8. Stream SinpByte1 has schema (c1 byte(10), c2 integer). The query returns the relation in Example 8-9.
Example 8-7 hextoraw Function Query
<query id="q6"><![CDATA[
select * from SByt[range 2]
where
bytTest(c2) between hextoraw("5200") and hextoraw("5600")
]]></query>
length
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 in Example 8-10 and the data stream S2 in Example 8-11. Stream S2 has schema (c1 integer, c2 integer). The query returns the relation that Example 8-12.
Example 8-10 length Function Query
<query id="chr_len"><![CDATA[
select length(c1) from S4[range 5]
]]></query>
lk
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 Section 6.4, "LIKE Condition".
Consider the query q291 in Example 8-13 and the data stream SLk1 in Example 8-14. Stream SLk1 has schema (first1 char(20), last1 char(20)). The query returns the relation in Example 8-15.
Example 8-13 lk Function Query
<query id="q291"><![CDATA[
select * from SLk1
where
lk(first1,"^Ste(v|ph)en$") = true
]]></query>
nvl
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 datatype. If their datatypes 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
expr1is character data, then Oracle Event Processing convertsexpr2to character data before comparing them and returnsVARCHAR2in the character set ofexpr1. -
If
expr1is numeric, then Oracle Event Processing determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.
Consider the query q281 in Example 8-16 and the data stream SNVL in Example 8-17. Stream SNVL has schema (c1 char(20), c2 integer). The query returns the relation in Example 8-18.
prev
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:identifier1is the name of a correlation variable used in thePATTERNclause and defined in theDEFINEclause andidentifier2is the name of a stream attribute whose value in the previous event should be returned byprev. -
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 argumentidentifier2) in the nth previous event will be returned if such an event exists,NULLotherwise. -
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.
Consider query q2 in Example 8-19 and the data stream S1 in Example 8-20. 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 in Example 8-21.
Example 8-19 prev(identifier1.identifier2) Function Query
<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>
Example 8-20 prev(identifier1.identifier2) Function Stream Input
Timestamp Tuple 1000 35 3000 35 4000 10 5000 7
Example 8-21 prev(identifier1.identifier2) Function Stream Output
Timestamp Tuple Kind Tuple 5000: + 35,7
prev(identifier1.identifier2, const_int)
Consider query q35 in Example 8-22 and the data stream S15 in Example 8-23. 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 in Example 8-24.
Example 8-22 prev(identifier1.identifier2, const_int) Function Query
<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>
Example 8-23 prev(identifier1.identifier2, const_int) Function Stream Input
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
Example 8-24 prev(identifier1.identifier2, const_int) Function Stream Output
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 in Example 8-26 and the data stream S15 in Example 8-27. 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
c1in the current event equals the value ofc1in 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
5000000000Lnanoseconds.
The query returns the output stream that Example 8-28 shows. Notice that in the output stream, there is no output at 8000. Example 8-25 shows the contents of the partition (partitioned by the value of the c2 attribute) to which the event at 8000 belongs.
Example 8-25 Partition Containing the Event at 8000
Timestamp Tuple 1000 45,20 6000 45,20 7000 45,20 8000 45,20
As Example 8-25 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.
Example 8-26 prev(identifier1.identifier2, const_int, const_bigint) Function Query
<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>
rawtohex
rawtohex converts byte containing a raw value to hexadecimal digits in the CHAR character set.
See Also:
Consider the query byte_to_hex in Example 8-29 and the data stream S5 in Example 8-30. 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 hexidecimal digits in the character set of your current locale. The query returns the relation in Example 8-31.
Example 8-29 rawtohex Function Query
<query id="byte_to_hex"><![CDATA[
select rawtohex(c2) from S5[range 4]
]]></query>
systimestamp
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 in Example 8-32 and the data stream S0 in Example 8-33. Stream S0 has schema (c1 float, c2 integer). The query returns the relation in Example 8-34. For more information about case, see "case_expr".
Example 8-32 systimestamp Function Query
<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>
to_bigint
to_bigint returns a bigint number equivalent of its integer argument.
For more information, see:
Consider the query q282 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.
Example 8-35 to_bigint Function Query
<query id="q282"><![CDATA[
select nvl(to_bigint(c1), 5.2) from S11
]]></query>
to_boolean
to_boolean returns a value of true or false for its bigint or integer expression argument.
For more information, see:
Consider the query q282 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.
Example 8-38 to_boolean Function Query
<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>
to_char
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 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.
Example 8-41 to_char Function Query
<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>
to_double
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.
For more information, see:
Consider the query q282 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.
Example 8-44 to_double Function Query
<query id="q282"><![CDATA[
select nvl(to_double(c1), 5.2) from S11
]]></query>
to_float
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.
For more information, see:
Consider the query q1 in Example 8-47 and the data stream S11 in Example 8-48. Stream S1 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-49.
Example 8-47 to_float Function Query
<query id="q1"><![CDATA[
select nvl(to_float(c1), 5.2) from S11
]]></query>
to_timestamp
to_timestamp converts char literals that conform to java.text.SimpleDateFormat format models to timestamp datatypes. There are two forms of the to_timestamp function distinguished by the number of arguments:
-
char: this form of theto_timestampfunction converts a singlecharargument that contains acharliteral that conforms to the defaultjava.text.SimpleDateFormatformat model (MM/dd/yyyy HH:mm:ss) into the correspondingtimestampdatatype. -
char1, char2: this form of theto_timestampfunction converts thechar1argument that contains acharliteral that conforms to thejava.text.SimpleDateFormatformat model specified in the secondchar2argument into the correspondingtimestampdatatype. -
long: this form of theto_timestampfunction converts a singlelongargument 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 correspondingtimestampdatatype represented as a number in milliseconds since "the epoch" with a date format that conforms to the defaultjava.text.SimpleDateFormatformat model (MM/dd/yyyy HH:mm:ss).
For more information, see:
Consider the query q277 in Example 8-50 and the data stream STs2 in Example 8-51. Stream STs2 has schema (c1 integer, c2 char(20)). The query returns the relation that Example 8-52.
Example 8-50 to_timestamp Function Query
<query id="q277"><![CDATA[
select * from STs2
where
to_timestamp(c2,"yyMMddHHmmss") = to_timestamp("09/07/2005 10:13:48")
]]></query>
xmlcomment
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 delimitedStringconstant.
The return type of this function is xmltype. The exact schema depends on that of the input stream of XML data.
See Also:
Consider the query tkdata64_q1 in Example 8-53 and data stream tkdata64_S in Example 8-54. Stream tkdata64_S has schema (c1 char(30)). The query returns the relation in Example 8-55.
Example 8-53 xmlcomment Function Query
<query id="tkdata64_q1"><![CDATA[
xmlconcat(xmlelement("parent", c1), xmlcomment("this is a comment"))
from tkdata64_S
]]></query>
Example 8-54 xmlcomment Function Stream Input
Timestamp Tuple c 30 1000 "san jose" 1000 "mountain view" 1000 1000 "sunnyvale" 1003 1004 "belmont"
Example 8-55 xmlcomment Function Relation Output
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
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 ofxmltypearguments. For more information, see non_mt_arg_list::=.
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 "SQL/XML (SQLX)".
To concatenate datatypes other than xmltype, use CONCAT. For more information, see "concat".
See Also:
Consider the query tkdata64_q1 in Example 8-53 and the data stream tkdata64_S in Example 8-54. Stream tkdata64_S has schema (c1 char(30)). The query returns the relation in Example 8-55.
Example 8-56 xmlconcat Function Query
<query id="tkdata64_q1"><![CDATA[
select
xmlconcat(xmlelement("parent", c1), xmlcomment("this is a comment"))
from tkdata64_S
]]></query>
Example 8-57 xmlconcat Function Stream Input
Timestamp Tuple c 30 1000 "san jose" 1000 "mountain view" 1000 1000 "sunnyvale" 1003 1004 "belmont"
Example 8-58 xmlconcat Function Relation Output
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
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 inxqryargs_list. For more information, see const_string::=. -
xqryargs_list: A list of one or more bindings between stream elements and XQuery variables or XPath operators. For more information, see xqryargs_list::=.
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 "SQL/XML (SQLX)".
Consider the query q1 in Example 8-59 and the XML data stream S in Example 8-60. 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 in Example 8-61.
Example 8-59 xmlexists Function Query
<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>
Example 8-60 xmlexists Function Stream Input
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>"
xmlquery
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 inxqryargs_list. For more information, see const_string::=. -
xqryargs_list: A list of one or more bindings between stream elements and XQuery variables or XPath operators. For more information, see xqryargs_list::=.
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 "SQL/XML (SQLX)".
Consider the query q1 in Example 8-62 and the XML data stream S in Example 8-63. 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 in Example 8-64.
Example 8-62 xmlquery Function Query
<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>
Example 8-63 xmlquery Function Stream Input
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>"