1-800-THE-TREE (1-800-843-8733)
 

Oracle PL/SQL Programming: Hands-On

 
Course: 493     Type: Hands-On Training     Duration: 5 Days

Quick Enroll    

You Will Learn How To
  • Develop efficient PL/SQL programs to access Oracle databases
  • Create stored procedures and functions for maximum reuse and minimum code maintenance
  • Design modular applications using packages
  • Manage data retrieval for front-end applications
  • Bulk bind collections to increase the speed of data movement operations
  • Invoke native dynamic SQL to develop high-level abstract code

Course Benefits
The Oracle PL/SQL language--a flexible procedural extension to SQL--increases productivity, performance, scalability, portability and security. In this course, you gain the practical knowledge to write PL/SQL programs. You learn to build stored procedures, design and execute modular applications, and increase the efficiency of data movement.

Who Should Attend
Programmers and others working with PL/SQL. A working knowledge of SQL and PL/SQL at the level of Course 926, "Oracle Database 11 Comprehensive Introduction," or Course 593, "Oracle Database 10g Comprehensive Introduction," is assumed.

Hands-On Training
Extensive hands-on exercises provide experience writing modular PL/SQL code. Exercises include:
  • Encapsulating data manipulation statements in stored procedures and packages
  • Performing complex data manipulation with cursors
  • Leveraging EXCEPTIONs to handle runtime errors
  • Creating triggers to handle data integrity & derivation
  • Utilizing weak & strong cursor variables for dynamic SQL
  • Denormalizing data with user-written functions
  • Creating global variables in bodiless packages for session processing

Course 493 Content
Introduction and Overview
PL/SQL fundamentals
  • Declaring variables
  • Anchoring variables to database definitions
  • Flow control constructs
Oracle9i and 10g PL/SQL features
  • Bulk bind native dynamic SQL
  • CASE statement process flow
  • Referencing PL/SQL records in DML
  • Improving performance with native compilation
  • Handling regular expressions with Oracle 10g functions
  • Associative arrays subscripted by VARCHAR2
  • Multiset operators for collections
Data Manipulation Techniques
Maintaining data with DML statements
  • Employing the RETURNING INTO clause
  • Solving the fetch-across-commit problem
Managing data retrieval with cursors
  • Implications of explicit and implicit cursors
  • Cursor attributes
  • Simplifying cursor processing with cursor FOR LOOPs
  • Embedding cursor expressions in SELECT statements
Cursor variables
  • Strong vs. weak cursor variables
  • Passing cursor variables to other programs
  • Defining REF CURSORS in packages
Developing Well-Structured and Error-Free Code
Error handling using EXCEPTIONs
  • Predefined and user EXCEPTIONs
  • Propagation and scope
  • "Retrying" problem transactions with EXCEPTION processing
Debugging PL/SQL blocks
  • Simplifying testing and debugging with conditional compilation
  • Interpreting compiler messages
  • Applying structured testing techniques
Achieving Maximum Reusability
Writing stored procedures and functions
  • Calling and invoking server-side logic
  • Passing input and output parameters
  • Implementing an autonomous transaction
  • Definer rights vs. invoker rights
Coding user-written functions
  • Calling PL/SQL functions from SQL
  • Building table-valued functions
Developing safe triggers
  • Employing :OLD and :NEW bind variables in row-level triggers
  • Implementing complex business rules
  • Avoiding unreliable trigger constructs
  • Exploiting schema and database triggers
Exploiting Complex Datatypes
Collection types
  • PL/SQL tables, nested tables, VARRAYs
  • Stepping through dense and non-consecutive collections
Bulk binding for high performance
  • Moving data into and out of PL/SQL blocks
  • BULK COLLECT INTO
  • FORALL
  • BULK cursor attributes
  • BULK EXCEPTION handling
Invoking Native Dynamic SQL
Finessing the compiler
  • The EXECUTE IMMEDIATE statement
  • The RETURNING INTO clause
Types of dynamic SQL
  • Building SQL statements during runtime
  • Autogenerating standard code
Package Tips and Techniques
  • Package structure: SPEC and BODY
  • Eliminating dependency problems
  • Overloading for polymorphic effects
  • Evaluating application frameworks
  • Bodiless packages for all application definitions
  • Declaring and using persistent global variables

Related Courses
Oracle is a registered trademark of Oracle Corporation. PL/SQL is a trademark of Oracle Corporation.
  
 
Request More Info

Salutation

First Name

Last Name

Company

Zip Code

Country
   Codes
Work Phone

Extension

E-mail

A representative will contact you to follow up your request.
Privacy Statement

Save an EXTRA $200 on a 10-Day Training Pass!

Oracle PL/SQL Programming: Hands-On
Upcoming Dates
Sep 8 - 12, 2008
 Boston (Waltham)
Sep 15 - 19, 2008
 Toronto
Sep 29 - Oct 3, 2008
 Los Angeles
Oct 6 - 10, 2008
 New York
Oct 6 - 10, 2008
 Ottawa
Oct 6 - 10, 2008
 Philadelphia
Oct 20 - 24, 2008
 Chicago (Schaumburg)
Oct 20 - 24, 2008
 Washington, DC (Rockville, MD)
Oct 27 - 31, 2008
 Atlanta
Nov 3 - 7, 2008
 Washington, DC (Reston, VA)

Oracle PL/SQL Programming: Hands-On
Bring Learning Tree On-Site

Course Tuition
$ 2,950 Standard Tuition
Tuition with a Savings Plan
$ 1,580 10-Day Pass
$ 1,665 Training Passport
$ 1,830 Flex-Pass
$ 2,095 Voucher 10-Pack
$ 2,655 Alumni Gold Discount
$ 2,620 Government Discount
 

 

Oracle PL/SQL Programming: Hands-On
Oracle PL/SQL Programming: Hands-On
Course participants designing applications using packages.
The most recent 100 evaluations scored this course at:

  (3.75/4.00)


Oracle Today


Certification Core Course CPE 29 Credits 2 Hour(s) College Credit
Customer Service or Enroll: 1-800-843-8733