PL/SQL packages, triggers, and settings in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize the characteristics of PL/SQL packages and triggers and identify the configuration options that control PL/SQL performance.

1. PL/SQL packages

A package is a group of functions and procedures that are stored together. Grouping functions and procedures in this manner improves performance and facilitates maintenance.

PL/SQL packages consist of two objects.

package specification

package body

package specification: The package specification is sometimes known as the package header. It contains the definition of the functions, procedures, and variables for the package. It's object type is PACKAGE.
package body: The package body contains the actual code for the subprograms that are defined in the package specification. It's object type is PACKAGE BODY.

To call procedures and functions from within a package, you use dot notation syntax. The package name and the procedure or function name are separated by a dot.

packagename.procedureorfunctionname

The characteristics of package bodies fall into four categories.

separateness from specification

subprograms

compiling

wrapping

separateness from specification: Because package bodies are separate from package specifications, you can change and recompile the code of the body without invalidating objects that depend on the specification.
subprograms: Package bodies contain the code for the subprograms defined in the package specification. The specification holds information on how to call subprograms within the package, whereas the body holds the subprogram code that is executed.
compiling: Package bodies cannot be compiled unless the package specification has already been compiled. You can create a specification without a body, but you cannot create a body without a specification.
wrapping: You can wrap package bodies to hide the code, using a standalone program called WRAP. This wraps or obfuscates PL/SQL source code, so you can distribute PL/SQL applications without exposing the source code.

This is an example of the code for a package body called MONEY, which creates the COMPUTETAX function and the GIVERAISE procedure.

CREATE OR REPLACE PACKAGE BODY money AS
FUNCTION computetax (salary NUMBER) RETURN NUMBER IS
BEGIN
IF salary<5000 THEN
RETURN salary*.15;
ELSE
RETURN salary*.33;
END IF;
END computetax;
PROCEDURE giveraise AS
BEGIN
EXECUTE IMMEDIATE 'UPDATE hr.employees -
SET salary=salary*1.05';
END giveraise;
END money;

Question

Match the package components with their characteristics.

Options:

Contains code for subprograms

Contains definitions of procedures, functions, and variables

Can be wrapped to conceal source code

Has the PACKAGE object type

Targets:

package body

package specification

Answer

The package body contains code for subprograms and can be wrapped. The package specification is of the PACKAGE object type and contains definitions of procedures, functions, and variables.

Oracle Database 10g features over 350 built-in PL/SQL packages, which enable you to access extended functionality such as advanced queuing, encryption, and heterogeneous services.

Built-in packages also include administration and maintenance utilities.

Examples of commonly used built-in packages are

DBMS_OBFUSCATION_TOOLKIT

DBMS_RESOURCE_MANAGER

DBMS_SCHEDULER

DBMS_SESSION

DBMS_STATS

DBMS_OBFUSCATION_TOOLKIT: The DBMS_OBFUSCATION_TOOLKIT built-in package encrypts, decrypts, and computes checksums.
DBMS_RESOURCE_MANAGER: You use the DBMS_RESOURCE_MANAGER built-in package to maintain resource manager objects.
DBMS_SCHEDULER: The DBMS_SCHEDULER built-in package enables the scheduling of functions and procedures that can be called from any PL/SQL program.
DBMS_SESSION: The DBMS_SESSION built-in package enables PL/SQL access to ALTER SESSION and SET ROLE statements.
DBMS_STATS: You use the DBMS_STATS built-in package to collect, view, and modify optimizer statistics.

2. PL/SQL triggers

Triggers are PL/SQL code objects that automatically fire when a specified event occurs in the database.

Examples of triggering events in Oracle are

inserting a record into a table

logging in to the database

attempting to drop a table or change settings

Triggers can call procedures and functions, assist in value-based auditing, enforce constraints, and automate tasks.

Note

Experienced developers usually keep the trigger's code as short as possible and place any lengthy code in a separate package.

The SECURE_EMPLOYEES trigger shown here logs all DDL statements (INSERT, UPDATE, and DELETE) to a holding table.

Most events used to fire a trigger can do so either before or after they occur. For INSERT, UPDATE, and DELETE statements, the trigger can fire once per statement, or for each row that is modified.

Supplement

Selecting the link title opens the resource in a new browser window.

Launch window

View the events that can fire a PL/SQL trigger.

Question

Identify the events that can be used to fire a DML trigger.

Options:

DELETE

INSERT

SELECT

UPDATE

Answer

Events that can be used to fire a trigger include INSERT, UPDATE, and DELETE.

Option 1 is correct. The DELETE statement can fire a trigger once for the statement, or with each row that is modified.

Option 2 is correct. The INSERT statement can fire a trigger once for the statement, or with each row that is modified.

Option 3 is incorrect. The SELECT statement does not modify anything in the database and so does not fire a trigger.

Option 4 is correct. The UPDATE statement can fire a trigger once for the statement, or with each row that is modified.

3. PL/SQL configuration options

You use PL/SQL compiler settings to control PL/SQL performance.

The most important initialization parameters for controlling PL/SQL performance are

PLSQL_CODE_TYPE

PLSQL_DEBUG

PLSQL_OPTIMIZE_LEVEL

PLSQL_WARNINGS

PLSQL_CODE_TYPE: The PLSQL_CODE_TYPE parameter allows you to specify how compiled PL/SQL code is stored. Interpreted bytecode is the default, and provides better debugging for development. Native machine code gives the best runtime performance, as it runs up to twice as quickly as interpreted bytecode.
PLSQL_DEBUG: Setting the PLSQL_DEBUG parameter to TRUE enables additional debugging features for development, and stores code as interpreted, regardless of how the PLSQL_CODE_TYPE parameter is set.
PLSQL_OPTIMIZE_LEVEL: You should set the PLSQL_OPTIMIZE_LEVEL parameter to the default level of 1 to optimize compiler performance. If code changes are infrequent and applications typically use stored PL/SQL, setting the level to 2 provides better runtime performance but less effective compiler performance.
PLSQL_WARNINGS: The PLSQL_WARNINGS parameter accepts two arguments. The first enables or disables warning messages from the PL/SQL compiler. The second argument applies the first to ALL warning messages, or limits it to SEVERE, INFORMATIONAL, or PERFORMANCE messages. Setting the parameter to DISABLE:ALL ensures the best performance.

Question

Match the PL/SQL compiler settings with their characteristics.

Options:

PLSQL_CODE_TYPE

PLSQL_DEBUG

PLSQL_OPTIMIZE_LEVEL

PLSQL_WARNINGS

Targets:

Specifies how compiled PL/SQL code is stored

Enables additional debugging features for development

Optimizes compiler performance when set to 1

Controls compiler warning messages

Answer

The PL/SQL compiler settings have been matched with their characteristics.

Interpreted bytecode provides better debugging, but native machine code gives the best runtime performance.

When enabled, the PLSQL_DEBUG parameter always stores code as interpreted.

For applications that use stored PL/SQL, level 2 provides better runtime performance but less effective compiler performance.

Warning messages from the compiler can be completely enabled or disabled, or limited to SEVERE, INFORMATIONAL, or PERFORMANCE messages.

Summary

A PL/SQL package is a group of functions and procedures. It consists of the package specification and the package body. The package specification contains definitions for the package's functions, procedures, and variables. The package body contains the actual code for the specified subprograms. Oracle Database 10g features several built-in PL/SQL packages.

Triggers are PL/SQL code objects that automatically fire when a specified event occurs in the database. Triggers can call procedures and functions, assist in value-based auditing, enforce constraints, and automate tasks. Most events can fire a trigger either before or after they occur.

You use PL/SQL compiler settings to control PL/SQL performance. The most important initialization parameters for controlling PL/SQL performance are PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL, and PLSQL_WARNINGS.

Oracle 10g

Tuesday, September 4, 2007