Oracle PL\SQL Training:

Course Content Details:

 Introduction to PL/SQL:

  What is PL/SQL

  PL/SQL Environment

  Benefits of PL/SQL

  Overview of the Types of PL/SQL blocks

  Create and Execute a Simple Anonymous Block

  Generate Output from a PL/SQL Block

 Interacting with the Oracle Server:

  Identify the SQL Statements You Can Use in PL/SQL

  Include SELECT Statements in PL/SQL

  Retrieve Data in PL/SQL with the SELECT Statement

  Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements

  Manipulate Data in the Server Using PL/SQL

  The SQL Cursor concept

  Use SQL Cursor Attributes to Obtain Feedback on DML

  Save and Discard Transactions

Triggers:

  Beyond Declarative Integrity

  Triggers

  Types of Triggers

  Row-Level Triggers

  Cascading Triggers and Mutating Tables

  Generating an Error

  Triggers on Views

  Schema and System Triggers

  Maintaining Triggers

PL/SQL Variables and Datatypes:

  Declaring Variables

  Datatypes

  Subtypes

  Character Data

  Dates and Timestamps

  Date Intervals

  Anchored Types

  Assignment and Conversions

  Selecting into a Variable

PL/SQL Syntax and Logic:

  PL/SQL Blocks and Programs

  Conditional Statements - IF/THEN

  Conditional Statements - CASE

  Comments and Labels

  Loops

  WHILE and FOR Loops

Stored Procedures and Functions:

  Stored Subprograms

  Procedures and Functions

  Creating a Stored Procedure

  Calling a Stored Procedure

  Passing Parameters and Default Arguments

  Parameter Modes

  Creating a Stored Function

  Calling a Stored Function

  Stored Functions and SQL

  Local Procedures and Functions

Exception Handling:

  SQLCODE and SQLERRM

  Exception Handlers

  Nesting Blocks

  Scope and Name Resolution

  User-Defined Exceptions

  Compile-Time Warnings

Records and Collections:

  Record Variables

  Using the %ROWTYPE Attribute

  VARRAY and Nested TABLE Collections

  Using Nested TABLEs

  Using VARRAYs

  Associative Array Collections

  Collection Methods

  Iterating Through Collections

  FORALL Statement

  BULK COLLECT Clause

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

Using Packages:

  Packages

  Oracle-Supplied Packages

  The DBMS_OUTPUT Package

  The DBMS_UTILITY Package

  The UTL_FILE Package

  The DBMS_METADATA Package

  XML Packages

  Networking Packages

  Other Supplied Packages

Creating Packages:

  Structure of a Package

  The Package Interface and Implementation

  Package Variables

  Overloading Package Functions and Procedures

  Named Parameter Notation

  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

  PL/SQL Stored Program Compilation

  The PL/SQL Execution Environment

  Dependencies and Validation

  Maintaining Stored Programs

The PL/SQL Execution Environment:

  The Server Process

  PL/SQL Execution

  The PL/SQL Compiler

  Compiler Optimization

  SQL - Parse

  SQL - Execute and Fetch

  Server Memory

  Latches

  Locks

Advanced Cursors:

  Cursor Types

  Cursors and Storage

  Spanning Commits Across FETCHes

  Dynamic SQL in PL/SQL

  Bulk Operations

  Bulk Returns

  Limiting Results

  Cursor Parameters

  Cursor Variables

  Strong and Weak Cursors

  Using Cursor Variables

  Cursor Type Errors

  Cursor Subqueries

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 for DML and DDL

  Using DBMS_SQL for Queries

  Retrieving Meta Information with DBMS_SQL

Object-Oriented Oracle:

  Introducing Object-Oriented Oracle

  Defining Object Types and Tables in SQL

  Querying and Modifying Object Data

  Object Method

  Inheritance

  Type Evolution

  Object Views

  Object Types in PL/SQL

  REF Pointers

  Object Functions and Operators

Tuning PL/SQL:

  PL/SQL vs SQL

  PL/SQL Performance Tips

  Tuning Goals

  Monitoring Wait Events

  DBMS_PROFILER

  DBMS_TRAC

  Execution Plans

  Interpreting Explain Plan Results

  Execution Plan Details

  Trace Files

  TKPROF

  Using trcsess

  DBMS_APPLICATION_INFO

 

Debugging and Error Handling:

  Exception Management

  Exception Propagation

  User-Defined Exceptions

  Exception Error Messages

  Stack Management

  Debugging with DBMS_OUTPUT

  Debugging with a Table

  Using UTL_FILE

  Using DBMS_DEBUG

  SQL Developer

  Avoiding Bugs

Advanced Programming Topics:

  Autonomous Transactions

  Invokers Rights

  Fine-Grained Access Control with DBMS_RLS

  Creating Pipes with DBMS_PIPE

  Writing to and Reading from a Pipe

  Table Functions

  Pipelined Table Functions

  Enabling parallel execution

  DETERMINISTIC Functions

 

For Inquiries you can walk-in to our Co-operate office (485 US Highway 1 South, Building C, Suite # 105,Iselin,NJ-08830) between 9 am to 6 pm.

Please Email to Training@VForceInfoTech.com OR Call On 732-218-5912 Ext 7008/7010