Develop Pl/Sql Program Units

Overview of PL/SQL

• Distinguishing between anonymous PL/SQL blocks and PL/SQL subprograms

• Describing the PL/SQL developmentenvironments

Working with Procedure Builder

• Describe the features of Procedure Builder

• Manage program units using the Object Navigator

• Create and compile program units using the Program Unit Editor

• Invoke program units using the PL/SQL interpreter

Creating Procedures

• Describe the usese of procedures

• Create client-side and server-side procedures

• Create procedures with arguments

• Invoke a procedure

• Remove a procedure

Creating Functions

• Describe the uses of functions

• Create client-side and server-side functions

• Invoke a function

• Remove a function

• Differentiate between a procedure and a function

Creating Packages

• Describe packages and list their possible components

• Create a package to group together related variables, cursors, constructs, exceptions, procedures and functions

• Make a package construct either public or private

• Invoke a package construct

• Write packages that make use of the overloading feature of PL/SQL

• Avoid errors with mutually referential subprograms

• Initialize public or private variables with an automatic one time only procedure

• Declare ref cursors in a package

Creating Database Triggers

• Describe database triggers and their use

• Create database triggers

• Describe database trigger firing rules

• Drop database triggers

• Additional Package Concepts

Managing Subprograms

• Describe system privilege requirements

• Track procedural dependencies

• Predict the effect of changing a database object upon stored procedures and functions

• Describe object dependencies

• Debug client-side and server-side subprograms

Handling exceptions

• User defined exceptions

• Pre-defined Oracle Server exceptions

• Non-pre-defined Oracle Exceptions