Course Description
This course introduces the fundamentals of Oracle's procedural language, PL/SQL, the engine behind the full range of Oracle technology.
The course introduces the syntactic transactional languages controls evolving to cover the full range of PL/SQLprogramming functionality found within the Oracle 11g RDBMS.
This practical course is an essential foundation study course needed for all Oracle 11g RDBMS professionals to maximise their utilisation of Oracle 11g and the productive benefits that can be achieved through the essential deployment of PL/SQL functionality across the business.
Target Student:
Oracle Analyst/Programmers
Oracle Technicians
Oracle DBA™s
Oracle Developers
Prerequisites:
PC Fundamentals
Oracle SQL
Delivery Method: Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.
Course Content
PL/SQL Basics
Introduction
PL/SQL Block Structure
Declaring
Variable Types Datatypes
Creating Variables and
Constants
_CS_Type Identifiers
Basic Data Processing
Select Into Command
Procedural Processing
Simple or Infinite Loops While Loops
For Loops
IF Statements
GOTO
Data Processing
Creating Explicit Cursors
Working with Cursors Using Cursors with Loops
Parameterised Cursors Cursor Attributes
Implicit Cursors
Cursor for Loops
Error Handling
Introduction to Exceptions Creating Programmer Defined Exceptions
Exceptions
Redefining Oracle Exceptions
Raising Application Errors
Using Error Variables
Modularization
PL/SQL block structure
Procedures
Functions
Parameters
Stored PL/SQL code
User Schemas Hints Dependencies Invoker Rights
Overloaded Modules
Local Modules Shared Global Area Code Wrapping
Packages
Creating Packages
Dropping Packages
Parameter passing by reference
Serially reusable packages
Transactional Control
Commit Rollback Savepoint
Autonomous Transactions
Extending the Type System
PL/SQL Collections
Associated Arrays
Nested Tables
Bulk Binds
Bulk Exceptions
Caching Data
Database Triggers
Writing Database Triggers
Database Trigger Syntax
Accessing Column
Values in Triggers
Trigger Restrictions
Database Trigger Firing
Sequence
Disabling Triggers
Enabling Triggers
Dropping Triggers
Database Triggers (from 9i)
Data Dictionary Views
Naming Conventions
Package security
Oracle Supplied Packages
DBMS_OUTPUT
DBMS_JOB
DBMS_PROFILER
DBMS_RANDOM
File IO with UTL_FILE
package
Dynamic SQL Methods
Performing Dynamic SQL with
DBMS_SQL Package
Native Dynamic SQL
Large Object Support
Large Object Principles
Using DBMS_LOB
Web PL/SQL Development
Output with htp.p
Specific htp procedures
PL/SQL Toolkit Packages
owa_cookie
owa_image
owa_pattern
owa_util
utl_http
10g PLSQL Language Improvements
Improvements discussion
Regular Expressions
Additional floating point types
Nested table enhancements
Utility packages for compression/mail/warnings
11g PL/SQL Features and Enhancements
SIMPLE_INTEGER Datatype
CONTINUE Statement
Cross-Session PL/SQL Function Result Cache Enhancements to Regular Expression Built-in SQL
Functions
Sequences in PL/SQL Exspressions
Dynamic SQL Enhancements
Names and Mixed Notation in PL/SQL subprogram Invocations
New PL/SQL Compiler
Warning
PLS-00436 Restriction in
FORALL Statements Removed
PL/Scope
PL/SQL Native Compiler Generates Native Code Directly
Automatic Subprogram Inlining
11g Trigger Enhancements
PL/SQL Hierarchical Profiler
