Oracle SQL*Plus: The Definitive Guide

Oracle SQL*Plus: The Definitive Guide

Released March 1999
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565925786

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

This book is the definitive guide to SQL*Plus. If you want to take best advantage of the power and flexibility of this popular Oracle tool, you need this book. SQL*Plus is an interactive query tool that is ubiquitous in the Oracle world. It is present in every Oracle installation and is available to almost every Oracle developer and database administrator. SQL*Plus has been shipped with Oracle since at least version 6. It continues to be supported and enhanced with each new version of Oracle, including Oracle8 and Oracle8i. It is still the only widely available tool for writing SQL scripts. Despite this wide availability and usage, few developers and DBAs know how powerful SQL*Plus really is. This book introduces SQL*Plus, includes a quick reference to all of its syntax options, and, most important, provides chapters that describe, in step-by-step fashion, how to perform all of the tasks that Oracle developers and DBAs want to perform with SQL*Plus -- and maybe some they didn't realize they COULD perform with SQL*Plus. You will learn how to write and execute script files, generate ad hoc reports, extract data from the database, query the data dictionary tables, customize your SQL*Plus environment, and use the SQL*Plus administrative features (new in Oracle8i). This book is an indispensable resource for readers who are new to SQL*Plus, a task-oriented learning tool for those who are already using it, and a quick reference for every user. A table of contents follows:

  • Preface

  • Introduction to SQL*Plus

  • Interacting with SQL*Plus

  • Generating Reports with SQL*Plus

  • Writing SQL*Plus Scripts

  • Extracting Data with SQL*Plus

  • Exploring Your Database with SQL*Plus

  • Advanced Scripting

  • Tuning and Timing

  • The Product User Profile

  • Administration with SQL*Plus

  • Customizing Your SQL*Plus Environment

  • A. SQL*Plus Command Reference

  • B. Connect Strings and the SQL*Plus Command

Appendices

