Page 289
IN THIS CHAPTER
Page 290
SQL*Plus (pronounced see-quell plus) is an interactive tool for the Oracle RDBMS environment. You can use SQL*Plus simply to process SQL statements one at a time, to process SQL statements interactively with end users, to use PL/SQL for the procedural processing of SQL statements, to list and print query results, to format query results into reports , to describe the contents of a given table, and to copy data between databases.
This chapter concentrates on using SQL*Plus to format output into a variety of reports and introduces methods of using SQL*Plus to create dynamic, data-driven SQL*Plus programs and operating-system_specific command language programs.
SQL*Plus originated from the beginning of the Oracle RDBMS days as a product called User Friendly Interface (UFI). Before version 4 of Oracle RDBMS, UFI was used primarily to administer the Oracle environment. UFI later was renamed to SQL*Plus with the UFI product. There have been additions to several of the command capabilities, additional ways of starting SQL*Plus, and a changed role for SQL*Plus through the major releases of the Oracle RDBMS kernel. Before Oracle6, for example, using UFI or SQL*Plus SQL*DBA that took over many of the database responsibilities, such as backup and recovery and startup and shutdown. Oracle7 and 8 provide a new interface, called Enterprise Manager, to replace SQL*DBA as a database-management tool. SQL*Plus also exists in the world of client/server and is available with all the major graphical interfaces. (Specifics of these graphical interfaces are beyond the scope of this chapte
NOTE |
I highly recommend using Enterprise Manager to create and maintain the individual Oracle databases. I would restrict SQL*Plus through the use of Oracle's PRODUCT_USER_PROFILE table or Oracle profiles to prohibit end users from performing any administrativ |
SQL*Plus is the main ad hoc, character-mode interface to the Oracle RDBMS. You easily can use SQL*Plus to produce a variety of character-mode reports. You also can use SQL*Plus to create dynamic SQL*Plus scripts or even dynamic operating-system_specific command-
language programs. SQL*Plus can be used for some Oracle administration functions, and it
Page 291
can be programmed to be interactive during a specific terminal session. SQL*Plus can process ANSI SQL as well as PL/SQL blocks.
SQL*Plus has a variety of limitations; some are operating-system_specific. The following list of limits or maximum values is from Oracle SQL*Plus User's Guide and Reference, Appendix C:
Item | Limit |
Filename length | System dependent |
Username length | 30 characters |
User variable name length | 30 characters |
User variable value length | 240 characters |
Number of user variables | 1,024 |
Command-line length | 2,500 characters |
Length of a LONG value | The value of LINESIZE entered through SQL*Plus |
LINESIZE value | System dependent |
LONGCHUNKSIZE value | System dependent |
Maximum PAGESIZE | 50,000 lines |
Maximum ARRAYSIZE | 5,000 rows |
Output line size | System dependent |
Line size after | 3,000 characters variable substitution (internal only) |
Number of lines per SQL command | 500 ( assuming 80 characters per line) |
Number of rows in an array fetch | 5,000 |
Number of nested command files | 20 for VMS, CMS, UNIX; otherwise , 5 |
Number of characters in a | 500 characters COMPUTE command label |
Maximum PL/SQL error message size | 2KB |
Maximum ACCEPT character | 240 bytes string length |
Maximum page number | 99,999 |
SQL*Plus typically is available on any computer system that supports the Oracle RDBMS environment. In the client/server environment, SQL*Plus is available on all the major graphical interfaces, including Microsoft Windows, Windows 95, Windows NT, and Motif.
Page 292
There are six types of SQL*Plus commands:
These commands are described throughout this chapter.
SQL*Plus is an interactive, ad hoc environment that also can be preprogrammed with the use of SQL*Plus commands, SQL statements, and PL/SQL blocks submitted via a file. After successfully logging on to SQL*Plus, the user, regardless of the environment he or she is using, receives a SQL*Plus prompt: SQL>. You can change this prompt message to any text string by changing the SQL*Plus system variable SQLPROMPT.
You can enhance the basic SQL*Plus environment for each user or group of users by using a file named LOGIN.SQL. This file should be located in the directory or home environment from which SQL*Plus is initiated. Oracle and SQL*Plus run in a variety of computer environments; the method used to create files and the definition of the home environment vary greatly among types of computer operating systems. Typical contents of this file are various SET commands that alter the SQL*Plus default settings for the particular user.
TIP |
I have set up these LOGIN.SQL files to contain column-format commands for each column of the objects to which the particular user or group of users has access. This gives all ad hoc queries a polished appearance without the end user's having to input anything but the ad hoc query. |
The PRODUCT_USER_PROFILE table, owned by SYSTEM, is one way to provide product-level
security that enhances the security provided by the SQL GRANT and REVOKE commands. This level of security is used to disable certain SQL and SQL*Plus commands for individual users.
Page 293
There are various ways to initiate the SQL*Plus environment, depending on the type of computer platform being used. To leave the SQL*Plus environment, simply type EXIT at the SQL> prompt and press Return or Enter. To terminate a SQL*Plus command file, make EXIT the last line of the file.
SQL*Plus is a character-based tool that runs in both character-mode and graphical environments. The way in which the SQL*Plus environment is initiated varies greatly between the two types of environments. This section discusses the character-mode environment initiation syntax; the next section, "Graphical-Mode Environments," discusses the syntax required for the Windows 95/NT environments. Figure 13.1 shows the character-mode screen that appears when SQL*Plus starts.
The following syntax initiates SQL*Plus and prompts the user for a valid username and password:
PLUS80
Most UNIX environments implement the SQL*Plus command in lowercase, whereas Windows platforms implement versioning in the filename. For Oracle8 in character mode, the SQL*Plus executable name is plus80.exe, whereas the Windows interface is called plus80w.exe.