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.
Code: DB-021
Duration: 24 hours

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.

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

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


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
Bucharest €500
Krakow €500
Wroclaw €500

Invoices for the course will be issued in local currency. All fees above can change according to training location and delivery mode and are subject to change while scheduling. For individual participants the price is as displayed. For legal entities VAT is added to the displayed price.

Courses you may be interested in:

Recommended Reading:

Sign up

First Name*
Last Name*
Company
Email*
Preferred Location
Phone
Your Comment
Luxoft Warsaw - Warsaw Spire, plac Europejski 1, 00-844 Warszawa
Dimitrie Pompeiu nr 5-7 , building C, Et. 5, sect 2, Bucharest, 014459

Contact phone:

021 371 4858
Luxoft Poland Wroclaw - Silver Tower pl. Konstytucji 3-go Maja 3 50-048 Wroclaw
Aleja Generała Tadeusza Bora-Komorowskiego 25, Quattro Business Park Five, 31-476 Kraków, Poland

Contact phone:

+48 122110650
Success
Thank you.
Your request has been received.