book

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

Name: Oracle SQL*Plus: The Definitive Guide, 2nd Edition
Author: Jonathan Gennick
ISBN: 9780596552619

by Jonathan Gennick

November 2004

Intermediate to advanced

584 pages

15h 29m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Oracle SQL*Plus: The Definitive Guide, 2nd Edition
SPECIAL OFFER: Upgrade this ebook with O’Reilly
A Note Regarding Supplemental Files
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
1. Introduction to SQL*Plus
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.2. History of SQL*Plus
1.3. Why Master SQL*Plus?
1.4. Creating and Loading the Sample Tables
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. Command-Line SQL*Plus
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.2. Starting Windows SQL*Plus
2.3. Some Basic SQL*Plus Commands
2.3.1. EXIT2.3.2. PASSWORD2.3.3. HELP2.3.4. DESCRIBE2.3.5. CONNECT2.3.6. DISCONNECT2.3.7. HOST
2.4. Running SQL Queries
2.5. Working with PL/SQL
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.6. The SQL Buffer
2.7. Line Editing
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. Executing the Statement in the Buffer
2.8.1. If Your Statement Has an Error2.8.2. Doing It Again
2.9. Saving and Retrieving the Buffer
2.9.1. SAVE2.9.2. GET
2.10. The EDIT Command
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
2.11. Executing a Script
2.12. The Working Directory
3. Browser-Based 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.2. Executing SQL*Plus Commands
3.3. Running SQL Queries
3.3.1. Paging Through Results3.3.2. Getting Output as Plain Text
3.4. Working with PL/SQL
3.5. Executing Scripts
3.5.1. Using the @ Command3.5.2. Using the Load Script Button
3.6. iSQL*Plus Command History
4. A Lightning SQL Tutorial
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. Transactions
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. The Concept of Null
4.3.1. Detecting Nulls4.3.2. Nulls in Expressions
4.4. Table Joins
4.4.1. Inner Joins4.4.2. Outer Joins4.4.3. Full Outer Joins
4.5. Sorting Query Results
4.6. Grouping and Summarizing
4.6.1. The GROUP BY Clause4.6.2. The HAVING Clause
4.7. Subqueries
4.7.1. Treating Rowsets as Tables4.7.2. Testing for Representation4.7.3. Generating Data for INSERTs and UPDATEs
4.8. Unions
4.9. To Learn More
5. Generating Reports with SQL*Plus
5.1. Following a Methodology
5.2. Saving Your Work
5.3. Designing a Simple Report
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. Master/Detail Reports
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. Creating HTML Reports
6.1. Getting the Data into an HTML Table
6.2. Generating the Entire Page
6.2.1. Using SQL*Plus's Default Formatting6.2.2. Taking Control of the Page Format
6.3. Another Approach to Headers
6.4. Master/Detail Reports in HTML
7. Advanced Reports
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. Getting the Current Date into a Header
7.2.1. Getting the Date from Oracle7.2.2. Formatting the Date
7.3. Report Headers and Footers
7.4. Formatting Object Columns
7.5. Summary Reports
7.6. Taking Advantage of Unions
7.6.1. A Typical Union Example7.6.2. The UNION Query7.6.3. The Final Report
8. Writing SQL*Plus Scripts
8.1. Why Write Scripts?
8.2. Using Substitution Variables
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. Prompting for Values
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. Cleaning Up the Display
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. Executing a Script
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. Controlling Variable Substitution
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. Commenting Your Scripts
8.7.1. The REMARK Command8.7.2. The /* and */ Delimiters8.7.3. Double Hyphens (- -)8.7.4. Substitution Within Comments
8.8. Resetting Your SQL*Plus Environment
8.9. Scripting Issues with iSQL*Plus
9. Extracting and Loading Data
9.1. Types of Output Files9.1.1. Delimited Files9.1.2. Fixed-Width Files9.1.3. DML Files9.1.4. DDL Files
9.2. Limitations of SQL*Plus
9.3. Extracting the Data
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.4. An Excel-Specific HTML Hack
9.5. Reloading the Data
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. Exploring Your Database
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. Oracle's Data Dictionary Views
10.2.1. What Is the Data Dictionary?10.2.2. The View Types: user, all, and dba
10.3. Tables
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. Table Constraints
10.4.1. Check Constraints10.4.2. Primary Key and Unique Constraints10.4.3. Foreign Key Constraints
10.5. Indexes
10.6. Triggers
10.7. Synonyms
10.8. Table Security
10.9. Scripting the Data Dictionary
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
10.10. Using SQL to Write SQL
10.11. The Master Key
11. Advanced Scripting
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. Branching in SQL*Plus
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. Looping in SQL*Plus
11.3.1. Recursive Execution11.3.2. Looping Within PL/SQL
11.4. Validating and Parsing User Input
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. Error Handling
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
11.6. Returning Values to Unix
12. Tuning and Timing
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. Using EXPLAIN PLAN
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. Using AUTOTRACE
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. Improving on EXPLAIN PLAN Results
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
12.5. Where to Find More Tuning Information
13. The Product User Profile
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. Using the Product User Profile
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. Customizing Your SQL*Plus Environment
14.1. SQL*Plus Settings You Can Control
14.2. The Site and User Profiles
14.2.1. Customizing the SQL*Plus Prompt14.2.2. Choosing an Editor
14.3. Environment Variables That Affect SQL*Plus
14.3.1. Specifying a Search Path for Scripts14.3.2. Designating a Default Net Service Name14.3.3. Controlling Language and Character Set
14.4. Windows GUI SQL*Plus
14.5. iSQL*Plus User Preferences
A. SQL*Plus Command Reference
A.1. The Command to Invoke SQL*Plus
A.2. Commands You Can Issue Within SQL*Plus
Comment Delimiters (/* . . . */)
Double Hyphen (- -)
At Sign (@)
Double At Sign (@@)
Forward Slash (/)
ACCEPT
APPEND
ARCHIVE LOG
ATTRIBUTE
BREAK
BTITLE
CHANGE
CLEAR
COLUMN
COMPUTE
CONNECT
COPY
DEFINE
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
GET
HELP
HOST
INPUT
LIST
PASSWORD
PAUSE
PRINT
PROMPT
QUIT
RECOVER
REMARK
REPFOOTER
REPHEADER
RUN
SAVE
SET APPINFO
SET ARRAYSIZE
SET AUTOCOMMIT
SET AUTOPRINT
SET AUTORECOVERY
SET AUTOTRACE
SET BLOCKTERMINATOR
SET BUFFER
SET CLOSECURSOR
SET CMDSEP
SET COLSEP
SET COMPATIBILITY
SET CONCAT
SET COPYCOMMIT
SET COPYTYPECHECK
SET DEFINE
SET DESCRIBE
SET DOCUMENT
SET ECHO
SET EDITFILE
SET EMBEDDED
SET ESCAPE
SET FEEDBACK
SET FLAGGER
SET FLUSH
SET HEADING
SET HEADSEP
SET INSTANCE
SET LINESIZE
SET LOBOFFSET
SET LOGSOURCE
SET LONG
SET LONGCHUNKSIZE
SET MARKUP
SET MAXDATA
SET NEWPAGE
SET NULL
SET NUMFORMAT
SET NUMWIDTH
SET PAGESIZE
SET PAUSE
SET RECSEP
SET RECSEPCHAR
SET SCAN
SET SERVEROUTPUT
SET SHIFTINOUT
SET SHOWMODE
SET SPACE
SET SQLBLANKLINES
SQLCASE
SET SQLCONTINUE
SET SQLNUMBER
SET SQLPLUSCOMPATIBILITY
SET SQLPREFIX
SET SQLPROMPT
SET SQLTERMINATOR
SET SUFFIX
SET TAB
SET TERMOUT
SET TIME
SET TIMING
SET TRIMOUT
SET TRIMSPOOL
SET TRUNCATE
SET UNDERLINE
SET VERIFY
SET WRAP
SHOW
SHUTDOWN
SPOOL
START
STARTUP
STORE
TIMING
TTITLE
UNDEFINE
VARIABLE
WHENEVER
B. SQL*Plus Format Elements
B.1. Formatting Numbers
B.2. Formatting Character Strings
B.3. Formatting Dates
About the Author
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly

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

