Oracle PL/SQL Developer Certified Associate

Course Summary

This comprehensive training program prepares professionals to become Oracle PL/SQL Developer Certified Associates, focusing on developing stored procedures, functions, packages, triggers, and managing programmatic database access in Oracle environments. The course is built around Oracle’s 1Z0-149 certification exam, which validates a developer’s skills in writing efficient and secure PL/SQL code to interact with Oracle databases.

With hands-on practice, participants will learn to build modular, reusable, and optimized database-driven applications that are critical for enterprise systems across sectors like finance, manufacturing, healthcare, and logistics.


Why Choose This Course

PL/SQL is the foundation for advanced Oracle database development, enabling automation, business logic implementation, and secure data processing. This course helps you:

  • Develop real-world expertise in PL/SQL programming and Oracle data structures

  • Gain hands-on experience in writing secure, maintainable, and efficient PL/SQL code

  • Prepare thoroughly for the Oracle 1Z0-149 certification exam

  • Learn from industry-experienced instructors with deep Oracle application knowledge

  • Become proficient in using procedures, functions, cursors, and exception handling


Who Should Enroll

This course is ideal for:

  • Application developers building Oracle-based solutions

  • SQL developers and analysts moving into Oracle PL/SQL development

  • Professionals preparing for the Oracle PL/SQL Developer Certified Associate exam

  • Oracle DBAs who want to expand into PL/SQL scripting and program development

  • Organizations automating business logic within Oracle environments


Skills You Will Gain

  • Mastery of PL/SQL programming fundamentals including syntax, variables, and data types

  • Creating procedures, functions, packages, and triggers for business applications

  • Writing and managing explicit and implicit cursors

  • Implementing exception handling for secure application logic

  • Using dynamic SQL, bulk operations, and compiler features for performance

  • Applying PL/SQL best practices for modularity, reusability, and maintainability


Career Benefits

After completing this course, you will:

  • Be fully prepared to pass the Oracle 1Z0-149 PL/SQL Developer Associate exam

  • Qualify for roles such as Oracle Developer, PL/SQL Programmer, or Database Application Engineer

  • Gain in-demand skills that support ERP systems, banking platforms, healthcare apps, and more

  • Boost your credentials as a developer supporting mission-critical enterprise systems

  • Lay the groundwork for advanced Oracle certifications like OCP – Advanced PL/SQL Developer


Power Enterprise Development with Oracle PL/SQL

From backend logic to performance-optimized procedures, PL/SQL is essential for Oracle application development. This training gives you the tools and credentials to deliver secure, scalable solutions across domains.

Start your PL/SQL journey with expert-led training at Linux Training Center.

Course Syllabus

Modules

MODULE 1 :
- Introduction to PL/SQL
- Explain the need for PL/SQL
- Explain the benefits of PL/SQL
- Identify the different types of PL/SQL blocks
- Output messages in PL/SQL
- Declaring PL/SQL Variables
- Recognize valid and invalid identifiers
- List the uses of variables, declare and initialize variables, use bind variables
- List and describe various data types using the %TYPE attribute
- Writing Executable Statements
- Identify lexical units in a PL/SQL block
- Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
- Describe when implicit conversions take place and when explicit conversions have to be dealt with
- Write nested blocks and qualify variables with labels
- Write readable code with appropriate indentation
- Interacting with the Oracle Database Server
- Create PL/SQL executable blocks using DML and transaction control statements
- Make use of the INTO clause to hold the values returned by a SQL statement
- Writing Control Structures
- Identify the uses and types of control structures (IF, CASE statements and expressions)
- Construct and identify loop statements
- Apply guidelines when using conditional control structures
- Working with Composite Data Types
- Create user-defined PL/SQL records
- Create a record with the %ROWTYPE attribute
- Create an INDEX BY table and INDEX BY table of records
- Describe the differences among records, tables, and tables of records
- Using Explicit Cursors
- Distinguish between usage of implicit and explicit cursors, use SQL cursor attributes
- Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data
- Declare and use cursors with parameters
- Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause
- Handling Exceptions
- Define PL/SQL exceptions
- Recognize unhandled exceptions
- Handle different types of exceptions (pre-defined exceptions, non-predefined exceptions and user-defined exceptions)
- Propagate exceptions in nested blocks and call applications
- Creating Stored Procedures and Functions
- Differentiate between anonymous blocks and subprograms
- Create a simple procedure and invoke it from an anonymous block
- Create a simple function
- Create a simple function that accepts a parameter
- Differentiate between procedures and functions

MODULE 2 :
Oracle Database 11g: Develop PL/SQL Program Units
- Creating Procedures
- Differentiate between anonymous blocks and subprograms, use a modularized and layered subprogram design, and identify the benefits of subprograms
- Create a simple procedure and invoke it from an anonymous block
- Work with procedures
- Handle exceptions in procedures, remove a procedure, and display a procedure's information
- Creating Functions
- Differentiate between a procedure and a function
- Describe the uses of functions
- Work with functions (create, invoke and remove functions)
- Creating Packages
- Identify the benefits and the components of packages
- Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information)
- Working with Packages
- Overload package subprograms, use forward declarations
- Create an initialization block in a package body
- Manage persistent package data states for the life of a session and use PL/SQL tables and records in packages
- Using Oracle-Supplied Packages in Application Development
- Describe how the DBMS_OUTPUT package works
- Use UTL_FILE to direct output to operating system files
- Describe the main features of UTL_MAIL
- Using Dynamic SQL
- Describe the execution flow of SQL statements
- Use Native Dynamic SQL (NDS)
- Use the DBMS_SQL package
- Design Considerations for PL/SQL Code
- Create standard constants and exceptions
- Write and call local subprograms
- Control the run-time privileges of a subprogram
- Perform autonomous transactions
- Use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause
- Use bulk binding and the RETURNING clause with DML
- Creating Triggers
- Describe different types of triggers and their uses
- Create database triggers
- Manage triggers
- Creating Compound, DDL, and Event Database Triggers
- Create triggers on DDL statements
- Create triggers on system events
- Using the PL/SQL Compiler
- Describe the new PL/SQL compiler and features
- Use the new PL/SQL compiler initialization parameters
- Use the new PL/SQL compile time warnings
- Managing PL/SQL Code
- Describe and use conditional compilation
- Hide PL/SQL source code using dynamic obfuscation and the Wrap utility
- Managing Dependencies
- Track and manage procedural dependencies