0294-0297

Previous Table of Contents Next

Page 294

After SQL*Plus starts, enter a valid password and press Enter. SQL*Plus then prompts you for a password. The password does not appear on-screen. The output from starting SQL*Plus in character mode should look similar to the following:

 SQL*Plus: Release 8.0.3.0.0 - Production on Tue Jul 8 21:25:8 1997 (c) Copyright 1997 Oracle Corporation.  All rights reserved. Enter username: 

The following syntax initiates SQL*Plus but does not prompt for the user ID or password:

PLUS80 userid /password

If the user ID or password is not valid, SQL*Plus generates an error and then prompts the user for a valid user ID and password.

Using the option _S or _SILENT (_S spelled out) does not display the SQL*Plus version and copyright information:

 PLUS80 -S userid/password 

This code is handy when you are initiating reports written in SQL*Plus from a menu system and you want the appearance of a seamless application.

The following syntax initiates the SQL*Plus environment and connects the user to the remote database identified by the database name :

PLUS80 userid/password@database

This database name can be a SQL*Net connect string, a SQL*Net alias name, or a SQL*Net version 2 database instance name.

This syntax initiates the SQL*Plus environment and executes the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file (SQL*Plus command file):

 PLUS80 userid/password @filename 

The contents of this file are covered in the section "SQL*Plus Formatting Commands," later in this chapter.

TIP

Always use an operating system-dependent, fully qualified filename when specifying a filename to execute.

This syntax initiates the SQL*Plus environment and expects the first line of the file to contain a valid user ID and password, in this format:

 PLUS80 @filename 

If the user ID and password are valid, SQL*Plus processes the SQL*Plus commands and the SQL (or PL/SQL blocks) contained in the file.

This syntax initiates the SQL*Plus environment and executes the SQL*Plus commands and the SQL (or PL/SQL blocks) contained in the file:

 PLUS80 userid/password @filename param1 param2 ... 

Page 295

The command-line parameters are passed to variables inside the SQL*Plus command file and are identified inside this file by &1, &2, and so on. Using these parameters is covered in the section "Advanced Reporting Techniques," later in this chapter.

Graphical-Mode Environments

This section discusses the syntax required to initiate the SQL*Plus environment from the Windows 95/NT graphical environments. When started in graphical mode, SQL*Plus displays a Log On window that requests a valid username, a password, and a connect string, as shown in Figure 13.2.

Figure 13.2.
The Windows NT
SQL*Plus Log On
screen.

SQL*Plus Execute Commands

You use the execute commands to initiate the processing of SQL statements and PL/SQL blocks, to measure the processing time of SQL or PL/SQL statements, to execute non-Oracle programs, to execute SQL*Forms programs, or to obtain additional help. Table 13.1 lists the execute commands.

Table 13.1. The execute commands.

Command	Function
/	Executes the SQL statement or PL/SQL block currently in the SQL buffer. (This is probably the most-used of the SQL*Plus commands.)
HELP topic	Provides online assistance with SQL, PL/SQL, or SQL*Plus commands.
HOST	Executes non-Oracle commands (operating system_dependent) without leaving SQL*Plus.
RUN	Displays and executes the contents of the SQL buffer.
TIMING	Displays the system CPU time with the SQL prompt.

NOTE

The RUNFORM option, which supports the execution of SQL*Forms programs from within SQL*Plus, no longer is supported in Oracle8.

Page 296

NOTE

I do not recommend this TIMING feature for tuning purposes; EXPLAIN_PLAN is a more accurate tool for gathering individual SQL statement statistics. EXPLAIN_PLAN is discussed in more detail in Chapter 23, "Performance Tuning and Optimization."

SQL*Plus Editing Commands

The SQL buffer is a work area assigned to the SQL*Plus environment. This buffer contains only SQL or PL/SQL syntax. You can use the commands listed in Table 13.2 to load, save, and manipulate the contents of this buffer.

Table 13.2. SQL editing commands.

Command	Function
A new text or APPEND new text	Appends text to the end of the current line of the SQL buffer.
C/target text/new text/ or	Changes the target text to the CHANGE/target text/new text/ new text on the current line in the SQL buffer.
CLEAR BUFFER or CL BUFF	Deletes all lines in the SQL buffer.
DEL	Deletes the current line in the SQL buffer.
DEL y	Deletes line y from the SQL buffer.
DEL y z	Deletes from line y to line z in the SQL buffer.
DEL *	Deletes the current line from the SQL buffer.
DEL LAST	Deletes the last line in the SQL buffer.
EDIT filename	Uses an operating-system_dependent text editor. To edit the SQL buffer with an operating system_ dependent text editor, simply leave off the filename.
GET filename	Reads an operating-system_dependent file into the SQL buffer.
I text or INPUT text	Adds the text after the current line in the SQL buffer.
L number or LIST number	Displays the contents of the SQL buffer. When the number syntax is used, LIST will display the line number and make that line the current line in the SQL buffer.

Page 297

Command	Function
LIST y z	Displays lines y to z from the SQL buffer.
LIST LAST	Lists the last line in the SQL buffer.
SAVE filename	Saves the contents of the SQL buffer to an operating-system_dependent file.
START filename param1 param2 ...	START executes the contents of the SQL*Plus command file named in filename and passes any input parameters to the SQL*Plus command file.

TIP

An excellent method to use when you are creating SQL*Plus command files is to use these editing features to arrive at the query results desired: SAVE to the operating system and then edit that file with EDIT to add the formatting and other desired features.

TIP

I find it convenient to use the START feature when I create various database objects. It enables me to have complete control over the order in which the objects are created. I simply create a SQL*Plus command file named INSTALL.SQL, create each DDL statement in its own SQL*Plus command file, and then add a START command in the INSTALL.SQL file for each of the DDL SQL*Plus command files.

SQL*Plus Formatting Commands

You use the SQL*Plus formatting commands to manipulate the result set from a SQL query. The formatting commands follow:

• BREAK ON column_name and options
  This command controls the organization of rows returned by the query. BREAK can manipulate the appearance of the output by specifying under what conditions a BREAK should occur and what actions should be taken at the BREAK. The appearance of the output can be controlled by skipping a line or skipping to the top of the next page and providing totals when used with COMPUTE. Any number of lines can be skipped at a BREAK point. BREAK points can be defined at the column level, for multiple columns , on a row, on a page, or on a report. See the COMPUTE command for BREAK examples. Entering BREAK by itself at the SQL prompt displays the current BREAK settings.

Previous Table of Contents Next