Oracle PL SQL Training

• Understand Exceptions
• Handle Exceptions with PL/SQL
• Trap Predefined Oracle Server Errors
• Trap Non-Predefined Oracle Server Errors
• Trap User-Defined Exceptions
• Propagate Exceptions
• RAISE_APPLICATION_ERROR Procedure

• Understand Stored Procedures and Functions
• Differentiate between anonymous blocks and subprograms
• Create a Simple Procedure
• Create a Simple Procedure with IN parameter
• Create a Simple Function
• Execute a Simple Procedure
• Execute a Simple Function

• • Identity the advantages of Packages
• • Describe Packages
• • List the components of a Package
• • Develop a Package
• How to enable visibility of a Package’s components?
• Create the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
• Invoke Package Constructs
• View PL/SQL Source Code Using the Data Dictionary
• Overloading Subprograms in PL/SQL
• Use the STANDARD Package
• Use Forward Declarations to Solve Illegal Procedure Reference
• Implement Package Functions in SQL and Restrictions
• Persistent State of Packages
• Persistent State of a Package Cursor
• Control Side Effects of PL/SQL Subprograms
• Invoke PL/SQL Tables of Records in Packages

• The Execution Flow of SQL
• What is Dynamic SQL?
• Declare Cursor Variables
• Dynamically executing a PL/SQL Block
• Configure Native Dynamic SQL to Compile PL/SQL Code
• Invoke DBMS_SQL Package
• Implement DBMS_SQL with a Parameterized DML Statement
• Dynamic SQL Functional Completeness

• Standardize Constants and Exceptions
• Understand Local Subprograms
• Write Autonomous Transactions
• Implement the NOCOPY Compiler Hint
• The Cross-Session PL/SQL Function Result Cache
• Usage of Bulk Binding to Improve Performance

• • Describe Triggers
• • Identify the Trigger Event Types and Body
• • Business Application Scenarios for Implementing Triggers
• • Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
• • Identify the Trigger Event Types, Body, and Firing (Timing)
• Statement Level Triggers Versus Row Level Triggers
• Create Instead of and Disabled Triggers
• How to Manage, Test, and Remove Triggers?
• What are Compound Triggers?
• Identify the Timing-Point Sections of a Table Compound Trigger
• Compound Trigger Structure for Tables and Views
• Implement a Compound Trigger to Resolve the Mutating Table Error
• Compare Database Triggers to Stored Procedures
• Create Triggers on DDL Statements
• Create Database-Event and System-Event Triggers
• System Privileges Required to Manage Triggers

• Overview of Schema Object Dependencies
• Query Direct Object Dependencies using the USER_DEPENDENCIES View
• Query an Object’s Status
• Invalidation of Dependent Objects
• Display the Direct and Indirect Dependencies
• Fine-Grained Dependency Management in Oracle Database 11g
• Understand Remote Dependencies
• Recompile a PL/SQL Program Unit
   

Structure
Writing Anonymous Blocks

Oracle Datatypes
TYPE and ROWTYPE declarations
Value assignments

Implicit and Explicit Cursors
Cursor Manipulation statements
OPEN FETCH CLOSE EXIT WHEN

Creating and Calling Procedures

Creating and Calling Functions

Package Header
Package Body

Format of standard packages in an application
Spec and Body
NOCOPY hint theory

Database triggers
CALLing procedures from triggers

EXECUTE IMMEDIATE
DBMS_SQL package

DBMS_OUTPUT
UTL_FILE

Associative Arrays
Nested tables
VARRAYs