Oracle Database 11g delivers economies of scale on easily managed low-cost grids, making it easier to:

  • Reduce cost of downtime with maximum availability architecture
  • Change IT systems faster using Real Application Testing
  • Partition and compress data to run queries faster using less disks
  • Securely protect and audit data, and enable total recall of data
  • Make productive use of standby resources with Active Data Guard

This course addresses Oracle Database 11g program units and is focused on:

  • Subprograms, Packages, and Exception Handling in PL/SQL
  • Packages, Dynamic SQL, and Coding Considerations in PL/SQL
  • Using Triggers, the PL/SQL Compiler, and Managing Code
  • Managing PL/SQL Code and Dependencies

What You’ll Learn

  • Steps for designing, debugging, invoking, and bundling PL/SQL subprograms, procedures and functions, and for handling their exceptions
  • Steps for creating and using efficient PL/SQL packages, including Oracle-supplied packages, executing dynamic SQL, and improving the performance of code
  • Steps for creating, managing, and using database triggers and for using the new PL/SQL compiler, its initialization parameters, and its compile time warnings
  • Steps for performing conditional compilation to selectively use PL/SQL code, wrapping code, and tracking dependencies

Who Needs to Attend

Application developers, database designers, database administrators, forms developers, PL/SQL developers, and technical

Prerequisites

Experience and knowledge with SQL, specifically the Oracle Database 11g: SQL Fundamentals I and Oracle Database 11g: SQL Fundamentals II learning

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Oracle Database 11g: Subprograms, Packages, and Exception Handling in PL/SQL

  • Benefits of modularized and layered subprogram design
  • Creating and calling procedures and passing parameters
  • Handling exceptions in procedures and dropping procedures
  • Creating and invoking functions
  • Using SQL Developer to debug PL/SQL subprograms
  • Benefits and components of a package and the steps for creating one
  • Create a package specification and package body in a given scenario and invoke the package’s constructs

2. Oracle Database 11g: Packages, Dynamic SQL, and Coding Considerations in PL/SQL

  • Overloading subprograms, using forward declarations, writing package initialization blocks, and maintaining persistent package state
  • Using the Oracle-supplied packages DBMS_OUTPUT, UTL_FILE, and UTL_MAIL
  • Using Oracle-supplied packages to generate a simple Web page and schedule PL/SQL code for execution
  • Constructing and executing SQL statements at run time, using Native Dynamic SQL (NDS) statements in PL/SQL
  • Alter a package so that it contains overloaded subprograms, use an Oracle-supplied package, and execute dynamic SQL in a given scenario
  • Designing packages so that code is easy to maintain, efficient, and readable, and so that PL/SQL applications perform better
  • Binding whole arrays of values in a single operation and retrieving information about a row affected by a SQL operation

3. Oracle Database 11g: Using Triggers, the PL/SQL Compiler, and Managing Code

  • Creating and using database triggers
  • Enabling, disabling, testing, managing, and removing database triggers
  • Creating and using compound, DDL, and event database triggers
  • Create statement and row triggers, including a trigger for a business rule and a package with triggers that resolve a mutating table issue
  • Benefits and capabilities of the PL/SQL compiler and its parameters
  • Using the PL/SQL compiler initialization parameters, compiling a procedure, and managing compiler warnings

4. Oracle Database 11g: Managing PL/SQL Code and Dependencies

  • Selectively including PL/SQL source code by using conditional compilation and for wrapping a PL/SQL unit
  • Predicting how changing a database object affects procedures and functions by tracking procedural dependencies
  • Managing remote dependencies and recompiling a PL/SQL program unit
  • Use conditional compilation, wrap PL/SQL code, and manage dependencies in a given scenario