Oracle. Advanced PL/SQL

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 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.

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