Oracle Program with PL/SQL

Course Contents - DAY 1Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

• What is PL/SQL?
• Basic Elements
• Variables and Constants
• Data Types
• Initializing Variables and Assigning Values
• Using SQL Statements in Code
• Generating Output to SQL or SQL Developer

• IF THEN ELSE Statements
• CASE Expressions
• The Basic Loop Construct
• WHILE and FOR Loops
• Nested and Labelled Loops
• The GOTO Statement
• The CONTINUE Statement

• What is a Cursor?
• Implicit and Explicit Cursors
• Cursor Operations
• Declaring, Opening and Closing Cursors
• Fetching Rows
• Status Checking
• Using Cursors FOR UPDATE
• The Cursor FOR Loop
• Parameterised Cursors

• The EXCEPTION Section
• Types of Exception
• Handling Named System-Raised Exceptions
• Handling Unnamed System-Raised Exceptions
• User-Declared Exceptions and Application Errors
• When others then Null
• Nested and Labelled Blocks
• Propagation of Exceptions
• Scope of Variables and Cursors

• Declaring Record Types
• Handling PL/SQL Records
• Nested Records
• Declaring PL/SQL Index-By Tables or Associative Arrays
• PL/SQL Table Built In Functions
• Manipulating PL/SQL Tables or Associative Arrays

• DML Triggers
• The CREATE TRIGGER Statement
• Writing Trigger Code
• INSTEAD OF Triggers
• Calling Procedures from Triggers
• Coding Restrictions
• System Event and DDL Triggers
• Attribute Functions
• Compound Triggers
• Create Trigger Follows Clause
• Managing Triggers
• Privileges required for Triggers
• Dictionary Information Concerning triggers

• What is a Procedure?
• The CREATE PROCEDURE Statement
• Procedure Parameters
• Invoking Procedures
• Local Subprograms
• Named Association Parameter Passing
• Definer's Right and Invoker's Rights
• Autonomous Transactions
• Managing Procedures
• Privileges Required for Procedures
• Dictionary Information Concerning Procedures
• The Call Statement

• What is a Function?
• The CREATE FUNCTION Statement
• Executing Functions
• Invoker's Rights
• Autonomous Transactions
• DBMS_OUTPUT
• Using Functions in SQL Statements
• Deterministic and Parallel-Enabled Functions
• Function Result Cache
• Managing Functions
• Privileges Required for Functions
• Dictionary Information Concerning Functions

• What is a Package?
• Public and Private Components
• Creating a Package
• Example Package
• Persistent States
• One-time-only Procedures
• Overloading
• Purity Level Checking
• Forward Declarations
• Wrapping Packages
• Managing Packages
• Privileges Required for Packages
• Dictionary Information Concerning Packages Features

• Overview of PL/SQL Records, Index-by Tables and Associative Arrays
• Using PL/SQL Records and Tables in Packages
• Table Built-In Functions
• The NOCOPY Hint
• Bulk Collection
• Bulk Binding DML Statements

• Declare Cursor Variables
• Use Cursor Variables
• Open and Close Cursor Variables
• Fetch Rows
• Cursor Variable Attributes
• Pass Cursor Variables as Parameters
• Open and Close Cursor Variables
• Refcursor Datatype

• Dependent and Referenced Objects
• Invalidation and Recompilation
• Local and Remote Dependencies
• Recompilation Considerations

• Introduction
• Native Compilation
• Automatic Recompilation
• Automatic program Sublining
• WHEN OTHERS ... THEN NULL
• Data Dictionary Information
• Compiler Warning Categories
• Using the DBMS_WARNING Package

• Overview of Oracle-Supplied Packages
• Using the DBMS_SQL Package
• Using Native Dynamic SQL
• The DBMS_METADATA Package
• The UTL_MAIL Package
• The DBMS_APPLICATION_INFO Package
• The DBMS_UTILITY Package
• Scheduling Jobs using the DBMS_SCHEDULER Package