Building Program Units Professionally
- Creating and managing procedures and functions
- Packages: overloading, initialisation, persistent package state
- Design decisions for maintainable PL/SQL code
- Managing dependencies and fine-grained dependency tracking
Collections and Bulk Processing
- Associative arrays, nested tables and VARRAYs compared
- BULK COLLECT with LIMIT, FORALL with SAVE EXCEPTIONS
- Error handling in bulk binding with SQL%BULK_EXCEPTIONS
- Pipelined table functions
Dynamic SQL and Built-in Packages
- EXECUTE IMMEDIATE and DBMS_SQL compared
- Oracle-supplied packages in application development
- DBMS_SCHEDULER, UTL_FILE, DBMS_LOB and DBMS_METADATA
- Native compilation and consistent use of bind variables
Advanced Triggers
- DML, DDL and database event triggers
- Compound triggers and their purpose
- Recognising and resolving the mutating table problem
Large Objects and Modern Data Types
- LOBs and SecureFile LOBs
- Processing JSON data in the database
- Using JSON Relational Duality Views from PL/SQL
- The VECTOR type, DBMS_VECTOR and DBMS_VECTOR_CHAIN, loading ONNX models
Performance Analysis and Tuning
- Controlling the PL/SQL compiler: PLSQL_OPTIMIZE_LEVEL and compiler warnings
- Result Cache for SQL and PL/SQL
- Profiling and tracing with DBMS_PROFILER, DBMS_HPROF and PL/Scope
- Measuring and reducing context switches, PRAGMA UDF and WITH FUNCTION
Security in PL/SQL
- Preventing SQL injection: bind variables and DBMS_ASSERT
- Definer rights versus invoker rights (AUTHID)
- Schema privileges instead of individual grants
- SQL Firewall as an additional layer of protection
Integration and Outlook
- Connecting PL/SQL with external C and Java routines
- JavaScript stored procedures as an alternative to PL/SQL
- Select AI and RAG patterns from PL/SQL
- Preparation for exam 1Z0-149
