Oracle7 Server SQL Reference

Object Names and Qualifiers

This section tells provides:

rules for naming objects and object location qualifiers

guidelines for naming objects and qualifiers

Object Naming Rules

The following rules apply when naming objects:

1. Names must be from 1 to 30 characters long with these exceptions:

Names of databases are limited to 8 characters.

Names of database links can be as long as 128 characters.

2. Names cannot contain quotation marks.

4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.

5. Names can only contain alphanumeric characters from your database character set and the characters _, $, and #. You are strongly discourage from using $ and #.

If your database character set contains multi-byte characters, It is recommended that each name for a user or a role contain at least one single-byte character.

Names of database links can also contain periods (.) and ampersands (@).

6. A name cannot be an Oracle7 reserved word. The following list contains these reserved words. Words followed by an asterisk (*) are also ANSI reserved words. Note: You cannot use special characters from European or Asian character sets in a database name, global database name, or database link names. For example, the umlaut is not allowed.

Reserved words

ACCESS ADD ALL* ALTER AND* ANY* AS* ASC* AUDIT BETWEEN* BY* CHAR* CHECK* CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE* CURRENT* DATE DECIMAL DEFAULT* DELETE* DESC* DISTINCT* DROP

ELSE EXCLUSIVE EXISTS* FILE FLOAT* FOR* FROM* GRANT* GROUP* HAVING* IDENTIFIED IMMEDIATE IN* INCREMENT INDEX INITIAL INSERT* INTEGER* INTERSECT INTO* IS* LEVEL LIKE* LOCK LONG

MAXEXTENTS MINUS MODE MODIFY NOAUDIT NOCOMPRESS NOT* NOWAIT NULL* NUMBER OF* OFFLINE ON* ONLINE OPTION* OR* ORDER* PCTFREE PRIOR PRIVILEGES* PUBLIC* RAW RENAME RESOURCE REVOKE ROW ROWID ROWLABEL ROWNUM ROWS

SELECT* SESSION SET* SHARE SIZE SMALLINT* START SUCCESSFUL SYNONYM SYSDATE TABLE* THEN TO* TRIGGER UID UNION* UNIQUE* UPDATE* USER* VALIDATE VALUES* VARCHAR VARCHAR2 VIEW* WHENEVER WHERE* WITH*

PL/SQL User's Guide and Reference

7. The word DUAL should not be used as a name for an object or part. DUAL is the name of a dummy table frequently accessed by Oracle7 tools such as SQL*Plus and SQL*Forms.

8. The Oracle7 SQL language contains other keywords that have special meanings. Because these keywords are not reserved, you can also use them as names for objects and object parts. However, using them as names may make your SQL statements more difficult for you to read.

The following list contains keywords. Keywords marked with asterisks (*) are also ANSI reserved words. For maximum portability to other implementations of SQL, do not use the following words as object names. Keywords

ADMIN AFTER ALLOCATE ANALYZE ARCHIVE ARCHIVELOG AUTHORIZATION* AVG* BACKUP BEGIN* BECOME BEFORE BLOCK BODY CACHE CANCEL CASCADE CHANGE CHARACTER* CHECKPOINT CLOSE* COBOL* COMMIT* COMPILE CONSTRAINT CONSTRAINTS CONTENTS CONTINUE* CONTROLFILE COUNT* CURSOR* CYCLE

DATABASE DATAFILE DBA DEC* DECLARE* DISABLE DISMOUNT DOUBLE* DUMP EACH ENABLE END* ESCAPE* EVENTS EXCEPT EXCEPTIONS EXEC* EXPLAIN EXECUTE EXTENT EXTERNALLY FETCH* FLUSH FREELIST FREELISTS FORCE FOREIGN* FORTRAN* FOUND* FUNCTION GO* GOTO* GROUPS INCLUDING INDICATOR* INITRANS INSTANCE INT*

KEY* LANGUAGE* LAYER LINK LISTS LOGFILE MANAGE MANUAL MAX* MAXDATAFILES MAXINISTANCES MAXLOGFILES MAXLOGHISTORY MAXLOGMEMBERS MAXTRANS MAXVALUE MIN* MINEXTENTS MINVALUE MODULE* MOUNT NEXT NEW NOARCHIVELOG NOCACHE NOCYCLE NOMAXVALUE NOMINVALUE NONE NOORDER NORESETLOGS NORMAL NOSORT NUMERIC*

OFF OLD ONLY OPTIMAL OPEN* OWN PACKAGE PARALLEL PASCAL* PCTINCREASE PCTUSED PLAN PLI* PRECISION* PRIMARY* PRIVATE PROCEDURE* PROFILE QUOTA READ REAL* RECOVER REFERENCES* REFERENCING RESETLOGS RESTRICTED REUSE ROLE ROLES ROLLBACK*

SAVEPOINT SCHEMA* SCN SECTION* SEGMENT SEQUENCE SHARED SNAPSHOT SOME* SORT SQLCODE* SQLERROR* STATEMENT_ID STATISTICS STOP STORAGE SUM* SWITCH SYSTEM TABLES TABLESPACE TEMPORARY THREAD TIME TRACING TRANSACTION TRIGGERS TRUNCATE UNDER UNLIMITED UNTIL USE USING WHEN WRITE WORK*

. Objects in the same namespace must have different names.

Figure 2 - 1

Figure 2 - 1. Namespaces For Schema Objects

Figure 2 - 2

Figure 2 - 2. Namespaces For Other Objects

Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

overloading

the procedure

or function

. Such names can contain any combination of characters including spaces, ignoring rules 3 through 7 in this list. This exception is allowed for portability, but it is recommended that you do not break rules 3 through 7.

Once you have given an object a name enclosed in double quotation marks, you must use double quotation marks whenever you refer to the object.

You may want to enclose a name in double quotation marks for any of these reasons:

if you want it to contain spaces

if you want it to be case-sensitive

if you want it to begin with a character other than an alphabetic character, such as a numeric character

if you want it to contain characters other than alphanumeric characters and _, $, and #

if you want to use a reserved word as a name

By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

emp

	"emp"

	"Emp"

	"EMP "

Note that Oracle7 interprets the following names the same, so they cannot be used for different objects in the same namespace:

emp

EMP

	"EMP"

If you give a user or password a quoted name, the name cannot contain lowercase letters.

Database link names cannot be quoted. Examples

The following are valid examples of names:

ename
horse
scott.hiredate
"EVEN THIS & THAT!"
a_very_long_and_valid_name

Although column aliases, table aliases, usernames, or passwords are not objects or parts of objects, they must also follow these naming rules with these exceptions

Column aliases and table aliases only exist for the execution of a single SQL statement and are not stored in the database, so rule 9 does not apply to them.

Passwords do not have namespaces, so rule 9 does not apply to apply to them.

Do not use quotation marks to make usernames and passwords case-sensitive. For additional rules for naming users and passwords, see the CREATE USER command .

Object Naming Guidelines

There are several helpful 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 tables.

When naming objects, balance the objective of keeping names short and easy to use with the objective of making name as long and descriptive as possible. When in doubt, choose the more descriptive name because the objects in the database may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a database with names like PMDD instead of PAYMENT_DUE_DATE.

Using consistent naming rules helps users understand the part that each table plays in your application. One such rule might be to begin the names of all tables belonging to the FINANCE application with FIN_.

Use the same names to describe the same things across tables. For example, the department number columns of the EMP and DEPT tables are both named DEPTNO.