Introduction to Oracle 11g Programming - ORA501

Relational Database and SQL Overview

• Review of Relational Database Terminology
• Relational Database Management Systems
• Introduction to SQL
• Oracle Versioning and History
• Logical and Physical Storage Structures
• Connecting to a SQL Database
• Datatypes
• Sample Database

Using Oracle SQL*Plus

• SQL*Plus
• The SQL Buffer
• Buffer Manipulation Commands
• Running SQL*Plus Scripts
• Tailoring Your SQL*Plus Environment
• Viewing Table Characteristics
• SQL*Plus Substitution Variables
• Interactive SQL*Plus Scripts
• SQL*Plus LOB Support
• Graphical Clients

SQL Queries - The SELECT Statement

• The SELECT Statement
• The CASE...WHEN Expression
• Choosing Rows with the WHERE Clause
• NULL Values
• Compound Expressions
• IN and BETWEEN
• Pattern Matching: LIKE and REGEXP_LIKE
• Creating Some Order

Scalar Functions

• SQL Functions
• Using SQL Functions
• String Functions
• Regular Expression Functions
• Numeric Functions
• Date Functions
• Date Formats
• Conversion Functions
• Literal Values
• Intervals
• Oracle Pseudocolumns

SQL Queries - Joins

• Selecting from Multiple Tables
• Joining Tables
• Self Joins
• Outer Joins

Aggregate Functions and Advanced Techniques

• Subqueries
• Correlated Subqueries
• The EXISTS Operator
• The Aggregate Functions
• Nulls and DISTINCT
• Grouping Rows
• Combining SELECT Statements

Data Manipulation and Transactions

• The INSERT Statement
• The UPDATE Statement
• The DELETE Statement
• Transaction Management
• Concurrency
• Explicit Locking
• Data Inconsistencies
• Loading Tables From External Sources

Data Definition and Control Statements

• Datatypes
• Defining Tables
• Constraints
• Inline Constraints
• Modifying Table Definitions
• Deleting a Table Definition
• Controlling Access to Your Tables

Other Database Objects

• Views
• Creating Views
• Updatable Views
• Sequences
• Synonyms

Triggers

• Beyond Declarative Integrity
• Triggers
• Types of Triggers
• Trigger Sequencing
• 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
• Named Parameter Notation
• Default Arguments
• Creating a Stored Function
• Stored Functions and SQL
• Invoker’s 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
• User-Defined Object Types
• VARRAY and Nested TABLE Collections
• Using Nested TABLEs
• Using VARRAYs
• Collections 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 Operations
• 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
• LOB Storage and SECUREFILEs
• 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 - The Data Dictionary

• Introducing the Data Dictionary
• DBA, ALL, and USER Data Dictionary Views
• Some Useful Data Dictionary Queries

Appendix B: 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 C: PL/SQL Versions, Datatypes, and Language LimitsAppendix D: Oracle 11g Supplied Packages