Oracle PL/SQL Training Program

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