Welcome to Comnet Group Inc.

Courses

Oracle Database 11g Programming with PL/SQL

Course number: OD11-CGI002

Course Description:

A good Oracle database administrator must gain and maintain a mastery of his or her environment, its physical structures, and its maintenance routines. An administrator must understand the data being stored well enough to tune and improve performance, to anticipate and prevent data failure, and to recover lost data should a failure occur. In this course, you will develop an advanced understanding of complex Oracle database concepts, maintenance and recovery routines, and database management. You have an Oracle installation and need to broaden your knowledge of advanced Oracle administrative functionality so that you can maintain the healthiest possible database.

Course Objective:
You will use PL/SQL to create programs with program units such as procedures, functions, packages, and database triggers to enhance data manipulations.

Performance-Based Objectives
Upon successful completion of this course, students will be able to:

  • write a simple PL/SQL program to familiarize themselves with the fundamentals and structure of PL/SQL blocks.
  • create a PL/SQL program using variables and lexical units.
  • use control structures to help them incorporate decision-making approach to programming.
  • handle PL/SQL exceptions.
  • work with PL/SQL cursors to embed SQL in PL/SQL code or call PL/SQL functions within SQL structures.
  • create PL/SQL subprograms.
  • use PL/SQL collections and records to enhance database performance during data processing.
  • develop and use PL/SQL packages.
  • create triggers to automatically trigger validation procedures when a table is modified.
  • use the PL/SQL compiler.
  • manage dependencies.
  • use dynamic SQL to handle changes in data definitions without recompiling.

Target Student:

This course is designed for IT Professionals with programming experience. The target student will also have experience in using SQL with Oracle 10g or 11g.

Prerequisites

Students enrolling in this class should have knowledge of working with Oracle databases. To ensure your success, we recommend that you first complete the following Element K courses or have equivalent knowledge.

  • Oracle Database 11g: Introduction, and
  • Oracle Database 11g: SQL Fundamentals I & II
Course Outline
Lesson 1: Getting Started with PL/SQL Programming
  • Topic 1A: Examine PL/SQL Basics
  • Topic 1B: Write a Simple PL/SQL Program Block
Lesson 2: Using Variables and Lexical Units
  • Topic 2A: Use PL/SQL Variables and Data Types
  • Topic 2B: Use PL/SQL Lexical Units
Lesson 3: Using Control Structures
  • Topic 3A: Use PL/SQL Operators and Expressions
  • Topic 3B: Use Conditional Statements
  • Topic 3C: Use Iterative Statements
Lesson 4: Handling PL/SQL Exceptions
  • Topic 4A: Handle System-Defined Exceptions
  • Topic 4B: Handle User-Defined Exceptions
Lesson 5: Working with PL/SQL Cursors
  • Topic 5A: Manage Data Using Cursors
  • Topic 5B: Retrieve Data Using the Cursor with Looping Mechanisms
Lesson 6: Creating PL/SQL Subprograms
  • Topic 6A: Use Stored Subprograms
  • Topic 6B: Create User-Defined Subprograms
Lesson 7: Improving Database Performance with PL/SQL Collections and Records
  • Topic 7A: Work with Collections
  • Topic 7B: Create Record Variables
Lesson 8: Using PL/SQL Packages
  • Topic 8A: Create Packages
  • Topic 8B: Work with Product-Specific Packages
Lesson 9: Creating Triggers
  • Topic 9A: Create a Simple Trigger
  • Topic 9B: Create a Compound Trigger
Lesson 10: Using the PL/SQL Compiler
  • Topic 10A: Apply Conditional Compilation
  • Topic 10B: Hide PL/SQL Code
Lesson 11: Managing Dependencies
  • Topic 11A: Work with Trigger Dependencies
  • Topic 11B: Manage Package Dependencies
Lesson 12: Use Dynamic SQL
  • Topic 12A: Use Native Dynamic SQL
  • Topic 12B: Work with the DBMS_SQL Package

Available Formats

Classroom
Live Online
Register