Course Contents
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.
Training Course Contents
PL/SQL Basics
- Introduction
- PL/SQL Block Structure
Declaring
- Variable Types
- Datatypes
- Creating Variables and Constants
- %Type Identifiers
Basic Data Processing
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
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
Trigger Firing Sequence
- Disabling Triggers
- Enabling Triggers
- Dropping Triggers
- Database Triggers (from 9i)
Data Dictionary Views
- 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 Improvement
- Improvements discussion
- Regular Expressions
- Additional floating point types
- Nested table enhancements
- Utility packages for compression/mail/warnings