This chapter presents general rules for names and parameters used in TimesTen SQL statements. It includes the following topics:
Basic names, or simple names, identify columns, tables, views and indexes. Basic names must follow these rules:
The maximum length of a basic name is 30 characters.
A name can consist of any combination of letters (A to Z a to z), decimal digits (0 to 9), $, #, @, or underscore (_). For identifiers, the first character must be a letter (A-Z a-z) and not a digit or special character. However, for parameter names, the first character can be a letter (A-Z a-z), a decimal digit (0 to 9), or special characters $, #, @, or underscore (_).
TimesTen changes lowercase letters (a to z) to the corresponding uppercase letters (A to Z). Thus names are not case-sensitive.
If you enclose a name in quotation marks, you can use any combination of characters even if they are not in the set of legal characters. When the name is enclosed in quotes, the first character in the name can be any character, including one or more spaces.
If a column, table, or index is initially defined with a name enclosed in quotation marks and the name does not conform to the rule noted in the second bullet, then that name must always be enclosed in quotation marks whenever it is subsequently referenced.
Unicode characters are not allowed in names.
The owner name is the user name of the account that created the table. Tables and indexes defined by TimesTen itself have the owner SYS or TTREP. User objects cannot be created with owner names SYS or TTREP. TimesTen converts all owner and table names to upper case.
Owners of tables in TimesTen are determined by the user ID settings or login names. For cache groups, Oracle database table owner names must always match TimesTen table owner names.
Owner names may be specified by the user during table creation, in addition to being automatically determined if they are left unspecified. See "CREATE TABLE". When creating owner names, follow the same rules as those for creating basic names. See "Basic names".
Basic names and user names are simple names. In some cases, simple names are combined and form a compound identifier, which consists of an owner name combined with one or more basic names, with periods (.) between them.
In most cases, you can abbreviate a compound identifier by omitting one of its parts. If you do not use a fully qualified name, a default value is automatically for the missing part. For example, if you omit the owner name (and the period) when you refer to tables you own, TimesTen generates the owner name by using your login name.
A complete compound identifier, including all of its parts, is called a fully qualified name. Different owners can have tables and indexes with the same name. The fully qualified name of these objects must be unique.
The following are compound identifiers:
Column identifier: [[Owner.]TableName.]ColumnName
[Owner.]IndexName
Table identifier: [Owner.]TableName
Row identifier: [[Owner.]TableName.]rowid
In SQL syntax, object names that share the same namespace must each be unique. This is so that when a name is referenced in any SQL syntax, the exact object can be found.
If the object name provided is not qualified with the name (namespace) of the user that owns it, then the search order for the object is as follows:
Search for any match from all object names within the current user namespace. If there is a match, the object name is resolved.
If no match is found in the user namespace, search for any match from the PUBLIC namespace, which contains objects such as public synonyms. Public synonyms are pre-defined for SYS and TTREP objects. If there is a match, the object name is resolved. Otherwise, the object does not exist.
Any tables, views, materialized views, sequences, private synonyms, PL/SQL packages, functions, procedures, and cache groups owned by the same user share one namespace and so the names for each of these objects must be unique within that namespace. Indexes are created in their own namespace.
For example, because tables and views are in the same namespace, a table and a view owned by the same user cannot have the same name. However, tables and indexes are in different namespaces, so a table and an index owned by the same user can have the same name.
Tables that are owned by separate users can have the same name, since they exist in separate user namespaces.
Dynamic parameters pass information between an application program and TimesTen. TimesTen uses dynamic parameters as placeholders in SQL commands and at runtime replaces the parameters with actual values.
A dynamic parameter name must be preceded by a colon (:) when used in a SQL command and must conform to the TimesTen rules for basic names. However, unlike identifiers, parameter names can start with any of the following characters:
Uppercase letters: A to Z
Lowercase letters: a to z
Digits: 0 to 9
Special characters: # $ @ _
Note:
Instead of using a:DynamicParameter sequence, the application can use a ? for each dynamic parameter.Enhanced ":" style parameter markers have this form:
:parameter [INDICATOR] :indicator
The :indicator is considered to be a component of the :parameter. It is not counted as a distinct parameter. Do not specify '?' for this style of parameter marker.
Consider this SQL statement:
SELECT * FROM t1 WHERE c1=:a AND c2=:a AND c3=:b AND c4=:a;
Traditionally in TimesTen, multiple instances of the same parameter name in a SQL statement are considered to be multiple occurrences of the same parameter. When assigning parameter numbers to parameters, TimesTen assigns parameter numbers only to the first occurrence of each parameter name. The second and subsequent occurrences of a given name do not get their own parameter numbers. In this case, a TimesTen application binds a value for every unique parameter in a SQL statement. It cannot bind different values for different occurrences of the same parameter name nor can it leave any parameters or parameter occurrences unbound.
In Oracle Database, multiple instances of the same parameter name in a SQL statement are considered to be different parameters. When assigning parameter numbers, Oracle Database assigns a number to each parameter occurrence without regard to name duplication. An Oracle database application, at a minimum, binds a value for the first occurrence of each parameter name. For the subsequent occurrences of a given parameter, the application can either leave the parameter occurrence unbound or it can bind a different value for the occurrence.
The following table shows a query with the parameter numbers that TimesTen and Oracle Database assign to each parameter.
| Query | TimesTen parameter number | Oracle Database parameter number |
|---|---|---|
SELECT * |
||
FROM t1 |
||
WHERE c1=:a |
1 | 1 |
AND c2=:a |
1 | 2 |
AND c3=:b |
2 | 3 |
AND c4=:a; |
1 | 4 |
The total number of parameter numbers for TimesTen in this example is 2. The total number of parameters for Oracle Database in this example is 4. The parameter bindings provided by an application produce different results for the traditional TimesTen behavior and the Oracle Database behavior.
You can use the DuplicateBindMode general connection attribute to determine whether applications use traditional TimesTen parameter binding for duplicate occurrences of a parameter in a SQL statement or Oracle-style parameter binding. Oracle-style parameter binding is the default.
Consider this statement:
SELECT :a FROM dual;
TimesTen cannot infer the data type of parameter a from the query. TimesTen returns this error:
2778: Cannot infer type of parameter from its use The command failed.
Use the CAST function to declare the data type for parameters:
SELECT CAST (:a AS NUMBER) FROM dual;