Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

In banking, insurance, telecoms and enterprise ERP landscapes, core business logic and end-of-day batch pipelines execute right next to the data in Oracle PL/SQL. Written with naive row-by-row cursor loops or scattered standalone procedures, PL/SQL code suffers from heavy SQL–PL/SQL context switching and becomes difficult to maintain.

This course covers PL/SQL block architecture, composite data types (Records and Collections), explicit and parameterised Cursors, structured exception handling and autonomous error logging, modular Package design, Compound Triggers, secure Native Dynamic SQL, and high-speed bulk processing with BULK COLLECT and FORALL.

Who Should Attend?

  • PL/SQL and database developers building core business logic, integrations and batch/ETL routines on Oracle
  • Backend software engineers working with Oracle-backed enterprise systems
  • Engineering teams refactoring and tuning existing PL/SQL packages
  • Prerequisite: working knowledge of SQL querying (SELECT, JOINs and DML)

Key Takeaways

  • Use %TYPE, %ROWTYPE, Records, Associative Arrays and Nested Tables idiomatically
  • Encapsulate procedures, functions, private helpers and types inside cohesive PL/SQL Packages
  • Implement persistent error logging with FORMAT_ERROR_BACKTRACE and PRAGMA AUTONOMOUS_TRANSACTION
  • Replace slow row-by-row loops with BULK COLLECT ... LIMIT and FORALL ... SAVE EXCEPTIONS
  • Write injection-safe, cursor-sharing Native Dynamic SQL using bind variables and DBMS_ASSERT

Curriculum

PL/SQL Architecture, Block Structure and Composite Types
  • Interaction between the PL/SQL and SQL engines, block anatomy and variable scoping
  • Anchored declarations (%TYPE, %ROWTYPE) and custom PL/SQL Record types
  • Collections compared: Associative Arrays (INDEX BY), Nested Tables, VARRAYs and collection methods
Cursor Management and Enterprise Exception Handling
  • Implicit vs Explicit Cursors, Cursor FOR loops, parameterised cursors and returning SYS_REFCURSOR result sets
  • Predefined exceptions, PRAGMA EXCEPTION_INIT and raising domain errors with RAISE_APPLICATION_ERROR
  • Capturing line numbers with FORMAT_ERROR_BACKTRACE and logging via PRAGMA AUTONOMOUS_TRANSACTION
Procedures, Functions and Modular Package Design
  • Parameter modes (IN, OUT, IN OUT, NOCOPY) and purity rules for calling PL/SQL functions from SQL
  • Separating Package Specifications from Package Bodies, subprogram overloading and session state
  • Definer's vs Invoker's Rights (AUTHID CURRENT_USER), dependency invalidation and RESULT_CACHE
Triggers, Mutating Tables and Secure Dynamic SQL
  • Row-level vs statement-level DML triggers and when logic belongs in a Package instead of a Trigger
  • Understanding the Mutating Table error (ORA-04091) and resolving it cleanly with a COMPOUND TRIGGER
  • Native Dynamic SQL (EXECUTE IMMEDIATE), bind variables (USING) and SQL injection defence with DBMS_ASSERT
High-Performance PL/SQL: Bulk Processing and Profiling
  • The cost of SQL–PL/SQL context switches in row-by-row batch loops
  • Chunked fetching with BULK COLLECT INTO ... LIMIT to protect PGA memory, plus Pipelined Table Functions
  • Batched DML with FORALL ... SAVE EXCEPTIONS and profiling bottlenecks with DBMS_HPROF

Other Courses in This Category

  • Relational Data Modelling, SQL Normalization and Schema Optimization

    Conceptual/logical/physical data modelling, 1NF–3NF/BCNF normalization rules, preventing data anomalies, controlled denormalization, natural vs surrogate keys, constraints and zero-downtime schema evolution.

    Databases Corporate
    Live Course
    5 modules
    View
  • SQL Server Database Querying Fundamentals SQL Querying Fundamentals

    Foundational SQL Server course covering relational database concepts, data modeling, normalization, indexing, database objects (Tables, Views, Stored Procedures), and JOIN queries.

    Databases Corporate
    Live Course
    8 modules
    View
  • SQL Server Database Querying Training SQL Server Querying

    Comprehensive SQL Server and T-SQL querying course covering SSMS, SELECT workflows, multi-table JOINs (INNER, OUTER, CROSS, SELF), filtering and pagination, data types, and built-in Scalar, Aggregate, and Window functions.

    Databases Corporate
    Live Course
    7 modules
    View
  • Big Data Querying and Analytics with Apache Spark SQL

    Apache Spark SQL and the DataFrame API, Catalyst Optimizer and Adaptive Query Execution (AQE), Parquet/Delta Lake/Iceberg table formats, join strategies and fixing data skew — for teams building big-data analytics.

    Databases Corporate
    Live Course
    5 modules
    View
  • PostgreSQL for Developers: From Fundamentals to Advanced SQL

    PostgreSQL data types, advanced SQL (CTEs, window functions, LATERAL), JSONB document modelling, full-text search, B-Tree/GIN/GiST/BRIN indexes, PL/pgSQL, MVCC/locking and partitioning — for application developers.

    Databases Corporate
    Live Course
    5 modules
    View
  • PostgreSQL Database Administration, High Availability and Operations

    PostgreSQL architecture, memory and WAL tuning, MVCC/Autovacuum and bloat control, PgBouncer connection pooling, PITR backups with pgBackRest, Streaming/Logical Replication and Patroni high availability.

    Databases Corporate
    Live Course
    5 modules
    View

Get in touch