Capability Development / Database & DataScience
Oracle PL/SQL Programming
This intensive PL/SQL program transforms participants into confident Oracle database programmers capable of building secure, scalable, and high-performance database applications. The course progresses from core PL/SQL foundations, variables, control structures, and cursors, through modular programming with procedures, functions, packages, and triggers. Participants also learn robust exception handling, debugging, dynamic SQL, bulk processing, and dependency management, alongside compiler tuning techniques for production-ready code. The strong applied focus helps learners write cleaner code, automate business logic, and build reusable program units that support enterprise Oracle solutions.
Program Overview
This intensive PL/SQL program transforms participants into confident Oracle database programmers capable of building secure, scalable, and high-performance database applications. The course progresses from core PL/SQL foundations, variables, control structures, and cursors, through modular programming with procedures, functions, packages, and triggers. Participants also learn robust exception handling, debugging, dynamic SQL, bulk processing, and dependency management, alongside compiler tuning techniques for production-ready code. The strong applied focus helps learners write cleaner code, automate business logic, and build reusable program units that support enterprise Oracle solutions.
Audience Profile
Oracle SQL developers moving into procedural programming; Application developers building database-driven logic; Database administrators who need to write and review PL/SQL code; and Technical professionals preparing for Oracle developer certification.
Course Syllabus
Module 1: Introduction
Topics
- Oracle Database focus areas and PL/SQL development environments
- Oracle SQL Developer interface and specifications
- Coding PL/SQL in SQL*Plus and SQL Developer Web
Module 2: Introduction to PL/SQL
Topics
- Limitations of SQL and the benefits of PL/SQL
- PL/SQL runtime architecture and block structure
- Block types: anonymous blocks, procedures, and functions
- Executing an anonymous block and enabling output
Module 3: Declaring PL/SQL Variables
Topics
- Variable naming rules, declaration, and initialization
- Data types for strings, numbers, dates, and Booleans
- The %TYPE attribute and LOB data type variables
- Composite data types (records and collections) and bind variables
Module 4: Writing Executable Statements
Topics
- Lexical units and PL/SQL block syntax guidelines
- Using SQL functions and sequences within PL/SQL
- Nested blocks and variable scope and visibility
- Operators, programming guidelines, and code indentation
Module 5: Using SQL Statements Within a PL/SQL Block
Topics
- SELECT statements and retrieving data in PL/SQL
- Avoiding naming ambiguities
- Using PL/SQL to insert, update, delete, and merge data
- The implicit SQL cursor and its attributes
Module 6: Writing Control Structures
Topics
- IF, IF-ELSIF-ELSE, and CASE expressions and statements
- Handling NULLs in conditional logic
- Basic, WHILE, and FOR loops
- Nested loops, labels, and the CONTINUE statement
Module 7: Working with Composite Data Types
Topics
- PL/SQL records versus collections
- Creating records and using the %ROWTYPE attribute
- Associative arrays (INDEX BY tables) and collection methods
- Nested tables and variable-size arrays (VARRAYs)
Module 8: Using Explicit Cursors
Topics
- Implicit versus explicit cursors
- Declaring, opening, fetching, and closing cursors
- Cursor FOR loops and explicit cursor attributes
- Cursors with parameters, the FOR UPDATE clause, and WHERE CURRENT OF
Module 9: Handling Exceptions
Topics
- What an exception is and how exceptions are trapped
- Predefined, non-predefined, and user-defined exceptions
- The RAISE statement and RAISE_APPLICATION_ERROR procedure
- Propagating exceptions across sub-blocks
Module 10: Introducing Stored Procedures and Functions
Topics
- What PL/SQL subprograms are
- Differences between anonymous blocks and subprograms
- Procedure and function syntax
- Creating, invoking, and passing parameters to procedures and functions
Module 11: Creating Procedures
Topics
- Modularized program design and benefits of modularization
- Creating, compiling, and calling procedures
- Parameters and parameter modes: IN, OUT, and IN OUT
- Handled versus unhandled exceptions, and removing/viewing procedures
Module 12: Creating Functions
Topics
- Creating and compiling functions
- Differences between procedures and functions
- Calling functions from SQL expressions and related restrictions
- Controlling side effects and passing parameters to functions
Module 13: Debugging Subprograms
Topics
- Debugging workflow and the debugging log toolbar
- Setting breakpoints and compiling for debug mode
- Stepping through code: step into, step over, and step out
- Debugging subprograms remotely
Module 14: Creating Packages
Topics
- What a package is and its advantages
- Components of a PL/SQL package: specification and body
- Creating and invoking package subprograms
- Bodiless packages and viewing packages via the data dictionary
Module 15: Working with Packages
Topics
- Overloading subprograms in PL/SQL and its restrictions
- The STANDARD package
- Package instantiation, initialization, and persistent state
- Using user-defined package functions in SQL
Module 16: Using Oracle-Supplied Packages in Application Development
Topics
- The DBMS_OUTPUT package
- The UTL_FILE package for file processing
- The UTL_MAIL package for sending email with attachments
Module 17: Using Dynamic SQL
Topics
- What dynamic SQL is and when to use it
- Native Dynamic SQL and the EXECUTE IMMEDIATE statement
- Dynamic SQL with DDL, DML, and anonymous blocks
- BULK COLLECT INTO, OPEN FOR, and the DBMS_SQL package
Module 18: Creating Triggers
Topics
- What triggers are and why they are used
- Trigger event types and timing
- Creating DML statement-level and row-level triggers
- OLD/NEW qualifiers, conditional predicates, and INSTEAD OF triggers
Module 19: Creating Compound, DDL, and Event Database Triggers
Topics
- Compound trigger structure and restrictions
- Resolving mutating table errors with compound triggers
- Creating triggers on DDL statements and system events (LOGON/LOGOFF)
Module 20: Design Considerations for the PL/SQL Code
Topics
- Standardizing constants and exception handling
- Definer's rights versus invoker's rights
- Autonomous transactions and the NOCOPY and PARALLEL_ENABLE hints
- The function result cache, DETERMINISTIC clause, and bulk binding with FORALL
Module 21: Tuning the PL/SQL Compiler
Topics
- Initialization parameters for PL/SQL compilation
- PL/SQL compile-time warnings and warning categories
- Setting compiler warning levels with PLSQL_WARNINGS
- Using the DBMS_WARNING package
Module 22: Managing Dependencies
Topics
- Dependencies in a schema and how they work
- Direct and indirect dependencies
- Fine-grained dependency management
- Remote dependencies, timestamp/signature checking, and recompilation