.webp)
.svg)
Program overview
Online
On-site
Hybrid
Oracle PL/SQL Programming
Four days of PL/SQL for developers building and maintaining Oracle application logic. Covers language fundamentals, cursors, exceptions, procedures, functions and packages, collections, bulk processing with BULK COLLECT and FORALL, triggers, dynamic SQL, JSON and LOBs, compilation and dependencies, performance tuning and secure coding. Delivered on Oracle 19c and 23ai with labs on a realistic schema.
Duration:
4 Days
Level:
Intermediate
.avif)
1500+ users onboarded
Who will Benefit from this Training?
Training Objectives
Build a high-performing, job-ready tech team.
Personalise your team’s upskilling roadmap and design a befitting, hands-on training program with Uptut
Key training modules
Comprehensive, hands-on modules designed to take you from basics to advanced concepts
Download Curriculum
- PL/SQL Language Foundations
- Block structure: declaration, execution and exception sections
- Anonymous blocks, named units and where each belongs
- How PL/SQL and SQL engines interact and the cost of context switching
- Development environment setup and compilation feedback
- Coding standards, naming and formatting conventions
- Hands-on: build and compile a first set of blocks with deliberate errors to diagnose
- Types, Variables and Control Structures
- Scalar types, anchored declarations with %TYPE and %ROWTYPE
- Subtypes, constants and variable scope and visibility
- Conditional logic with IF and CASE
- Loop constructs and loop control statements
- Nested blocks, labels and scope resolution
- Hands-on: implement a business rules routine with layered control flow
- SQL Within PL/SQL
- SELECT INTO and handling no data found and too many rows
- DML statements and implicit cursor attributes
- Transaction control: COMMIT, ROLLBACK and savepoints
- Autonomous transactions and their appropriate use
- The RETURNING clause for capturing modified values
- Hands-on: build a transactional routine with correct commit boundaries
- Cursors and Result Set Processing
- Explicit cursor declaration, opening, fetching and closing
- Cursor FOR loops and parameterised cursors
- Cursor variables, REF CURSOR and returning result sets to clients
- FOR UPDATE and WHERE CURRENT OF for locked processing
- Choosing between cursor loops and set-based SQL
- Hands-on: return a result set to a calling application via a REF CURSOR
- Exception Handling
- Predefined, non-predefined and user-defined exceptions
- RAISE_APPLICATION_ERROR and designing error codes
- SQLCODE, SQLERRM and DBMS_UTILITY error stack functions
- Exception propagation across nested blocks and program units
- Logging errors without swallowing them
- Hands-on: implement an error handling and logging framework
- Procedures and Functions
- Parameter modes, defaults and named notation
- Functions in SQL statements and the purity requirements
- Deterministic functions and result cache
- Invoker rights versus definer rights
- Overloading, forward declaration and recursion
- Hands-on: build a set of stored program units with documented interfaces
- Packages
- Specification and body separation as an interface contract
- Public and private constructs and information hiding
- Package state, initialisation blocks and session persistence
- Overloading within packages and cursor variable exposure
- Useful supplied packages: DBMS_OUTPUT, UTL_FILE, DBMS_SCHEDULER, DBMS_LOB
- Hands-on: refactor standalone procedures into a coherent package API
- Collections and Records
- Associative arrays, nested tables and VARRAYs compared
- Records, nested records and record based DML
- Collection methods, iteration and sparse collections
- Multilevel collections and collections of records
- Persistent collection types in the database
- Hands-on: process a complex dataset in memory using collections
- Bulk Processing and Performance Patterns
- BULK COLLECT with LIMIT for controlled memory use
- FORALL for bulk DML and SAVE EXCEPTIONS for partial failure
- Measuring the context switch cost you eliminate
- Pipelined table functions and parallel enabled functions
- When set-based SQL still beats any PL/SQL approach
- Hands-on: convert a row-by-row routine to bulk processing and benchmark both
- Triggers
- DML trigger types, timing and row versus statement level
- Compound triggers and resolving mutating table errors
- INSTEAD OF triggers on views
- System and DDL event triggers
- Trigger ordering, FOLLOWS and maintainability concerns
- Hands-on: implement an audit and validation trigger set without mutating errors
- Dynamic SQL
- EXECUTE IMMEDIATE and native dynamic SQL
- Bind variables, USING and INTO clauses
- DBMS_SQL for statements with unknown structure at compile time
- SQL injection in PL/SQL and defending with DBMS_ASSERT
- Performance implications of dynamic versus static SQL
- Hands-on: build a dynamic query routine and prove it resists injection
- JSON, Large Objects and External Data
- JSON storage, generation and querying from PL/SQL
- JSON_TABLE, path expressions and PL/SQL JSON object types
- CLOB, BLOB and BFILE handling with DBMS_LOB
- UTL_FILE for server side file reading and writing
- Calling web services and handling structured responses
- Hands-on: parse a JSON payload into relational tables
- Dependencies, Compilation and Code Management
- Dependency tracking, invalidation and recompilation
- Fine-grained dependency behaviour and its effect on deployment
- Conditional compilation for version specific code
- Compiler warnings, PLSQL_WARNINGS and treating warnings as defects
- Native compilation and optimisation levels
- Hands-on: diagnose and resolve an invalidation cascade
- Profiling and Tuning PL/SQL
- DBMS_PROFILER and DBMS_HPROF for hierarchical profiling
- Identifying context switching and excessive SQL execution
- NOCOPY hints, parameter passing and memory behaviour
- Caching strategies: result cache, deterministic functions and package variables
- Reducing PGA consumption in high concurrency workloads
- Hands-on: profile a slow package and deliver a measured improvement
- Debugging, Testing and Secure Coding
- Instrumentation and application tracing with DBMS_APPLICATION_INFO
- Interactive debugging in modern development tools
- Unit testing PL/SQL with utPLSQL and building a regression suite
- Least privilege execution and code security review
- Wrapping, source protection and deployment practice
- Code review checklist for production PL/SQL
- Hands-on: capstone package delivered with tests, instrumentation and a review
Hands-on Experience with Tools
Training Delivery Format
Flexible, comprehensive training designed to fit your schedule and learning preferences
Opt-in Certifications
AWS, Scrum.org, DASA & more
.avif)
100% Live
on-site/online training
.avif)
Hands-on
Labs and capstone projects
.avif)
Lifetime Access
to training material and sessions
Skill-Gap Assessment
Analysing skill gap and assessing business requirements to craft a unique program
1
Personalisation
Customising curriculum and projects to prepare your team for challenges within your industry
2
Implementation
Supplementing training with consulting support to ensure implementation in real projects
3
Why this course
- Bulk processing changes the numbers: Converting row-by-row loops to BULK COLLECT and FORALL routinely cuts runtimes by an order of magnitude, and it gets a dedicated module.
- Packages as the unit of design: Teams learn to structure code into packages with clear interfaces rather than accumulating standalone procedures.
- Injection-safe dynamic SQL: Native dynamic SQL and DBMS_SQL are taught with bind variables and DBMS_ASSERT rather than avoided.
- Debugging and testing included: Instrumentation, profiling and unit testing are part of the course, so code arrives maintainable rather than merely working.
Training objectives
- Write structured PL/SQL blocks using appropriate types and control flow
- Embed SQL in PL/SQL and use implicit and explicit cursors correctly
- Implement layered exception handling with meaningful error contracts
- Build procedures, functions and packages with clean interfaces
- Use collections and records to process structured data in memory
- Apply BULK COLLECT and FORALL to eliminate context switching overhead
- Implement triggers appropriately and avoid mutating table problems
- Write injection-safe dynamic SQL using bind variables
- Work with JSON, large objects and file handling from PL/SQL
- Manage dependencies, invalidation and conditional compilation
- Profile and tune PL/SQL for measurable performance improvement
- Instrument, debug, unit test and secure production PL/SQL code
Who will benefit
- Application developers working against Oracle databases
- Database developers and application DBAs
- Developers migrating to Oracle from another platform
- Data engineers building Oracle based ETL logic
- Technical leads setting PL/SQL coding standards
.avif)
Lead the Digital Landscape with Cutting-Edge Tech and In-House " Techsperts "
Discover the power of digital transformation with train-to-deliver programs from Uptut's experts. Backed by 70,000+ professionals across the world's leading tech innovators.
.svg)
.svg)
.svg)
.svg)
.svg)
Recommended Training
Continue your learning journey with these hand-picked courses and bundles
Frequently Asked Questions
2. Will my team get any practical experience with this training?
.svg)
With our focus on experiential learning, we have made the training as hands-on as possible with assignments, quizzes and capstone projects, and a lab where trainees will learn by doing tasks live.
3. What is your mode of delivery - online or on-site?
We conduct both online and on-site training sessions. You can choose any according to the convenience of your team.
4. Will trainees get certified?
Yes, all trainees will get certificates issued by Uptut under the guidance of industry experts.
5. What do we do if we need further support after the training?
We have an incredible team of mentors that are available for consultations in case your team needs further assistance. Our experienced team of mentors is ready to guide your team and resolve their queries to utilize the training in the best possible way. Just book a consultation to get support.