Oracle: PL/SQL Language for Developers
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.
wydawany jest certyfikat
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