Oracle 11g Advanced PL/SQL Programming

Training Description

This extended Oracle 11g Advanced PL/SQL Programming course is designed to give PL/SQL programmers who are already confident programming with PL/SQL's basic features (cursors, exceptions, etc.) an exploration of the advanced features of the language. Participants learn how to use dynamic SQL and autonomous transactions, all types of PL/SQL collections, cursor variables, implement fine-grained access control, manipulate LOBs and SecureFiles, understand and influence the compiler, enable subprogram inlining, use the PL/SQL function result cache, and profile and trace PL/SQL code. Optimization techniques are emphasized throughout the course.

Training  Objectives

After having completed this course, you will be able to:

• Build and execute SQL and PL/SQL dynamically
• Define and manipulate all three types of collections
• Compare the three types of collections
• Define autonomous transactions
• Use common PL/SQL built-in packages
• Utilize cursor variables
• Change PL/SQL's default security by using invoker's rights
• Increase performance of large processing by performing bulk processing
• Use Fine-Grained Access Control
• Manipulate Large Objects using DBMS_LOB package
• Understand and influence the PL/SQL compiler
• Improve performance of your code with SQL and PL/SQL Caching

• Use PL/SCOPE and DBMS_METADATA packages

• Trace and debug your code using the DBMS_PROFILER and DBMS_TRACE packages

Prerequisites

SQL, PL/SQL Programming and Relational Database Design Concepts

Audience

Application developers, database administrators and system administrators

Training Outline

Overview of the Development Environments

• SQL Developer
• SQL*Plus

Using Dynamic SQL and PL/SQL

Defining Autonomous Transactions

Overview of Common PL/SQL package built-ins

Overloading Code

Implement Invoker's Rights

Using Cursor Variables

• Define both weak and strong cursor variables
• bind SQL statement to a cursor variable
• Pass cursor variables as program parameters
• Compare cursor variables to static cursors

Using Collections

• Overview of collections
• Use Associative arrays
• Use Nested tables
• Use Varrays
• Write PL/SQL programs that use collections

Performing Bulk Operations

• Bulk fetches
• Bulk DML
• Using FORALL statement
• Using RETURNING in a DML statement
• SELECT... INTO a collection

Calling Java from PL/SQL

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
• Use the DBMS_RLS package
• Implement a policy
• Query associated data dictionary views

Manipulating Large Objects

• Describe a LOB object
• Manage internal LOBs
• Describe BFILEs
• Create and use the DIRECTORY object to access and use BFILEs
• Describe the DBMS_LOB package
• Remove LOBs

Administering SecureFile LOBs

• Introduction to SecureFile LOBs
• Enable the environment for SecureFile LOBs
• Use SecureFile LOBs to store documents
• Convert BasicFile LOBs to SecureFile LOB format
• Enable deduplication and compression
• Enable encryption

Tuning and Performance

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

Improving Performance with SQL and PL/SQL Caching

• Describe result caching
• Use SQL query result cache
• Use PL/SQL function cache

Analyzing PL/SQL Code

• Use PL/SCOPE to determine identifier types and usages
• Use the DBMS_METADATA package to obtain metadata from the data dictionary

Profiling and Tracing PL/SQL Code

• Use the DBMS_PROFILER package
• Use the DBMS_TRACE package