Back to Search Results

Training Course Summary:

This 5 day Oracle training course introduces the fundamentals of Oracle's procedural language, PL/SQL, the engine behind the full range of Oracle technology.

The Oracle training course also introduces the syntactic transactional languages controls evolving to cover the full range of PL/SQL programming functionality found within the Oracle 10g RDBMS.

This practical Oracle training course is an essential foundation study course needed for all Oracle 10g RDBMS professionals to maximise their utilisation of Oracle 10g and the productive benefits that can be achieved through the essential deployment of PL/SQL functionality across the business.

Oracle Database 10g also introduces some new PL/SQL language features and some new supplied PL/SQL packages. In this release, there is an increase in runtime performance. Oracle Database 10g brings a new PL/SQL compiler and a newly tuned PL/SQL execution environment. Additionally, the system for the native compilation of PL/SQL has been substantially improved. PL/SQL packages are used to extend the functionality of the Oracle Database when SQL cannot be extended for a required purpose.

Pre-Requisites:

PC Fundamentals
Oracle 10g SQL (O011)

Who Should Attend:

Oracle Analyst/Programmers
Oracle Technicians
Oracle DBA's
Oracle Developers

Training Course Overview/Content:

PL/SQL Basics
Introduction
PL/SQL Block Structure
back to top
Declaring
Variable Types
Datatypes
Creating Variables and Constants
%Type Identifiers
back to top
Basic Data Processing
Select ... Into Command
back to top
Procedural Processing
Simple or Infinite Loops
While Loops
For Loops
IF Statements
GOTO
back to top
Data Processing
Creating Explicit Cursors
Working with Cursors
Using Cursors with Loops
Parameterised Cursors
Cursor Attributes
Implicit Cursors
Cursor for Loops
back to top
Error Handling
Introduction to Exceptions
Creating Programmer Defined Exceptions
Exceptions
Redefining Oracle Exceptions
Raising Application Errors
Using Error Variables
back to top
Modularization
PL/SQL block structure
Procedures
Functions
Parameters
back to top
Stored PL/SQL code
User Schemas
Hints
Dependencies
Invoker Rights
Overloaded Modules
Local Modules
Shared Global Area
Code Wrapping
back to top
Packages
Creating Packages
Dropping Packages
Parameter passing by reference
back to top
Transactional Control
Commit
Rollback
Savepoint
Autonomous Transactions
back to top
Extending the Type System
PL/SQL Collections
Associated Arrays
Nested Tables
Bulk Binds
Bulk Exceptions
Caching Data
back to top
Database Triggers
Writing Database Triggers
Database Trigger Syntax
Accessing Column Values in Triggers
Trigger Restrictions
back to top
Trigger Firing Sequence
Disabling Triggers
Enabling Triggers
Dropping Triggers
Database Triggers (from 9i)
back to top
Data Dictionary Views
DBMS_OUTPUT
DBMS_JOB
DBMS_PROFILER
DBMS_RANDOM
back to top
File IO with UTL_FILE package

back to top
Dynamic SQL
Methods
Performing Dynamic SQL with DBMS_SQL Package
Native Dynamic SQL
back to top
Large Object Support
Large Object Principles
Using DBMS_LOB
back to top
Web PL/SQL Development
Output with htp.p
Specific htp procedures
back to top
PL/SQL Toolkit Packages
owa_cookie
owa_image
owa_pattern
owa_util
utl_http
back to top
10g PLSQL Language Improvement
Improvements discussion
Regular Expressions
Additional floating point types
Nested table enhancements
Utility packages for compression/mail/warnings