Oracle SQL Plus

by Gennick, Jonathan

• ISBN13: 9780596008857
• ISBN10: 0596008856
• Edition: 3rd
• Format: Paperback
• Copyright: 2004-11-30
• Publisher: Oreilly & Associates Inc

Summary

This small slim volume is a concise guide to using Oracle's SQL*Plus tool which acts as a front-end to the SQL querying language. Oracle SQL*Plus Pocket Reference does not attempt to teach you how to use the language but provides a quick reference to the commands available and the ways in which they can be pressed into service.It's terse but informative, as befits a pocket guide, and so small that the usually entertaining O'Reilly colophon is omitted--which is a shame. The first half covers basic interactions such as string handling and naming variables, moving on to deal with selecting data, formatting reports and tuning SQL statements plus a section on formatting elements that let you do almost anything with number and date displays, including showing dates with a BC or AD indicator. In the tuning section there is brief coverage of execution plans for SQL statements and of how to tune the Oracle optimiser that puts those plans into operation. The second half forms the SQL command reference section, everything from ACCEPT to WHENEVER.

Author Biography

Jonathan Gennick is an O'Reilly Media, Inc. 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 Media, Inc., 2001); Oracle SQLPlus Pocket Reference (O'Reilly Media, Inc., 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly Media, Inc., 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly Media, Inc., 2002) and editing Practical PostgreSQL (O'Reilly Media, Inc., 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

Introduction	1	(2)
Command-Line SQL*Plus	3	(7)
Starting SQL*Plus	3	(3)
Entering Commands	6	(3)
Strings in SQL*Plus Commands	9	(1)
Specifying Filenames	9	(1)
Naming Variables	10	(1)
Browser-Based SQL*Plus	10	(9)
Starting and Stopping iSQL*Plus	11	(1)
Connecting to iSQL*Plus	11	(1)
Connecting to a Database	12	(1)
Connecting as SYSDBA	13	(2)
Creating SYSDBA Users	15	(1)
Entering and Executing Commands	16	(1)
Getting Output as Plain Text	16	(1)
Executing Scripts	17	(1)
Disconnecting from iSQL*Plus	18	(1)
Selecting Data	19	(25)
The Select Statement	19	(4)
Nulls	23	(2)
Case Expressions	25	(2)
Table Joins (Oracle8i and Earlier)	27	(1)
Table Joins (Oracle9i and Higher)	28	(7)
Partition Outer Joins	35	(3)
Summary Queries	38	(3)
Unions	41	(1)
Partition Operations	42	(1)
Flashback Queries	43	(1)
Modeling Data	44	(8)
Model Clause Syntax	44	(5)
Model Clause Functions	49	(1)
Model Clause Example	50	(2)
Inserting Data	52	(3)
Inserting One Row	53	(1)
Inserting the Results of a Query	53	(1)
Multitable Inserts (Oracle9i and Higher)	54	(1)
Updating Data	55	(2)
Simple Updates	55	(1)
Noncorrelated Subqueries in the Set Clause	56	(1)
Correlated Subqueries in the Set Clause	56	(1)
Deleting Data	57	(2)
Simple Deletes	57	(1)
Deleting All Rows (Truncate)	57	(1)
Deleting Duplicate Rows	58	(1)
Merging Data (Oracle9i and Higher)	59	(3)
Transaction Management	62	(3)
Set Transaction	63	(1)
Savepoint	64	(1)
Commit	64	(1)
Rollback	64	(1)
Formatting Text Reports	65	(5)
Column Headings	65	(1)
Column Formats	66	(1)
Page Width and Length	66	(1)
Page Headers and Footers	67	(1)
Page Breaks	68	(1)
Report Breaks	68	(2)
Formatting HTML Reports	70	(6)
The Concatenation Approach	71	(3)
Generating Full Pages	74	(2)
Tuning SQL	76	(13)
Creating the Plan Table	77	(1)
Explaining a Query	78	(2)
Using DBMS_XPLAN	80	(1)
Querying the Plan Table	81	(2)
Using Optimizer Hints	83	(6)
SQL*Plus Format Elements	89	(7)
Formatting Numbers	90	(2)
Formatting Character Strings	92	(1)
Formatting Dates	93	(3)
SQL*Plus Command Reference	96	(43)
Index	139