In an attempt to provide English-like naming conventions, Access object names may contain spaces and many special characters. In addition, Access names have a 64 character maximum length and may begin with any valid character. With Oracle, object names must begin with a letter, contain only the characters A-Z, 0-9, _, $, #, cannot duplicate an Oracle reserved word, and must be 30 characters or less in length. When designing new Access databases to be used with Oracle, it is desirable to adopt Oracle naming rules.
Although Oracle names may be specified in upper and lower case, it is generally desirable to adopt an upper case naming convention. In fact, SQL*Plus automatically converts lower case names to upper case before compilation. This avoids the need to surround mixed-case names with quotes when issuing SQL statements.
When migrating Access tables to Oracle, Scriptoria applies the following rules when renaming Access table and field names to conform to ORACLE naming conventions.
Special characters, incompatible with Oracle, are replaced by an underscore. When names contain consecutive underscores, a singe one replaces them.
Names beginning with a number (0-9) are prefixed with ‘N’.
Names beginning with an underscore are prefixed with ‘U’.
Names are truncated to 30 characters.
Trailing underscores are removed.
For example, the Access field name “1: - For the Money” is renamed to “N1_FOR_THE_MONEY”. In this case, the three characters “:- “ are converted to a single underscore.
Although Scriptoria ensures that all converted table names are unique, it does not check for duplicate column names that may result from truncating Access field names to 30 characters.
Table names, renamed by Scriptoria, may be renamed by modifying the User System Table “USys_TableOptionSettings_Ora.” To accomplish this, set Show System Objects (found in the Access Options menu) to Yes. This will display all Access system tables. Modify the xTblName field in USys_TableOptionSettings_Ora to the desired table names.