Introduction to PL/SQL

• PL/SQL Overview
• List the benefits of PL/SQL Subprograms
• Overview of the Types of PL/SQL blocks
• Create a Simple Anonymous Block
• Generate the Output from a PL/SQL Block
• 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

PL/SQL Identifiers

• List the different Types of Identifiers in a PL/SQL subprogram
• Usage of the Declarative Section to Define Identifiers
• Use of variables to store data
• Scalar Data Types
• %TYPE Attribute
• Bind Variables
• Sequences in PL/SQL Expressions

Write Executable Statements

• Basic PL/SQL Block Syntax Guidelines
• How to comment code?
• SQL Functions in PL/SQL
• Data Type Conversion
• Nested Blocks
• Operators in PL/SQL

Interaction with the Oracle Server 

• SELECT Statements in PL/SQL to Retrieve data
• Data Manipulation in the Server Using PL/SQL
• The SQL Cursor concept
• Learn to use SQL Cursor Attributes to Obtain Feedback on DML
• How to save and discard transactions?

Control Structures

• Conditional processing Using IF Statements
• Conditional processing Using CASE Statements
• Simple Loop Statement
• While Loop Statement
• For Loop Statement
• The Continue Statement

Usage of Composite Data Types

• PL/SQL Records
• The %ROWTYPE Attribute
• Insert and Update with PL/SQL Records
• Associative Arrays (INDEX BY Tables)
• INDEX BY Table Methods
• INDEX BY Table of Records

Explicit Cursors

• Understand Explicit Cursors
• Declare the Cursor
• How to open the Cursor?
• Fetching data from the Cursor
• How to close the Cursor?
• Cursor FOR loop
• Explicit Cursor Attributes
• FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

• What are Exceptions?
• Handle Exceptions with PL/SQL
• Trap Predefined Oracle Server Errors
• Trap Non-Predefined Oracle Server Errors
• Trap User-Defined Exceptions
• Propagate Exceptions
• RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions

• What are Stored Procedures and Functions?
• Differentiate between anonymous blocks and subprograms
• Create a Simple Procedure
• Create a Simple Procedure with IN parameter
• Create a Simple Function
• Execute a Simple Procedure
• Execute a Simple Function.