Course Outline
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 COLLECTandFORALLstatements - 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
Requirements
- 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
Testimonials (3)
I liked very much the format of the training, hands on, directly in sql developer.
Andreea - OPEN COURSE
Course - Oracle Database 19c PL and Advanced SQL
The exercises were explained very good.
Monica - DB Global Technology
Course - PL/SQL in Oracle 19c
An excellent workshop led by the instructor. The training style is modeled on a laboratory, where the participant works together with the instructor to set hypotheses, choose research methodology, conduct analysis, and perform demonstrations. Good materials, examples from real life.
Robert Pisek - Biuro Projektowania Systemow Cyfrowych sp. z o.o.
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
