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
