Intro to Oracle 10g PL/SQL Programming – ORA508

• Beyond Declarative Integrity
• Triggers
• Types of Triggers
• Row-Level Triggers
• Trigger Predicates
• Trigger Conditions
• Using SEQUENCEs
• Cascading Triggers and Mutating Tables
• Generating an Error
• Maintaining Triggers

PL/SQL Variables and Datatypes

• Anonymous Blocks
• Declaring Variables
• Datatypes
• Subtypes
• Character Data
• Dates and Timestamps
• Date Intervals
• Anchored Types
• Assignment and Conversions
• Selecting into a Variable
• Returning into a Variable

PL/SQL Syntax and Logic

• Conditional Statements – IF/THEN
• Conditional Statements – CASE
• Comments and Labels
• Loops
• WHILE and FOR Loops
• SQL in PL/SQL
• Local Procedures and Functions

Stored Procedures and Functions

• Stored Subprograms
• Creating a Stored Procedure
• Procedure Calls and Parameters
• Parameter Modes
• Creating a Stored Function
• Stored Functions and SQL
• Invokers Rights

Exception Handling

• SQLCODE and SQLERRM
• Exception Handlers
• Nesting Blocks
• Scope and Name Resolution
• Declaring and Raising Named Exceptions
• User-Defined Exceptions

Records, Collections, and User-Defined Types

• Record Variables
• Using the %ROWTYPE Attribute
• VARRAY and Nested TABLE Collections
• Using Nested TABLEs
• Using VARRAYs
• Collection in Database Tables
• Associative Array Collections
• Collection Methods
• Iterating Through Collections

Cursors

• Multi-Row Queries
• Declaring and Opening Cursors
• Fetching Rows
• Closing Cursors
• The Cursor FOR Loop
• FOR UPDATE Cursors
• Cursor Parameters
• The Implicit (SQL) Cursor

• Bulk Binding
• BULK COLLECT Clause
• FORALL Statement
• FORALL Variations
• Bulk Returns
• Bulk Fetching with Cursors

Using Packages

• Packages
• Oracle-Supplied Packages
• The DBMS_OUTPUT Package
• The DBMS_UTILITY Package
• The UTL_FILE Package
• Creating Pipes with DBMS_PIPE
• Writing to and Reading from a Pipe
• The DBMS_METADATA Package
• XML Packages
• Networking Packages
• Other Supplied Packages

Creating Packages

• Structure of a Package
• The Package Interface and Implementation
• Package Variables and Package State
• Overloading Package Functions and Procedures
• Forward Declarations
• Strong REF CURSOR Variables
• Weak REF CURSOR Variables

Working with LOBs

• Large Object Types
• Oracle Directories
• LOB Locators
• Internal LOBs
• External LOBs
• Temporary LOBs
• The DBMS_LOB Package

Maintaining PL/SQL Code

• Privileges for Stored Programs
• Data Dictionary
• PL/SQL Stored Program Compilation
• Conditional Compilation
• Compile-Time Warnings
• The PL/SQL Execution Environment
• Dependencies and Validation
• Maintaining Stored Programs

Appendix A – Dynamic SQL

• Generating SQL at Runtime
• Native Dynamic SQL vs. DBMS_SQL Package
• The EXECUTE IMMEDIATE Statement
• Using Bind Variables
• Multi-row Dynamic Queries
• Bulk Operations with Dynamic SQL
• Using DBMS_SQL
• DBMS_SQL Subprograms

Appendix B – PL/SQL Versions, Datatypes, and Language Limits

Appendix C – Oracle 10g Supplied Packages