Oracle: PL/SQL Language for Developers

Oracle: PL/SQL Language for Developers

The course offers a more detailed study of the procedural language Oracle PL/SQL. During the course we explain how to use the PL/SQL stored objects of Oracle DBMS (procedures, functions, packages, triggers), which offer powerful possibilities to create business logic in Oracle based applications. New features of 10g version are discussed as well.

32 hours
Course type
32 hours
Schedule and prices
€ 750 *
Training for 7-8 or more people? Customize trainings for your specific needs
Oracle: PL/SQL Language for Developers
32 hours
Schedule and prices
€ 750 *
Training for 7-8 or more people? Customize trainings for your specific needs


The course covers the Oracle 11g R2 version of the PL/SQL language. During the training we cover subjects related to the use of stored Oracle DBMS PL/SQL objects (procedures, functions, packages, triggers), which provide powerful capabilities for creating business logic for applications using Oracle technology. We will also talk about the new features of the 11 g version.

In addition the course covers some of the standard packages, dynamic SQL, mass loading of data using collections, ways of influencing the compilation of PL/SQL-code and the mechanism of dependency management. Oracle SQL Developer is used for developing.

After completing the course, a certificate
is issued on the Luxoft Training form


  • Creating, modifying and deleting stored PL/SQL objects
  • Handling exceptions more effectively
  • How to use standard Oracle database packages
  • How to use dynamic SQL
  • Working with object types and collections more effectively

Target Audience

  • Database administrators
  • System administrators
  • Application developers
  • Software Testers


  • Knowledge of the course
  • DB-018 Oracle Database: Basics of PL/SQL Language



  • PL/SQL programming constructs
  • Description of HR (Human Resources) schema, used in the course
  • Working with Oracle SQL Developer
  • Editing and execution of an anonymous block in SQL Developer
  • Preservation and execution of SQL scripts
  • Links to additional resources

Creating procedures

  • Description of procedure usage
  • Creating procedures
  • Creating procedures with parameters
  • Calling a procedure
  • Exception handling
  • Deleting a procedure

Creating functions

  • Description of using functions
  • Creating functions
  • Calling functions
  • Functions in SQL statements
  • Deleting functions
  • Differences between procedures and functions

Creating packages

  • Description of packages and list of packages’ components
  • Package specification and package body
  • Creating packages, defining private and public structures
  • Calling package objects
  • Deleting packages

Additional features of working with packages

  • Overloading package procedures and functions
  • Avoiding mistakes in mutual references of routines
  • Initialization of variables in one time procedure
  • Using package functions in SQL
  • Saving states of package variables, cursors, tables and records
  • Using PL/SQL record tables in packages

Using some of the standard Oracle packages (for input/output)

  • Overview of standard Oracle packages
  • DBMS_OUTPUT package
  • UTL_FILE package
  • UTL_MAIL package
  • DBMS_SCHEDULER package
  • DBMS_METADATA package
  • DBMS_LOB package

Dynamic SQL

  • Phases of SQL statement execution
  • Execution of dynamic SQL statements using EXECUTE IMMEDIATE
  • Execution of dynamic SQL statements using DBMS_SQL package

PL/SQL code design

  • Creating constants and exceptions in packages
  • Local subroutines
  • Creator's rights and calling stored procedure rights
  • Using NOCOPY hint for program parameters
  • Autonomous transactions
  • Using DETERMINISTIC clause
  • Using collections for bulk operations

Creating database triggers

  • Description of different types of triggers
  • Defining database triggers and using triggers
  • Creating database triggers
  • String and operator triggers on tables
  • Triggers on view, INSTEAD OF
  • Rules for database triggers
  • Deleting database triggers

Triggers on DDL statements and database events

  • Creating triggers on DDL statements
  • Triggers on system events in the database
  • Restrictions on database triggers
  • LOGON and LOGOFF triggers
  • CALL statement in a trigger
  • Triggers design recommendations

Using PL/SQL compiler

  • Initialization parameters for PL/SQL compilation
  • Compile-time warnings
  • DBMS_WARNING package
  • PLW 06009 warning message

PL/SQL code management

  • PLSQL_CCFLAGS parameter
  • DBMS_DB_VERSION package
  • Conditional compilation
  • Hiding code (obfuscation)
  • Convolution utility
  • DBMS_DDL package

Dependency tracking

  • Overview of object dependencies
  • Procedural dependencies in data dictionary views
  • Assessing the impact of changes to database objects on the stored procedures
  • Managing local and remote dependencies

Setting the value of the REMOTE_DEPENDENCIES_MODE initialization parameter

Schedule and prices
Code: DB-021
Location: Online
Duration: 32 hours
Language: English
Time: 10:00-14:00
Trainer Oleksandr Holota
€ 750 *
Still have questions?
Connect with us