Oracle SQL*Plus Pocket Reference, 3rd Edition

Oracle SQL*Plus Pocket Reference, 3rd Edition

by Jonathan Gennick
Released November 2004
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596008857

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 Buy on ebooks.com
Start your free trial

Book description

The Oracle SQLPlus Pocket Reference is a must-have for anyone working with Oracle databases, especially those looking to maximize the effectiveness of SQLPlus. As Oracle's long-standing interactive query tool, SQLPlus is available at every Oracle site, from the largest data warehouse to the smallest single-user system. Despite its wide use, however, SQLPlus is still often not completely understood or fully utilized.Database administrators and developers alike will therefore find the Oracle SQLPlus Pocket Reference to be extremely beneficial. In addition to summarizing all of the SQLPlus syntax and format options, including new Oracle Database 10g features, this handy, on-the-job guide specifically shows readers how to:

  • Differentiate between SQL and SQLPlus
  • Interact with SQLPlus from both the command line and the web browser
  • Select, insert, update, and delete data
  • Format both text and HTML reports with SQLPlus
  • Specify SQLPlus commands and format elements
  • Tune SQL queries
The new third edition of this book has been updated for Oracle Database 10g to include information on both SQLPlus and SQL. New SQL information includes the SELECT statement's new MODEL clause, flashback queries, partition outer joins, and DBMS_XPLAN.With its quick-reference format and compact size, the Oracle SQLPlus Pocket Reference follows in the long line of successful "pocket references" offered by O'Reilly. It also serves as the ideal companion to O'Reilly's larger, more comprehensive book on SQLPlus, the bestselling Oracle SQLPlus: The Definitive Guide.Author Jonathan Gennick is an editor for O'Reilly specializing in database and programming titles, having amassed some 17 years of programming and database management experience.

Publisher resources

View/Submit Errata

Table of contents

  1. Oracle SQL*Plus: Pocket Reference
  2. 1. Oracle SQL*Plus Pocket Reference
    1. Introduction
      1. Acknowledgments
      2. Conventions
    2. Command-Line SQL*Plus
      1. Starting SQL*Plus
        1. Syntax for the sqlplus command
      2. Entering Commands
        1. Entering SQL*Plus commands
        2. Entering SQL statements
        3. Entering PL/SQL blocks
      3. Strings in SQL*Plus Commands
      4. Specifying Filenames
      5. Naming Variables
    3. Browser-Based SQL*Plus
      1. Starting and Stopping iSQL*Plus
      2. Connecting to iSQL*Plus
      3. Connecting to a Database
      4. Connecting as SYSDBA
      5. Creating SYSDBA Users
      6. Entering and Executing Commands
      7. Getting Output as Plain Text
      8. Executing Scripts
      9. Disconnecting from iSQL*Plus
    4. Selecting Data
      1. The SELECT Statement
        1. Selecting columns from a table
        2. Ordering query results
        3. Restricting query results
        4. Using column aliases
        5. Factoring out subqueries
      2. Nulls
        1. Using the NVL function
        2. Using the COALESCE function
      3. CASE Expressions
        1. Simple CASE expressions
        2. Searched CASE expressions
      4. Table Joins (Oracle8i and Earlier)
        1. Inner and outer joins
      5. Table Joins (Oracle9i and Higher)
        1. Sample tables
        2. Inner joins
        3. Join order
        4. Left and right outer joins
        5. Full outer joins
        6. Specifying join conditions
      6. Partition Outer Joins
      7. Summary Queries
        1. Using aggregate functions
        2. Using GROUP BY
        3. Restricting summarized results
        4. Using ALL and DISTINCT
      8. Unions
      9. Partition Operations
      10. Flashback Queries
    5. Modeling Data
      1. MODEL Clause Syntax
      2. MODEL Clause Functions
      3. MODEL Clause Example
    6. Inserting Data
      1. Inserting One Row
      2. Inserting the Results of a Query
      3. Multitable Inserts (Oracle9i and Higher)
    7. Updating Data
      1. Simple Updates
      2. Noncorrelated Subqueries in the SET Clause
      3. Correlated Subqueries in the SET Clause
    8. Deleting Data
      1. Simple Deletes
      2. Deleting All Rows (TRUNCATE)
      3. Deleting Duplicate Rows
    9. Merging Data (Oracle9i and Higher)
    10. Transaction Management
      1. SET TRANSACTION
      2. SAVEPOINT
      3. COMMIT
      4. ROLLBACK
    11. Formatting Text Reports
      1. Column Headings
      2. Column Formats
      3. Page Width and Length
      4. Page Headers and Footers
        1. Defining a title
        2. Getting the date into a title
      5. Page Breaks
      6. Report Breaks
        1. The BREAK command
        2. The COMPUTE command
    12. Formatting HTML Reports
      1. The Concatenation Approach
      2. Generating Full Pages
        1. Controlling page format
        2. Controlling column format
    13. Tuning SQL
      1. Creating the Plan Table
      2. Explaining a Query
        1. EXPLAIN PLAN syntax
        2. EXPLAIN PLAN example
      3. Using DBMS_XPLAN
      4. Querying the Plan Table
      5. Using Optimizer Hints
        1. Hint query blocks
        2. Optimizer goal hints
        3. Access method hints
        4. Join order hints
        5. Join operation hints
        6. Query transformation hints
        7. Miscellaneous hints
    14. SQL*Plus Format Elements
      1. Formatting Numbers
      2. Formatting Character Strings
      3. Formatting Dates
    15. SQL*Plus Command Reference
      1. Comment Delimiters (/*...*/)
      2. Double Hyphen ( -- )
      3. At Sign (@)
      4. Double At Sign (@@)
      5. Forward Slash (/)
      6. ACCEPT
      7. APPEND
      8. ARCHIVE LOG
      9. ATTRIBUTE
      10. BREAK
      11. BTITLE
      12. CHANGE
      13. CLEAR
      14. COLUMN
      15. COMPUTE
      16. CONNECT
      17. COPY
      18. DEFINE
      19. DEL
      20. DESCRIBE
      21. DISCONNECT
      22. EDIT
      23. EXECUTE
      24. EXIT
      25. GET
      26. HELP
      27. HOST
      28. INPUT
      29. LIST
      30. PASSWORD
      31. PAUSE
      32. PRINT
      33. PROMPT
      34. QUIT
      35. RECOVER
        1. Oracle9i Database Release 2 syntax
        2. Oracle Database 10g syntax
      36. REMARK
      37. REPFOOTER
      38. REPHEADER
      39. RUN
      40. SAVE
      41. SET
        1. Parameter settings
      42. SHOW
      43. SHUTDOWN
      44. SPOOL
      45. START
      46. STARTUP
      47. STORE
      48. TIMING
      49. TTITLE
      50. UNDEFINE
      51. VARIABLE
      52. WHENEVER
  3. A. O’reilly®: Oracle SQL*Plus Pocket Reference
  4. Index
  5. About the Author
  6. Copyright

Product information

  • Title: Oracle SQL*Plus Pocket Reference, 3rd Edition
  • Author(s): Jonathan Gennick
  • Release date: November 2004
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596008857