This module discussed now SQL*Plus is the command-line interface that allows you to interact with your Oracle database.
Using SQL*Plus, you can perform just about any administrative task imaginable. It is true, however, that you have to type a lot.
Now that you have completed this module, you should be able to do the following:
- Generate SQL*Plus reports with page headers, page footers, formatted columns, and column titles
- Execute scripts
- Spool a report and script output to a file
- Use the SQL*Plus built-in line-editing commands
- Use substitution variables and prompt a user for input
- Use SQL*Plus to write SQL scripts
For a database administrator, the SQL*Plus scripting capabilities are perhaps the most important of the items in the above list.
You should automate as much as possible. Anytime you find yourself performing a repetitive task, take some time to think about writing a script to do the work for you. In this module, you used the following SQL*Plus commands:
| COLUMN | To set the display width for a column, and to set the numeric display format for a column |
| @ | To execute SQL script files |
| @@ | To call one SQL script from another |
| BREAK | To suppress repeating values, define line breaks, and define page breaks |
| TTITLE | To define page headers |
| BTITLE | To define page footers |
| SET LINESIZE | To define the width of a page |
| SET PAGESIZE | To define the length of a page in terms of the number of lines that will fit on that page |
| SPOOL | To write SQL*Plus output to a file |
| SET TERMOUT OFF | To inhibit the display of SQL*Plus output on the terminal screen |
| SET TERMOUT ON | To require all SQL*Plus output to display on the screen |
| SET NEWPAGE | Controls the number of blanks lines printed between pages; when set to zero this causes SQL*Plus to begin each page with a formfeed character |
| PROMPT | Displays a message to the person running a SQL*Plus script |
| ACCEPT | Prompts the person running a SQL*Plus script for input |
| DEFINE | Defines a substitution variable and assigns it a value |
| & | Identifies a substitution variable |
| C | Allows you to change a line of the current SQL statement by doing a search and replace |
| L | Displays the statement currently in the buffer |
| I | Allows you to insert a line into the SQL statement currently held in the buffer |
| SET FEEDBACK | Controls whether SQL*Plus displays messages telling you how many rows were affected by a SQL query |
| SET ECHO | Controls whether SQL*Plus displays commands on the screen when executing a script file |
| SET VERIFY | Controls whether SQL*Plus displays before and after images of lines containing substitution variables |
| SET TRIMSPOOL | Controls whether SQL*Plus trims trailing spaces from lines written to a spool file |
| SET RECSEP | Controls whether SQL*Plus prints a blank line between records when it needs to wrap a long column value to a second line |
This module introduced you to the following terms:
- ad-hoc: Done on an irregular or spontaneous basis. An ad-hoc report, for example, is one that is designed on the spot and run only once, or maybe a very few times.
- foreign-key: A field, or a set of fields, in a table that reference a record in another table. Foreign keys are often used in parent-child relationships. In an order-entry system, for example, line-item records would typically have one or more fields
(the foreign-key) that identified the parent order record.
- formfeed character: A special character used to tell a printer to advance to a new page. Formfeed characters were a standard item in the days before page description languages such as Postscript.
- registry variable: A variable that is stored in the Windows Registry, and that must be edited using the regedit utility.
- schema: A schema is named collection of database objects. In Oracle, schemas are tightly linked to users and
each user has one schema with a name matching the user's name, and the term schema is sometimes used synonymously with user.
- SQL buffer: A memory area used by SQL*Plus to hold the most recently entered SQL statement or PL/SQL block.
- sqlpath: The name of a UNIX environment variable, or on NT a registry entry, that defines a search path for SQL*Plus to use in looking for a script file executed by a user.
In the next module, you will learn how to create and manage users.