You are previewing Oracle SQL*Plus Pocket Reference, 3rd Edition.
O'Reilly logo
Oracle SQL*Plus Pocket Reference, 3rd Edition

Book Description

This portable, quick-reference guide is a must-have for developers and DBAs looking to maximize use of SQLPlus. The new third edition of the Oracle SQLPlus Pocket Reference updates the SQLPlus syntax and format options for Oracle Database 10g, Oracle's latest database release. It also describes both command-line and browser-based SQLPlus, and provides information on new SQL features.

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