Database object identifiers
Every database object has a name. In an SQL statement, you can use a quoted identifier or a nonquoted identifier to name a database object.
A quoted identifier starts and ends with double quotation marks (
"). If you name a schema object by using a quoted identifier, you must use double quotation marks (") when you reference this object.
A nonquoted identifier does not contain punctuation marks.
However, database names, global database names, database link names, disk group names, and pluggable database (PDB) names are not case-sensitive and are stored in uppercase. If you specify such names by using quoted identifiers, the quotation marks are ignored.
We do not recommend that you use quoted identifiers to name database objects in ApsaraDB for OceanBase. These quoted identifiers are compatible with SQL Plus. However, other tools that manage database objects may fail to recognize these quoted identifiers.
Identifier usage rules
The following rules apply to both quoted and nonquoted identifiers unless otherwise specified:
An identifier is 1 to 128 bytes in length.
If an identifier contains multiple parts separated by periods (.), each part can be up to 128 bytes in length. Each period delimiter consumes one byte. Each double quotation mark consumes 1 byte. The following string provides an example:
The string contains three parts: schema, table, and column. Each part can be up to 128 bytes in length. Each of the quotation marks and periods is a single-byte character. Therefore, the total length of the identifier in this example can be up to 392 bytes in length.
Use reserved words as identifiers
Nonquoted identifiers cannot be SQL reserved words in ApsaraDB for OceanBase. Quoted identifiers can be reserved words. However, we recommend that you do not use reserved words as quoted identifiers. Based on the tools that you plan to use to access database objects, the names may be further limited by other product-specific reserved words.
The reserved word
ROWID is an exception to this rule. You cannot use the uppercase word
ROWID as a column name, regardless of whether the word is quoted or nonquoted. You cannot use an all-caps quoted identifier as a column name. However, you can use a quoted identifier that contains one or more lowercase letters as a column name, for example, "Rowid" or "Rowid".
Use words with special meanings as identifiers
In ApsaraDB for OceanBase, the SQL language contains other words that have special meanings. These words include data types, schema names, function names, the virtual system table
DUAL, and keywords. The keywords are the all-caps words in SQL statements, such as
DISABLE. These words are not reserved. However, ApsaraDB for OceanBase uses them inside the system in specific ways. Therefore, if you use these words as the names of objects and object parts, your SQL statements may be difficult to read and may lead to unpredictable results. In particular, do not use the names of SQL built-in functions as the names of schema objects and user-defined functions.
Use ASCII characters as identifiers
Characters in the ASCII character set provide optimal compatibility across different platforms and operating systems. Therefore, we recommend that you use these characters in database names, global database names, and database link names. You can use only the characters from the ASCII character set in the names of common users and common roles in a multitenant container database (CDB).
Characters in passwords
Passwords can contain multibyte characters, such as Chinese characters or Chinese punctuation marks.
Beginning of identifiers
Nonquoted identifiers must start with an alphabetic character from the database character set. Quoted identifiers can start with all characters.
Signs in identifiers
Nonquoted identifiers can contain only alphanumeric characters from the database character set and underscores (_). However, nonquoted identifiers that are used in database link names can contain periods (.) and at signs (@). Quoted identifiers can contain all characters, punctuation marks, and spaces. However, both quoted and nonquoted identifiers cannot contain double quotation marks (") or the null character (\0).
Limits of object names in namespaces
Tables, views, and private synonyms in the same namespace cannot have the same name.
Identifier case sensitivity
Nonquoted identifiers are not case-sensitive. ApsaraDB for OceanBase stores them in uppercase. Quoted identifiers are case-sensitive. By enclosing names in double quotation marks, you can assign the following names to different objects in the same namespace:
"employees" "Employees" "EMPLOYEES"
Nonquoted identifiers are not case-sensitive. Therefore, ApsaraDB for OceanBase considers the following names the same. You cannot use these names for different objects in the same namespace:
employees EMPLOYEES "EMPLOYEES"
When you store or compare all-caps identifiers, the uppercase form of each character in the identifiers is determined by applying the capitalization rules of the database character set. The language rules that are specified in the session setting
NLS_SORT are not considered. The SQL function
UPPER, rather than the function
NLS_UPPER, is applied to the identifiers.
If you apply the capitalization rules of the database character set to natural languages, the capitalization result may be incorrect. For example, based on the capitalization rules of the database character set, the German lowercase letter
β does not have an uppercase form. Therefore, this letter does not change when you convert an identifier that contains this letter to an all-caps word.
The capitalization rules of the database character set ensure that identifiers are displayed in the same form regardless of the session language. To display an identifier correctly in a specific natural language, quote the identifier to keep the lowercase form or convert the letters to the correct uppercase letters in this language.
Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.
Procedure and function names
If the parameters in two procedures or functions in the same package have different quantities and data types, the procedures or functions can have the same name. Creating multiple procedures or functions with the same name using different parameters in the same package is called overloaded procedures or functions.