During this course, you’ll explore Oracle PL/SQL stored program units, including triggers, procedures, functions and packages. Closely aligned to Oracle’s Database curriculum, you’ll also gain the practical skills and techniques that help you to develop robust, maintainable code within real-world Oracle environments.
Oracle PL-SQL Stored Program Units
Build advanced PL/SQL skills that drive real impact
Over three days, you’ll develop the capability to write and manage stored programme units. You’ll explore how to streamline code, improve performance and maintain cleaner, more efficient Oracle applications. This course will also help you prepare for the Oracle Database: Program with PL/SQL 1Z0-149 exam.
Learning objectives
- Create and manage stored procedures
- Develop effective PL/SQL triggers
- Build structured, reusable packages
- Apply Oracle-supplied packages in real scenarios
- Work with cursor variables for dynamic data handling
- Manage dependencies across stored units
- Improve performance using bulk binding techniques
- Implement native dynamic SQL with confidence
What you’ll learn
Build practical skills in writing and managing PL/SQL programme units including triggers, procedures, packages and cursor variables. Learn how to optimise code, handle dependencies and apply Oracle-supplied packages to improve performance, reduce errors and streamline operations across your Oracle database environments.
Cursor variables (REF CURSORs)
Learn how to declare, use and manage REF CURSORs to return query results dynamically. These are ideal when working with flexible SQL or returning result sets from procedures. Gaining confidence with cursor variables gives you the knowledge to build more adaptable, modular applications that respond dynamically to user inputs or changing business logic.
Triggers
Explore how to write and manage Oracle triggers that execute in response to data changes or system events. You’ll cover DML, DDL and compound triggers, and learn how to use them effectively. Mastering triggers enhances automation and ensures data consistency, reducing the need for manual checks and improving database reliability.
Procedures and functions
Understand how to create and manage PL/SQL procedures and functions for structured, reusable code. You'll explore parameter techniques, subprograms, and autonomous transactions. Learning these skills enables you to build cleaner, more secure applications that reduce code duplication, simplify debugging and improve long-term system maintainability.
Oracle-supplied packages
Our course will introduce you to key Oracle-supplied packages including DBMS_SQL and DBMS_SCHEDULER which help you automate and simplify development tasks. Learning how to apply these packages saves time, improves reliability and lets you focus on building better functionality by avoiding the need to write low-level operations from scratch.
What’s included
- Three days of instructor-led training in a live virtual classroom
- Interactive hands-on live labs
- All relevant course materials
Key facts
Ideal for
Oracle developers, DBAs and technical specialists who need to create or maintain PL/SQL triggers, procedures, functions and packages as part of their day-to-day role.
Prerequisites
You’ll need a good working knowledge of Oracle PL/SQL and Oracle SQL. Experience using SQL*Plus or Oracle SQL Developer is also a must.
Learning experience
Our experienced trainers and interactive labs create a focused, practical environment – so you can put theory into practice from day one.
FAQs
This course gives you the hands-on skills to build, manage and troubleshoot PL/SQL programme units. It’s ideal for anyone ready to take their Oracle development skills up a level or aiming for certification.
Why should I use PL/SQL packages in my Oracle applications?
When it comes to organising code, packages are a powerful tool. They let you group related procedures, functions, variables and even cursors into a single, reusable unit. This helps keep your applications structured and manageable, especially as they grow in size. You’ll learn how to build packages with public and private components, manage package states, use forward declarations and even wrap packages for added security. It's a key skill for any dev working in Oracle environments.
How do Oracle triggers work and when should I use them?
Oracle triggers are PL/SQL code blocks that fire automatically when specific events occur in the database, like before or after an insert, update or delete. You’ll learn how to use them to enforce business rules, maintain audit trails or automate routine database tasks without needing manual input. This course explores both row-level and statement-level triggers, along with system and DDL triggers, so you know exactly when and how to apply them in real-world applications.
How do I use REF CURSORs in PL/SQL?
REF CURSORs, also known as cursor variables, allow you to open and pass query results dynamically between different PL/SQL programme units. They’re especially useful when you don’t know in advance what query structure you’ll need. On this course, you’ll get hands-on experience using REF CURSORs to fetch rows, pass results between procedures and use them flexibly in more modular, reusable PL/SQL code.
What our customers say
"This is now my second course with John at StayAhead and once again the delivery was simply excellent. I was a little bit worried coming in that I wouldn't have the requisite knowledge, however, I found that it was just about the perfect level between being able to understand the concepts and stretching the grey matter. Best fun I've had at work for a while now and would very highly recommend the course to anyone who's taking on more PL/SQL work in their job. Hoping to come back for more in the future!"
Ashley, AppOps Engineer (Jr DBA) - Government
"Really informative course, structured to support all forms of learning and at a good pace."
Owen, Graduate Trainee Engineer - Utilities
Why study with StayAhead
96% customer satisfaction
We combine experience and passion to deliver training that meets the highest standards
30+ years’ experience
Industry-leading specialists delivering high-impact IT training
Experiential instructor-led training
Courses are delivered by experienced instructors using hands-on, practical live labs