Oracle Database 11g: Advanced PL/SQL (D52601) – Outline

Detailed Course Outline

Introduction
  • Course objectives
  • Course agenda
  • Tables and data used for this course
  • Overview of the development environments: SQL Developer, SQL Plus
PL/SQL Programming Concepts Review
  • Identify PL/SQL block structure
  • Create procedures
  • Create functions
  • List restrictions and guidelines on calling functions from SQL expressions
  • Create packages
  • Review of implicit and explicit cursors
  • List exception syntax
  • Identify the Oracle supplied packages
Designing PL/SQL Code
  • Describe the predefined data types
  • Create subtypes based on existing types for an application
  • List the different guidelines for cursor design
  • Cursor variables
Using Collections
  • Overview of collections
  • Use Associative arrays
  • Use Nested tables
  • Use VARRAYs
  • Compare nested tables and VARRAYs
  • Write PL/SQL programs that use collections
  • Use Collections effectively
Manipulating Large Objects
  • Describe a LOB object
  • Use BFILEs
  • Use DBMS_LOB.READ and DBMS_LOB.WRITE to manipulate LOBs
  • Create a temporary LOB programmatically with the DBMS_LOB package
  • Introduction to SecureFile LOBs
  • Use SecureFile LOBs to store documents
  • Convert BasicFile LOBs to SecureFile LOB format
  • Enable reduplication and compression
Using Advanced Interface Methods
  • Calling External Procedures from PL/SQL
  • Benefits of External Procedures
  • C advanced interface methods
  • Java advanced interface methods
Performance and Tuning
  • Understand and influence the compiler
  • Tune PL/SQL code
  • Enable intra unit inlining
  • Identify and tune memory issues
  • Recognize network issues
Improving Performance with Caching
  • Describe result caching
  • Use SQL query result cache
  • PL/SQL function cache
  • Review PL/SQL function cache considerations
Analyzing PL/SQL Code
  • Finding Coding Information
  • Using DBMS_DESCRIBE
  • Using ALL_ARGUMENTS
  • Using DBMS_UTILITY.FORMAT_CALL_STACK
  • Collecting PL/Scope Data
  • The USER/ALL/DBA_IDENTIFIERS Catalog View
  • DBMS_METADATA Package
Profiling and Tracing PL/SQL Code
  • Tracing PL/SQL Execution
  • Tracing PL/SQL: Steps
Implementing VPD with Fine-Grained Access Control
  • Understand how fine-grained access control works overall
  • Describe the features of fine-grained access control
  • Describe an application context
  • Create an application context
  • Set an application context
  • List the DBMS_RLS procedures
  • Implement a policy
  • Query the dictionary views holding information on fine-grained access
Safeguarding Your Code Against SQL Injection Attacks
  • SQL Injection Overview
  • Reducing the Attack Surface
  • Avoiding Dynamic SQL
  • Using Bind Arguments
  • Filtering Input with DBMS_ASSERT
  • Designing Code Immune to SQL Injections
  • Testing Code for SQL Injection Flaws