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
Online
English
DB-021
Oracle: PL/SQL Language for Developers
Sign Up
Duration
32 hours
Location
Online
Language
English
Code
DB-021
Schedule and prices
€ 550
Training for 7-8 or more people? Customize trainings for your specific needs
Oracle: PL/SQL Language for Developers
Sign Up
Duration
32 hours
Location
Online
Language
English
Code
DB-021
Schedule and prices
€ 550
Training for 7-8 or more people? Customize trainings for your specific needs

Description

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

Objectives

  • 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

Roadmap

Introduction
  • 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
  • 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 package 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
  • Using USER_DEPENDENCIES and DEPTREE views
  • Managing local and remote dependencies
  • Setting the value of the REMOTE_DEPENDENCIES_MODE initialization parameter
Schedule and prices
View:
Register for the next course
Registering in advance ensures you have priority. We’ll notify you when we schedule the next course on this topic
+
Courses you may be interested in
Oracle® 11g, Basics of PL/SQL Language
The course is an introduction to the Oracle PL/SQL procedural language. The language version used in DBMS 11g is considered in this training.
DB-018
24 hours
Sign Up
Development with Java® SE. Beginner
The training covers all the specific topics related to Java development, from UML basics to OOP implementation in Java. A special emphasis is made on syntax basics and key practices of object-oriented programming.
Online:
07.02.2022 - 18.02.2022
Basics of .NET Development and C# Language
The course covers specifics of Microsoft .NET 4.0, program structure, syntax of C# 4.0 language, as well as development skills in Visual Studio® 2010.
Online:
13.12.2021 - 17.12.2021
View Catalog
Your benefits
Expertise
Our trainers are industry experts, involved in software development project
Live training
Facilitated online so that you can interact with the trainer and other participants
Practice
A focus on helping you practice your new skills
Still have questions?
Connect with us
Thank you.
Your request has been received.
Thank you!
The form has been submitted successfully.