Welcome to Comnet Group Inc.


Oracle Database 11g Advanced Programming with PL/SQL

Course number: CGIOD11GAP40

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 design and fine-tune PL/SQL to interface with the database and other applications using advance PL/SQL programming.

Performance-Based Objectives

Upon successful completion of this course, students will be able to:

  • summarize the fundamental concepts of PL/SQL.
  • design PL/SQL code to develop procedures that are easier to use and maintain.
  • use collections to access, retrieve, and manipulate a set of similar data types.
  • use advanced interface methods.
  • implement Fine Grained Access Control to enforce security and control access to a database.
  • manipulate large objects using PL/SQL.
  • implement SecureFile LOBs.
  • compile and tune PL/SQL programs to improve performance.
  • use caching to improve performance.
  • analyze PL/SQL code.
  • profile and trace PL/SQL code.
  • identify the methods for safeguarding PL/SQL code against SQL injection attacks.
  • Recommend: SQL Fundamentals I & I I
  • Require: Oracle Database 11g: Programming with PL/SQL or equivalent knowledge.
Target Student

IT Professional with programming experience for Oracle and experience with PL/SQL.

Course Outline
Lesson 1: Fundamentals of PL/SQL
  • Topic 1A: PL/SQL Development Environments
  • Topic 1B: Listing restrictions on calling functions from SQL expressions
Lesson 2: Designing PL/SQL Code
  • Topic 2A: Get Started with Cursor Design
  • Topic 2B: Use Cursor Variables
  • Topic 2C: Create Subtypes Based on Existing Data Types
Lesson 3: Using Collections
  • Topic 3A: Create Collections
  • Topic 3B: Manipulate Collections Using Collection Methods
Lesson 4: Using Advanced Interface Methods
  • Topic 4A: Execute Procedures Overview
  • Topic 4B: Execute External C Programs from PL/SQL
  • Topic 4C: Execute Java Programs from PL/SQL
Lesson 5: Implementing VPD with Fine Grained Access Control
  • Topic 5A: Overview of Fine Grained Access Control
  • Topic 5B: Implement FGAC
Lesson 6: Manipulating Larger Objects
  • Topic 6A: Use LOB Data Types
  • Topic 6B: Use DBMS_LOB PL/SQL Package
  • Topic 6C: Use Temporary LOBs
  • Topic 6D: Manage LOB Data Type
Lesson 7: Implement SecureFile LOBs
  • Topic 7A: Migrate BasicFile LOB to the SecureFile LOB Format
  • Topic 7B: Enable SecureFile LOB Deduplication, Compression, and Encryption
Lesson 8: Compiling and Tuning to Improve Performance
  • Topic 8A: Use Native and Interpreted Compilation Methods
  • Topic 8B: Tune PL/SQL Codes
  • Topic 8C: Enable IntraUnit Inlining
Lesson 9: Using Cache to Improve Performance
  • Topic 9A: Describe New Result Cache Features in Oracle 11g
  • Topic 9B: Write Queries Using Result Cache Hint
  • Topic 9C: Set up PL/SQL Functions to Use PL/SQL Result Caching
Lesson 10: Analyzing PL/SQL Code
  • Topic 10A: Run Reports on Source Code
  • Topic 10B: Use DBMS_METADATA to Retrieve Object Definitions
Lesson 11: Profiling and Tracing PL/SQL Codes
  • Topic 11A: Profile PL/SQL Applications
  • Topic 11B: Trace PL/SQL Program Execution
Lesson 12: Identifying Methods for Safeguarding PL/SQL Code Against SQL Injection Attacks
  • Topic 12A: Describe SQL Injection
  • Topic 12B: List Methods to Reduce the Attack Surface
  • Topic 12C: Discuss Methods to Filter Input with DBMS_ASSERT
  • Topic 12D: Identify Methods for Designing Code Immune to SQL Injections
  • Topic 12E: List Methods for Testing Code for SQL Injection Flaws

Available Formats

Live Online