Oracle Database 10g Develop PLSQL Program Units

Day 1

• Introduction
• Lesson 1 : Creating Stored Procedures
• Lesson 2 : Creating Stored Functions
• Lesson 3 : Creating Packages
• Lesson 4 : Using More Package Concepts

Day 2

• Lesson 5 : Using Oracle-Supplied Packages in Application Development
• Lesson 6 : Dynamic SQL and Metadata
• Lesson 7 : Design Considerations for PL/SQL Code
• Lesson 8 : Managing Dependencies

Day 3

• Lesson 9 : Manipulating Large Objects
• Lesson 10 : Creating Triggers
• Lesson 11 : Applications for Triggers
• Lesson 12 : Understanding and Influencing the PL/SQL Compiler

Lesson 1 : Creating Stored Procedures

• Describe and create a procedure
• Create procedures with parameters
• Differentiate between formal and actual parameters
• Use different parameter-passing modes
• Invoke a procedure
• Handle exceptions in procedures
• Remove a procedure

Lesson 2 : Creating Stored Functions

• Describe the uses of functions
• Create stored functions
• Invoke a function
• Remove a function
• Differentiate between a procedure and a function

Lesson 3 : Creating Packages

• Describe packages and list their components
• Create a package to group together related variables, cursors, constants, exceptions, procedures, and functions
• Designate a package construct as either public or private
• Invoke a package construct
• Describe the use of a bodiless package

Lesson 4 : Using More Package Concepts

• Overload package procedures and functions
• Use forward declarations
• Create an initialization block in a package body
• Manage persistent package data states for the life of a session
• Use PL/SQL tables and records in packages
• Wrap source code stored in the data dictionary so that it is not readable

Lesson 5 : Using Oracle-Supplied Packages in Application Development

• Describe how the DBMS_OUTPUT package works
• Use UTL_FILE to direct output to operating system files
• Use the HTP package to generate a simple Web page
• Describe the main features of UTL_MAIL
• Call the DBMS_SCHEDULER package to schedule PL/SQL code for execution

Lesson 6 : Dynamic SQL and Metadata

• Describe the execution flow of SQL statements
• Build and execute SQL statements dynamically using Native Dynamic SQL (that is, with EXECUTE IMMEDIATE statements)
• Compare Native Dynamic SQL with the DBMS_SQL package approach
• Use the DBMS_METADATA package to obtain metadata from the data dictionary as XML or creation DDL that can be used to re-create the objects

Lesson 7 : Design Considerations for PL/SQL Code

• Use package specifications to create standard constants and exceptions
• Write and call local subprograms
• Set the AUTHID directive to control the run-time privileges of a subprogram
• Execute subprograms to perform autonomous transactions
• Use bulk binding and the RETURNING clause with DML
• Pass parameters by reference using a NOCOPY hint
• Use the PARALLEL ENABLE hint for optimization

Lesson 8 : Managing Dependencies

• Track procedural dependencies
• Predict the effect of changing a database object on stored procedures and functions
• Manage procedural dependencies

Lesson 9 : Manipulating Large Objects

• ADDM
• Compare and contrast LONG and LOB (large object) data types
• Create and maintain LOB data types
• Differentiate between internal and external LOBs
• Use the DBMS_LOB PL/SQL package
• Describe the use of temporary LOBs

Lesson 10 : Creating Triggers

• Describe the different types of triggers
• Describe database triggers and their uses
• Create database triggers
• Describe database trigger-firing rules
• Remove database triggers

Lesson 11 : Applications for Triggers

• Create additional database triggers
• Explain the rules governing triggers
• Implement triggers

Lesson 12 : Understanding and Influencing the PL/SQL Compiler

• Describe native and interpreted compilations
• List the features of native compilation
• Switch between native and interpreted compilations
• Set parameters that influence PL/SQL compilation
• Query data dictionary views on how PL/SQL code is compiled
• Use the compiler warning mechanism and the DBMS_WARNING package to implement compiler warnings