| Previous | Table of Contents | Next |
SQL*Plus is the main adhoc, character-mode interface to the Oracle RDBMS. SQL*Plus can be used simply to process SQL statements one at a time, process SQL statements interactively with end users, utilize PL/SQL for procedural processing of SQL statements, list and print query results, format query results into reports, describe the contents of a given table, and copy data between databases. SQL*Plus can easily be used to produce a variety of types of character-mode reports. SQL*Plus can also be used 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. SQL*Plus can be programmed to be interactive during a specific terminal session. SQL*Plus can process ANSI SQL as well as PL/SQL blocks.
Note:
I highly recommend using svrmgr to create and maintain the individual Oracle databases. I would restrict SQL*Plus through the use of PRODUCT_USER_PROFILE table or PROFILES to prohibit end users from performing any administrative task.
This section will concentrate on utilizing SQL*Plus to format output into a variety of reports and will introduce methods of utilizing SQL*Plus to create dynamic data-driven SQL*Plus programs and operating system-dependent command language programs.
SQL*Plus has a variety of limitations, some being operating system specific. A complete list of limits or maximum values is found in Oracle SQL*Plus User’s Guide and Reference, Appendix C.
SQL*Plus is typically available on any computer system that supports the Oracle RDBMS environment. In the client/server environment, SQL*Plus is available on all of the major graphical interfaces, including MS Windows and Motif.
There are 6 types of SQL*Plus commands:
SQL*Plus is an interactive adhoc environment that can also be pre-programmed with the use of SQL*Plus commands, SQL statements, and/or PL/SQL blocks submitted via a file. Upon successful login to SQL*Plus, the user, regardless of environment, will receive a SQL*Plus prompt, SQL>.
The basic SQL*Plus environment can be enhanced for each user or group of users by utilizing a file named LOGIN.SQL. This file should be located in the directory from which SQL*Plus is to be initiated (operating system dependent). Typical contents of this file are various SET commands that alter the SQL*Plus default settings for this particular user.
Tip:
I have created these LOGIN.SQL files to contain column format commands for each column of the objects that the particular user or group of users has access to. This gives all adhoc queries a polished appearance.
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.
There are various ways of initiating the SQL*Plus environment depending on the type of computer platform being utilized. To leave the SQL*Plus environment, simply type EXIT at the SQL> prompt and press Return or Enter.
The syntax in Listing 22.4 will initiate SQL*Plus and prompt the user for a valid username and password. Most UNIX environments implement the SQL*Plus command in lower case. Enter a valid password and press Enter or Return. SQL*Plus will then prompt for a password. The password will not appear on the screen.
Listing 22.4. SQL*Plus with logon prompt.
Unix> sqlplus SQL*Plus Version 3.1.0 - Production on Tue Aug 1 14:30:20 1995 Copyright " Oracle Corporation 1979, 1991. All rights reserved. Enter user-name:
The following syntax will initiate SQL*Plus but not prompt for the user ID or password. If either user ID or password is not valid, SQL*Plus will give an error message and then prompt the user for a valid user ID and password as seen in Listing 22.4.
SQLPLUS userid/password.
The -S or -SILENT will not display the SQL*Plus version and copyright information in the following code line. This is handy when initiating reports written in SQL*Plus from a menu system where the appearance of a seamless application is desired.
SQLPLUS -S userid/password.
The following syntax will initiate the SQL*Plus environment and connect the user to the remote database identified by the database name. This database name can be a SQL*Net connect string, a SQL*Net alias name, or a SQL*Net database instance name.
SQLPLUS userid/password@database.
The following syntax will initiate the SQL*Plus environment and execute the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file (SQL*Plus command file). The contents of this file are covered later in this chapter.
SQLPLUS userid/password @filename.
Note:
Notice the difference between the last two examples, where sqlplus userid/password@database will access SQL*Net and sqlplus userid/password @filename will read a SQL*Plus command file. Notice the lack of a space in the first example after password and the space in the second example.
Tip:
Always use the operating system dependent full path name with this filename.
The following syntax will initiate the SQL*Plus environment and expect the very first line of the file to contain a valid user ID/password, in this exact format. If the user ID and password are valid, SQL*Plus will process the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file.
SQLPLUS @filename.
Tip:
SQL*Net and a SQL*Plus command file can be used together in either of two ways: sqlplus userid/password@database @filename or the first line of the SQL*Plus command file contains a valid userid/password@database. To terminate a SQL*Plus command file, make EXIT the last line of the file.
The following syntax will initiate the SQL*Plus environment and execute the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file. The command line parameters will be passed to variables inside the SQL*Plus command file and be identified inside this file by &1, &2, and so on. The usage of these parameters is covered later in this chapter.
SQLPLUS userid/password @filename param1 param2 ...
| Previous | Table of Contents | Next |