book

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

by Jonathan Gennick

November 2004

Intermediate to advanced

584 pages

15h 29m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Why I Wrote This Book

1.1. What Is SQL*Plus?1.1.1. Uses for SQL*Plus1.1.2. SQL*Plus's Relation to SQL, PL/SQL, and the Oracle Database
1.4.1. The Data Model1.4.2. The Tables1.4.2.1. EMPLOYEE table1.4.2.2. PROJECT table1.4.2.3. PROJECT_HOURS table1.4.3. The Data1.4.3.1. EMPLOYEE table1.4.3.2. PROJECT table1.4.3.3. PROJECT_HOURS table1.4.4. Loading the Sample Data1.4.4.1. Step 1: Download and unzip the script files1.4.4.2. Step 2: Start SQL*Plus1.4.4.3. Step 3: Log into your Oracle database1.4.4.4. Step 4: Run the bld_db.sql script file
2.1. Starting Command-Line SQL*Plus2.1.1. Connecting to a Default Database2.1.2. Connecting to a Remote Database2.1.3. Specifying Login Details on the Command Line
2.3.1. EXIT2.3.2. PASSWORD2.3.3. HELP2.3.4. DESCRIBE2.3.5. CONNECT2.3.6. DISCONNECT2.3.7. HOST
2.5.1. What Is a PL/SQL Block?2.5.2. Executing a PL/SQL Block2.5.2.1. Where's the output?2.5.2.2. Rules for entering PL/SQL blocks2.5.3. Executing a Single PL/SQL Statement
2.7.1. The Current Line2.7.2. Line-Editing Commands2.7.2.1. Getting a statement into the buffer2.7.2.2. LIST2.7.2.3. APPEND2.7.2.4. CHANGE2.7.2.5. DEL2.7.2.6. INPUT2.7.2.7. Retyping a line
2.8.1. If Your Statement Has an Error2.8.2. Doing It Again
2.9.1. SAVE2.9.2. GET
2.10.1. Choosing Your Editor2.10.2. Invoking the Editor2.10.3. Beware Editing Conflicts!2.10.4. Formatting Your Command2.10.5. Getting Back to SQL*Plus
3.1. Starting iSQL*Plus3.1.1. Starting the iSQL*Plus Server3.1.2. Using a Supported Browser3.1.3. Connecting to a Database3.1.4. Connecting as an Administrator3.1.4.1. Creating iSQL*Plus DBA users3.1.4.2. Authenticating to iSQL*Plus as a DBA3.1.5. Ending a Session
3.3.1. Paging Through Results3.3.2. Getting Output as Plain Text
3.5.1. Using the @ Command3.5.2. Using the Load Script Button
4.1. Data Manipulation Statements4.1.1. Inserting Data into a Table4.1.2. Retrieving Data from a Table4.1.2.1. Multiple conditions4.1.2.2. Negating conditions4.1.2.3. Table aliases4.1.2.4. Column aliases4.1.3. Updating Data with New Values4.1.4. Deleting Data from a Table4.1.5. Merging Data into a Table
4.2.1. Protecting Data Integrity4.2.2. Backing Out of Unwanted Changes4.2.3. You're Always Using Transactions4.2.4. Understanding Transaction Types
4.3.1. Detecting Nulls4.3.2. Nulls in Expressions
4.4.1. Inner Joins4.4.2. Outer Joins4.4.3. Full Outer Joins
4.6.1. The GROUP BY Clause4.6.2. The HAVING Clause
4.7.1. Treating Rowsets as Tables4.7.2. Testing for Representation4.7.3. Generating Data for INSERTs and UPDATEs
5.1. Following a Methodology
5.3.1. Step 1: Formulate the Query5.3.2. Step 2: Format the Columns5.3.2.1. Column headings5.3.2.2. Numeric display formats5.3.2.3. Text display formats5.3.2.4. Report output after formatting the columns5.3.3. Step 3: Add Page Headers and Footers5.3.3.1. The top title5.3.3.2. The bottom title5.3.3.3. Setting the line width5.3.3.4. Report output with page titles5.3.4. Step 4: Format the Page5.3.4.1. How many lines on a page?5.3.4.2. Setting the pagesize5.3.4.3. Setting the page advance5.3.5. Step 5: Print It5.3.5.1. Spooling to a file5.3.5.2. The final script5.3.5.3. Executing the report5.3.5.4. Printing the file
5.4.1. Suppressing Duplicate Column Values5.4.2. Page and Line Breaks5.4.2.1. Adding a page break5.4.2.2. Adding a line break5.4.2.3. Report output with page and line breaks5.4.3. Master/Detail Formatting5.4.3.1. Retrieve the employee information into substitution variables5.4.3.2. Modify the page heading to print the employee name and ID5.4.3.3. Revisit the report width and the width of the remaining fields5.4.3.4. Printing data in a page footer
6.1. Getting the Data into an HTML Table
6.2.1. Using SQL*Plus's Default Formatting6.2.2. Taking Control of the Page Format
7.1. Totals and Subtotals7.1.1. Printing Subtotals7.1.2. Printing Grand Totals7.1.2.1. Grand totals and pagination7.1.2.2. Grand totals and the final detail record7.1.2.3. Grand totals and the lack of a label
7.2.1. Getting the Date from Oracle7.2.2. Formatting the Date
7.6.1. A Typical Union Example7.6.2. The UNION Query7.6.3. The Final Report
8.1. Why Write Scripts?
8.2.1. What Is a Substitution Variable?8.2.2. Using Single-Ampersand Variables8.2.2.1. The report for one specific employee8.2.2.2. Generalizing the report with substitution variables8.2.2.3. Running the report8.2.2.4. When TERMOUT is off8.2.3. Using Double-Ampersand Variables8.2.3.1. An example that prompts twice for the same value8.2.3.2. A modified example that prompts once8.2.3.3. A final caveat
8.3.1. The ACCEPT Command8.3.2. The PROMPT Command8.3.2.1. Using PROMPT to summarize the script8.3.2.2. Using PROMPT to explain the output
8.4.1. Turning Off Verification8.4.2. Turning Off Feedback8.4.3. Turning Off Command Echoing8.4.4. Turning Off All Terminal Output
8.5.1. Invoking a Script from the Command Line8.5.2. Accessing Command-Line Arguments8.5.3. Specifying a Search Path for Scripts8.5.4. Placing SQL*Plus Commands into a Shell Script8.5.5. Creating a Windows Shortcut8.5.5.1. Starting the SQL*Plus executable8.5.5.2. Creating the shortcut8.5.6. Executing a Script Over the Internet
8.6.1. The Escape Character8.6.1.1. Enabling the escape feature8.6.1.2. Escaping an ampersand8.6.1.3. Changing the escape character8.6.2. The Concatenation Character8.6.2.1. Turning off the concatenation feature8.6.2.2. Changing the concatenation character8.6.3. Enabling and Disabling Substitution8.6.4. Changing the Substitution Variable Prefix Character
8.7.1. The REMARK Command8.7.2. The /* and */ Delimiters8.7.3. Double Hyphens (- -)8.7.4. Substitution Within Comments
9.1. Types of Output Files9.1.1. Delimited Files9.1.2. Fixed-Width Files9.1.3. DML Files9.1.4. DDL Files
9.3.1. Formulate the Query9.3.2. Format the Data9.3.2.1. Comma-delimited9.3.2.2. Fixed-width9.3.2.3. DML9.3.2.4. DDL9.3.3. Spool the Extract to a File9.3.4. Make Your Extract Script User-Friendly
9.5.1. Executing DDL and DML9.5.2. Running SQL*Loader9.5.2.1. The control file9.5.2.2. Building a control file for comma-delimited data9.5.2.3. Building a control file for fixed-width data9.5.2.4. Loading the data9.5.3. Using an External Table9.5.3.1. Creating a directory9.5.3.2. Creating an external table9.5.3.3. Loading the data
10.1. The DESCRIBE Command10.1.1. Describing a Table10.1.2. Describing Stored Functions and Procedures10.1.3. Describing Packages and Object Types10.1.4. Why DESCRIBE Is Not Enough
10.2.1. What Is the Data Dictionary?10.2.2. The View Types: user, all, and dba
10.3.1. Listing Tables You Own10.3.1.1. The recycle bin10.3.1.2. Tables owned by other users10.3.1.3. External tables10.3.1.4. Object tables10.3.1.5. A combined list of tables10.3.2. Listing Column Definitions for a Table
10.4.1. Check Constraints10.4.2. Primary Key and Unique Constraints10.4.3. Foreign Key Constraints
10.9.1. Running the Script10.9.2. When the Parameter Is Omitted10.9.3. Separating Owner and Table Names10.9.4. Generating the Index Headings
11.1. Bind Variables11.1.1. Declaring Bind Variables11.1.2. Using Bind Variables and Substitution Variables Together11.1.2.1. From substitution to bind11.1.2.2. From bind to substitution11.1.3. Displaying the Contents of a Bind Variable11.1.3.1. Using the PRINT command11.1.3.2. PRINTing CLOB variables11.1.3.3. PRINTing REFCURSOR variables11.1.3.4. SELECTing a bind variable11.1.4. When and How to Use Bind Variables11.1.4.1. Calling PL/SQL procedures and functions from SQL*Plus11.1.4.2. Using REFCURSOR variables11.1.4.3. Testing application queries
11.2.1. Simulating Branching by Adjusting the WHERE Clause11.2.2. Simulate Branching by Using REFCURSOR Variables11.2.3. Branching Using a Multilevel File Structure11.2.4. Using SQL to Write SQL11.2.5. Using PL/SQL11.2.6. Using an Operating-System Scripting Language
11.3.1. Recursive Execution11.3.2. Looping Within PL/SQL
11.4.1. Validating Input with ACCEPT11.4.1.1. ACCEPTing numeric values11.4.1.2. ACCEPTing date values11.4.2. Validating Input with SQL11.4.3. Parsing Input with SQL
11.5.1. The WHENEVER Command11.5.1.1. WHENEVER SQLERROR11.5.1.2. Capturing SQL*Plus return codes11.5.1.3. PL/SQL errors and WHENEVER11.5.1.4. WHENEVER OSERROR
12.1. Using SQL*Plus Timers12.1.1. The SET TIMING Command12.1.2. The TIMING Command12.1.2.1. Starting and stopping a timer12.1.2.2. Displaying the value of a timer12.1.2.3. Nesting timers12.1.2.4. Finding out how many timers you have going12.1.2.5. Stopping all timers
12.2.1. Creating the Plan Table12.2.2. Explaining a Query12.2.3. Interpreting the Results12.2.3.1. Using DBMS_XPLAN to display an execution plan12.2.3.2. Using a SELECT statement to display an execution plan12.2.3.3. Making sense of the results
12.3.1. Granting Access to the Performance Views12.3.2. Executing a Query with AUTOTRACE On12.3.2.1. Showing statistics and the plan12.3.2.2. Showing only the plan12.3.2.3. Suppressing the query output12.3.2.4. Turning AUTOTRACE off
12.4.1. Knowing Good Results from Bad12.4.2. Creating Indexes12.4.3. Rewriting the Query12.4.4. Using Hints12.4.4.1. Syntax for a hint12.4.4.2. Specifying table and index names12.4.4.3. Hint conflicts and applicability12.4.4.4. Hint query blocks12.4.5. Oracle's Hint Syntax12.4.5.1. Optimizer goal hints12.4.5.2. Access method hints12.4.5.3. Query transformation hints12.4.5.4. Join order hints12.4.5.5. Join operation hints12.4.5.6. Parallel execution hints12.4.5.7. Other hints
13.1. What Is the Product User Profile?13.1.1. Why Does the Product User Profile Exist?13.1.2. The product_profile Table13.1.3. How the Product User Profile Works13.1.4. Product User Profile Limitations13.1.4.1. Issues related to PL/SQL13.1.4.2. Issues related to roles
13.2.1. Creating the Profile Table13.2.2. Limiting Access to Commands and Statements13.2.2.1. Commands and statements that can be disabled13.2.2.2. Disabling a command or statement13.2.2.3. Re-enabling a command or statement13.2.3. Limiting Access to Roles13.2.3.1. Disabling a role13.2.3.2. Re-enabling a role13.2.4. Reporting on the Product User Profile13.2.4.1. Listing all restrictions13.2.4.2. Listing restrictions for a particular user
14.1. SQL*Plus Settings You Can Control
14.2.1. Customizing the SQL*Plus Prompt14.2.2. Choosing an Editor
14.3.1. Specifying a Search Path for Scripts14.3.2. Designating a Default Net Service Name14.3.3. Controlling Language and Character Set
A.1. The Command to Invoke SQL*Plus
B.1. Formatting Numbers

Overview

Despite its wide availability and usage, few developers and DBAs have mastered the true power of Oracle SQLPlus. 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 SQLPlus: The Definitive Guide clearly describes how to perform, step-by-step, all of the tasks that Oracle developers and DBAs want to perform with SQLPlus--and maybe some you didn't realize you could perform.With Oracle SQLPlus: 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 SQLPlus 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 SQLPlus. 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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Publisher Resources

ISBN: 0596007469Supplemental Content Errata Page

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

by Jonathan Gennick

Overview

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

Oracle SQL*Plus: The Definitive Guide

Oracle PL/SQL by Example, 6th Edition

Oracle PL/SQL Programming, 6th Edition

Oracle PL/SQL Programming, 5th Edition

Publisher Resources