WIXLIB

SQL*Plus User’s Guide and ReferenceRelease 10.2B14357-01June 2005

SQL*Plus User’s Guide and Reference, Release 10.2B14357-01Copyright 1996, 2005 Oracle. All rights reserved.Primary Author:Simon WattContributor: Alison Goggin, Alison Holloway, Anil Samuel, Christopher Jones, Luan Nim, RichardRendell, Andrei Souleimanian.The Programs (which include both the software and documentation) contain proprietary information; theyare provided under a license agreement containing restrictions on use and disclosure and are also protectedby copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly,or decompilation of the Programs, except to the extent required to obtain interoperability with otherindependently created software or as specified by law, is prohibited.The information contained in this document is subject to change without notice. If you find any problems inthe documentation, please report them to us in writing. This document is not warranted to be error-free.Except as may be expressly permitted in your license agreement for these Programs, no part of thesePrograms may be reproduced or transmitted in any form or by any means, electronic or mechanical, for anypurpose.If the Programs are delivered to the United States Government or anyone licensing or using the Programs onbehalf of the United States Government, the following notice is applicable:U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical datadelivered to U.S. Government customers are "commercial computer software" or "commercial technical data"pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. Assuch, use, duplication, disclosure, modification, and adaptation of the Programs, including documentationand technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle licenseagreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, CommercialComputer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City,CA 94065The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherentlydangerous 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 suchpurposes, and we disclaim liability for any damages caused by such use of the Programs.Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective owners.The Programs may provide links to Web sites and access to content, products, and services from thirdparties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites.You bear all risks associated with the use of such content. If you choose to purchase any products or servicesfrom a third party, the relationship is directly between you and the third party. Oracle is not responsible for:(a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with thethird party, including delivery of products or services and warranty obligations related to purchasedproducts or services. Oracle is not responsible for any loss or damage of any sort that you may incur fromdealing with any third party.

ContentsPreface . xiiiAudience. xiiiDocumentation Accessibility . xiiiRelated Documents . xivConventions . xvWhat’s New in SQL*Plus? . xviiNew Features in SQL*Plus Release 10.2.xviiSQL*Plus Quick Start . xixSQL*Plus Resources . xixSQL*Plus Overview . xixSQL*Plus Prerequisites . xxiiStarting SQL*Plus Command-line . xxiiStarting SQL*Plus Windows GUI . xxivStarting iSQL*Plus. xxivStarting SQL*Plus Instant Client . xxvConnecting to a Different Database. xxvSample Schemas and SQL*Plus. xxvRunning your first Query. xxviExiting SQL*Plus . xxviiPart I1SQL*Plus Getting StartedSQL*Plus User InterfaceSQL*Plus Command-line User Interface.The Command-line Screen.Changing the Command-line Font and Font Size .Windows Graphical User Interface.Using the Mouse to Copy Text to the Command Prompt .Using Command Keys.Using the Windows GUI Menus.Changing the Windows GUI Font and Font Size .iSQL*Plus User Interface .1-11-11-11-21-31-31-31-61-7iii

iSQL*Plus Navigation. 1-7iSQL*Plus Login Screen. 1-8iSQL*Plus DBA Login Screen . 1-9iSQL*Plus Workspace. 1-11iSQL*Plus DBA Workspace . 1-13iSQL*Plus History Screen. 1-13iSQL*Plus Input Required Screen. 1-14iSQL*Plus Preferences Screen. 1-15Preferences and Equivalent SET Commands . 1-182Configuring SQL*PlusSQL*Plus and iSQL*Plus Environment Variables. 2-1SQL*Plus and iSQL*Plus Configuration. 2-4Site Profile . 2-5User Profile. 2-5Storing and Restoring SQL*Plus System Variables. 2-6Installing Command-line Help . 2-7Configuring Oracle Net Services . 2-9iSQL*Plus Application Server Configuration . 2-9Changing the iSQL*Plus Application Server Port in Use. 2-9Testing if the iSQL*Plus Application Server is Running . 2-10Setting the Level of iSQL*Plus Logging. 2-11Setting the Session Time Out. 2-11Enabling Restricted Database Access . 2-12Enabling iSQL*Plus DBA Access . 2-13Enabling SSL with iSQL*Plus . 2-15Enabling iSQL*Plus or iSQL*Plus Help . 2-18Enabling Unauthorised Access Banner. 2-18Enabling URL Access. 2-19Enabling User Defined HTML Markup . 2-19iSQL*Plus Web Browser Configuration . 2-20Session Integrity . 2-20Retained Session Settings. 2-20Windows Graphical User Interface Configuration. 2-20Setting Options and Values Using the Environment Dialog . 2-21Customizing Registry Entries that affect SQL*Plus on Windows . 2-223Starting SQL*PlusLogin Username and Password .Secure External Password Store.Expired Password .Expired Password Screen in iSQL*Plus .Changing your Password .Changing Your Password in iSQL*Plus.Connecting to a Database .Net Service Name.Full Connection Identifier .iv3-13-23-23-23-33-33-43-43-5

Easy Connection Identifier . 3-5Connectionless Session with /NOLOG . 3-6Starting SQL*Plus . 3-6Starting Command-line SQL*Plus . 3-6Getting Command-line Help . 3-7Starting the Windows Graphical User Interface. 3-7Starting the iSQL*Plus Application Server. 3-8To Check the HTTP Port used by the iSQL*Plus Application Server . 3-9Stopping the iSQL*Plus Application Server. 3-10Starting iSQL*Plus. 3-10Starting iSQL*Plus as a DBA . 3-10Starting iSQL*Plus from a URL. 3-11Getting Help in iSQL*Plus . 3-12Exiting SQL*Plus. 3-12Exiting the Command-line User Interface . 3-13Exiting the Windows Graphical User Interface . 3-13Exiting the iSQL*Plus User Interface. 3-13SQLPLUS Program Syntax . 3-13Options . 3-14Logon . 3-18Start . 3-19Part II4Using SQL*PlusSQL*Plus BasicsEntering and Executing Commands . 4-1The SQL Buffer . 4-2Executing Commands . 4-2Listing a Table Definition. 4-3Listing PL/SQL Definitions. 4-3Running SQL Commands. 4-4Understanding SQL Command Syntax . 4-5Running PL/SQL Blocks . 4-6Creating Stored Procedures . 4-6Running SQL*Plus Commands. 4-7Understanding SQL*Plus Command Syntax . 4-8System Variables that Affect How Commands Run. 4-8Stopping a Command while it is Running . 4-9Running Operating System Commands. 4-9Pausing the Display . 4-10Saving Changes to the Database Automatically . 4-10Interpreting Error Messages. 4-115Using Scripts in SQL*PlusEditing Scripts. 5-1Writing Scripts with a System Editor. 5-1v

Editing Scripts in SQL*Plus Command-Line . 5-2Listing the Buffer Contents . 5-3Editing the Current Line . 5-4Appending Text to a Line . 5-5Adding a New Line. 5-6Deleting Lines . 5-7Placing Comments in Scripts . 5-7Using the REMARK Command . 5-7Using /*.*/ . 5-7Using -- . 5-8Notes on Placing Comments . 5-8Running Scripts . 5-10Running a Script as You Start SQL*Plus. 5-10Nesting Scripts. 5-11Exiting from a Script with a Return Code . 5-11Defining Substitution Variables . 5-11Using Predefined Variables. 5-12Using Substitution Variables . 5-12Where and How to Use Substitution Variables . 5-12Avoiding Unnecessary Prompts for Values . 5-14Restrictions . 5-17System Variables and iSQL*Plus Preferences . 5-17Substitution Variables in iSQL*Plus. 5-17iSQL*Plus Input Required Screen. 5-18Passing Parameters through the START Command . 5-19Communicating with the User. 5-20Receiving a Substitution Variable Value . 5-20Customizing Prompts for Substitution Variable . 5-21Sending a Message and Accepting Return as Input . 5-22Clearing the Screen . 5-22Using Bind Variables . 5-23Creating Bind Variables . 5-23Referencing Bind Variables. 5-23Displaying Bind Variables . 5-23Using REFCURSOR Bind Variables . 5-246Formatting SQL*Plus ReportsFormatting Columns .Changing Column Headings.Formatting NUMBER Columns .Formatting Datatypes .Copying Column Display Attributes .Listing and Resetting Column Display Attributes .Suppressing and Restoring Column Display Attributes.Printing a Line of Characters after Wrapped Column Values .Clarifying Your Report with Spacing and Summary Lines .Suppressing Duplicate Values in Break Columns.vi6-16-16-36-46-66-76-76-86-96-9

Inserting Space when a Break Column's Value Changes.Inserting Space after Every Row.Using Multiple Spacing Techniques.Listing and Removing Break Definitions .Computing Summary Lines when a Break Column's Value Changes.Computing Summary Lines at the End of the Report .Computing Multiple Summary Values and Lines .Listing and Removing COMPUTE Definitions .Defining Page and Report Titles and Dimensions .Setting the Top and Bottom Titles and Headers and Footers.Displaying System-Maintained Values in Titles .Listing, Suppressing, and Restoring Page Title Definitions .Displaying Column Values in Titles .Displaying the Current Date in Titles .Setting Page Dimensions.Storing and Printing Query Results .Creating a Flat File .Sending Results to a File .Sending Results to a Printer.7Generating HTML Reports from SQL*PlusCreating Reports using Command-line SQL*Plus .Creating Reports.Suppressing the Display of SQL*Plus Commands in Reports .HTML Entities .Creating Reports using iSQL*Plus 6-236-246-246-266-266-276-277-17-17-57-57-5Tuning SQL*PlusTracing Statements .Controlling the Autotrace Report .Execution Plan .Statistics .Collecting Timing Statistics .Tracing Parallel and Distributed Queries.Execution Plan Output in Earlier Databases.SQL*Plus Script Tuning .COLUMN NOPRINT .SET APPINFO OFF .SET ARRAYSIZE .SET DEFINE OFF .

SQL*Plus User’s