Oracle Database 11g: Advanced Programming with PL/SQL


Course length: 3.0 day(s)

Course Description

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

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

Prerequisites: Recommend: SQL Fundamentals I & I I

Require: Oracle Database 11g: Programming with PL/SQL or equivalent knowledge.

Hardware Requirements

To use Oracle 11g Release 1 (Standard Edition) on each student’s machine, you need the following hardware:

  • Intel® Pentium® of 800 MHz or higher processor.
  • 2 gigabyte (GB) of RAM or more.
  • 6 gigabytes (GB) of available hard-disk space or more.
  • CD-ROM or DVD drive.
  • Super VGA or higher resolution monitor.
  • Mouse or a compatible pointing device.
  • A projection system to display the instructor’s computer screen.

Software Requirements

Each computer requires the following software:

  • Oracle 11g Release 1 (Standard Edition) server software.
  • Oracle 11g Release 1 (Standard Edition) client software.
  • Microsoft® Office™ Professional 2007 or higher.
  • JDK 6.0 (64 bit) or later.
  • SQL Developer

Course 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


您的电子邮箱地址不会被公开。 必填项已用 * 标注