iSQL Plus. User's Guide and Reference

264 Pages·2001·2.055 MB·English

by  Watt S.

iSQL*Plus
User’s Guide and Reference
Release 9.0.1
June 2001
Part No. A88826-01
iSQL*Plus User’s Guide and Reference, Release 9.0.1
Part No. A88826-01
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Author: Simon Watt
Contributors: Andrew Code, Alison Goggin, Alison Holloway, Christopher Jones, Anita Lam, Luan
Nim, Andrei Souleimanian, Christopher Tan, Ian Wu.
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle Net, Oracle HTTP Server, Oracle7, Oracle8, Oracle8i,
Oracle9i, PL/SQL, iSQL*Plus and SQL*Plus are trademarks or registered trademarks of Oracle
Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments.................................................................................................................. vii
Preface............................................................................................................................................................ ix
1 Introduction to iSQL*Plus
What is iSQL*Plus?............................................................................................................................ 1-2
iSQL*Plus Architecture..................................................................................................................... 1-2
2 Configuring iSQL*Plus
System Requirements........................................................................................................................ 2-2
Installation........................................................................................................................................... 2-2
Configuring the Client Tier.............................................................................................................. 2-3
Configuring the Middle Tier............................................................................................................ 2-4
Configuring the iSQL*Plus Server............................................................................................. 2-4
Configuring the Oracle HTTP Server........................................................................................ 2-7
Configuring the Database Tier......................................................................................................... 2-8
iSQL*Plus Security............................................................................................................................. 2-8
Configuring Globalization Support............................................................................................. 2-12
3 The iSQL*Plus User Interface
The iSQL*Plus Log In Screen........................................................................................................... 3-2
The iSQL*Plus Work Screen............................................................................................................. 3-4
The Change Password Screen.......................................................................................................... 3-6
iii
4 Using iSQL*Plus
Running iSQL*Plus............................................................................................................................ 4-2
Entering Statements........................................................................................................................... 4-2
Loading Scripts.................................................................................................................................... 4-3
Entering Substitution Variables....................................................................................................... 4-4
Creating Dynamic Reports................................................................................................................ 4-6
Using SET and COLUMN Commands........................................................................................... 4-9
Getting Help........................................................................................................................................ 4-9
Exiting iSQL*Plus............................................................................................................................... 4-9
5 Command Reference
SQL*Plus Command Summary........................................................................................................ 5-2
@ (“at” sign).......................................................................................................................................... 5-5
@@ (double “at” sign)......................................................................................................................... 5-7
/ (slash).................................................................................................................................................. 5-9
ARCHIVE LOG................................................................................................................................. 5-10
ATTRIBUTE....................................................................................................................................... 5-13
BREAK................................................................................................................................................ 5-15
BTITLE................................................................................................................................................ 5-20
CLEAR................................................................................................................................................. 5-21
COLUMN........................................................................................................................................... 5-23
COMPUTE.......................................................................................................................................... 5-34
CONNECT.......................................................................................................................................... 5-40
COPY................................................................................................................................................... 5-42
DEFINE............................................................................................................................................... 5-46
DESCRIBE.......................................................................................................................................... 5-48
DISCONNECT.................................................................................................................................. 5-54
EXECUTE............................................................................................................................................ 5-55
HELP.................................................................................................................................................... 5-56
LIST..................................................................................................................................................... 5-57
PRINT.................................................................................................................................................. 5-59
PROMPT............................................................................................................................................. 5-61
RECOVER.......................................................................................................................................... 5-62
REMARK............................................................................................................................................ 5-69
REPFOOTER...................................................................................................................................... 5-70
iv
REPHEADER..................................................................................................................................... 5-72
RUN..................................................................................................................................................... 5-75
SET....................................................................................................................................................... 5-76
SHOW................................................................................................................................................. 5-96
SHUTDOWN................................................................................................................................... 5-101
START............................................................................................................................................... 5-103
STARTUP.......................................................................................................................................... 5-105
TIMING............................................................................................................................................ 5-108
TTITLE.............................................................................................................................................. 5-110
UNDEFINE....................................................................................................................................... 5-114
VARIABLE....................................................................................................................................... 5-115
WHENEVER OSERROR............................................................................................................... 5-122
WHENEVER SQLERROR............................................................................................................. 5-123
A SQL*Plus Error Messages
B Security
C Unsupported SQL*Plus Commands
Glossary
Index
v
vi
Send Us Your Comments
iSQL*Plus User’s Guide and Reference, Release 9.0.1
Part No. A88826-01
Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this
document. Your input is an important part of the information used for revision.
(cid:1) Did you find any errors?
(cid:1) Is the information clearly presented?
(cid:1) Do you need more information? If so, where?
(cid:1) Are the examples correct? Do you need more examples?
(cid:1) What features did you like most?
If you find any errors or have any other suggestions for improvement, please indicate the document
title and part number, and the chapter, section, and page number (if available). You can send com-
ments to us in the following ways:
(cid:1) Electronic mail: [email protected]
(cid:1) FAX: [phone] Attn: SQL*Plus Documentation Manager
(cid:1) Postal service:
SQL*Plus Documentation Manager
Australian Product Development Centre
Oracle Corporation Australia Pty Ltd
324 St Kilda Road,
Melbourne, VIC 3004,
Australia
If you would like a reply, please give your name, address, telephone number, and (optionally) elec-
tronic mail address.
If you have proble ms with the software, please contact your local Oracle Support Services. There is
also an iSQL*Plus Discussion Forum on the Oracle Technology Network located at
http://technet.oracle.com/ which may be helpful for iSQL*Plus issues.
vii
viii
Preface
The iSQL*Plus User’s Guide and Reference provides information about the iSQL*Plus
user interface, and the Oracle HTTP Server and Oracle9i configuration required to
use iSQL*Plus.
This preface contains these topics:
(cid:1) Audience
(cid:1) Organization
(cid:1) Related Documentation
(cid:1) Conventions
(cid:1) Documentation Accessibility
ix
Audience
iSQL*Plus User’s Guide and Reference is intended for end users and system
administrators who perform the following tasks:
(cid:1) Enter, edit, run store, retrieve, and save SQL commands and PL/SQL blocks.
(cid:1) Calculate, store, and print query results.
(cid:1) List column definitions for any table.
(cid:1) Access and copy data between databases.
(cid:1) Perform database administration.
To use this document, you need a basic understanding of the SQL database
language. If you do not have any familiarity with this database tool, you should
refer to the Oracle9i SQL Reference. If you plan to use the PL/SQL database language
in conjunction with SQL*Plus, refer to the PL/SQL User’s Guide and Reference for
information on using PL/SQL.
Organization
This document contains:
Chapter1, "Introduction to iSQL*Plus"
Provides introductory information to help you get started with iSQL*Plus.
Chapter2, "Configuring iSQL*Plus"
Explains how to configure your web browser, the Oracle HTTP Server and
Oracle9i.
Chapter3, "The iSQL*Plus User Interface"
Provides a description of the screens in the iSQL*Plus User Interface.
Chapter4, "Using iSQL*Plus"
Explains how to use iSQL*Plus and its features.
Chapter5, "Command Reference"
Provides a reference for commands specific to iSQL*Plus.
x