Cart 0

Oracle® PL/SQL Training
Course 493
5 DAY COURSE

Price:
Course Outline

This Oracle® PL/SQL training course will teach you to increase the productivity, performance, scalability, portability, and security of your Oracle database. With this training, you will learn how to execute PL/SQL programs in Oracle, build stored procedures, design, and execute modular applications, and increase the efficiency of data movement — essential skills for any Oracle developer.

Oracle® PL/SQL Training Benefits

  • In this Oracle PL/SQL Programming training, you will learn how to:

    • Develop efficient PL/SQL programs to access Oracle databases. 
    • Create stored procedures and functions for reuse and maintenance. 
    • Design modular applications using packages. 
    • Manage data retrieval for front-end applications. 
    • Invoke native dynamic SQL to develop high-level abstract code. 

  • Oracle PL/SQL Programming Training Course Prerequisites

    Students taking this course should have basic knowledge of SQL as taught in Learning Tree course 925, Introduction to SQL Course OR Learning Tree course 2226, {course:2226}.

Oracle PL/SQL Course Outline

Module 1: Introduction and Overview

PL/SQL fundamentals

  • Declaring and anchoring variables to database definitions
  • Flow control constructs

Oracle 11g and 12c PL/SQL features

  • PL/Scope in Oracle 11g
  • Returning implicit cursor results from stored procedures in Oracle 12c
  • Declaring local functions within SELECT statements in Oracle 12c

Module 2: 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
  • 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

Module 3: Developing Well-Structured and Error-Free Code

Error handling using 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
  • Building and applying a test bed
  • Leveraging the debugging facilities in SQL Developer

Module 4: Achieving Maximum Reusability

Writing stored procedures and functions

  • Calling and invoking server-side logic
  • Passing input and output parameters
  • Implementing an autonomous transaction

Coding user-written functions

  • Calling PL/SQL functions from SQL
  • Building table-valued functions

Developing safe triggers

  • Employing :OLD and :NEW variables
  • Avoiding unreliable trigger constructs
  • Exploiting schema and database triggers

Module 5: 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 and FORALL
  • BULK cursor attributes
  • BULK EXCEPTION handling

Module 6: 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

Module 7: Package Tips and Techniques

  • Package structure: SPEC and BODY
  • Eliminating dependency problems
  • Overloading for polymorphic effects
  • Evaluating application frameworks
  • Declaring and using persistent global variables
Course Dates

For course questions or any customer service inquiry, please contact your Customer Service team at BAHCustomerService@LearningTree.com.

We are excited that Learning Tree now offers a deferred direct bill payment option for Booz Allen employees. The deferred direct bill payment option enables employees to enroll in learning opportunities with no upfront costs. This payment option will require the employee to sign a Family Educational Rights and Privacy Act (FERPA) agreement with Learning Tree to release grades/completion to Booz Allen to satisfy the FlexEd Program completion requirement. Note, Learning Tree may also be used for the FlexEd Program reimbursement payment option.

Attendance Method
Additional Details (optional)

Private Team Training

Interested in this course for your team? Please complete and submit the form below and we will contact you to discuss your needs and budget.