Table of contents

  1. Oracle SQL*Plus: The Definitive Guide
    1. Preface
      1. Why I Wrote This Book
      2. Objectives of This Book
      3. Structure of This Book
      4. Audience
      5. Obtaining the Scripts and Sample Data
      6. Conventions Used in This Book
      7. Which Platform and Version?
      8. How to Contact Us
      9. Acknowledgments
    2. 1. Introduction to SQL*Plus
      1. What Is SQL*Plus?
        1. Uses for SQL*Plus
        2. SQL*Plus’s Relation to SQL, PL/SQL and the Oracle Database
      2. History of SQL*Plus
      3. Why Master SQL*Plus?
      4. Creating and Loading the Sample Tables
        1. The Data Model
        2. The Tables
          1. The EMPLOYEE table
          2. The PROJECT table
          3. The PROJECT_HOURS table
        3. The Data
          1. The EMPLOYEE table
          2. The PROJECT table
          3. The PROJECT_HOURS table
        4. Loading the Sample Data
          1. Step 1: Download and unzip the script files
          2. Step 2: Start SQL*Plus
          3. Step 3: Log into your Oracle database
          4. Step 4: Run the BLD_DB.SQL script file
    3. 2. Interacting with SQL*Plus
      1. Starting SQL*Plus
        1. Command-Line Versus GUI Versions
        2. Operating System Authentication
        3. Starting the GUI Version
          1. Logging into a database
          2. Making a mistake on the logon dialog
          3. Missing the Product User Profile
        4. Starting the Command-Line Version
        5. Command-Line Options
      2. Some Basic SQL*Plus Commands
        1. EXIT
        2. PASSWORD
        3. HELP
        4. DESCRIBE
        5. CONNECT
        6. DISCONNECT
      3. Running SQL Queries
      4. Working with PL/SQL
        1. What Is a PL/SQL Block?
        2. Executing a PL/SQL Block
          1. Where’s the output?
          2. Rules for entering PL/SQL blocks
        3. Executing a Single PL/SQL Command
      5. The SQL Buffer
      6. Line Editing
        1. The Current Line
        2. Line-Editing Commands
          1. Getting a statement into the buffer
          2. LIST
          3. APPEND
          4. CHANGE
          5. DEL
          6. INPUT
          7. Retyping a line
      7. Executing the Statement in the Buffer
        1. If Your Statement Has an Error
        2. Doing It Again
      8. Saving and Retrieving the Buffer
        1. SAVE
        2. GET
      9. The EDIT Command
        1. Invoking the Editor
        2. Formatting Your Command
        3. Getting Back to SQL*Plus
      10. Executing a Script
      11. The Working Directory
    4. 3. Generating Reports with SQL*Plus
      1. Following a Methodology
      2. Saving Your Work
      3. Designing a Simple Report
        1. Step 1: Formulate the Query
        2. Step 2: Format the Columns
          1. The COLUMN command
          2. Column headings
          3. Column display formats
          4. Report output after formatting the columns
        3. Step 3: Add Page Headers and Footers
          1. The top title
          2. The bottom title
          3. Setting the line width
          4. Report output with page titles
        4. Step 4: Format the Page
          1. How many lines on a page?
          2. Setting the pagesize
          3. Setting the page advance
        5. Step 5: Print It!
          1. Spooling to a file
          2. The final script
          3. Executing the report
          4. Printing the file
      4. Advanced Report Formatting
        1. The BREAK Command
        2. Suppressing Duplicate Column Values
        3. Page and Line Breaks
          1. Adding a page break
          2. Adding a line break
          3. Report output with page and line breaks
        4. Master/Detail Formatting
          1. Retrieve the employee information into user variables
          2. Modify the page heading to print the employee name and ID
          3. Revisit the report width and the width of the remaining fields
          4. Printing data in a page footer
      5. Totals and Subtotals
        1. The COMPUTE Command
          1. Syntax of the COMPUTE command
          2. Printing computed values
          3. COMPUTE commands are cumulative
        2. Printing Subtotals
        3. Print Grand Totals
      6. Other Reporting Topics
        1. Getting the Current Date into a Header
          1. Getting the date from Oracle
          2. Formatting the date
        2. Report Headers and Footers
          1. REPHEADER and REPFOOTER command syntax
          2. An example
        3. Formatting Object Columns
        4. Summary Reports
        5. Taking Advantage of Unions
          1. A typical example
          2. The UNION query
          3. The final report
    5. 4. Writing SQL*Plus Scripts
      1. Why Write Scripts?
      2. Using Substitution Variables
        1. What Is a Substitution Variable?
        2. Using Single-Ampersand Variables
          1. The report for one specific employee
          2. Generalizing the report with substitution variables
          3. Running the report
          4. When TERMOUT is off
        3. Using Double-Ampersand Variables
          1. An example that prompts twice for the same value
          2. A modified example that prompts once
          3. A final caveat
      3. Prompting for Values
        1. The ACCEPT Command
          1. Syntax for the ACCEPT command
          2. Using ACCEPT to get the table name
        2. The PROMPT Command
          1. Syntax for the PROMPT command
          2. Using PROMPT to summarize the script
          3. Using PROMPT to explain the output
      4. Cleaning Up the Display
        1. Turning Verification Off
        2. Turning Feedback Off
        3. Turning Command Echoing Off
        4. Turning Off All Terminal Output
      5. Packaging Your Script
        1. Creating a Windows Shortcut
          1. Starting the SQL*Plus executable
          2. Creating the shortcut
        2. Creating a Unix Command
      6. The DEFINE and UNDEFINE Commands
        1. The DEFINE Command
          1. Syntax for the DEFINE command
          2. Defining a variable
          3. Examining a variable
          4. Listing all variables
          5. Usage notes
        2. The UNDEFINE Command
          1. Syntax for the UNDEFINE command
          2. Deleting a variable
      7. Controlling Variable Substitution
        1. The Escape Character
          1. Turning on the escape feature
          2. Escaping an ampersand
          3. Changing the escape character
        2. The Concatenation Character
          1. Turning off the concatenation feature
          2. Changing the concatenation character
        3. Enabling and Disabling Substitution
        4. Changing the Substitution Variable Prefix Character
          1. Changing the substitution variable prefix character
      8. Commenting Your Scripts
        1. The REMARK Command
        2. The /* and */ Delimiters
        3. Double Hyphens (- -)
        4. Substitution Within Comments
    6. 5. Extracting and Loading Data
      1. Types of Output Files
        1. Delimited Files
        2. Fixed-Width Files
        3. DML Files
        4. DDL Files
      2. Limitations of SQL*Plus
      3. Extracting the Data
        1. Formulate the Query
        2. Format the Data
          1. Comma-delimited
          2. Fixed-width
          3. DML
          4. DDL
        3. Spool the Extract to a File
        4. Make Your Extract Script User-Friendly
      4. Reloading the Data
        1. Executing DDL and DML
        2. Running SQL*Loader
          1. The control file
            1. Building a control file for comma-delimited data
            2. Building a control file for fixed-width data
          2. Loading the data
    7. 6. Exploring Your Database
      1. The DESCRIBE Command
        1. Syntax for DESCRIBE
        2. Describing a Table
        3. Describing Stored Functions and Procedures
        4. Describing Packages and Object Types
        5. Why DESCRIBE Is Not Enough
      2. Oracle’s Data Dictionary Views
        1. The View Types: ALL, DBA, and USER
        2. Dictionary Views Used in This Chapter
          1. The ALL_TABLES view
          2. The ALL_TAB_COLUMNS view
          3. The ALL_CONSTRAINTS view
          4. The ALL_CONS_COLUMNS view
          5. The ALL_INDEXES view
          6. The ALL_IND_COLUMNS view
          7. The ALL_TRIGGERS view
          8. The ALL_TRIGGER_COLS view
          9. The ALL_SYNONYMS view
          10. The ALL_TAB_PRIVS view
          11. The ALL_COL_PRIVS view
      3. Tables
        1. Listing Tables You Own
        2. Listing Column Definitions for a Table
          1. The LIST_COLUMNS.SQL script
          2. Notes about the LIST_COLUMNS script
          3. Running LIST_COLUMNS
      4. Table Constraints
        1. Different Constraint Types Need Different Queries
          1. CHECK constraints
          2. PRIMARY KEY and UNIQUE constraints
          3. FOREIGN KEY constraints
        2. Listing the Constraints for a Table
          1. The LIST_CONSTRAINTS.SQL script
          2. Running the LIST_CONSTRAINTS script
      5. Indexes
        1. Listing Indexes for a Table
        2. Index Scripts
          1. The SHOW_INDEX.SQL script
          2. Running the SHOW_INDEX script
          3. The LIST_INDEXES.SQL script
          4. Running the LIST_INDEXES.SQL script
      6. Triggers
        1. Looking at Trigger Definitions
        2. Trigger Scripts
          1. The SHOW_TRIGGER.SQL script
          2. Running the SHOW_TRIGGER.SQL script
          3. The LIST_TRIGGERS.SQL script
          4. Running the LIST_TRIGGERS script
      7. Synonyms
        1. Looking at Synonyms
        2. Synonym Scripts
          1. The SHOW_SYN.SQL script
          2. Running the SHOW_SYN.SQL script
          3. The LIST_SYN script
          4. Running the LIST_SYN.SQL script
      8. Table Security
        1. Looking at Table Security
        2. Security Scripts
          1. The SHOW_SECURITY.SQL script
          2. Running the SHOW_SECURITY script
      9. Finding More Information
    8. 7. Advanced Scripting
      1. Bind Variables
        1. Declaring Bind Variables
        2. Using Bind Variables and Substitution Variables Together
          1. From substitution to bind
          2. From bind to substitution
        3. Displaying the Contents of a Bind Variable
          1. The PRINT command
          2. Printing CLOB variables
          3. Printing REFCURSOR variables
          4. SELECTing a bind variable
        4. When and How to Use Bind Variables
          1. Calling PL/SQL procedures and functions from SQL*Plus
          2. Using REFCURSOR variables
          3. Testing application queries
      2. Branching in SQL*Plus
        1. Approaches to Branching
        2. Simulating Branching by Adjusting the WHERE Clause
        3. Using REFCURSOR Variables to Simulate Branching
        4. Branching Using a Multilevel File Structure
        5. Using SQL to Write SQL
        6. Using PL/SQL
        7. Using a Scripting Language Instead
      3. Looping in SQL*Plus
        1. Recursive Execution
        2. Generating a File of Commands
        3. Looping Within PL/SQL
          1. The ANALYZE TABLE script revisited
      4. Validating and Parsing User Input
        1. Validating Input with ACCEPT
          1. ACCEPTing numeric values
          2. ACCEPTing date values
        2. Validating Input with SQL
        3. Parsing Input with SQL
      5. Error Handling
        1. The WHENEVER Command
          1. WHENEVER SQLERROR
          2. PL/SQL errors and WHENEVER
          3. WHENEVER OSERROR
    9. 8. Tuning and Timing
      1. Using SQL*Plus Timers
        1. The SET TIMING Command
        2. The TIMING Command
          1. Starting and stopping a timer
          2. Displaying the value of a timer
          3. Nesting timers
          4. Finding out how many timers you have going
          5. Stopping all timers
      2. Using EXPLAIN PLAN
        1. Creating the Plan Table
        2. Explaining a Query
        3. Interpreting the Results
          1. The plan table query
          2. The SHOW_PLAN script
          3. Executing the SHOW_PLAN script
          4. Making sense of the results
      3. Using AUTOTRACE
        1. Granting Access to the Performance Views
        2. Executing a Query with AUTOTRACE On
          1. Showing statistics and the plan
          2. Showing just the plan
          3. Suppressing the query output
          4. Turning AUTOTRACE off
      4. Improving on EXPLAIN PLAN Results
        1. Knowing Good Results from Bad
        2. Creating Indexes
        3. Rewriting the Query
        4. Using Hints
          1. Syntax for a hint
          2. Optimizer goal hints
          3. Access method hints
          4. Join order hints
          5. Join operation hints
          6. Parallel execution hints
          7. Other hints
      5. Where to Find More Tuning Information
    10. 9. The Product User Profile
      1. What Is the Product User Profile?
        1. Why Does the Product User Profile Exist?
        2. The PRODUCT_PROFILE Table
        3. How the Product User Profile Works
        4. Product User Profile Limitations
          1. Issues related to PL/SQL
          2. Issues related to roles
      2. Using the Product User Profile
        1. Creating the Profile Table
        2. Limiting Access to Commands
          1. Commands that can be disabled
          2. Disabling a command
          3. Re-enabling a command
        3. Limiting Access to Roles
          1. Disabling a role
          2. Re-enabling a role
        4. Reporting on the Product User Profile
          1. Listing all restrictions
          2. Listing restrictions for a particular user
    11. 10. Administration with SQL*Plus
      1. Connecting for Administrative Purposes
        1. Privileges You Will Need
        2. The Oracle Password File
        3. Connecting in an Administrative Role
          1. Connecting as SYSOPER or SYSDBA
          2. Connecting as INTERNAL
      2. Starting and Stopping a Database
        1. The States of a Database
        2. Starting a Database
        3. Stopping a Database
          1. SHUTDOWN NORMAL
          2. SHUTDOWN TRANSACTIONAL
          3. SHUTDOWN IMMEDIATE
          4. SHUTDOWN ABORT
      3. Looking at Your Database
        1. Looking at the SGA
        2. Looking at Initialization Parameters
        3. Looking at Archive and Recovery Information
      4. Database Backup and Recovery
        1. Backups
          1. Cold backups
          2. Hot backups
        2. Archiving
          1. Turning on archive log mode
          2. Starting the archive log process
          3. Displaying the archive log status
          4. Manually archiving a log file
          5. Stopping the archive log process
          6. Turning off archive log mode
        3. Recovery
          1. Full database recovery
          2. Tablespace and datafile recovery
          3. Autorecovery
        4. Other Things You Need to Know
    12. 11. Customizing Your SQL*Plus Environment
      1. SQL*Plus Settings You Can Control
      2. The Site and User Profiles
        1. The Site Profile
        2. The User Profile
      3. Environment Variable Settings
        1. Setting an Environment Variable
          1. Unix
          2. Windows 95/98/NT
          3. Windows 3.1
        2. Environment Variables That Affect SQL*Plus
          1. LOCAL
          2. NLS_LANG
          3. PRO80
          4. PLUS80
          5. RDBMS80
          6. SQLPATH
          7. PLUS_DFLT
      4. The SET Command
        1. Report Output and Format Settings
          1. SET COLSEP
          2. SET EMBEDDED
          3. SET HEADSEP
          4. SET HEADING
          5. SET LINESIZE
          6. SET MAXDATA
          7. SET NEWPAGE
          8. SET NULL
          9. SET NUMFORMAT
          10. SET NUMWIDTH
          11. SET PAGESIZE
          12. SET RECSEP
          13. SET RECSEPCHAR
          14. SET SHIFTINOUT
          15. SET SPACE
          16. SET TAB
          17. SET TRIMOUT
          18. SET TRIMSPOOL
          19. SET TRUNCATE
          20. SET UNDERLINE
          21. SET WRAP
        2. Feedback Settings
          1. SET AUTOPRINT
          2. SET DOCUMENT
          3. SET ECHO
          4. SET FEEDBACK
          5. SET PAUSE
          6. SET SERVEROUTPUT
          7. SET SHOWMODE
          8. SET SQLPROMPT
          9. SET TERMOUT
          10. SET TIME
          11. SET VERIFY
        3. Input Settings
          1. BLOCKTERMINATOR
          2. BUFFER
          3. CMDSEP
          4. SQLBLANKLINES
          5. SQLCASE
          6. SQLCONTINUE
          7. SQLNUMBER
          8. SQLPREFIX
          9. SQLTERMINATOR
          10. SUFFIX
        4. Operational Settings
          1. SET APPINFO
          2. SET ARRAYSIZE
          3. SET AUTOCOMMIT
          4. SET CLOSECURSOR
          5. SET COMPATIBILITY
          6. SET COPYCOMMIT
          7. SET FLAGGER
          8. SET FLUSH
        5. Substitution Variable Settings
          1. SET CONCAT
          2. SET DEFINE
          3. SET ESCAPE
          4. SET SCAN
        6. Large Object Settings
          1. SET LOBOFFSET
          2. SET LONG
          3. SET LONGCHUNKSIZE
        7. Tuning and Timing Settings
          1. SET AUTOTRACE
          2. SET TIMING
        8. Database Administration Settings
          1. SET AUTORECOVERY
          2. SET LOGSOURCE
        9. Miscellaneous Settings
          1. SET COPYTYPECHECK
          2. SET EDITFILE
          3. SET INSTANCE
      5. The SHOW Command
    13. A. SQL*Plus Command Reference
      1. SQLPLUS
      2. Comment Delimiters (/*...*/)
      3. Double Hyphen (- -)
      4. At Sign (@)
      5. Double At Sign (@@)
      6. Forward Slash (/)
      7. ACCEPT
      8. APPEND
      9. ARCHIVE LOG
      10. ATTRIBUTE
      11. BREAK
      12. BTITLE
      13. CHANGE
      14. CLEAR
      15. COPY
      16. COLUMN
      17. COMPUTE
        1. Syntax of the COMPUTE command
      18. CONNECT
      19. DEFINE
      20. DEL
      21. DESCRIBE
      22. DISCONNECT
      23. EDIT
      24. EXECUTE
      25. EXIT
      26. GET
      27. HELP
      28. HOST
      29. INPUT
      30. LIST
      31. PASSWORD
      32. PAUSE
      33. PRINT
      34. PROMPT
      35. QUIT
      36. RECOVER
      37. REMARK
      38. REPFOOTER
      39. REPHEADER
      40. RUN
      41. SAVE
      42. SET
      43. SHOW
      44. SHUTDOWN
      45. SPOOL
      46. START
      47. STARTUP
      48. STORE
      49. TIMING
      50. TTITLE
      51. UNDEFINE
      52. VARIABLE
      53. WHENEVER
    14. B. SQL*Plus Format Elements
      1. Formatting Numbers
      2. Formatting Character Strings
      3. Formatting Dates
    15. Index
    16. Colophon

Product information

  • Title: Oracle SQL*Plus: The Definitive Guide
  • Author(s):
  • Release date: March 1999
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565925786