PART I Language Syntax
1 Overview of PL/SQL
An Introduction to the PL/SQL Language
PL/SQL Syntax Fundamentals
Statements
Identifiers
Comments
Literals
Declaration Section
Variables
Datatypes
Constants
Other Declared Elements
Processing Section
Expressions
Assignment Statements
Conditional Stateents
Loops
Cursors
Implicit Cursors
Explicit Cursors
Cursor Attributes
Loops Revisited:The Cursor FOR Loop
Advanced Datatype Declaration
%TYPE
%ROWTYPE
Exception-Handling Section
System-Defined Exceptions
User-Defined Exceptions
Working with Blocks
Nested Blocks
Scope:Declared Elements
Exception Scope and Exception Propagation
An Introduction to Program Units
Anonymous Blocks
Named Program Units
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
2 Procedures
Uses of Procedures
What Can You Do with Procedures?
Where Can You Store Procedures?
Creating,Altering,and Dropping Procedures
Creating Procedures
Altering Procedures
Dropping Procedures
Invoking Procedures
Executing a Procedure from a PL/SQL Block
Executing a Procedure from the SQL*Plus Command Line
Parameters
Parameter Declaration Syntax
Parameter Datatypes
Default Values
Parameter Types
Positional Notation versus Named Notation
Data Dictionary Resources for Procedures
USER_OBJECTS
USER_OBJECT_SIZE
USER_SOURCE
USER_ERRORS
SHOW ERRORS
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
3 Functions
Uses of Functions
Functions versus Procedures
Creating,Altering,and Dropping Functions
Creating Functions
Altering Functions
Dropping Functions
Invoking Functions
Functions Called from PL/SQL Expressions
Functions Called from SQL Statements
Client-Side Functions
Parameters
Passing Parameters by Reference
RETURN
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
4 Packages
Uses of Packages
Creating,Altering,and Dropping Packages
Creating a Package Specification
Creating a Package Body
Public versus Private Constructs
Global Constructs
Altering a Package
Dropping a Package
Changes to a Package Specification that Require a Change to the Body
Invoking Packaged Constructs
Referencing Packaged Constructs
Using Packaged Constructs
Data Dictionary Resources for Packages
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
5 Triggers
Uses of Triggers
Creating,Altering,and Dropping Triggers
Creating Triggers
The:old and:new Qualifiers
Conditional Predicates
Firing Rules
Restrictions
Altering Triggers
Dropping Triggers
Enabling and Disabling Triggers
Disabling and Enabling a Named Trigger
Enabling and Disabling a Named Tables’s Triggers
INSTEAD OF Triggers
Non-DML Triggers
DDL Triggers
Data Dictionary Resources for Triggers
USER_TRIGGERS
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
6 Working with Program Units
Client-Side versus Server-Side Program Units
Invoking Server-Side Program Units
Invoking Client-Side Program Units
The Trade-of—Where to Put Them?
Local Subprograms
Forward Declarations
Overloading
Creating Overloaded Modules
Namespaces for Program Units
Initializing Variables with a One-Time-Only Procedure
Functions and Purity Levels
WNDS and RNDS
WNPS and RNPS
TRUST
Purity Levels for the Package Initialization Section
DEFAULT
The Benefits of PRAGMA
Persistent States
Persistent Variables and Constants
Persistent Tables,Records,and Types
Persistent Cursors
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
7 Working with Oracle''s PL/SQL Packages
DBMS_OUTPUT
SET SERVEROUTPUT ON
ENABLE and DISABLE
PUT_LINE,PUT,and NEW_LINE
GET_LINE and GET_LINES
DBMS_JOB
JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL
The SUBMIT Procedure
The REMOVE Procedure
The RUN Procedure
The CHANGE,NEXT_DATE,WHAT,KINTERVAL,and INSTANCE Procedures
Other Procedures:USER_EXPORT,BROKEN,and ISUBMIT
Monitoring Batch Jobs with USER_JOBS
DBMS_DDL
The ALTER_COMPILE Procedure
The ANALYZE_OBJECT Procedure
Monitoring the Results of ALTER_COMPILE and ANALYZE_OBJECT
DBMS_PIPE
The PACK_MESSAGE Procedure
The SEND_MESSAGE Procedure
The RECEIVE_MESSAGE Procedure
The NEXT_ITEM_TYPE Procedure
The UNPACK_MESSAGE Procedure
Other Procedures
DBMS_SQL
The OPEN_CURSOR Function and the CLOSE_CURSOR Procedure
The PARSE Procedure
The DEFINE_CONLUMN Procedure
The EXECUTE Function
The FETCH_ROWS Function
The COLUMN_VALUE Procedure
The BIND_VARIABLE Procedure
Native Dynamic SQL
Compilation Errors
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
PATR II Development Tools
8 Using Oracle PL/SQL Tools
SQL*Plus
The SQL*Plus Buffer Editor
Using Text Editors
Executing Text Files
PL/SQL Blocks and the Buffer
Procedure Builder
Object Navigator
Creating a Program Unit with Procedure Builder
Executing a Program Unit
Moving Program Units
Working with Libraries
Creating Database Triggers
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
9 Debugging PL/SQL
Debugging Concepts
Debugging Techniques
Code Formatting:An Example
Debugging Tools
Debugging with SQL*Plus
Debugging Compilation Errors with SQL*Plus
SHOW ERROR
Debugging Execution Error in SQL*Plus
Debugging with Procedure Builder
Starting Debugging:Setting Breakpoints
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
PART III Privileges and Interdependence
10 Managing Privileges
System Privileges
Roles
System and Object Privilege Requirements for Program Units
System Privileges for Program Units
Object Privileges for Program Units
Owner and Invoker Rights
Owner Rights
Invoker Rights
Granting and Revoking Privileges
Data Dictionary Resources
SESSION_PRIVS
SESSION_ROLES
USER_SYS_PRIVS
USER_ROLE_PRIVS
DBA_SYS_PRIVS
USER_TAB_PRIVS,USER_TAB_PRIVS_MADE,and USER_TAB_PRIVS_RECD
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
11 Managing Interdependencies
Tracking Dependencies
USER_DEPENDENCIES
DEPTREE and IDEPTREE
Dependency Issues Within a Single Database
Dependency Issues Across Multiple Databases
The Timestamp Mode
The Signature Mode
Avoiding Recompilation Errors
Chapter Summary
Two-Minute Drill
Chapter Questions
Answers to Chapter Quetions
PART IV Practice Exams
12 Practice Exams
Practice Exam #1
Answers to Practice Exam #1
Practice Exam #2
Answers to Practice Exam #2
Practice Exam #3
Answers to Practice Exam #3