Oracle SQL Plus

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.

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 jonathan@gennick.com, and you can learn more about him personally by visiting his website at http://gennick.com.

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

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Summary

Author Biography

Table of Contents

Supplemental Materials