Request Info

Salutation

First Name

Last Name

Job Title

Department

Mailstop

Company

Address

P.O. Box

City

State

Zip Code

Country
List

Work Phone

Extension

E-mail

Information provided will be used to communicate with you about our products.
Privacy Statement

A representative will contact you to follow up your request.

 

Oracle® PL/SQL Programming: Hands-On


Course 4935 Days

 E-mail E-mail  Print Print  Q&A Q&A  PDF PDF  Facebook Facebook  Twitter Twitter

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 11g 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 and derivation
  • Utilizing weak and 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

Oracle 10g and 11g PL/SQL features

  • PL/Scope in Oracle 11g
  • CASE statement process flow
  • Referencing PL/SQL records in DML
  • Improving performance with native compilation
  • Handling regular expressions with Oracle 10g functions
  • Compound triggers in Oracle 11g
  • 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 nonconsecutive 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

<< Back to Oracle Database Course List
 

Related Courses


Oracle is a registered trademark of Oracle Corporation. PL/SQL is a trademark of Oracle Corporation.
 
Oracle PL/SQL Programming:  Hands-On

Training Dates

Live online classroom training. Participate in person or in live, online events (highlighted) via Learning Tree AnyWareTM.
Mar 5 - 9Chicago (Schaumburg) enroll
Mar 26 - 30Reston, VA enroll
Apr 9 - 13Atlanta enroll
Apr 9 - 13Toronto enroll
Apr 16 - 20Rockville, MD enroll
Apr 23 - 27Alexandria, VA enroll
Apr 30 - May 4New York enroll
Apr 30 - May 4Ottawa enroll
Jun 11 - 15Chicago (Schaumburg) enroll
Jun 18 - 22Reston, VA enroll
Jun 25 - 29Boston enroll
Jul 9 - 13Los Angeles enroll
Jul 9 - 13Rockville, MD enroll
Jul 16 - 20New York enroll
Jul 23 - 27Philadelphia enroll

For AnyWare enrollments, please register at least 10 days prior to the start of the course.

More Dates and Locations.

Tuition

$ 3,190Standard Tuition
Tuition with a Savings Plan
$ 1,700Premium-Pass
$ 1,833Training Passport
$ 2,250Voucher 10-Pack
$ 2,267Triple-Pack
$ 2,833Government Discount
$ 2,870Alumni Gold Discount

On-Site &
Custom Training

Bring this or any Learning Tree course to your location or have it customized for your organization.

Oracle PL/SQL Programming:  Hands-On

Course participants designing applications using packages.


Average Attendee Evaluation

Evaluations in the last 12 months

5 stars:
77%
4 stars:
22%
3 stars:
1%
2 stars:   0%
1 star:   0%

 
What You Need to Know About Oracle 11g
 
Oracle 10g : An Introduction

Certification Core Course CPE 29 Credits 2 Hour(s) College Credit