Oracle PL/SQL Programming 26ai

6 Saturdays (9am to 11am) | Intermediate to Advanced | Course Notes

Course Content

Week 1 — PL/SQL Foundations & New Data Types

• Review of PL/SQL architecture: blocks, variables, control structures, and exception handling
• Understanding the BOOLEAN data type — now fully supported in SQL and PL/SQL[reference:0]
• Working with the new VECTOR data type for AI workloads in PL/SQL[reference:1]
• Creating and declaring BINARY vectors in PL/SQL for similarity searches[reference:2]
• Using the JACCARD distance metric for binary vector similarity in PL/SQL[reference:3]
• Dimension-wise arithmetic support for vectors in PL/SQL[reference:4]
• Sparse vector support in PL/SQL[reference:5]
• Best practices for PL/SQL development in the AI database

Week 2 — Advanced PL/SQL Programming & SQL Integration

• Writing complex PL/SQL procedures, functions, and packages
• Using bulk operations (BULK COLLECT, FORALL) for high-performance data processing
• Integrating PL/SQL with SQL: TABLE functions and PIPELINED functions
• Working with JSON in PL/SQL: JSON_VALUE and aggregate type conversions[reference:6]
• Using the JSON_ARRAY constructor by query in PL/SQL[reference:7]
• Dynamic SQL with EXECUTE IMMEDIATE and DBMS_SQL
• Implementing Autonomous Transactions for independent transaction control
• Error handling and logging frameworks for enterprise applications

Week 3 — SQL Simplifications & Language Enhancements

• Leveraging time bucketing functions for time-series analysis[reference:8]
• Using non-positional INSERT syntax for cleaner code[reference:9]
• Implementing GROUP BY ALL for aggregated rollups[reference:10]
• Enhanced CASE control structures in PL/SQL[reference:11]
• Working with Assertions — a new constraint type for data integrity[reference:12]
• Calendar functions and aggregation filters[reference:13]
• Data Use Case Domains for column metadata and validation[reference:14]
• Improved error messages for better debugging[reference:15]

Week 4 — Security: SQL Firewall & Unified Auditing

• Understanding the Oracle SQL Firewall — bundled with Oracle AI Database[reference:16]
• Configuring SQL Firewall policies from PL/SQL
• Using SQL Firewall for real-time protection against SQL injection and unauthorized queries[reference:17]
• Integrating SQL Firewall with PL/SQL applications
• Implementing Unified Auditing for PL/SQL execution monitoring
• Using schema-level privileges for simplified access control
• Working with Azure AD OAuth2 integration for secure authentication[reference:18]
• Best practices for securing PL/SQL code and database objects

Week 5 — Performance Tuning & Optimization

• Understanding Dynamic Statistics for PL/SQL functions[reference:19]
• Using the Automatic SQL Transpiler to convert PL/SQL functions into SQL expressions[reference:20]
• Optimizing PL/SQL function calls in SQL queries
• Using the SQL Diagnostic Report for deep performance analysis
• Working with optimizer statistics and execution plans
• Performance tuning for vector operations and AI workloads in PL/SQL
• Implementing Priority Transactions to handle lock contention[reference:21]
• Using Telemetry Streaming for real-time performance monitoring

Week 6 — Advanced Features & Capstone Project

• Working with Wide Tables — up to 4096 columns using MAX_COLUMNS=EXTENDED[reference:22]
• PL/SQL procedures for creating and managing wide tables[reference:23]
• Integrating PL/SQL with AI Vector Search, JSON Duality, and Property Graphs
• Using Blockchain Table Log History for immutable data[reference:24]
• Implementing Sessionless Transactions and Lock-Free Reservations[reference:25]
• Deploying PL/SQL applications with CI/CD pipelines
• Version control and team development best practices
• Capstone Project: Build a complete PL/SQL application integrating AI Vector Search, JSON, and SQL Firewall security for a real-world business scenario

Project: Design and implement an enterprise PL/SQL application leveraging Oracle AI Database 26ai features including vector operations, JSON processing, SQL Firewall security, and performance optimization.

Starting Date: 4th July 2026
Course Fee: £2990