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