Oracle SQL*Plus: The Definitive Guide: The Definitive Guide

by Jonathan Gennick

Overview

Despite its wide availability and usage, few developers and DBAs have mastered the true power of Oracle SQL*Plus. This bestselling book—now updated for Oracle 10g—is the only in-depth guide to this interactive query tool for writing SQL scripts. It's an essential resource for any Oracle user. The new second edition of Oracle SQL*Plus: The Definitive Guide clearly describes how to perform, step-by-step, all of the tasks that Oracle developers and DBAs want to perform with SQL*Plus—and maybe some you didn't realize you could perform. With Oracle SQL*Plus: The Definitive Guide, you'll expertly:

• write and execute script files
• generate ad hoc reports
• extract data from the database
• query the data dictionary tables
• customize an SQL*Plus environment
• and much more

It also includes a handy quick reference to all of its syntax options and an often-requested chapter on SQL itself, along with a clear, concise, and complete introduction. This book is truly the definitive guide to SQL*Plus. It's an indispensable resource for those who are new to SQL*Plus, a task-oriented learning tool for those who are already using it, and an immediately useful quick reference for every user. If you want to leverage the full power and flexibility of this popular Oracle tool, you'll need this book.

Product Details

ISBN-13:	9780596007461
Publisher:	O'Reilly Media, Incorporated
Publication date:	11/12/2004
Series:	Definitive Guides
Edition description:	Second Edition
Pages:	582
Product dimensions:	7.00(w) x 9.19(h) x 1.11(d)

About the Author

Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQL*Loader (O'Reilly & Associates, 2001); Oracle SQL*Plus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews Universityin Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at [e-mail], and you can learn more about him personally by visiting his website at http://gennick.com.

Table of Contents

Preface; Why I Wrote This Book; Objectives of This Book; What's New in the Second Edition?; Which Platform and Version?; Structure of This Book; Obtaining the Scripts and Sample Data; What About Those Names?; Conventions Used in This Book; Using Code Examples; Comments and Questions; Acknowledgments from the First Edition; Second Edition Acknowledgments; Chapter 1: Introduction to SQL*Plus; 1.1 What Is SQL*Plus?; 1.2 History of SQL*Plus; 1.3 Why Master SQL*Plus?; 1.4 Creating and Loading the Sample Tables; Chapter 2: Command-Line SQL*Plus; 2.1 Starting Command-Line SQL*Plus; 2.2 Starting Windows SQL*Plus; 2.3 Some Basic SQL*Plus Commands; 2.4 Running SQL Queries; 2.5 Working with PL/SQL; 2.6 The SQL Buffer; 2.7 Line Editing; 2.8 Executing the Statement in the Buffer; 2.9 Saving and Retrieving the Buffer; 2.10 The EDIT Command; 2.11 Executing a Script; 2.12 The Working Directory; Chapter 3: Browser-Based SQL*Plus; 3.1 Starting iSQL*Plus; 3.2 Executing SQL*Plus Commands; 3.3 Running SQL Queries; 3.4 Working with PL/SQL; 3.5 Executing Scripts; 3.6 iSQL*Plus Command History; Chapter 4: A Lightning SQL Tutorial; 4.1 Data Manipulation Statements; 4.2 Transactions; 4.3 The Concept of Null; 4.4 Table Joins; 4.5 Sorting Query Results; 4.6 Grouping and Summarizing; 4.7 Subqueries; 4.8 Unions; 4.9 To Learn More; Chapter 5: Generating Reports with SQL*Plus; 5.1 Following a Methodology; 5.2 Saving Your Work; 5.3 Designing a Simple Report; 5.4 Master/Detail Reports; Chapter 6: Creating HTML Reports; 6.1 Getting the Data into an HTML Table; 6.2 Generating the Entire Page; 6.3 Another Approach to Headers; 6.4 Master/Detail Reports in HTML; Chapter 7: Advanced Reports; 7.1 Totals and Subtotals; 7.2 Getting the Current Date into a Header; 7.3 Report Headers and Footers; 7.4 Formatting Object Columns; 7.5 Summary Reports; 7.6 Taking Advantage of Unions; Chapter 8: Writing SQL*Plus Scripts; 8.1 Why Write Scripts?; 8.2 Using Substitution Variables; 8.3 Prompting for Values; 8.4 Cleaning Up the Display; 8.5 Executing a Script; 8.6 Controlling Variable Substitution; 8.7 Commenting Your Scripts; 8.8 Resetting Your SQL*Plus Environment; 8.9 Scripting Issues with iSQL*Plus; Chapter 9: Extracting and Loading Data; 9.1 Types of Output Files; 9.2 Limitations of SQL*Plus; 9.3 Extracting the Data; 9.4 An Excel-Specific HTML Hack; 9.5 Reloading the Data; Chapter 10: Exploring Your Database; 10.1 The DESCRIBE Command; 10.2 Oracle's Data Dictionary Views; 10.3 Tables; 10.4 Table Constraints; 10.5 Indexes; 10.6 Triggers; 10.7 Synonyms; 10.8 Table Security; 10.9 Scripting the Data Dictionary; 10.10 Using SQL to Write SQL; 10.11 The Master Key; Chapter 11: Advanced Scripting; 11.1 Bind Variables; 11.2 Branching in SQL*Plus; 11.3 Looping in SQL*Plus; 11.4 Validating and Parsing User Input; 11.5 Error Handling; 11.6 Returning Values to Unix; Chapter 12: Tuning and Timing; 12.1 Using SQL*Plus Timers; 12.2 Using EXPLAIN PLAN; 12.3 Using AUTOTRACE; 12.4 Improving on EXPLAIN PLAN Results; 12.5 Where to Find More Tuning Information; Chapter 13: The Product User Profile; 13.1 What Is the Product User Profile?; 13.2 Using the Product User Profile; Chapter 14: Customizing Your SQL*Plus Environment; 14.1 SQL*Plus Settings You Can Control; 14.2 The Site and User Profiles; 14.3 Environment Variables That Affect SQL*Plus; 14.4 Windows GUI SQL*Plus; 14.5 iSQL*Plus User Preferences; Appendix A: SQL*Plus Command Reference; A.1 The Command to Invoke SQL*Plus; A.2 Commands You Can Issue Within SQL*Plus; Appendix B: SQL*Plus Format Elements; B.1 Formatting Numbers; B.2 Formatting Character Strings; B.3 Formatting Dates; Colophon;