Introduction To Oracle 10g Programming Course

Topics covered on the 5 day Introduction To Oracle 10g Programming course

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
• Using iSQL*Plus
• Graphical Clients

SQL QUERIES - THE SELECT STATEMENT

• The SELECT Statement
• The CASE...WHEN Statement
• 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
• 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
• 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
• 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
• 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 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
• 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 LIMITS APPENDIX D – ORACLE 10G SUPPLIED PACKAGES