2 Basic Elements of Oracle CQL
This chapter provides a reference for fundamental parts of Oracle Continuous Query Language (Oracle CQL), including data types, literals, nulls, and more. Oracle CQL is the query language used in Oracle Stream Explorer applications.
This chapter includes the following sections:
Before using the statements described in Using Oracle CQL, you should familiarize yourself with the concepts covered in this chapter.
2.1 Data Types
Each value manipulated by Oracle Stream Explorer has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle Event Processing to treat values of one data type differently from values of another. For example, you can add values of INTEGER data type, but not values of CHAR data type.When you create a stream, you must specify a data type for each of its elements. When you create a user-defined function, you must specify a data type for each of its arguments. These data types define the domain of values that each element can contain or each argument can have. For example, attributes with TIMESTAMP as data type 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 data types that you can use. The syntax of Oracle CQL data types appears in the diagrams that follow.
If Oracle CQL does not support a data type that your events use, you can use an Oracle CQL data cartridge or a user-defined function to evaluate that data type in an Oracle CQL query.
For more information, see:
datatype::=
variable_length_datatype::=
fixed_length_datatype::=
2.1.1 Oracle CQL Built-in Data Types
Table 2-1 summarizes Oracle CQL built-in data types. Refer to the syntax in the preceding sections for the syntactic elements.
Consider these data type and data type literal restrictions when defining event types. For more information, see Developing Applications for Event Processing with Oracle Stream Explorer.
|
Table 2-1 Oracle CQL Built-in Data Type Summary
|
2.1.2 Handling Other Data Types Using Oracle CQL Data Cartridges
If your event uses a data type that Oracle CQL does not support, you can use an Oracle CQL data cartridge to evaluate that data type in an Oracle CQL query. See Developing Applications with Oracle CQL Data Cartridges.
2.1.3 Handling Other Data Types Using a User-Defined Function
If your event uses a data type that Oracle CQL does not support, you can create a user-defined function to evaluate that data type in an Oracle CQL query.
package com.oracle.app;
public enum ProcessStatus {
OPEN(1),
CLOSED(0)}
}
package com.oracle.app;
import com.oracle.capp.ProcessStatus;
public class ServiceOrder {
private String serviceOrderId;
private String electronicSerialNumber;
private ProcessStatus status;
...
}
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;
}
}
<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>
<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 User-Defined Functions.
2.2 Data Type Comparison Rules
This section describes how Oracle Stream Explorer compares values of each data type.
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 Data Type Conversion
Generally an expression cannot contain values of different data types. For example, an arithmetic expression cannot multiply 5 by 10 and then add 'JAMES'. However, Oracle Stream Explorer supports both implicit and explicit conversion of values from one data type 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 data type conversion functions.
Implicit data type 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 Data Type Conversion
Oracle Stream Explorer automatically converts a value from one data type 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
|
The following rules govern the direction in which Oracle Stream Explorer makes implicit data type conversions:
During
SELECTFROMoperations, Oracle Stream Explorer 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 asc1 < 2.0, wherec1is of typeINTEGER.Conversions from
FLOATtoBIGINTare exact.Conversions from
BIGINTtoFLOATare inexact if theBIGINTvalue uses more bits of precision that supported by theFLOAT.When comparing a character value with a
TIMESTAMPvalue, Oracle Stream Explorer converts the character data toTIMESTAMP.When you use a Oracle CQL function or operator with an argument of a data type other than the one it accepts, Oracle Stream Explorer converts the argument to the accepted data type wherever supported.
When making assignments, Oracle Stream Explorer converts the value on the right side of the equal sign (
=) to the data type of the target of the assignment on the left side.During concatenation operations, Oracle Stream Explorer converts from noncharacter data types to
CHAR.During arithmetic operations on and comparisons between character and noncharacter data types, Oracle Stream Explorer converts from numeric types to
CHARas Table 2-2 shows.
2.2.4.2 Explicit Data Type Conversion
You can explicitly specify data type conversions using Oracle CQL conversion functions. Table 2-3 shows Oracle CQL functions that explicitly convert a value from one data type to another. Unsupported conversions are marked with a --.
|
Table 2-3 Explicit Type Conversion Matrix
|
2.2.4.3 SQL Data Type 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 Stream Explorer data type equivalents that Oracle Stream Explorer supports.
For more information, see:
2.2.4.4 Oracle Data Cartridge Data Type Conversion
At run time, Oracle Stream Explorer maps between Oracle CQL and data cartridge data types according to the data cartridge's implementation. For more information, see Developing Applications with Oracle CQL Data Cartridges.
2.2.4.5 User-Defined Function Data Type Conversion
At run time, Oracle Stream Explorer maps between the Oracle CQL data type you specify for a user-defined function's return type and its Java data type equivalent.
For more information, see User-Defined Function Data Types.
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 Stream Explorer 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 Stream Explorer 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 Stream Explorer 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::=
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::=
where
+ or - indicates a positive or negative value. If you omit the sign, then a positive value is the default.
digitis one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.forFindicates that the number is a 32-bit binary floating point number of typeFLOAT.dorDindicates that the number is a 64-bit binary floating point number of typeDOUBLE. pcbpel/cep/src/oracle/cep/common/Constants.BIGINT_LENGTHIf you omit
forFanddorD, then the number is of typeINTEGER.The suffixes
forFanddorDare supported only in floating-point number literals, not in character strings that are to be converted toINTEGER. For example, if Oracle Stream Explorer is expecting anINTEGERand it encounters the string'9', then it converts the string to the JavaInteger9. However, if Oracle Stream Explorer 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 Stream Explorer truncates the value. If the range of the literal exceeds the range supported by BIGINT or FLOAT, then Oracle Stream Explorer 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 Stream Explorer 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 Stream Explorer supports datetime data type 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 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, Oracle CQL cannot parse the MyTimestamp element.
<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.
<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, Oracle CQL can process events 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 Datetime Format Models.
<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 Stream Explorer 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.
pcbpel/cep/src/oracle/cep/common/Constants.BIGINT_LENGTH
2.3.4.1 INTERVAL DAY TO SECOND
Stores time in terms of days, hours, minutes, and seconds.
Specify DAY TO SECOND interval literals using the following syntax:
interval_value::=
where const_string is a TIMESTAMP value that conforms to the appropriate datetime format model (see Datetime Format Models).
Restriction on the Leading Field:
If you specify a trailing field, then it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. As a result of this restriction, if SECOND is the leading field, the interval literal cannot have any trailing field.
The valid range of values for the trailing field are as follows:
SECOND: 0 to 59.999999999
Examples of the various forms of INTERVAL DAY TO SECOND literals follow:
| Form of Interval Literal | Interpretation |
|---|---|
|
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. In this example, stream tkdata2_SIn1 has schema (c1 integer, c2 interval).
<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>
Using INTERVAL DAY TO SECOND in the define clause of pattern match:
query 'select its.itemId from ch0 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 "00:00:05.00" HOUR TO SECOND)), C AS (to_timestamp(C.element_time) - to_timestamp(A.element_time) >= INTERVAL "00:05.00" MINUTE TO SECOND) ) as its'
Input:
send [itemId=2 temp=30] send [itemId=2 temp=55] thread:sleep 5000 send [itemId=2 temp=125]
Output:
-> insert event: {itemId=2}
2.3.4.2 INTERVAL YEAR TO MONTH
Stores time in terms of years and months.
Examples of the various forms of INTERVALYEARTOMONTH literals follow:
| Form of Interval Literal | Interpretation |
|---|---|
|
12 years and 10 months. |
Note:
If used in the query DDL, the INTERVAL YEAR TO MONTH notation is used to specify constant in the CQL query. Specify a constant interval value instead of a variable.
The code sample given below uses the tuples listed below:
<event-type type-name="IntervalInputTupleEvent"> <properties> <property name="interval_inputevent" type="interval year to month"/> </properties> </event-type> <event-type type-name="IntervalOutputTupleEvent"> <properties> <property name="interval_outputevent" type="interval year to month"/> </properties> </event-type>
The following is a sample query for INTERVAL YEARS TO MONTH:
Query:
select interval_inputevent as interval_outputevent from inputChannel
Input:
send [interval_inputevent='INTERVAL "212-10" YEAR(3) TO MONTH']
Output:
-> insert event: {interval_outputevent=+212-10}
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 Stream Explorer 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
function to translate a value ofintdata type tobigintdata type.In the
to_floatfunction to translate a value ofintorbigintdata type tofloatdata type
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 Datetime Literals.
Table 2-4 Datetime Format Models
| Format Model | Example |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
You can use a datetime format model in the following functions:
to_timestamp: to translate the value of achardata type to aTIMESTAMPdata type.
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 data type 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 Stream Explorer treats a character value with a length of zero as it is, not like SQL. However, do not use null to represent a numeric value of zero, because they are not equivalent.
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 Stream Explorer 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
|
For more information, see Null Conditions.
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 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 Sorting Query Results), but not other clauses in the query.
The following example shows how to define alias badItem for a stream element its.itemId in a SELECT list and alias its for a MATCH_RECOGNIZE clause.
<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 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.
The following example shows how to define aliases bid and ask after partitioned range window operators.
<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 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.
<?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 Stream Explorer 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 Data Types.
To define a type alias using the aliases element:
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 or user-defined windows. This section provides:
For more information, see Lexical Conventions.
2.8.1 Schema Object Naming Rules
Every Oracle Stream Explorer 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 Stream Explorer object.
The following list of rules applies to identifiers:
Identifiers cannot be Oracle Stream Explorer reserved words.
Depending on the Oracle product you plan to use to access an Oracle Stream Explorer 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 data types (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 Stream Explorer 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 Schema Object Naming Rules.