Oracle11g: Advanced PL/SQL

• Introduction to PL/SQL

PL/SQL Programming Concepts: Review

• List restrictions on calling functions from SQL expressions
• Hand exceptions
• Manage dependencies
• Use Oracle-supplied packages

Designing PL/SQL Code

• Identify guidelines for cursor design
• Use cursor variables
• Create subtypes based on existing types

Working with Collections

• Create collections (nested table, array and associative arrays, PL SQL tables)
• Use collections methods
• Manipulate collections
• Distinguish between the different types of collections and their uses

Using Advanced Interface Methods

• Execute external C programs from PL/SQL
• Execute Java programs from PL/SQL

Implementing Fine-Grained Access Control for VPD

• Explain the process of fine-grained access control
• Implement and test fine-grained access control

Manipulating Large Objects

• Create and manage LOB data types
• Use the DBMS_LOB PL/SQL package
• Use of temporary LOBs

Administering SecureFile LOBs

• Describe SecureFile LOB features
• Enable SecureFile LOB deduplication, compression, and encryption
• Migrate BasicFile LOBs to the SecureFile LOB format

Performance and Tuning

• Use native and interpreted compilation methods
• Tune PL/SQL code
• Enable intraunit inlining

Improving Performance with Caching

• Improve memory usage by caching SQL result sets and using the DBMS_RESULT_CACHE package
• Write queries that use the result cache hint
• Set up PL/SQL functions to use PL/SQL result caching

Analyzing PL/SQL Code

• Run reports on source code
• Determine identifier types and usages
• Use DBMS_METADATA to retrieve object definitions

Profiling and Tracing PL/SQL Code

• Trace PL/SQL program execution
• Profile PL/SQL applications

Safeguarding Your Code Against SQL Injection Attacks

• Describe SQL injections
• Reduce attack surfaces
• Use DBMS_ASSERT
• Design immune code
• Test code for SQL injection flaws