Oracle Database 12c/18c/19c Advanced PLSQL

Day 1

• Lesson 1 : Introduction
• Lesson 2 : PL/SQL Programming Concepts: Review
• Lesson 3 : Designing PL/SQL Code
• Lesson 4 : Working with Collections

Day 2

• Lesson 5 : Using Advanced Interface Methods
• Lesson 6 : Implementing Fine-Grained Access Control for VPD
• Lesson 7 : Manipulating Large Objects
• Lesson 8 : Administering SecureFile LOBs
• Lesson 9 : Performance and Tuning

Day 3

• Lesson 10 : Improving Performance with Caching
• Lesson 11 : Analyzing PL/SQL Code
• Lesson 12 : Profiling and Tracing PL/SQL Code
• Lesson 13 : Safeguarding Your Code Against SQL Injection Attacks

Lesson 1 : Introduction

• Design PL/SQL packages and program units that execute efficiently
• Write code to interface with external applications and the operating system
• Create PL/SQL applications that use collections
• Write and tune PL/SQL code effectively to maximize performance
• Implement a virtual private database with fine-grained access control
• Write code to interface with large objects and use SecureFile LOBs
• Perform code analysis to find program ambiguities, test, trace, and profile PL/SQL code

Lesson 2 : PL/SQL Programming Concepts: Review

• Describe PL/SQL basics
• List restrictions on calling functions from SQL expressions
• Identify how explicit cursors are processed
• Handle exceptions
• Use the raise_application_error procedure
• Manage dependencies
• Use Oracle-supplied packages

Lesson 3 : Designing PL/SQL Code

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

Lesson 4 : Working with Collections

• Create collections
• Nested table, varray
• Associative arrays/PLSQL tables
  • Integer indexed
  • String indexed
• Use collections methods
• Manipulate collections
• Distinguish between the different types of collections and when to use them

Lesson 5 : Using Advanced Interface Methods

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

Lesson 6 : Implementing Fine-Grained Access Control for VPD

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

Lesson 7 : Manipulating Large Objects

• Compare and contrast LONG and large object (LOB) data types
• Create and maintain LOB data types
• Differentiate between internal and external LOBs
• Use the DBMS_LOB PL/SQL package
• Describe the use of temporary LOBs

Lesson 8 : Administering SecureFile LOBs

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

Lesson 9 : Performance Tuning

• Understand and influence the compiler
• Tune PL/SQL code
• Enable intraunit inlining

Lesson 10 : Improving Performance with Caching

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

Lesson 11 : Analyzing PL/SQL Code

• Use the supplied packages and dictionary views to find coding information
• Determine identifier types and usages with PL/Scope
• Use the DBMS_METADATA package to obtain metadata from the data dictionary as XML or creation DDL that can be used to re-create the objects

Lesson 12 : Profiling and Tracing PL/SQL Code

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

Lesson 13 : 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