WA1737 Oracle Database 11g: PL/SQL Fundamentals II - Develop Program Units Training and Courseware

The objective of this course is to equip students to develop database server-side PL/SQL program units within an Oracle database.

What you will learn

Among the specific subjects discussed in this course are:

• Understanding application partitioning within a client/server or multi-tiered web-based systems architecture.
• Understanding the basic form and structure of program units stored within the database.
• Building and maintaining database-resident program units.
• Encapsulating program units within packages and taking advantage of accompanying advanced programming techniques such as cursor variables and cursor expressions.
• Handling intricate theoretical challenges, such as mutating tables.
• Building and maintaining DML-event and system-event database triggers, including advanced techniques using both simple triggers and compound triggers.
• Discuss the storage and execution model for database programs and how one can write efficient programs to maximize performance.
• Using system-supplied packages to extend the power of your SQL statements and PL/SQL applications.

Audience

Target audience for this course is all Oracle professionals. Among the specific groups for whom this course will be helpful are:

• Application designers and developers
• Database administrators

Duration

Three days.

Course Outline

1. Introducing database program units

• About database program units
• Types of pl/sql program units
• Types of stored program units
• Advantages of using stored program units

2. Creating stored procedures & functions

• About stored procedures & functions
• Creating procedures & functions
• Executing procedures & functions

3. Maintaining stored procedures & functions

• Recompiling & dropping programs
• Data dictionary storage
• Managing dependencies

4. Creating & maintaining packages

• About packages
• Creating packages
• Advanced programming techniques
• Maintaining packages

5. Advanced cursor techniques

• Using cursor variables
• Using cursor expressions

6. Using system-supplied packages

• Dbms_output()
• Utl_file()

7 Creating database triggers

• About database triggers
• Statement-level triggers
• Row-level triggers
• Examples of triggers
• Instead of triggers
• Employing triggers within an application

8. Maintaining database triggers

• Call syntax
• Trigger maintenance tasks
• Show errors trigger
• Drop trigger
• Alter trigger
• Handling multiple triggers for a table
• Handling mutating table issues

9. Implementing system event triggers

• What are system event triggers?
• Defining the scope
• Available system events
• System event attributes

Buy on-site and customized class

Attend a public class