- Overview
- Predefined Shortcuts
- User-Defined Shortcuts
- Using Variables
- Table and Column Name Select Lists
- Using Aliases and Autoreplacement Substitutions
- SQL Templates
- Code Completion Templates
- Executing SQL Statements
- Executing SQL Scripts
- Editing Result-Set Data
- Saving Result-Set Data
- Printing Result-Set Data
- Examining Explain Plans
- Examining Basic Performance Info
- Using Auto Trace
- SQL*Plus Compatibility
- Scripts That Write Scripts
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Aliases and Autoreplacement 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.
NOTE
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 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.
CAUTION
DO NOT edit this file with TOAD running. When TOAD exits, it rewrites this file and any changes you make will be lost!
Figure 3.17 illustrates the format of this file. The format is <table name>=<alias name>.
)
Figure
3.17 TOAD alias setup.
TIP
You will learn a method of quickly creating this file in the "Scripts That Write Scripts" section later in this chapter.
TOAD aliases are easy to use. Figure 3.18 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
3.18 TOAD alias usage.
[ic:Keyboard]The '.' signals TOAD to see if this is an alias.
NOTE
Notice in Figure 3.19 that there is both an INVENTORY table and an INV table. The alias INV was resolved in Figure 3.18 to the INVENTORY table, not the INV table. Notice the column names in Figure 3.19 compared to those in Figure 3.18. 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 3.20.
Shift+Ctrl+T ignores the alias request.
NOTE
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.
){kind=small}
Figure
3.19 Available tables in the SCOTT schema.
)
Figure
3.20 Ignore the alias in action.
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 (illustrated in Figure 3.21) 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 3.22.
){kind=small}
Figure
3.21 Autoreplace substitution key sequence.
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 3.24.
Figure 3.23 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 3.21 and Figure 3.22.
){kind=small}
Figure
3.22 Autoreplace substitution in action.
)
Figure
3.23 Editing the PLSQLSUB.TXT file.
)
Figure
3.24 Adding autoreplace substitution in the Editor Options screen.
TOAD allows autosubstitution to be maintained by language type. You can edit and add to the list 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).
CAUTION
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 allows you to export and import these autosubstitution definitions. 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. See Figure 3.25.
)
Figure
3.25 Saving and loading autoreplace definitions.
When loading autoreplace definitions in from a saved file, all previous substitutions currently in TOAD are lost and replaced with the new definitions.
NOTE
These .ACE files are in a binary format and are only to be used with the TOAD Load facility (from the Editing Options menu).