Oracle: Programming with PL/SQL II

• Proficiency in SQL and familiarity with the Oracle database environment
• Solid understanding of basic PL/SQL concepts and experience in writing PL/SQL code

Audience

• Developers
• PL/SQL practitioners

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's proprietary procedural extension to SQL. It is a powerful programming language designed to work seamlessly with the Oracle Database, enabling users to create sophisticated database-driven applications.

This instructor-led, live training (online or onsite) is aimed at advanced-level developers and database administrators who wish to use PL/SQL to develop high-performing, secure, and scalable database solutions while leveraging advanced programming techniques and integrations.

By the end of this training, participants will be able to:

• Work with complex data types, collections, and bulk operations for efficient data management.
• Develop object types, table functions, and custom aggregates to enhance database functionality.
• Apply performance tuning techniques, use native compilation, and safeguard against SQL injection.
• Implement application contexts, VPD, and secure program units for robust database solutions.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Introduction

Complex Data Types and Collections

• Utilizing subtypes for specialized data representations
• Implementing cursor variables and dynamic cursors for flexible data retrieval
• Working with associative arrays and collections to manage sets of data
• Performing bulk operations using BULK COLLECT and FORALL statements
• Handling exceptions in bulk operations effectively

Object-Oriented Features in Oracle Databases

• Defining object types (classes) and their methods
• Creating persistent collections for long-term data storage
• Developing table functions for advanced data manipulation
• Implementing streaming functions for real-time data processing
• Designing custom aggregate functions to extend SQL capabilities

Advanced Function Usage in SQL

• Invoking PL/SQL functions within SQL statements
• Understanding constraints, purity levels, and determinism in function design

Organizing PL/SQL Program Units

• Managing permissions and security within PL/SQL code
• Utilizing naming contexts to avoid identifier conflicts

Integrating PL/SQL with Other Languages

• Incorporating Java methods within PL/SQL programs
• Linking C language procedures for performance-critical operations
• Employing native compilation techniques to enhance execution speed

Pre-built Solutions and Security Mechanisms

• Creating application contexts for session-specific information
• Implementing Virtual Private Database (VPD) for fine-grained access control
• Managing user sessions and contexts effectively
• Profiling and tracing PL/SQL code to identify performance bottlenecks

Optimizing PL/SQL Code Performance

• Utilizing bind variables in SQL statements to enhance execution plans
• Understanding cursor sharing and its impact on performance
• Applying query optimization techniques for efficient data retrieval
• Configuring compilation settings for optimal performance
• Implementing caching strategies for functions and query results

Preventing SQL Injection Attacks

• Adopting best practices to safeguard PL/SQL code against injection vulnerabilities

Summary and Next Steps