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.
Prerequisites
- 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
- Topic 1A: PL/SQL Development Environments
- Topic 1B: Listing restrictions on calling functions from SQL expressions
- Topic 2A: Get Started with Cursor Design
- Topic 2B: Use Cursor Variables
- Topic 2C: Create Subtypes Based on Existing Data Types
- Topic 3A: Create Collections
- Topic 3B: Manipulate Collections Using Collection Methods
- Topic 4A: Execute Procedures Overview
- Topic 4B: Execute External C Programs from PL/SQL
- Topic 4C: Execute Java Programs from PL/SQL
- Topic 5A: Overview of Fine Grained Access Control
- Topic 5B: Implement FGAC
- 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
- Topic 7A: Migrate BasicFile LOB to the SecureFile LOB Format
- Topic 7B: Enable SecureFile LOB Deduplication, Compression, and Encryption
- Topic 8A: Use Native and Interpreted Compilation Methods
- Topic 8B: Tune PL/SQL Codes
- Topic 8C: Enable IntraUnit Inlining
- 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
- Topic 10A: Run Reports on Source Code
- Topic 10B: Use DBMS_METADATA to Retrieve Object Definitions
- Topic 11A: Profile PL/SQL Applications
- Topic 11B: Trace PL/SQL Program Execution
- 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
Live Online