Lesson 18

SQL*Plus Conclusion

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

Glossary

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.