Using Aliases and Auto Replacement Substitutions

TOAD supports the use of its own alias names . Aliases are convenient to shorten keystrokes, using short names instead of rather long table names. TOAD also allows the automatic replacement of text, or in this case, these aliases. The purpose of the alias is to shorten keystrokes. The purpose of the automatic replacement is to automatically resolve the TOAD alias to the full table or column name .

TOAD aliases are used to shorten the number of keystrokes to develop SQL. TOAD aliases are not to be confused with Oracle RDBMS table aliases, which are used in qualifying columns in a multitable SQL statement.

TOAD aliases enable you to

• Access the Column Name Select drop-down list

• Type the alias as a shortcut a shortcut rather than typing the full table name

Aliases are easy to set up with any text editor. Make sure TOAD is not running (on network installations, make sure all users are off TOAD) and edit the ALIASES.TXT file found in the TOAD installation directory under the TEMPS subdirectory.

DO NOT edit this file with TOAD running. When TOAD exits, it rewrites this file and any changes you make will be lost!

Figure 4.15 illustrates the format of this file. The format is < table name >= < alias name > .

Figure 4.15. TOAD alias setup.

In Chapter 3, the section "Scripts That Write Scripts" discusses a method of quickly creating this file.

TOAD aliases are easy to use. Figure 4.16 shows a simple SQL statement using an alias to get to the Column Name Select drop-down. Notice that the user typed in 'inv.'. The '.' signaled the Column Name Select drop-down and the alias was resolved to the INVENTORY table.

Figure 4.16. TOAD alias usage.

The '.' signals TOAD to see if this is an alias.

Notice in Figure 4.17 that there is both an INVENTORY table and an INV table. The alias INV was resolved in Figure 4.16 to the INVENTORY table, not the INV table. Notice the column names in Figure 4.17 compared to those in Figure 4.16. Granted, this is a poor naming convention, but bear with the example. To get the INV table displayed, use Shift+Ctrl+T (or Edit, Columns drop-down no alias) to ignore the alias and get the correct list of columns; see Figure 4.18.

Figure 4.17. Available tables to TOAD user.

Figure 4.18. Ignore the alias in action.

Shift+Ctrl+T ignores the alias request.

If an alias is identified in the SQL statement, and a Column Select is activated, the alias is automatically added to ALIASES.TXT.

TOAD scans only the first FROM clause in any SQL statement, so any TOAD aliases in complex SQL statements that have subqueries, for example, will not be found and resolved.

Autoreplace substitution replaces a short string with the full name. This differs from TOAD aliases because aliases make reference to a different name but do not change the text of the SQL statement. When autoreplace substitution is defined, it happens automatically when you press the spacebar. Autoreplace substitution is activated by typing the short sequence (notice the "INV_" string illustrated in Figure 4.19) and pressing the spacebar (the autoreplace delimiter key defaults to the spacebar). This will then automatically substitute the predefined string in place of the short key sequence; see Figure 4.20.

Figure 4.19. Autoreplace substitution key sequence.

Figure 4.20. Autoreplace substitution in action.

Replacement happens after pressing the spacebar.

There are two ways to create automatic replacement substitution strings. You can edit the PLSQLSUB.TXT file in the < TOAD home directory >\temps directory. The format is the same as the alias: < short string > = < replacement string > . The other way is to enter the substitution string by using the Edit, Editor Options, Auto Replace tab. See Figure 4.22.

Figure 4.22. Adding autoreplace substitution using the Editor Options screen.

Figure 4.21 illustrates the contents of this file. Notice the common typo 'teh' will automatically be converted to 'the' because this sequence will always be followed by pressing the spacebar. Also notice the 'inv' and 'Inv_' strings that were used in Figure 4.19 and Figure 4.20.

Figure 4.21. Editing the PLSQLSUB.TXT file.

TOAD allows autosubstitution to be maintained by language type. You can edit and add to the list by using the Editor Options, Auto Replace window. Supported languages for autoreplacement are HTML ( < TOAD home >\temps\HTMLSUB.TXT ), INI ( < TOAD home >\temps\INISUB.TXT ), JAVA ( < TOAD home >\temps\JAVASUB.TXT ), and TEXT ( < TOAD home >\temps\TEXTSUB.TXT ).

Be sure TOAD is NOT running when you are editing any of these files in the TEMPS directory. TOAD rewrites these files when closing, and any changes made to the files with TOAD running will be lost.

TOAD enables you to export and import these autosubstitution definitions. See Figure 4.22. This is a convenient way to move them when setting up a new computer to use TOAD. It also might be convenient to have various autosubstitution files by application. This allows you to have specific substitutions for various applications, same alias but resolved to different names perhaps.

When loading autoreplace definitions in from a saved file, all previous substitutions currently in TOAD are lost and replaced with the new definitions.

These .ACE files are in a binary format and are only to be used with the TOAD Load facility (from the Editing Options menu).