RelationalDBDesign RelationalDBDesign

SQL* Plus CLI  «Prev  Next»
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:
  1. Generate SQL*Plus reports with page headers, page footers, formatted columns, and column titles
  2. Execute scripts
  3. Spool a report and script output to a file
  4. Use the SQL*Plus built-in line-editing commands
  5. Use substitution variables and prompt a user for input
  6. 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:

COLUMNTo 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
BREAKTo suppress repeating values, define line breaks, and define page breaks
TTITLETo define page headers
BTITLETo define page footers
SET LINESIZETo define the width of a page
SET PAGESIZETo define the length of a page in terms of the number of lines that will fit on that page
SPOOLTo write SQL*Plus output to a file
SET TERMOUT OFFTo inhibit the display of SQL*Plus output on the terminal screen
SET TERMOUT ONTo require all SQL*Plus output to display on the screen
SET NEWPAGEControls the number of blanks lines printed between pages; when set to zero this causes SQL*Plus to begin each page with a formfeed character
PROMPTDisplays a message to the person running a SQL*Plus script
ACCEPTPrompts the person running a SQL*Plus script for input
DEFINEDefines a substitution variable and assigns it a value
&Identifies a substitution variable
CAllows you to change a line of the current SQL statement by doing a search and replace
LDisplays the statement currently in the buffer
IAllows you to insert a line into the SQL statement currently held in the buffer
SET FEEDBACKControls whether SQL*Plus displays messages telling you how many rows were affected by a SQL query
SET ECHOControls whether SQL*Plus displays commands on the screen when executing a script file
SET VERIFYControls whether SQL*Plus displays before and after images of lines containing substitution variables
SET TRIMSPOOLControls whether SQL*Plus trims trailing spaces from lines written to a spool file
SET RECSEPControls 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:
  1. 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.
  2. 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.
  3. 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.
  4. registry variable: A variable that is stored in the Windows Registry, and that must be edited using the regedit utility.
  5. 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.
  6. SQL buffer: A memory area used by SQL*Plus to hold the most recently entered SQL statement or PL/SQL block.
  7. 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.