0289-0293

Previous Table of Contents Next

Page 289

CHAPTER 13

SQL*Plus

IN THIS CHAPTER

  • A Brief History of SQL*Plus 290
  • Use and Limitations 290
  • Platforms 291
  • SQL*Plus Commands 292
  • Character-Mode Environments 293
  • Graphical-Mode Environments 295
  • SQL*Plus Reporting 301
  • SQL*Plus Additional Functionality 310

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.

A Brief History of SQL*Plus

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

Use and Limitations

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

Platforms

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

SQL*Plus Commands

There are six types of SQL*Plus commands:

  • Commands that initiate the SQL*Plus environment
  • SQL*Plus execute commands
  • SQL*Plus editing commands
  • SQL*Plus formatting commands
  • Miscellaneous commands
  • Access commands for various databases

These commands are described throughout this chapter.

Commands That Initiate the SQL*Plus Environment

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.

Character-Mode Environments

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.


Figure 13.1.
The Windows NT
SQL*Plus character-
mode display.

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.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net