Oracle Database 11g: Advanced Programming with PL/SQL


Description You will design and fine-tune PL/SQL to interface with the database and other applications using advance PL/SQL programming.

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

Learning 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.

Course Content

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