Oracle: PL/SQL language for developers

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.

• 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

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