Validating and Parsing User Input

Whenever you ask a user for input, you run the risk that it won't make sense. Maybe you are asking for a number, and the user types in some letters. Maybe you are asking for a date, and the user enters a bad value for the month. The SQL*Plus ACCEPT command offers some support for dealing with these situations. You can do more, if you need to, with some creative use of SQL.

Validating Input with ACCEPT

The ACCEPT command implements several options to help you validate user input. Throughout most of this book, the ACCEPT commands have been mostly written like this:

ACCEPT my_variable PROMPT 'Enter a value >'

This is a least-common-denominator version of the ACCEPT command that should work with any release of SQL*Plus. It takes whatever string the user types in and assigns it to the variable. If you need to go beyond this, ACCEPT allows you to specify a datatype and does not accept input that doesn't convert to the type you specify. ACCEPT also allows you to specify a format string that the input data must match. You can take good advantage of these options to make your scripts more bulletproof.

Tip

The ACCEPT command options illustrated in the following subsections apply to SQL*Plus Versions 8.0.3 and above. Not all options will be available under previous releases. The ACCEPT command is one that has changed a lot over the years. Check the documentation for the release you are using to see which options are available to you.

ACCEPTing numeric values

If you ...

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.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596007469Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Jonathan Gennick

Validating and Parsing User Input

Validating Input with ACCEPT

Tip

ACCEPTing numeric values

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.

More than 5,000 organizations count on O’Reilly

Julian F.

Addison B.

Amir M.

Mark W.

You might also like

Oracle SQL*Plus: The Definitive Guide

Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, 5th Edition

Oracle PL/SQL Programming, 6th Edition

Publisher Resources