Lesson 1

SQL*Plus is the primary command-line interface to an Oracle database. It is similar to Server Manager but has many added capabilities that make it a suitable tool for script writing and for reporting. This module provides an introduction to SQL*Plus and shows you how to use some of the more advanced features to write scripts and generate well-organized reports. When you have completed this module, you should be comfortable with the following tasks:

1. Generating SQL*Plus reports with page headers, page footers, formatted columns, and column titles
2. Executing scripts
3. Spooling report and script output to a file
4. Using the SQL*Plus built-in, line-editing commands
5. Using substitution variables and prompting a user for input
6. Use SQL*Plus and SQL to generate SQL scripts based on information in the data dictionary

Mastering SQL*Plus is important, partly because of what you can do with it, but also because it is so widely available. Think of it as the standard language of database administration. Not every Oracle site has Enterprise Manager installed. If you move around a lot doing work for different clients, a knowledge of SQL*Plus and SQL allows you to get work done regardless of what other tools the client has.

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface. There is also the SQL*Plus Instant Client which is a stand-alone command-line interface available on platforms that support the OCI Instant Client. SQL*Plus Instant Client connects to any available Oracle database, but does not require its own Oracle database installation. See the Oracle Call Interface Programmer's Guide for more information on the OCI Instant Client. SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:

1. Format, perform calculations on, store, and print from query results
2. Examine table and object definitions
3. Develop and run batch scripts
4. Perform database administration

You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.
Connections to an Oracle7 database from SQL*Plus 10.2 are not supported.

The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to serve the needs of users with some database experience, yet straightforward enough for new users who are just learning to work with the Oracle Database. The SQL*Plus language is easy to use. For example, to rename a column labelled LAST_NAME with the heading "Family Name", enter the command:

COLUMN LAST_NAME HEADING 'Family Name'

Similarly, to list column definitions for the EMPLOYEES table, enter the command:

DESCRIBE EMPLOYEES

• How to Use the SQL*Plus Guide
  This guide provides information about SQL*Plus that applies to all operating systems. It also includes some Windows and UNIX specific information. Some aspects of SQL*Plus may differ on each operating system. Operating system specific details are covered in the Oracle Database Installation Guide provided for your system. Use these operating system specific guides in conjunction with this SQL*Plus User's Guide and Reference. Throughout this guide, examples showing how to enter commands use a common command syntax and a common set of sample tables.
  SQL*Plus Command-line and Windows GUI Architecture SQL*Plus command-line and the Windows GUI use a two-tier model comprising:
  
  • Client (command-line user interface).
  • Database (Oracle Database).
  
  SQL*Plus Client
  The command-line user interface is the character based terminal implementation. The Windows GUI is an alternate user interface available in Windows installations.
  Oracle Database Oracle Database Net components provide communication between the SQL*Plus Client and Oracle Database.
  The two tiers may be on the same machine.

iSQL*Plus Architecture