Oracle SQL*Plus

by Gennick, Jonathan

• ISBN13: 9780596007461
• ISBN10: 0596007469
• Edition: 2nd
• Format: Paperback
• Copyright: 2005-01-06
• Publisher: Oreilly & Associates Inc

Summary

Our new edition of this highly popular book describes how to perform, in step-by-step fashion, 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. Readers will learn how to 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. This edition also includes an often-requested chapter on SQL itself, along with a complete introduction. It's an indispensable resource for readers new to SQL*Plus, a task-oriented learning tool for those already using it, and a quick reference for all SQL*Plus users.

Author Biography

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 SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus 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 University in 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	xi
1. Introduction to SQL*Plus	1	(19)
What Is SQL*Plus?	1	(6)
History of SQL*Plus	7	(3)
Why Master SQL*Plus?	10	(1)
Creating and Loading the Sample Tables	11	(9)
2. Command-Line SQL*Plus	20	(50)
Starting Command-Line SQL*Plus	20	(6)
Starting Windows SQL*Plus	26	(3)
Some Basic SQL*Plus Commands	29	(6)
Running SQL Queries	35	(3)
Working With PL/SQL	38	(5)
The SQL Buffer	43	(1)
Line Editing	43	(11)
Executing the Statement in the Buffer	54	(4)
Saving and Retrieving the Buffer	58	(2)
The EDIT Command	60	(6)
Executing a Script	66	(1)
The Working Directory	67	(3)
3. Browser-Based SQL*Plus	70	(19)
Starting iSQL*Plus	70	(8)
Executing SQL*Plus Commands	78	(2)
Running SQL Queries	80	(2)
Working with PL/SQL	82	(1)
Executing Scripts	83	(3)
iSQL*Plus Command History	86	(3)
4. A Lightning SQL Tutorial	89	(39)
Data Manipulation Statements	89	(9)
Transactions	98	(4)
The Concept of Null	102	(4)
Table Joins	106	(7)
Sorting Query Results	113	(1)
Grouping and Summarizing	114	(4)
Subqueries	118	(6)
Unions	124	(3)
To Learn More	127	(1)
5. Generating Reports with SQL*Plus	128	(29)
Following a Methodology	128	(1)
Saving Your Work	129	(1)
Designing a Simple Report	129	(15)
Master/Detail Reports	144	(13)
6. Creating HTML Reports	157	(17)
Getting the Data into an HTML Table	157	(4)
Generating the Entire Page	161	(6)
Another Approach to Headers	167	(3)
Master/Detail Reports in HTML	170	(4)
7. Advanced Reports	174	(32)
Totals and Subtotals	174	(13)
Getting the Current Date into a Header	187	(2)
Report Headers and Footers	189	(4)
Formatting Object Columns	193	(3)
Summary Reports	196	(4)
Taking Advantage of Unions	200	(6)
8. Writing SQL*Plus Scripts	206	(37)
Why Write Scripts?	206	(1)
Using Substitution Variables	207	(8)
Prompting for Values	215	(5)
Cleaning Up the Display	220	(2)
Executing a Script	222	(9)
Controlling Variable Substitution	231	(4)
Commenting Your Scripts	235	(3)
Resetting Your SQL*Plus Environment	238	(2)
Scripting Issues with iSQL*Plus	240	(3)
9. Extracting and Loading Data	243	(30)
Types of Output Files	244	(1)
Limitations of SQL*Plus	245	(1)
Extracting the Data	246	(11)
An Excel-Specific HTML Hack	257	(2)
Reloading the Data	259	(14)
10. Exploring Your Database	273	(35)
The DESCRIBE Command	273	(5)
Oracle's Data Dictionary Views	278	(2)
Tables	280	(6)
Table Constraints	286	(6)
Indexes	292	(1)
Triggers	293	(2)
Synonyms	295	(1)
Table Security	296	(1)
Scripting the Data Dictionary	297	(6)
Using SQL to Write SQL	303	(3)
The Master Key	306	(2)
11. Advanced Scripting	308	(50)
Bind Variables	309	(16)
Branching in SQL*Plus	325	(12)
Looping in SQL*Plus	337	(4)
Validating and Parsing User Input	341	(8)
Error Handling	349	(6)
Returning Values to Unix	355	(3)
12. Tuning and Timing	358	(43)
Using SQL*Plus Timers	358	(5)
Using EXPLAIN PLAN Statements	363	(16)
Using AUTOTRACE	379	(6)
Improving on EXPLAIN PLAN Results	385	(14)
Where to Find More Tuning Information	399	(2)
13. The Product User Profile	401	(17)
What Is the Product User Profile?	401	(7)
Using the Product User Profile	408	(10)
14. Customizing Your SQL*Plus Environment	418	(11)
SQL*Plus Settings You Can Control	418	(1)
The Site and User Profiles	419	(3)
Environment Variables That Affect SQL*Plus	422	(3)
Windows GUI SQL*Plus	425	(1)
iSQL*Plus User Preferences	426	(3)
A. SQL*Plus Command Reference	429	(96)
B. SQL*Plus Format Elements	525	(8)
Index	533