Oracle. Advanced PL/SQL

This advanced course dives into the extended capabilities of Oracle PL/SQL for database interaction, optimized code, and secure application design. It covers dynamic SQL, PL/SQL collections, fine-grained access control, and performance tuning. Oracle SQL Developer will be used for hands-on development.
  • duration 24 hours
  • Language English
  • format Online
duration
24 hours
location
Online
Language
English
Code
DB-028
price
€ 650 *

Available sessions

To be determined



Training for 7-8 or more people?
Customize trainings for your specific needs

Description

The Oracle. Advanced PL/SQL course explores the advanced capabilities of Oracle’s PL/SQL, focusing on creating efficient, secure, and high-performance applications. Participants will learn to leverage advanced PL/SQL features, including complex cursors, collections, large objects, and optimized interfaces with external applications. Topics include detailed access control, optimized data manipulation with collections, and advanced interface methods using C and Java. Additionally, students will cover performance improvement techniques, result caching, and strategies for securing code against SQL injection. Practical exercises enable participants to apply PL/SQL’s extended functionality in real-world applications, enhancing the reliability and efficiency of their database interactions.

 

This course begins with a review of core PL/SQL programming concepts, including block structures, cursors, exceptions, and dependencies, setting the foundation for advanced topics. Participants then learn to design optimal PL/SQL code, create custom subtypes, and handle complex cursor operations.

 

Key modules cover PL/SQL collections (associative arrays, nested tables, and varrays) and best practices for manipulating large objects (LOBs), including SecureFile LOBs for secure data handling. Advanced interface methods are introduced for integrating external C and Java routines, expanding PL/SQL’s functionality in multi-language applications.

 

Performance-focused sessions address code optimization, compiler configuration, and caching mechanisms, providing essential skills for building high-performance PL/SQL applications. The course also emphasizes profiling, tracing, and security practices, with a module on fine-grained access control and multitenant architecture to protect sensitive data and ensure compliance. Practical exercises solidify these concepts, allowing participants to design secure, efficient, and scalable database solutions.

 

Upon completion, participants will be able to:

  • Create efficient PL/SQL code for database and application interactions.
  • Utilize collections, LOBs, and external procedures for optimized data management.
  • Implement fine-grained access control and mitigate SQL injection risks.
  • Apply PL/SQL performance tuning techniques, caching, and memory optimization.
  • Use Oracle SQL Developer for debugging, profiling, and tracing PL/SQL code.

 

The course is structured as 60% theory and 40% hands-on practice, enabling participants to apply advanced PL/SQL techniques in real-world scenarios. Labs focus on optimizing performance, implementing secure access, and managing complex data structures.

After completing the course, a certificate is issued on the Luxoft Training form

Objectives

After completion of training, the trainees will be able to:

  1. Write code interacting with other applications and operating system;
  2. Create applications using collections;
  3. Apply the mechanism of detailed access control;
  4. Write code interacting with large objects (LOBs) and use SecureFile LOBs;
  5. Create applications with optimal performance.

Target Audience

  • Developers
  • Application developers
  • PL/SQL developers

Prerequisites

  • SQL experience at least 1 year.

Roadmap

Introduction

  • Course Objectives
  • Course Agenda
  • Describe the development environments
  • Identify the tables, data, and tools used in this course

PL/SQL Programming Concepts: Review

  • Identify PL/SQL block structure
  • Packages, procedures and functions
  • Cursors
  • Handle exceptions
  • Dependencies

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
  • Describe cursor variables
  • White List

Overview of Collections

  • Overview of collections
  • Use Associative arrays
  • Navigate using associative methods
  • Use Nested tables
  • Use Varrays
  • Compare nested tables and varrays

Using Collections

  • Write PL/SQL programs that use collections
  • Use Collections effectively
  • Enhancements to PL/SQL Type Binds

Manipulating Large Objects

  • Working with LOBs
  • Overview of SecureFile LOBs

Using Advanced Interface Methods

  • Calling External Procedures from PL/SQL
  • Benefits of External Procedures
  • Understand how an external routine is called from PL/SQL
  • 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
  • Use PL/SQL function cache
  • Review PL/SQL function cache considerations

Analyzing PL/SQL Code

  • Finding Coding Information
  • PL/Scope Concepts
  • DBMS_METADATA Package
  • PL/SQL Enhancements

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

The Multitenant architecture

  • Oracle Database In-Memory option


Related courses

You may also be interested in

Discover more about professional growth and skills development

contact us