Oracle: PL/SQL Language for Developers
Duration
32
hours
Location
Online
Language
English
Code
DB-021
-
€
750
*
Training for 7-8 or more people?
Customize trainings
for your specific needs
Description
After completing the course, a certificate
is issued on the Luxoft Training form
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
Prerequisites
- Knowledge of the course
- DB-018 Oracle Database: Basics of PL/SQL Language
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
- 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
- Using USER_DEPENDENCIES and DEPTREE views
- Managing local and remote dependencies
Setting the value of the REMOTE_DEPENDENCIES_MODE initialization parameter
Schedule and prices
10:00-14:00
Code:
DB-021
Location:
Online
Duration:
32 hours
Language:
English
Time:
10:00-14:00
Trainer
Oleksandr Holota
€
750
*
10:00-14:00
Code:
DB-021
Location:
Online
Duration:
32 hours
Language:
English
Time:
10:00-14:00
€
.
.
.
*