Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Jonathan Gennick
Oracle SQL*Plus: The Definitive Guide
Oracle SQL*Plus: The Definitive Guide
Preface
Why I Wrote This Book
Objectives of This Book
Structure of This Book
Audience
Obtaining the Scripts and Sample Data
Conventions Used in This Book
Which Platform and Version?
How to Contact Us
Acknowledgments
1. Introduction to SQL*Plus
What Is SQL*Plus?
Uses for SQL*Plus
SQL*Plus’s Relation to SQL, PL/SQL and the Oracle Database
History of SQL*Plus
Why Master SQL*Plus?
Creating and Loading the Sample Tables
The Data Model
The Tables
The EMPLOYEE table
The PROJECT table
The PROJECT_HOURS table
The Data
The EMPLOYEE table
The PROJECT table
The PROJECT_HOURS table
Loading the Sample Data
Step 1: Download and unzip the script files
Step 2: Start SQL*Plus
Step 3: Log into your Oracle database
Step 4: Run the BLD_DB.SQL script file
2. Interacting with SQL*Plus
Starting SQL*Plus
Command-Line Versus GUI Versions
Operating System Authentication
Starting the GUI Version
Logging into a database
Making a mistake on the logon dialog
Missing the Product User Profile
Starting the Command-Line Version
Command-Line Options
Some Basic SQL*Plus Commands
EXIT
PASSWORD
HELP
DESCRIBE
CONNECT
DISCONNECT
Running SQL Queries
Working with PL/SQL
What Is a PL/SQL Block?
Executing a PL/SQL Block
Where’s the output?
Rules for entering PL/SQL blocks
Executing a Single PL/SQL Command
The SQL Buffer
Line Editing
The Current Line
Line-Editing Commands
Getting a statement into the buffer
LIST
APPEND
CHANGE
DEL
INPUT
Retyping a line
Executing the Statement in the Buffer
If Your Statement Has an Error
Doing It Again
Saving and Retrieving the Buffer
SAVE
GET
The EDIT Command
Invoking the Editor
Formatting Your Command
Getting Back to SQL*Plus
Executing a Script
The Working Directory
3. Generating Reports with SQL*Plus
Following a Methodology
Saving Your Work
Designing a Simple Report
Step 1: Formulate the Query
Step 2: Format the Columns
The COLUMN command
Column headings
Column display formats
Report output after formatting the columns
Step 3: Add Page Headers and Footers
The top title
The bottom title
Setting the line width
Report output with page titles
Step 4: Format the Page
How many lines on a page?
Setting the pagesize
Setting the page advance
Step 5: Print It!
Spooling to a file
The final script
Executing the report
Printing the file
Advanced Report Formatting
The BREAK Command
Suppressing Duplicate Column Values
Page and Line Breaks
Adding a page break
Adding a line break
Report output with page and line breaks
Master/Detail Formatting
Retrieve the employee information into user variables
Modify the page heading to print the employee name and ID
Revisit the report width and the width of the remaining fields
Printing data in a page footer
Totals and Subtotals
The COMPUTE Command
Syntax of the COMPUTE command
Printing computed values
COMPUTE commands are cumulative
Printing Subtotals
Print Grand Totals
Other Reporting Topics
Getting the Current Date into a Header
Getting the date from Oracle
Formatting the date
Report Headers and Footers
REPHEADER and REPFOOTER command syntax
An example
Formatting Object Columns
Summary Reports
Taking Advantage of Unions
A typical example
The UNION query
The final report
4. Writing SQL*Plus Scripts
Why Write Scripts?
Using Substitution Variables
What Is a Substitution Variable?
Using Single-Ampersand Variables
The report for one specific employee
Generalizing the report with substitution variables
Running the report
When TERMOUT is off
Using Double-Ampersand Variables
An example that prompts twice for the same value
A modified example that prompts once
A final caveat
Prompting for Values
The ACCEPT Command
Syntax for the ACCEPT command
Using ACCEPT to get the table name
The PROMPT Command
Syntax for the PROMPT command
Using PROMPT to summarize the script
Using PROMPT to explain the output
Cleaning Up the Display
Turning Verification Off
Turning Feedback Off
Turning Command Echoing Off
Turning Off All Terminal Output
Packaging Your Script
Creating a Windows Shortcut
Starting the SQL*Plus executable
Creating the shortcut
Creating a Unix Command
The DEFINE and UNDEFINE Commands
The DEFINE Command
Syntax for the DEFINE command
Defining a variable
Examining a variable
Listing all variables
Usage notes
The UNDEFINE Command
Syntax for the UNDEFINE command
Deleting a variable
Controlling Variable Substitution
The Escape Character
Turning on the escape feature
Escaping an ampersand
Changing the escape character
The Concatenation Character
Turning off the concatenation feature
Changing the concatenation character
Enabling and Disabling Substitution
Changing the Substitution Variable Prefix Character
Changing the substitution variable prefix character
Commenting Your Scripts
The REMARK Command
The /* and */ Delimiters
Double Hyphens (- -)
Substitution Within Comments
5. Extracting and Loading Data
Types of Output Files
Delimited Files
Fixed-Width Files
DML Files
DDL Files
Limitations of SQL*Plus
Extracting the Data
Formulate the Query
Format the Data
Comma-delimited
Fixed-width
DML
DDL
Spool the Extract to a File
Make Your Extract Script User-Friendly
Reloading the Data
Executing DDL and DML
Running SQL*Loader
The control file
Building a control file for comma-delimited data
Building a control file for fixed-width data
Loading the data
6. Exploring Your Database
The DESCRIBE Command
Syntax for DESCRIBE
Describing a Table
Describing Stored Functions and Procedures
Describing Packages and Object Types
Why DESCRIBE Is Not Enough
Oracle’s Data Dictionary Views
The View Types: ALL, DBA, and USER
Dictionary Views Used in This Chapter
The ALL_TABLES view
The ALL_TAB_COLUMNS view
The ALL_CONSTRAINTS view
The ALL_CONS_COLUMNS view
The ALL_INDEXES view
The ALL_IND_COLUMNS view
The ALL_TRIGGERS view
The ALL_TRIGGER_COLS view
The ALL_SYNONYMS view
The ALL_TAB_PRIVS view
The ALL_COL_PRIVS view
Tables
Listing Tables You Own
Listing Column Definitions for a Table
The LIST_COLUMNS.SQL script
Notes about the LIST_COLUMNS script
Running LIST_COLUMNS
Table Constraints
Different Constraint Types Need Different Queries
CHECK constraints
PRIMARY KEY and UNIQUE constraints
FOREIGN KEY constraints
Listing the Constraints for a Table
The LIST_CONSTRAINTS.SQL script
Running the LIST_CONSTRAINTS script
Indexes
Listing Indexes for a Table
Index Scripts
The SHOW_INDEX.SQL script
Running the SHOW_INDEX script
The LIST_INDEXES.SQL script
Running the LIST_INDEXES.SQL script
Triggers
Looking at Trigger Definitions
Trigger Scripts
The SHOW_TRIGGER.SQL script
Running the SHOW_TRIGGER.SQL script
The LIST_TRIGGERS.SQL script
Running the LIST_TRIGGERS script
Synonyms
Looking at Synonyms
Synonym Scripts
The SHOW_SYN.SQL script
Running the SHOW_SYN.SQL script
The LIST_SYN script
Running the LIST_SYN.SQL script
Table Security
Looking at Table Security
Security Scripts
The SHOW_SECURITY.SQL script
Running the SHOW_SECURITY script
Finding More Information
7. Advanced Scripting
Bind Variables
Declaring Bind Variables
Using Bind Variables and Substitution Variables Together
From substitution to bind
From bind to substitution
Displaying the Contents of a Bind Variable
The PRINT command
Printing CLOB variables
Printing REFCURSOR variables
SELECTing a bind variable
When and How to Use Bind Variables
Calling PL/SQL procedures and functions from SQL*Plus
Using REFCURSOR variables
Testing application queries
Branching in SQL*Plus
Approaches to Branching
Simulating Branching by Adjusting the WHERE Clause
Using REFCURSOR Variables to Simulate Branching
Branching Using a Multilevel File Structure
Using SQL to Write SQL
Using PL/SQL
Using a Scripting Language Instead
Looping in SQL*Plus
Recursive Execution
Generating a File of Commands
Looping Within PL/SQL
The ANALYZE TABLE script revisited
Validating and Parsing User Input
Validating Input with ACCEPT
ACCEPTing numeric values
ACCEPTing date values
Validating Input with SQL
Parsing Input with SQL
Error Handling
The WHENEVER Command
WHENEVER SQLERROR
PL/SQL errors and WHENEVER
WHENEVER OSERROR
8. Tuning and Timing
Using SQL*Plus Timers
The SET TIMING Command
The TIMING Command
Starting and stopping a timer
Displaying the value of a timer
Nesting timers
Finding out how many timers you have going
Stopping all timers
Using EXPLAIN PLAN
Creating the Plan Table
Explaining a Query
Interpreting the Results
The plan table query
The SHOW_PLAN script
Executing the SHOW_PLAN script
Making sense of the results
Using AUTOTRACE
Granting Access to the Performance Views
Executing a Query with AUTOTRACE On
Showing statistics and the plan
Showing just the plan
Suppressing the query output
Turning AUTOTRACE off
Improving on EXPLAIN PLAN Results
Knowing Good Results from Bad
Creating Indexes
Rewriting the Query
Using Hints
Syntax for a hint
Optimizer goal hints
Access method hints
Join order hints
Join operation hints
Parallel execution hints
Other hints
Where to Find More Tuning Information
9. The Product User Profile
What Is the Product User Profile?
Why Does the Product User Profile Exist?
The PRODUCT_PROFILE Table
How the Product User Profile Works
Product User Profile Limitations
Issues related to PL/SQL
Issues related to roles
Using the Product User Profile
Creating the Profile Table
Limiting Access to Commands
Commands that can be disabled
Disabling a command
Re-enabling a command
Limiting Access to Roles
Disabling a role
Re-enabling a role
Reporting on the Product User Profile
Listing all restrictions
Listing restrictions for a particular user
10. Administration with SQL*Plus
Connecting for Administrative Purposes
Privileges You Will Need
The Oracle Password File
Connecting in an Administrative Role
Connecting as SYSOPER or SYSDBA
Connecting as INTERNAL
Starting and Stopping a Database
The States of a Database
Starting a Database
Stopping a Database
SHUTDOWN NORMAL
SHUTDOWN TRANSACTIONAL
SHUTDOWN IMMEDIATE
SHUTDOWN ABORT
Looking at Your Database
Looking at the SGA
Looking at Initialization Parameters
Looking at Archive and Recovery Information
Database Backup and Recovery
Backups
Cold backups
Hot backups
Archiving
Turning on archive log mode
Starting the archive log process
Displaying the archive log status
Manually archiving a log file
Stopping the archive log process
Turning off archive log mode
Recovery
Full database recovery
Tablespace and datafile recovery
Autorecovery
Other Things You Need to Know
11. Customizing Your SQL*Plus Environment
SQL*Plus Settings You Can Control
The Site and User Profiles
The Site Profile
The User Profile
Environment Variable Settings
Setting an Environment Variable
Unix
Windows 95/98/NT
Windows 3.1
Environment Variables That Affect SQL*Plus
LOCAL
NLS_LANG
PRO80
PLUS80
RDBMS80
SQLPATH
PLUS_DFLT
The SET Command
Report Output and Format Settings
SET COLSEP
SET EMBEDDED
SET HEADSEP
SET HEADING
SET LINESIZE
SET MAXDATA
SET NEWPAGE
SET NULL
SET NUMFORMAT
SET NUMWIDTH
SET PAGESIZE
SET RECSEP
SET RECSEPCHAR
SET SHIFTINOUT
SET SPACE
SET TAB
SET TRIMOUT
SET TRIMSPOOL
SET TRUNCATE
SET UNDERLINE
SET WRAP
Feedback Settings
SET AUTOPRINT
SET DOCUMENT
SET ECHO
SET FEEDBACK
SET PAUSE
SET SERVEROUTPUT
SET SHOWMODE
SET SQLPROMPT
SET TERMOUT
SET TIME
SET VERIFY
Input Settings
BLOCKTERMINATOR
BUFFER
CMDSEP
SQLBLANKLINES
SQLCASE
SQLCONTINUE
SQLNUMBER
SQLPREFIX
SQLTERMINATOR
SUFFIX
Operational Settings
SET APPINFO
SET ARRAYSIZE
SET AUTOCOMMIT
SET CLOSECURSOR
SET COMPATIBILITY
SET COPYCOMMIT
SET FLAGGER
SET FLUSH
Substitution Variable Settings
SET CONCAT
SET DEFINE
SET ESCAPE
SET SCAN
Large Object Settings
SET LOBOFFSET
SET LONG
SET LONGCHUNKSIZE
Tuning and Timing Settings
SET AUTOTRACE
SET TIMING
Database Administration Settings
SET AUTORECOVERY
SET LOGSOURCE
Miscellaneous Settings
SET COPYTYPECHECK
SET EDITFILE
SET INSTANCE
The SHOW Command
A. SQL*Plus Command Reference
SQLPLUS
Comment Delimiters (/*...*/)
Double Hyphen (- -)
At Sign (@)
Double At Sign (@@)
Forward Slash (/)
ACCEPT
APPEND
ARCHIVE LOG
ATTRIBUTE
BREAK
BTITLE
CHANGE
CLEAR
COPY
COLUMN
COMPUTE
Syntax of the COMPUTE command
CONNECT
DEFINE
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
GET
HELP
HOST
INPUT
LIST
PASSWORD
PAUSE
PRINT
PROMPT
QUIT
RECOVER
REMARK
REPFOOTER
REPHEADER
RUN
SAVE
SET
SHOW
SHUTDOWN
SPOOL
START
STARTUP
STORE
TIMING
TTITLE
UNDEFINE
VARIABLE
WHENEVER
B. SQL*Plus Format Elements
Formatting Numbers
Formatting Character Strings
Formatting Dates
Index
Colophon
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Oracle SQL*Plus: The Definitive Guide
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset