Course Description

This instructor-led course provides experienced Oracle developers and database professionals with advanced PL/SQL programming techniques used to build scalable, secure, and high-performing database applications. Students will learn how to leverage collections, large objects, JSON data structures, performance tuning techniques, code profiling tools, and security best practices to develop enterprise-ready PL/SQL solutions.

Through hands-on labs and real-world development exercises, participants will gain practical experience applying advanced Oracle Database 19c features to solve common business and application development challenges.

Audience

• Oracle Developers
• Database Administrators
• Application Developers
• Database Architects
• Technical Consultants

Learning Objectives

Upon successful completion of this course, students will be able to:

• Develop advanced PL/SQL programs using Oracle best practices
• Build reusable and maintainable PL/SQL packages and components
• Utilize collections for efficient data processing
• Work with SecureFile LOBs and large object data
• Process and manipulate JSON data within Oracle databases
• Improve PL/SQL application performance through tuning and caching
• Analyze code execution using Oracle profiling and tracing tools
• Implement secure coding techniques to protect database applications
• Mitigate SQL injection vulnerabilities
• Apply advanced Oracle PL/SQL security mechanisms

Course Outline

Day 1: Advanced Program Design and Collections

Module 1: Advanced PL/SQL Development Practices

• Reviewing advanced PL/SQL language features
• Structuring maintainable PL/SQL applications
• Designing reusable program units
• Applying PL/SQL development best practices

Module 2: Packages and Modular Development

• Package specifications and package bodies
• Managing dependencies
• Encapsulation and code reuse
• Building maintainable application frameworks

Module 3: Working with Collections

• Collection fundamentals
• Associative arrays
• Nested tables
• VARRAYs
• Collection methods and operations

Module 4: Advanced Collection Processing

• Bulk Collect
• FORALL processing
• Reducing context switching
• Improving data processing efficiency

Hands-On Labs

• Creating reusable PL/SQL packages
• Building collection-based solutions
• Implementing bulk processing operations

Day 2: Modern Data Handling and Performance Optimization

Module 5: Managing Large Objects

• Understanding LOB architecture
• Working with BLOBs and CLOBs
• SecureFile LOB implementation
• Temporary LOB management

Module 6: JSON Data in Oracle

• JSON storage options
• Querying JSON documents
• JSON functions and operators
• Integrating JSON with PL/SQL applications

Module 7: Advanced Interface Methods

• Interfacing PL/SQL with external applications
• Data exchange strategies
• Application integration considerations

Module 8: Oracle Exadata and Cloud Services Overview

• Introduction to Oracle Exadata Express Cloud Service
• Cloud-based database development considerations
• PL/SQL development in modern Oracle environments

Hands-On Labs

• Managing SecureFile LOBs
• Processing JSON data
• Integrating external data structures into PL/SQL applications

Day 3: Performance Tuning, Diagnostics, and Security

Module 9: PL/SQL Performance and Tuning

• Identifying performance bottlenecks
• Improving execution efficiency
• Result caching techniques
• SQL versus PL/SQL optimization strategies

Module 10: Code Analysis and Profiling

• Using the PL/SQL Profiler
• Tracing PL/SQL execution
• Monitoring code performance
• Interpreting profiling results

Module 11: Securing PL/SQL Applications

• Secure coding principles
• Definer rights versus invoker rights
• Privilege management
• Protecting application logic

Module 12: Preventing SQL Injection Attacks

• Common attack methods
• Secure dynamic SQL techniques
• Input validation strategies
• Defensive programming practices

Module 13: Advanced Security Mechanisms

• Code protection techniques
• Auditing and monitoring
• Security best practices for enterprise applications

Hands-On Labs

• Profiling and optimizing PL/SQL code
• Identifying and resolving performance issues
• Implementing secure coding controls
• Mitigating SQL injection vulnerabilities

Why Train with Logical Operations?

Logical Operations delivers Oracle training through live instructor-led virtual and private onsite formats. Our courses emphasize hands-on learning, practical exercises, and real-world application development scenarios. Students leave with skills they can immediately apply to improve Oracle database performance, application scalability, and security within their organizations.