Oracle9i: Program with PL/SQL
Instructor Guide • Volume 2

40054GC11 Production 1.1 October 2001 D34008

Authors
Nagavalli Pataballa Priya Nathan

Copyright © Oracle Corporation, 1999, 2000, 2001. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Education Products, Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. All references to Oracle and Oracle products are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.

Technical Contributors and Reviewers
Anna Atkinson Bryan Roberts Caroline Pereda Cesljas Zarco Coley William Daniel Gabel Dr. Christoph Burandt Hakan Lindfors Helen Robertson John Hoff Lachlan Williams Laszlo Czinkoczki Laura Pezzini Linda Boldt Marco Verbeek Natarajan Senthil Priya Vennapusa Roger Abuzalaf Ruediger Steffan Sarah Jones Stefan Lindblad Susan Dee

Publisher
Sheryl Domingue

Contents
Curriculum Map I Introduction Course Objectives I-2 About PL/SQL I-3 PL/SQL Environment I-4 Benefits of PL/SQL I-5 Benefits of Subprograms I-10 Invoking Stored Procedures and Functions I-11 Summary I-12 Declaring Variables Objectives 1-2 PL/SQL Block Structure 1-3 Executing Statements and PL/SQL Blocks 1-4 Block Types 1-5 Program Constructs 1-6 Use of Variables 1-7 Handling Variables in PL/SQL 1-8 Types of Variables 1-9 Using iSQL*Plus Variables Within PL/SQL Blocks 1-10 Types of Variables 1-11 Declaring PL/SQL Variables 1-12 Guidelines for Declaring PL/SQL Variables 1-13 Naming Rules 1-14 Variable Initialization and Keywords 1-15 Scalar Data Types 1-17 Base Scalar Data Types 1-18 Scalar Variable Declarations 1-22 The %TYPE Attribute 1-23 Declaring Variables with the %TYPE Attribute 1-24 Declaring Boolean Variables 1-25 Composite Data Types 1-26 LOB Data Type Variables 1-27 Bind Variables 1-28 Using Bind Variables 1-30 Referencing Non-PL/SQL Variables 1-31 DBMS_OUTPUT.PUT_LINE 1-32 Summary 1-33 Practice 1 Overview 1-35

1

iii

2

Writing Executable Statements Objectives 2-2 PL/SQL Block Syntax and Guidelines 2-3 Identifiers 2-5 PL/SQL Block Syntax and Guidelines 2-6 Commenting Code 2-7 SQL Functions in PL/SQL 2-8 SQL Functions in PL/SQL: Examples 2-9 Data Type Conversion 2-10 Nested Blocks and Variable Scope 2-13 Identifier Scope 2-15 Qualify an Identifier 2-16 Determining Variable Scope 2-17 Operators in PL/SQL 2-18 Programming Guidelines 2-20 Indenting Code 2-21 Summary 2-22 Practice 2 Overview 2-23

3

Interacting with the Oracle Server Objectives 3-2 SQL Statements in PL/SQL 3-3 SELECT Statements in PL/SQL 3-4 Retrieving Data in PL/SQL 3-7 Naming Conventions 3-9 Manipulating Data Using PL/SQL 3-10 Inserting Data 3-11 Updating Data 3-12 Deleting Data 3-13 Merging Rows 3-14 Naming Conventions 3-16 SQL Cursor 3-18 SQL Cursor Attributes 3-19 Transaction Control Statements 3-21 Summary 3-22 Practice 3 Overview 3-24

iv

4

Writing Control Structures Objectives 4-2 Controlling PL/SQL Flow of Execution 4-3 IF Statements 4-4 Simple IF Statements 4-5 Compound IF Statements 4-6 IF-THEN-ELSE Statement Execution Flow 4-7 IF-THEN-ELSE Statements 4-8 IF-THEN-ELSIF Statement Execution Flow 4-9 IF-THEN-ELSIF Statements 4-11 CASE Expressions 4-12 CASE Expressions: Example 4-13 Handling Nulls 4-15 Logic Tables 4-16 Boolean Conditions 4-17 Iterative Control: LOOP Statements 4-18 Basic Loops 4-19 WHILE Loops 4-21 FOR Loops 4-23 Guidelines While Using Loops 4-26 Nested Loops and Labels 4-27 Summary 4-29 Practice 4 Overview 4-30 Working with Composite Data Types Objectives 5-2 Composite Data Types 5-3 PL/SQL Records 5-4 Creating a PL/SQL Record 5-5 PL/SQL Record Structure 5-7 The %ROWTYPE Attribute 5-8 Advantages of Using %ROWTYPE 5-10 The %ROWTYPE Attribute 5-11 INDEX BY Tables 5-13 Creating an INDEX BY Table 5-14 INDEX BY Table Structure 5-15 Creating an INDEX BY Table 5-16 Using INDEX BY Table Methods 5-17 INDEX BY Table of Records 5-18 Example of INDEX BY Table of Records 5-19 Summary 5-20 Practice 5 Overview 5-21

5

v

6

Writing Explicit Cursors Objectives 6-2 About Cursors 6-3 Explicit Cursor Functions 6-4 Controlling Explicit Cursors 6-5 Declaring the Cursor 6-9 Opening the Cursor 6-11 Fetching Data from the Cursor 6-12 Closing the Cursor 6-14 Explicit Cursor Attributes 6-15 The %ISOPEN Attribute 6-16 Controlling Multiple Fetches 6-17 The %NOTFOUND and %ROWCOUNT Attributes 6-18 Example 6-20 Cursors and Records 6-21 Cursor FOR Loops 6-22 Cursor FOR Loops Using Subqueries 6-24 Summary 6-26 Practice 6 Overview 6-27

7

Advanced Explicit Cursor Concepts Objectives 7-2 Cursors with Parameters 7-3 The FOR UPDATE Clause 7-5 The WHERE CURRENT OF Clause 7-7 Cursors with Subqueries 7-9 Summary 7-10 Practice 7 Overview 7-11

8

Handling Exceptions Objectives 8-2 Handling Exceptions with PL/SQL 8-3 Handling Exceptions 8-4 Exception Types 8-5 Trapping Exceptions 8-6 Trapping Exceptions Guidelines 8-7 Trapping Predefined Oracle Server Errors 8-8 Predefined Exceptions 8-11 Trapping Nonpredefined Oracle Server Errors 8-12 Nonpredefined Error 8-13 Functions for Trapping Exceptions 8-14 Trapping User-Defined Exceptions 8-16 User-Defined Exceptions 8-17 vi Calling Environments 8-18 Propagating Exceptions 8-19 The RAISE_APPLICATION_ERROR Procedure 8-20 RAISE_APPLICATION_ERROR 8-22 Summary 8-23 Practice 8 Overview 8-24 9 Creating Procedures Objectives 9-2 PL/SQL Program Constructs 9-4 Overview of Subprograms 9-5 Block Structure for Anonymous PL/SQL Blocks 9-6 Block Structure for PL/SQL Subprograms 9-7 PL/SQL Subprograms 9-8 Benefits of Subprograms 9-9 Developing Subprograms by Using iSQL*Plus 9-10 Invoking Stored Procedures and Functions 9-11 What Is a Procedure? 9-12 Syntax for Creating Procedures 9-13 Developing Procedures 9-14 Formal Versus Actual Parameters 9-15 Procedural Parameter Modes 9-16 Creating Procedures with Parameters 9-17 IN Parameters: Example 9-18 OUT Parameters: Example 9-19 Viewing OUT Parameters 9-21 IN OUT Parameters 9-22 Viewing IN OUT Parameters 9-23 Methods for Passing Parameters 9-24 DEFAULT Option for Parameters 9-25 Examples of Passing Parameters 9-26 Declaring Subprograms 9-27 Invoking a Procedure from an Anonymous PL/SQL Block 9-28 Invoking a Procedure from Another Procedure 9-29 Handled Exceptions 9-30 Unhandled Exceptions 9-32 Removing Procedures 9-34 Summary 9-35 Practice 9 Overview 9-37

10 Creating Functions Objectives 10-2 Overview of Stored Functions 10-3 vii Syntax for Creating Functions 10-4 Creating a Function 10-5 Creating a Stored Function by Using iSQL*Plus 10-6 Creating a Stored Function by Using iSQL*Plus: Example 10-7 Executing Functions 10-8 Executing Functions: Example 10-9 Advantages of User-Defined Functions in SQL Expressions 10-10 Invoking Functions in SQL Expressions: Example 10-11 Locations to Call User-Defined Functions 10-12 Restrictions on Calling Functions from SQL Expressions 10-13 Restrictions on Calling from SQL 10-15 Removing Functions 10-16 Procedure or Function? 10-17 Comparing Procedures and Functions 10-18 Benefits of Stored Procedures and Functions 10-19 Summary 10-20 Practice 10 Overview 10-21

11 Managing Subprograms Objectives 11-2 Required Privileges 11-3 Granting Access to Data 11-4 Using Invoker's-Rights 11-5 Managing Stored PL/SQL Objects 11-6 USER_OBJECTS 11-7 List All Procedures and Functions 11-8 USER_SOURCE Data Dictionary View 11-9 List the Code of Procedures and Functions 11-10 USER_ERRORS 11-11 Detecting Compilation Errors: Example 11-12 List Compilation Errors by Using USER_ERRORS 11-13 List Compilation Errors by Using SHOW ERRORS 11-14 ESCRIBE in iSQL*Plus 11-15 Debugging PL/SQL Program Units 11-16 Summary 11-17 Practice 11 Overview 11-19 12 Creating Packages Objectives 12-2 Overview of Packages 12-3 Components of a Package 12-4 Referencing Package Objects 12-5 Developing a Package 12-6 viii Creating the Package Specification 12-8 Declaring Public Constructs 12-9 Creating a Package Specification: Example 12-10 Creating the Package Body 12-11 Public and Private Constructs 12-12 Creating a Package Body: Example 12-13 Invoking Package Constructs 12-15 Declaring a Bodiless Package 12-17 Referencing a Public Variable from a Stand-Alone Procedure 12-18 Removing Packages 12-19 Guidelines for Developing Packages 12-20 Advantages of Packages 12-21 Summary 12-23 Practice 12 Overview 12-26 13 More Package Concepts Objectives 13-2 Overloading 13-3 Overloading: Example 13-5 Using Forward Declarations 13-8 Creating a One-Time-Only Procedure 13-10 Restrictions on Package Functions Used in SQL 13-11 User Defined Package: taxes_pack 13-12 Invoking a User-Defined Package Function from a SQL Statement 13-13 Persistent State of Package Variables: Example 13-14 Persistent State of Package Variables 13-15 Controlling the Persistent State of a Package Cursor 13-18 Executing PACK_CUR 13-20 PL/SQL Tables and Records in Packages 13-21 Summary 13-22 Practice 13 Overview 13-23 14 Oracle Supplied Packages Objectives 14-2 Using Supplied Packages 14-3 Using Native Dynamic SQL 14-4 Execution Flow 14-5 Using the DBMS_SQL Package 14-6 Using DBMS_SQL 14-8 Using the EXECUTE IMMEDIATE Statement 14-9 Dynamic SQL Using EXECUTE IMMEDIATE 14-11 Using the DBMS_DDL Package 14-12 Using DBMS_JOB for Scheduling 14-13 ix DBMS_JOB Subprograms 14-14 Submitting Jobs 14-15 Changing Job Characteristics 14-17 Running, Removing, and Breaking Jobs 14-18 Viewing Information on Submitted Jobs 14-19 Using the DBMS_OUTPUT Package 14-20 Interacting with Operating System Files 14-21 What Is the UTL_FILE Package? 14-22 File Processing Using the UTL_FILE Package 14-23 UTL_FILE Procedures and Functions 14-24 Exceptions Specific to the UTL_FILE Package 14-25 The FOPEN and IS_OPEN Functions 14-26 Using UTL_FILE 14-27 The UTL_HTTP Package 14-29 Using the UTL_HTTP Package 14-30 Using the UTL_TCP Package 14-31 Oracle-Supplied Packages 14-32 Summary 14-37 Practice 14 Overview 14-38

15 Manipulating Large Objects Objectives 15-2 What Is a LOB? 15-3 Contrasting LONG and LOB Data Types 15-4 Anatomy of a LOB 15-5 Internal LOBs 15-6 Managing Internal LOBs 15-7 What Are BFILEs? 15-8 Securing BFILEs 15-9 A New Database Object: DIRECTORY 15-10 Guidelines for Creating DIRECTORY Objects 15-11 Managing BFILEs 15-12 Preparing to Use BFILEs 15-13 The BFILENAME Function 15-14 Loading BFILEs 15-15 Migrating from LONG to LOB 15-17 The DBMS_LOB Package 15-19 DBMS_LOB.READ and DBMS_LOB.WRITE 15-22 Adding LOB Columns to a Table 15-23 Populating LOB Columns 15-24 Updating LOB by Using SQL 15-26 Updating LOB by Using DBMS_LOB in PL/SQL 15-27 Selecting CLOB Values by Using SQL 15-28 x Selecting CLOB Values by Using DBMS_LOB 15-29 Selecting CLOB Values in PL/SQL 15-30 Removing LOBs 15-31 Temporary LOBs 15-32 Creating a Temporary LOB 15-33 Summary 15-34 Practice 15 Overview 15-35

16 Creating Database Triggers Objectives 16-2 Types of Triggers 16-3 Guidelines for Designing Triggers 16-4 Database Trigger: Example 16-5 Creating DML Triggers 16-6 DML Trigger Components 16-7 Firing Sequence 16-11 Syntax for Creating DML Statement Triggers 16-13 Creating DML Statement Triggers 16-14 Testing SECURE_EMP 16-15 Using Conditional Predicates 16-16 Creating a DML Row Trigger 16-17 Creating DML Row Triggers 16-18 Using OLD and NEW Qualifiers 16-19 Using OLD and NEW Qualifiers: Example Using Audit_Emp_Table 16-20 Restricting a Row Trigger 16-21 INSTEAD OF Triggers 16-22 Creating an INSTEAD OF Trigger 16-23 Differentiating Between Database Triggers and Stored Procedures 16-28 Differentiating Between Database Triggers and Form Builder Triggers 16-29 Managing Triggers 16-30 DROP TRIGGER Syntax 16-31 Trigger Test Cases 16-32 Trigger Execution Model and Constraint Checking 16-33 Trigger Execution Model and Constraint Checking: Example 16-34 A Sample Demonstration for Triggers Using Package Constructs 16-35 After Row and After Statement Triggers 16-36 Demonstration: VAR_PACK Package Specification 16-37 Summary 16-40 Practice 16 Overview 16-41 17 More Trigger Concepts Objectives 17-2 Creating Database Triggers 17-3 xi Creating Triggers on DDL Statements 17-4 Creating Triggers on System Events 17-5 LOGON and LOGOFF Trigger Example 17-6 CALL Statements 17-7 Reading Data from a Mutating Table 17-8 Mutating Table: Example 17-9 Implementing Triggers 17-11 Controlling Security Within the Server 17-12 Controlling Security with a Database Trigger 17-13 Using the Server Facility to Audit Data Operations 17-14 Auditing by Using a Trigger 17-15 Enforcing Data Integrity Within the Server 17-16 Protecting Data Integrity with a Trigger 17-17 Enforcing Referential Integrity Within the Server 17-18 Protecting Referential Integrity with a Trigger 17-19 Replicating a Table Within the Server 17-20 Replicating a Table with a Trigger 17-21 Computing Derived Data Within the Server 17-22 Computing Derived Values with a Trigger 17-23 Logging Events with a Trigger 17-24 Benefits of Database Triggers 17-26 Managing Triggers 17-27 Viewing Trigger Information 17-28 Using USER_TRIGGERS 17-29 Summary 17-31 Practice 17 Overview 17-32 18 Managing Dependencies Objectives 18-2 Understanding Dependencies 18-3 Dependencies 18-4 Local Dependencies 18-5 A Scenario of Local Dependencies 18-7 Displaying Direct Dependencies by Using USER_DEPENDENCIES 18-8 Displaying Direct and Indirect Dependencies 18-9 Displaying Dependencies 18-10 Another Scenario of Local Dependencies 18-11 A Scenario of Local Naming Dependencies 18-12 Understanding Remote Dependencies 18-13 Concepts of Remote Dependencies 18-15 REMOTE_DEPENDENCIES_MODE Parameter 18-16 Remote Dependencies and Time Stamp Mode 18-17

xii

Remote Procedure B Compiles at 8:00 a.m. 18-19 Local Procedure A Compiles at 9:00 a.m. 18-20 Execute Procedure A 18-21 Remote Procedure B Recompiled at 11:00 a.m. 18-22 Signature Mode 18-24 Recompiling a PL/SQL Program Unit 18-25 Unsuccessful Recompilation 18-26 Successful Recompilation 18-27 Recompilation of Procedures 18-28 Packages and Dependencies 18-29 Summary 18-31 Practice 18 Overview 18-32

A B C D

Practice Solutions Table Descriptions and Data Creating Program Units by Using Procedure Builder REF Cursors Index Additional Practices Additional Practice Solutions Additional Practices: Table Descriptions and Data

xiii

xiv

Creating Packages

Copyright © Oracle Corporation, 2001. All rights reserved.

Schedule:

Timing 60 minutes 65 minutes 125 minutes

Topic Lecture Practice Total

Objectives

After completing this lesson, you should be able to do the following:

• • • • •
12-2

Describe packages and list their possible 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 a use for a bodiless package

Copyright © Oracle Corporation, 2001. All rights reserved.

Lesson Aim In this lesson you learn what a package is and what its components are. You also learn how to create and use packages.

Oracle9i: Program with PL/SQL 12-2

Overview of Packages

Packages:

• •

Group logically related PL/SQL types, items, and subprograms Consist of two parts: – Specification – Body

• •

Cannot be invoked, parameterized, or nested Allow the Oracle server to read multiple objects into memory at once

12-3

Copyright © Oracle Corporation, 2001. All rights reserved.

Packages Overview Packages bundle related PL/SQL types, items, and subprograms into one container. For example, a Human Resources package can contain hiring and firing procedures, commission and bonus functions, and tax exemption variables. A package usually has a specification and a body, stored separately in the database. The specification is the interface to your applications. It declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The package specification may also include PRAGMAs, which are directives to the compiler. The body fully defines cursors and subprograms, and so implements the specification. The package itself cannot be called, parameterized, or nested. Still, the format of a package is similar to that of a subprogram. Once written and compiled, the contents can be shared by many applications. When you call a packaged PL/SQL construct for the first time, the whole package is loaded into memory. Thus, later calls to constructs in the same package require no disk input/output (I/O).

Oracle9i: Program with PL/SQL 12-3

Components of a Package
Package specification
Public variable Procedure A declaration Public procedure

Private variable

Package body

Procedure B definition Procedure A definition

Private procedure

Public procedure Local variable

12-4

Copyright © Oracle Corporation, 2001. All rights reserved.

Package Development You create a package in two parts: first the package specification, and then the package body. Public package constructs are those that are declared in the package specification and defined in the package body. Private package constructs are those that are defined solely within the package body.
Scope of the Construct Public Description Can be referenced from any Oracle server environment Can be referenced only by other constructs which are part of the same package Placement within the Package Declared within the package specification and may be defined within the package body Declared and defined within the package body

Private

Note: The Oracle server stores the specification and body of a package separately in the database. This enables you to change the definition of a program construct in the package body without causing the Oracle server to invalidate other schema objects that call or reference the program construct.

Oracle9i: Program with PL/SQL 12-4

Referencing Package Objects
Package specification

Procedure A declaration

Package body

Procedure B definition Procedure A definition

12-5

Copyright © Oracle Corporation, 2001. All rights reserved.

Package Development (continued)
Visibility of the Construct Local Description A variable defined within a subprogram that is not visible to external users. Private (local to the package) variable: You can define variables in a package body. These variables can be accessed only by other objects in the same package. They are not visible to any subprograms or objects outside of the package. A variable or subprogram that can be referenced (and changed) outside the package and is visible to external users. Global package items must be declared in the package specification.

Global

Oracle9i: Program with PL/SQL 12-5

Developing a Package
Editor Code 1

iSQL*Plus 2 Load and run the file.sql

Oracle

Source code Compile P code Execute

12-6

Copyright © Oracle Corporation, 2001. All rights reserved.

How to Develop a Package 1. Write the syntax: Enter the code in a text editor and save it as a SQL script file. 2. Compile the code: Run the SQL script file to generate and compile the source code. The source code is compiled into P code.

Oracle9i: Program with PL/SQL 12-6

Developing a Package • •

Saving the text of the CREATE PACKAGE statement in two different SQL files facilitates later modifications to the package. A package specification can exist without a package body, but a package body cannot exist without a package specification.

12-7

Copyright © Oracle Corporation, 2001. All rights reserved.

How to Develop a Package There are three basic steps to developing a package, similar to those steps that are used to develop a stand-alone procedure. 1. Write the text of the CREATE PACKAGE statement within a SQL script file to create the package specification and run the script file. The source code is compiled into P code and is stored within the data dictionary. 2. Write the text of the CREATE PACKAGE BODY statement within a SQL script file to create the package body and run the script file. The source code is compiled into P code and is also stored within the data dictionary. 3. Invoke any public construct within the package from an Oracle server environment.

Oracle9i: Program with PL/SQL 12-7

Creating the Package Specification
Syntax:
CREATE [OR REPLACE] PACKAGE package_name IS|AS public type and item declarations subprogram specifications END package_name;

• • •

The REPLACE option drops and recreates the package specification. Variables declared in the package specification are initialized to NULL by default. All the constructs declared in a package specification are visible to users who are granted privileges on the package.
Copyright © Oracle Corporation, 2001. All rights reserved.

12-8

How to Create a Package Specification To create packages, you declare all public constructs within the package specification. • Specify the REPLACE option when the package specification already exists. • Initialize a variable with a constant value or formula within the declaration, if required; otherwise, the variable is initialized implicitly to NULL.
P aram eter package_name public type and item declarations subprogram specifications D e s c r ip tio n N a m e th e p a c k a g e D e c la re v a ria b le s , c o n s ta n ts, c u r so rs, e x c e p tio n s , o r ty p e s D e c la re th e P L /S Q L s u b p ro g ra m s

Syntax Definition

Oracle9i: Program with PL/SQL 12-8

Declaring Public Constructs
COMM_PACKAGE package

G_COMM Package specification

1

RESET_COMM procedure declaration

2

12-9

Copyright © Oracle Corporation, 2001. All rights reserved.

Example of a Package Specification In the slide, G_COMM is a public (global) variable, and RESET_COMM is a public procedure. In the package specification, you declare public variables, public procedures, and public functions. The public procedures or functions are routines that can be invoked repeatedly by other constructs in the same package or from outside the package.

Oracle9i: Program with PL/SQL 12-9

Creating a Package Specification: Example

CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 0.10; --initialized to 0.10 PROCEDURE reset_comm (p_comm IN NUMBER); END comm_package; /

• •

G_COMM is a global variable and is initialized to 0.10. RESET_COMM is a public procedure that is implemented in the package body.

12-10

Copyright © Oracle Corporation, 2001. All rights reserved.

Package Specification for COMM_PACKAGE In the slide, the variable G_COMM and the procedure RESET_COMM are public constructs.

Oracle9i: Program with PL/SQL 12-10

Creating the Package Body
Syntax:
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS private type and item declarations subprogram bodies END package_name;

• • •
12-11

The REPLACE option drops and recreates the package body. Identifiers defined only in the package body are private constructs. These are not visible outside the package body. All private constructs must be declared before they are used in the public constructs.
Copyright © Oracle Corporation, 2001. All rights reserved.

Creating the Package Body To create packages, define all public and private constructs within the package body. • Specify the REPLACE option when the package body already exists. • The order in which subprograms are defined within the package body is important: you must declare a variable before another variable or subprogram can refer to it, and you must declare or define private subprograms before calling them from other subprograms. It is quite common in the package body to see all private variables and subprograms defined first and the public subprograms defined last.

Syntax Definition Define all public and private procedures and functions in the package body.
Parameter package_name private type and item declarations subprogram bodies Description Is the name of the package Declares variables, constants, cursors, exceptions, or types Defines the PL/SQL subprograms, public and private

Oracle9i: Program with PL/SQL 12-11

Public and Private Constructs
COMM_PACKAGE package

G_COMM Package specification RESET_COMM procedure declaration

1 2

Package body

VALIDATE_COMM function definition

3

RESET_COMM procedure definition

2

12-12

Copyright © Oracle Corporation, 2001. All rights reserved.

Create a Package Body Example In the slide on this page: • • • 1 is a public (global) variable 2 is a public procedure 3 is a private function

You can define a private procedure or function to modularize and clarify the code of public procedures and functions. Note: In the slide, the private function is shown above the public procedure. When you are coding the package body, the definition of the private function has to be above the definition of the public procedure. Only subprograms and cursors declarations without body in a package specification have an underlying implementation in the package body. So if a specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. However, the body can still be used to initialize items declared in the package specification.

Oracle9i: Program with PL/SQL 12-12

Creating a Package Body: Example comm_pack.sql CREATE OR REPLACE PACKAGE BODY comm_package IS FUNCTION validate_comm (p_comm IN NUMBER) RETURN BOOLEAN IS v_max_comm NUMBER; BEGIN SELECT MAX(commission_pct) INTO v_max_comm FROM employees; IF p_comm > v_max_comm THEN RETURN(FALSE); ELSE RETURN(TRUE); END IF; END validate_comm; ...

12-13

Copyright © Oracle Corporation, 2001. All rights reserved.

Package Body for COMM_PACKAGE Define a function to validate the commission. The commission may not be greater than the highest commission among all existing employees.

Instructor Note The code in the slide is continued on the following page. A cursor can be declared and defined inside the package specification without requiring an underlying implementation in the package body. Cursors that are specified only with a return type should be implemented inside the package body. Example: CREATE OR REPLACE PACKAGE TESTCURSOR IS CURSOR C RETURN employees%ROWTYPE; ... END; CREATE OR REPLACE PACKAGE BODY TESTCURSOR IS CURSOR C RETURN employees%ROWTYPE IS SELECT * FROM employees; ... END;

Oracle9i: Program with PL/SQL 12-13

Creating a Package Body: Example comm_pack.sql PROCEDURE reset_comm (p_comm IN NUMBER) IS BEGIN IF validate_comm(p_comm) THEN g_comm:=p_comm; --reset global variable ELSE RAISE_APPLICATION_ERROR(-20210,'Invalid commission'); END IF; END reset_comm; END comm_package; /

12-14

Copyright © Oracle Corporation, 2001. All rights reserved.

Package Body for COMM_PACKAGE (continued) Define a procedure that enables you to reset and validate the prevailing commission.

Oracle9i: Program with PL/SQL 12-14

Invoking Package Constructs
Example 1: Invoke a function from a procedure within the same package.
CREATE OR REPLACE PACKAGE BODY comm_package IS . . . PROCEDURE reset_comm (p_comm IN NUMBER) IS BEGIN IF validate_comm(p_comm) THEN g_comm := p_comm; ELSE RAISE_APPLICATION_ERROR (-20210, 'Invalid commission'); END IF; END reset_comm; END comm_package;

12-15

Copyright © Oracle Corporation, 2001. All rights reserved.

Invoking Package Constructs After the package is stored in the database, you can invoke a package construct within the package or from outside the package, depending on whether the construct is private or public. When you invoke a package procedure or function from within the same package, you do not need to qualify its name. Example 1 Call the VALIDATE_COMM function from the RESET_COMM procedure. Both subprograms are in the COMM_PACKAGE package.

Oracle9i: Program with PL/SQL 12-15

Invoking Package Constructs
Example 2: Invoke a package procedure from iSQL*Plus.
EXECUTE comm_package.reset_comm(0.15)

Example 3: Invoke a package procedure in a different schema.
EXECUTE scott.comm_package.reset_comm(0.15)

Example 4: Invoke a package procedure in a remote database.
EXECUTE comm_package.reset_comm@ny(0.15)

12-16

Copyright © Oracle Corporation, 2001. All rights reserved.

Invoking Package Constructs (continued) When you invoke a package procedure or function from outside the package, you must qualify its name with the name of the package. Example 2 Call the RESET_COMM procedure from iSQL*Plus, making the prevailing commission 0.15 for the user session. Example 3 Call the RESET_COMM procedure that is located in the SCOTT schema from iSQL*Plus, making the prevailing commission 0.15 for the user session. Example 4 Call the RESET_COMM procedure that is located in a remote database that is determined by the database link named NY from iSQL*Plus, making the prevailing commission 0.15 for the user session. Adhere to normal naming conventions for invoking a procedure in a different schema, or in a different database on another node.

Instructor Note In example 4, the name of the schema from which the package is accessed is included in the database link.
Oracle9i: Program with PL/SQL 12-16

Declaring a Bodiless Package

CREATE OR REPLACE PACKAGE global_consts IS mile_2_kilo CONSTANT NUMBER := 1.6093; kilo_2_mile CONSTANT NUMBER := 0.6214; yard_2_meter CONSTANT NUMBER := 0.9144; meter_2_yard CONSTANT NUMBER := 1.0936; END global_consts; / EXECUTE DBMS_OUTPUT.PUT_LINE('20 miles = '||20* global_consts.mile_2_kilo||' km')

12-17

Copyright © Oracle Corporation, 2001. All rights reserved.

Declaring a Bodiless Package You can declare public (global) variables that exist for the duration of the user session. You can create a package specification that does not need a package body. As discussed earlier in this lesson, if a specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. Example In the example in the slide, a package specification containing several conversion rates is defined. All the global identifiers are declared as constants. A package body is not required to support this package specification because implementation details are not required for any of the constructs of the package specification.

Instructor Note This slide highlights two concepts: • • A package specification without a package body The uses of global variables

Oracle9i: Program with PL/SQL 12-17

Referencing a Public Variable from a Stand-Alone Procedure
Example:
CREATE OR REPLACE PROCEDURE meter_to_yard (p_meter IN NUMBER, p_yard OUT NUMBER) IS BEGIN p_yard := p_meter * global_consts.meter_2_yard; END meter_to_yard; / VARIABLE yard NUMBER EXECUTE meter_to_yard (1, :yard) PRINT yard

12-18

Copyright © Oracle Corporation, 2001. All rights reserved.

Example Use the METER_TO_YARD procedure to convert meters to yards, using the conversion rate packaged in GLOBAL_CONSTS. When you reference a variable, cursor, constant, or exception from outside the package, you must qualify its name with the name of the package.

Oracle9i: Program with PL/SQL 12-18

Removing Packages

To remove the package specification and the body, use the following syntax:
DROP PACKAGE package_name;

To remove the package body, use the following syntax:
DROP PACKAGE BODY package_name;

12-19

Copyright © Oracle Corporation, 2001. All rights reserved.

Removing a Package When a package is no longer required, you can use a SQL statement in iSQL*Plus to drop it. A package has two parts, so you can drop the whole package or just the package body and retain the package specification.

Oracle9i: Program with PL/SQL 12-19

Guidelines for Developing Packages • • • • • •

Construct packages for general use. Define the package specification before the body. The package specification should contain only those constructs that you want to be public. Place items in the declaration part of the package body when you must maintain them throughout a session or across transactions. Changes to the package specification require recompilation of each referencing subprogram. The package specification should contain as few constructs as possible.

12-20

Copyright © Oracle Corporation, 2001. All rights reserved.

Guidelines for Writing Packages Keep your packages as general as possible so that they can be reused in future applications. Also, avoid writing packages that duplicate features provided by the Oracle server. Package specifications reflect the design of your application, so define them before defining the package bodies. The package specification should contain only those constructs that must be visible to users of the package. That way other developers cannot misuse the package by basing code on irrelevant details. Place items in the declaration part of the package body when you must maintain them throughout a session or across transactions. For example, declare a variable called NUMBER_EMPLOYED as a private variable, if each call to a procedure that uses the variable needs to be maintained. When declared as a global variable in the package specification, the value of that global variable gets initialized in a session the first time a construct from the package is invoked. Changes to the package body do not require recompilation of dependent constructs, whereas changes to the package specification require recompilation of every stored subprogram that references the package. To reduce the need for recompiling when code is changed, place as few constructs as possible in a package specification.

Oracle9i: Program with PL/SQL 12-20

Advantages of Packages • • •

Modularity: Encapsulate related constructs. Easier application design: Code and compile specification and body separately. Hiding information: – Only the declarations in the package specification are visible and accessible to applications. – Private constructs in the package body are hidden and inaccessible. – All coding is hidden in the package body.

12-21

Copyright © Oracle Corporation, 2001. All rights reserved.

Advantages of Using Packages Packages provide an alternative to creating procedures and functions as stand-alone schema objects, and they offer several benefits. Modularity You encapsulate logically related programming structures in a named module. Each package is easy to understand, and the interface between packages is simple, clear, and well defined. Easier Application Design All you need initially is the interface information in the package specification. You can code and compile a specification without its body. Then stored subprograms that reference the package can compile as well. You need not define the package body fully until you are ready to complete the application. Hiding Information You can decide which constructs are public (visible and accessible) or private (hidden and inaccessible). Only the declarations in the package specification are visible and accessible to applications. The package body hides the definition of the private constructs so that only the package is affected (not your application or any calling programs) if the definition changes. This enables you to change the implementation without having to recompile calling programs. Also, by hiding implementation details from users, you protect the integrity of the package.

Oracle9i: Program with PL/SQL 12-21

Advantages of Packages • •

Added functionality: Persistency of variables and cursors Better performance: – The entire package is loaded into memory when the package is first referenced. – There is only one copy in memory for all users. – The dependency hierarchy is simplified.

•

Overloading: Multiple subprograms of the same name

12-22

Copyright © Oracle Corporation, 2001. All rights reserved.

Advantages of Using Packages (continued) Added Functionality Packaged public variables and cursors persist for the duration of a session. Thus, they can be shared by all subprograms that execute in the environment. They also enable you to maintain data across transactions without having to store it in the database. Private constructs also persist for the duration of the session, but can only be accessed within the package. Better Performance When you call a packaged subprogram the first time, the entire package is loaded into memory. This way, later calls to related subprograms in the package require no further disk I/O. Packaged subprograms also stop cascading dependencies and so avoid unnecessary compilation. Overloading With packages you can overload procedures and functions, which means you can create multiple subprograms with the same name in the same package, each taking parameters of different number or datatype.

Instructor Note Dependencies are covered in a later lesson.
Oracle9i: Program with PL/SQL 12-22

Summary

In this lesson, you should have learned how to:

• • • •

Improve organization, management, security, and performance by using packages Group related procedures and functions together in a package Change a package body without affecting a package specification Grant security access to the entire package

12-23

Copyright © Oracle Corporation, 2001. All rights reserved.

Summary You group related procedures and function together into a package. Packages improve organization, management, security, and performance. A package consists of package specification and a package body. You can change a package body without affecting its package specification.

Oracle9i: Program with PL/SQL 12-23

Summary

In this lesson, you should have learned how to:

• • • •

Hide the source code from users Load the entire package into memory on the first call Reduce disk access for subsequent calls Provide identifiers for the user session

12-24

Copyright © Oracle Corporation, 2001. All rights reserved.

Summary (continued) Packages enable you to hide source code from users. When you invoke a package for the first time, the entire package is loaded into memory. This reduces the disk access for subsequent calls.

Oracle9i: Program with PL/SQL 12-24

Summary

Command

Task

CREATE [OR REPLACE] PACKAGE Create (or modify) an existing package specification CREATE [OR REPLACE] PACKAGE Create (or modify) an existing BODY package body DROP PACKAGE DROP PACKAGE BODY Remove both the package specification and the package body Remove the package body only

12-25

Copyright © Oracle Corporation, 2001. All rights reserved.

Summary (continued) You can create, delete, and modify packages. You can remove both package specification and body by using the DROP PACKAGE command. You can drop the package body without affecting its specification.

Oracle9i: Program with PL/SQL 12-25

Practice 12 Overview

This practice covers the following topics:

• •

Creating packages Invoking package program units

12-26

Copyright © Oracle Corporation, 2001. All rights reserved.

Practice 12 Overview In this practice, you will create package specifications and package bodies. You will invoke the constructs in the packages, using sample data.

Oracle9i: Program with PL/SQL 12-26

Practice 12 1. Create a package specification and body called JOB_PACK. (You can save the package body and specification in two separate files.) This package contains your ADD_JOB, UPD_JOB, and DEL_JOB procedures, as well as your Q_JOB function. Note: Use the code in your previously saved script files when creating the package. a. Make all the constructs public. Note: Consider whether you still need the stand-alone procedures and functions you just packaged. b. Invoke your ADD_JOB procedure by passing values IT_SYSAN and SYSTEMS ANALYST as parameters. c. Query the JOBS table to see the result.

2. Create and invoke a package that contains private and public constructs. a. Create a package specification and package body called EMP_PACK that contains your NEW_EMP procedure as a public construct, and your VALID_DEPTID function as a private construct. (You can save the specification and body into separate files.) b. Invoke the NEW_EMP procedure, using 15 as a department number. Because the department ID 15 does not exist in the DEPARTMENTS table, you should get an error message as specified in the exception handler of your procedure. c. Invoke the NEW_EMP procedure, using an existing department ID 80. If you have time: 3. a. Create a package called CHK_PACK that contains the procedures CHK_HIREDATE and CHK_DEPT_MGR. Make both constructs public. (You can save the specification and body into separate files.) The procedure CHK_HIREDATE checks whether an employee’s hire date is within the following range: [SYSDATE - 50 years, SYSDATE + 3 months]. Note: • • • If the date is invalid, you should raise an application error with an appropriate message indicating why the date value is not acceptable. Make sure the time component in the date value is ignored. Use a constant to refer to the 50 years boundary.

• A null value for the hire date should be treated as an invalid hire date. The procedure CHK_DEPT_MGR checks the department and manager combination for a given employee. The CHK_DEPT_MGR procedure accepts an employee ID and a manager ID. The procedure checks that the manager and employee work in the same department. The procedure also checks that the job title of the manager ID provided is MANAGER. Note: If the department ID and manager combination is invalid, you should raise an application error with an appropriate message.

Oracle9i: Program with PL/SQL 12-27

Practice 12 (continued) b. Test the CHK_HIREDATE procedure with the following command: EXECUTE chk_pack.chk_hiredate('01-JAN-47') What happens, and why? c. Test the CHK_HIREDATE procedure with the following command: EXECUTE chk_pack.chk_hiredate(NULL) What happens, and why? d. Test the CHK_DEPT_MGR procedure with the following command: EXECUTE chk_pack.chk_dept_mgr(117,100) What happens, and why?

Oracle9i: Program with PL/SQL 12-28

More Package Concepts

Copyright © Oracle Corporation, 2001. All rights reserved.

Schedule:

Timing 50 minutes 45 minutes 95 minutes

Topic Lecture Practice Total

Objectives

After completing this lesson, you should be able to do the following:

• • • •

Write packages that use the overloading feature Describe errors with mutually referential subprograms Initialize variables with a one-time-only procedure Identify persistent states

13-2

Copyright © Oracle Corporation, 2001. All rights reserved.

Lesson Aim This lesson introduces more advanced features of PL/SQL, including overloading, forward referencing, a one-time-only procedure, and the persistency of variables, constants, exceptions, and cursors. It also looks at the effect of packaging functions that are used in SQL statements.

Oracle9i: Program with PL/SQL 13-2

Overloading • • •

Enables you to use the same name for different subprograms inside a PL/SQL block, a subprogram, or a package Requires the formal parameters of the subprograms to differ in number, order, or data type family Enables you to build more flexibility because a user or application is not restricted by the specific data type or number of formal parameters

Note: Only local or packaged subprograms can be overloaded. You cannot overload stand-alone subprograms.
13-3 Copyright © Oracle Corporation, 2001. All rights reserved.

Overloading This feature enables you to define different subprograms with the same name. You can distinguish the subprograms both by name and by parameters. Sometimes the processing in two subprograms is the same, but the parameters passed to them varies. In that case it is logical to give them the same name. PL/SQL determines which subprogram is called by checking its formal parameters. Only local or packaged subprograms can be overloaded. Stand-alone subprograms cannot be overloaded. Restrictions You cannot overload: • Two subprograms if their formal parameters differ only in data type and the different data types are in the same family (NUMBER and DECIMAL belong to the same family) • Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are PL/SQL subtypes of VARCHAR2) • Two functions that differ only in return type, even if the types are in different families You get a run-time error when you overload subprograms with the above features. Note: The above restrictions apply if the names of the parameters are also the same. If you use different names for the parameters, then you can invoke the subprograms by using named notation for the parameters.

Oracle9i: Program with PL/SQL 13-3

Overloading (continued) Resolving Calls The compiler tries to find a declaration that matches the call. It searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler stops searching if it finds one or more subprogram declarations in which the name matches the name of the called subprogram. For like-named subprograms at the same level of scope, the compiler needs an exact match in number, order, and data type between the actual and formal parameters. Instructor Note You can overload two subprograms if their formal parameters differ only in name or parameter mode. You can also overload two subprograms with the restrictions specified in the first bullet point under "restrictions" on page 13-3. If you invoke the overloaded method with named parameter notation, you do not get a run-time error. For example, consider the package OVERLOAD, which contains two overloaded procedures. The procedures differ only in the name of the parameters: CREATE OR REPLACE PACKAGE overload IS procedure p (x number); procedure p ( n number); END; / After you create the package body, execute procedure P as follows: EXECUTE overload.p(4) overload.p(4); END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00307: too many declarations of 'P' match this call ORA-06550: line 1, column 7: PL/SQL: Statement ignored Now, invoke the procedure with named notation: EXECUTE overload.p(x => 4)

EXECUTE overload.p(n=> 3) PL/SQL procedure successfully completed.

Oracle9i: Program with PL/SQL 13-4

Overloading: Example over_pack.sql CREATE OR REPLACE PACKAGE over_pack IS PROCEDURE add_dept (p_deptno IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0); PROCEDURE add_dept (p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0); END over_pack; /

13-5

Copyright © Oracle Corporation, 2001. All rights reserved.

Overloading: Example The slide shows the package specification of a package with overloaded procedures. The package contains ADD_DEPT as the name of two overloaded procedures. The first definition takes three parameters to be able to insert a new department to the department table. The second definition takes only two parameters, because the department ID is populated through a sequence.

Instructor Note (for page 13-6) Defaults for parameters are the most common cause of overload errors. Ask the class what happens if the parameter p_deptno has a default value in the first declaration of the procedure ADD_DEPT and the procedure ADD_DEPT is invoked with no arguments. You receive an error PLS-00307: too many declarations of 'ADD_DEPT' match this call.

Oracle9i: Program with PL/SQL 13-5

Overloading: Example over_pack_body.sql CREATE OR REPLACE PACKAGE BODY over_pack IS PROCEDURE add_dept (p_deptno IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0) IS BEGIN INSERT INTO departments (department_id, department_name, location_id) VALUES (p_deptno, p_name, p_loc); END add_dept; PROCEDURE add_dept (p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0) IS BEGIN INSERT INTO departments (department_id, department_name, location_id) VALUES (departments_seq.NEXTVAL, p_name, p_loc); END add_dept; END over_pack; /
13-6 Copyright © Oracle Corporation, 2001. All rights reserved.

Overloading Example (continued) If you call ADD_DEPT with an explicitly provided department ID, PL/SQL uses the first version of the procedure. If you call ADD_DEPT with no department ID, PL/SQL uses the second version. EXECUTE over_pack.add_dept (980,'Education',2500) EXECUTE over_pack.add_dept ('Training', 2400) SELECT * FROM departments WHERE department_id = 980;

SELECT * FROM departments WHERE department_name = 'Training';

Oracle9i: Program with PL/SQL 13-6

Overloading: Example • •

Most built-in functions are overloaded. For example, see the TO_CHAR function of the STANDARD package.
TO_CHAR TO_CHAR TO_CHAR TO_CHAR (p1 (p2 (p1 (p1 DATE) RETURN VARCHAR2; NUMBER) RETURN VARCHAR2; DATE, P2 VARCHAR2) RETURN VARCHAR2; NUMBER, P2 VARCHAR2) RETURN VARCHAR2;

FUNCTION FUNCTION FUNCTION FUNCTION

•

If you redeclare a built-in subprogram in a PL/SQL program, your local declaration overrides the global declaration.

13-7

Copyright © Oracle Corporation, 2001. All rights reserved.

Overloading Example (continued) Most built-in functions are overloaded. For example, the function TO_CHAR in the package STANDARD has four different declarations, as shown in the slide. The function can take either the DATE or the NUMBER data type and convert it to the character data type. The format into which the date or number has to be converted can also be specified in the function call. If you redeclare a built-in subprogram in another PL/SQL program, your local declaration overrides the standard or built-in subprogram. To be able to access the built-in subprogram, you need to qualify it with its package name. For example, if you redeclare the TO_CHAR function, to access the built-in function you refer it as: STANDARD.TO_CHAR. If you redeclare a built-in subprogram as a stand-alone subprogram, to be able to access your subprogram you need to qualify it with your schema name, for example, SCOTT.TO_CHAR.

Instructor Note There is a standard.sql script file in the RDBMS\ADMIN directory in the Oracle home directory. Using this script file, you can demonstrate overloading subprograms such as TO_CHAR or NVL from the STANDARD package.
Oracle9i: Program with PL/SQL 13-7

Using Forward Declarations
You must declare identifiers before referencing them.
CREATE OR REPLACE PACKAGE BODY forward_pack IS PROCEDURE award_bonus(. . .) IS BEGIN calc_rating(. . .); --illegal reference END; PROCEDURE calc_rating(. . .) IS BEGIN ... END; END forward_pack; /

13-8

Copyright © Oracle Corporation, 2001. All rights reserved.

Using Forward Declarations PL/SQL does not allow forward references. You must declare an identifier before using it. Therefore, a subprogram must be declared before calling it. In the example in the slide, the procedure CALC_RATING cannot be referenced because it has not yet been declared. You can solve the illegal reference problem by reversing the order of the two procedures. However, this easy solution does not always work. Suppose the procedures call each other or you absolutely want to define them in alphabetical order. PL/SQL enables for a special subprogram declaration called a forward declaration. It consists of the subprogram specification terminated by a semicolon. You can use forward declarations to do the following: • • • Define subprograms in logical or alphabetical order Define mutually recursive subprograms Group subprograms in a package

Mutually recursive programs are programs that call each other directly or indirectly. Note: If you receive a compilation error that CALC_RATING is undefined, it is only a problem if CALC_RATING is a private packaged procedure. If CALC_RATING is declared in the package specification, the reference to the public procedure is resolved by the compiler.

Oracle9i: Program with PL/SQL 13-8

Using Forward Declarations
CREATE OR REPLACE PACKAGE BODY forward_pack IS PROCEDURE calc_rating(. . .); PROCEDURE award_bonus(. . .) IS BEGIN calc_rating(. . .); . . . END; PROCEDURE calc_rating(. . .) IS BEGIN . . . END; END forward_pack; / -- forward declaration -- subprograms defined -- in alphabetical order

13-9

Copyright © Oracle Corporation, 2001. All rights reserved.

Using Forward Declarations (continued) • • The formal parameter list must appear in both the forward declaration and the subprogram body. The subprogram body can appear anywhere after the forward declaration, but both must appear in the same program unit.

Forward Declarations and Packages Forward declarations typically let you group related subprograms in a package. The subprogram specifications go in the package specification, and the subprogram bodies go in the package body, where they are invisible to the applications. In this way, packages enable you to hide implementation details.

Instructor Note A forward declaration has the same syntax as is used in the package specification for a public procedure or function.
Oracle9i: Program with PL/SQL 13-9

Creating a One-Time-Only Procedure
CREATE OR REPLACE PACKAGE taxes IS tax NUMBER; ... -- declare all public procedures/functions END taxes; / CREATE OR REPLACE PACKAGE BODY taxes IS ... -- declare all private variables ... -- define public/private procedures/functions BEGIN SELECT rate_value INTO tax FROM tax_rates WHERE rate_name = 'TAX'; END taxes; /
13-10 Copyright © Oracle Corporation, 2001. All rights reserved.

Define an Automatic, One-Time-Only Procedure A one-time-only procedure is executed only once, when the package is first invoked within the user session. In the preceding slide, the current value for TAX is set to the value in the TAX_RATES table the first time the TAXES package is referenced. Note: Initialize public or private variables with an automatic, one-time-only procedure when the derivation is too complex to embed within the variable declaration. In this case, do not initialize the variable in the declaration, because the value is reset by the one-time-only procedure. The keyword END is not used at the end of a one-time-only procedure. Observe that in the example in the slide, there is no END at the end of the one-time-only procedure.

Instructor Note In the example, TAX could be a global or local (to the package) variable; the one-time-only procedure would be the same.

Oracle9i: Program with PL/SQL 13-10

Restrictions on Package Functions Used in SQL
A function called from: • A query or DML statement can not end the current transaction, create or roll back to a savepoint, or ALTER the system or session. A query statement or a parallelized DML statement can not execute a DML statement or modify the database. • A DML statement can not read or modify the particular table being modified by that DML statement. Note: Calls to subprograms that break the above restrictions are not allowed.
13-11 Copyright © Oracle Corporation, 2001. All rights reserved.

•

Controlling Side Effects For the Oracle server to execute a SQL statement that calls a stored function, it must know the purity level of a stored functions, that is, whether the functions are free of side effects. Side effects are changes to database tables or public packaged variables (those declared in a package specification). Side effects could delay the execution of a query, yield order-dependent (therefore indeterminate) results, or require that the package state variables be maintained across user sessions. Various side effects are not allowed when a function is called from a SQL query or DML statement. Therefore, the following restrictions apply to stored functions called from SQL expressions: • A function called from a query or DML statement can not end the current transaction, create or roll back to a savepoint, or alter the system or session • A function called from a query statement or from a parallelized DML statement can not execute a DML statement or otherwise modify the database • A function called from a DML statement can not read or modify the particular table being modified by that DML statement Note: In releases prior to Oracle8i, the purity checking used to be performed during compilation time, by including the PRAGMA RESTRICT_REFERENCES compiler directive in the package specification. But from Oracle8i, a user-written function can be called from a SQL statement without any compile-time checking of its purity. You can use PRAGMA RESTRICT_REFERENCES to ask the PL/SQL compiler to verify that a function has only the side effects that you expect. SQL statements, package variable accesses, or calls to functions that violate the declared restrictions continue to raise PL/SQL compilation errors to help you isolate the code that has unintended effects. Note: The restrictions on functions discussed above are the same as those discussed in the lesson “Creating Functions.” Oracle9i: Program with PL/SQL 13-11

User Defined Package: taxes_pack
CREATE OR REPLACE PACKAGE taxes_pack IS FUNCTION tax (p_value IN NUMBER) RETURN NUMBER; END taxes_pack; /

CREATE OR REPLACE PACKAGE BODY taxes_pack IS FUNCTION tax (p_value IN NUMBER) RETURN NUMBER IS v_rate NUMBER := 0.08; BEGIN RETURN (p_value * v_rate); END tax; END taxes_pack; /

13-12

Copyright © Oracle Corporation, 2001. All rights reserved.

Example Encapsulate the function TAX in the package TAXES_PACK. The function is called from SQL statements on remote databases.

Instructor Note The example in the slide is a very simple demonstration, as it contains only one function in the package, which is not a common occurrence. This shows an example of a package function that can be used in a SQL statement. In versions prior to Oracle8i, if your package contains a one-time-only procedure, you must also assert the purity level of that section. The compiler needs to know whether your one-time-only procedure modifies package variables or database information. You do this in your package specification by providing the PRAGMA RESTRICT_REFERENCES clause with the name of the package and all applicable state parameters. You also use PRAGMA RESTRICT_REFERENCES for calling external procedures. Calling external procedures is discussed in the course Advanced PL/SQL.

Oracle9i: Program with PL/SQL 13-12

Invoking a User-Defined Package Function from a SQL Statement

SELECT taxes_pack.tax(salary), salary, last_name FROM employees;

…

13-13

Copyright © Oracle Corporation, 2001. All rights reserved.

Calling Package Functions You call PL/SQL functions the same way that you call built-in SQL functions. Example Call the TAX function (in the TAXES_PACK package) from a SELECT statement. Note: If you are using Oracle versions prior to 8i, you need to assert the purity level of the function in the package specification by using PRAGMA RESTRICT_REFERENCES. If this is not specified, you get an error message saying that the function TAX does not guarantee that it will not update the database while invoking the package function in a query.

Oracle9i: Program with PL/SQL 13-13

Persistent State of Package Variables: Example
CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; --initialized to 10 PROCEDURE reset_comm (p_comm IN NUMBER); END comm_package; / CREATE OR REPLACE PACKAGE BODY comm_package IS FUNCTION validate_comm (p_comm IN NUMBER) RETURN BOOLEAN IS v_max_comm NUMBER; BEGIN ... -- validates commission to be less than maximum -- commission in the table END validate_comm; PROCEDURE reset_comm (p_comm IN NUMBER) IS BEGIN ... -- calls validate_comm with specified value END reset_comm; END comm_package; /
13-14 Copyright © Oracle Corporation, 2001. All rights reserved.

Persistent State of Package Variables This sample package illustrates the persistent state of package variables. The VALIDATE_COMM function validates commission to be no more than maximum currently earned. The RESET_COMM procedure invokes the VALIDATE_COMM function. If you try to reset the commission to be higher than the maximum, the exception RAISE_APPLICATION_ERROR is raised. On the next page, the RESET_COMM procedure is used in the example. Note: Refer to page 13 of lesson 12 for the code of the VALIDATE_COMM function and the RESET_COMM procedure. In the VALIDATE_COMM function, the maximum salary from the EMPLOYEES table is selected into the variable V_MAXSAL. Once the variable is assigned a value, the value persists in the session until it is modified again. The example in the following slide shows how the value of a global package variable persists for a session.

Instructor Note (for page 13-15) Illustrate how the variable are stored in the PGA for the two different sessions.

Oracle9i: Program with PL/SQL 13-14

Persistent State of Package Variables
Time
9:00

Scott
EXECUTE comm_package.reset_comm (0.25) max_comm=0.4 > 0.25 g_comm = 0.25

Jones

9:30

INSERT INTO employees (last_name, commission_pct) VALUES ('Madonna', 0.8); max_comm=0.8 EXECUTE comm_package.reset_comm(0.5) max_comm=0.8 > 0.5 g_comm = 0.5

9:35

13-15

Copyright © Oracle Corporation, 2001. All rights reserved.

Controlling the Persistent State of a Package Variable You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time the user disconnects. 1. Initialize the variable within its declaration or within an automatic, one-time-only procedure. 2. Change the value of the variable by means of package procedures. 3. The value of the variable is released when the user disconnects. The sequence of steps in the slide shows how the state of a package variable persists. 9:00: When Scott invoked the procedure RESET_COMM with a commission percentage value 0.25, the global variable G_COMM was initialized to 10 in his session. The value 0.25 was validated with the maximum commission percentage value 0.4 (obtained from the EMPLOYEES table). Because 0.25 is less than 0.4, the global variable was set to 0.25. 9:30: Jones inserted a new row into EMPLOYEES table with commission percentage value 0.8. 9:35: Jones invoked the procedure RESET_COMM with a commission percentage value 0.5. The global variable G_COMM was initialized to 10 in his session. The value 0.5 was validated with the maximum commission percentage value 0.8 (because the new row has 0.8). Because 0.5 is less than 0.8, the global variable was set to 0.5.

Oracle9i: Program with PL/SQL 13-15

Persistent State of Package Variables
Time
9:00

Scott
EXECUTE comm_package.reset_comm (0.25) max_comm=0.4 > 0.25 g_comm = 0.25

Jones

9:30

INSERT INTO employees (last_name, commission_pct) VALUES ('Madonna', 0.8); max_comm=0.8 EXECUTE comm_package.reset_comm(0.5) max_comm=0.8 > 0.5 g_comm = 0.5

9:35 10:00 EXECUTE comm_package.reset_comm (0.6) max_comm=0.4 < 0.6 INVALID

11:00 11:01

ROLLBACK; EXIT

13-16

Copyright © Oracle Corporation, 2001. All rights reserved.

Controlling the Persistent State of a Package Variable (continued) 10:00: Scott invoked the procedure with commission percentage value of 0.6. This value is more than the maximum commission percentage 0.4 (Scott could not see new value because Jones did not complete the transaction). Hence, it was invalid.

Oracle9i: Program with PL/SQL 13-16

Persistent State of Package Variables
Time
9:00

Scott
EXECUTE comm_package.reset_comm (0.25) max_comm=0.4 > 0.25 g_comm = 0.25

Jones

9:30

INSERT INTO employees (last_name, commission_pct) VALUES ('Madonna', 0.8); max_comm=0.8 EXECUTE comm_package.reset_comm(0.5) max_comm=0.8 > 0.5 g_comm = 0.5

9:35 10:00 EXECUTE comm_package.reset_comm (0.6) max_comm=0.4 < 0.6 INVALID

11:00 11:01 11:45 12:00

VALID

ROLLBACK; EXIT Logged In again. g_comm = 10, max_comm=0.4 EXECUTE comm_package.reset_comm(0.25)

13-17

Copyright © Oracle Corporation, 2001. All rights reserved.

Controlling the Persistent State of a Package Variable (continued) 11:00 to 12:00: Jones rolled back the transaction and exited the session. The global value was initialized to 10 when he logged in at 11:45. The procedure was successful because the new value 0.25 is less than the maximum value 0.4.

Oracle9i: Program with PL/SQL 13-17

Controlling the Persistent State of a Package Cursor
Example:
CREATE OR REPLACE PACKAGE pack_cur IS CURSOR c1 IS SELECT employee_id FROM employees ORDER BY employee_id DESC; PROCEDURE proc1_3rows; PROCEDURE proc4_6rows; END pack_cur; /

13-18

Copyright © Oracle Corporation, 2001. All rights reserved.

Controlling the Persistent State of a Package Cursor Example Use the following steps to control a public cursor: 1. Declare the public (global) cursor in the package specification. 2. Open the cursor and fetch successive rows from the cursor, using one (public) packaged procedure, PROC1_3ROWS. 3. Continue to fetch successive rows from the cursor, and then close the cursor by using another (public) packaged procedure, PROC4_6ROWS. The slide shows the package specification for PACK_CUR.

Oracle9i: Program with PL/SQL 13-18

Controlling the Persistent State of a Package Cursor
CREATE OR REPLACE PACKAGE BODY pack_cur IS v_empno NUMBER; PROCEDURE proc1_3rows IS BEGIN OPEN c1; LOOP FETCH c1 INTO v_empno; DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno)); EXIT WHEN c1%ROWCOUNT >= 3; END LOOP; END proc1_3rows; PROCEDURE proc4_6rows IS BEGIN LOOP FETCH c1 INTO v_empno; DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno)); EXIT WHEN c1%ROWCOUNT >= 6; END LOOP; CLOSE c1; END proc4_6rows; END pack_cur; /
13-19 Copyright © Oracle Corporation, 2001. All rights reserved.

Controlling the Persistent State of a Package Cursor (continued) Example The slide on this page shows the package body for PACK_CUR to support the package specification. In the package body: 1. Open the cursor and fetch successive rows from the cursor by using one packaged procedure, PROC1_3ROWS. 2. Continue to fetch successive rows from the cursor and close the cursor, using another packaged procedure, PROC4_6ROWS.

Instructor Note It is not a good practice to leave cursors open when you exit the the PROC1_3ROWS procedure. If the procedure PROC4_6ROWS is invoked prior to the PROC1_3ROWS procedure, you get an exception because the cursor is not opened in the PROC4_6ROWS procedure.
Oracle9i: Program with PL/SQL 13-19

Executing PACK_CUR

SET SERVEROUTPUT ON EXECUTE pack_cur.proc1_3rows EXECUTE pack_cur.proc4_6rows

13-20

Copyright © Oracle Corporation, 2001. All rights reserved.

Result of Executing PACK_CUR The state of a package variable or cursor persists across transactions within a session. The state does not persist from session to session for the same user, nor does it persist from user to user.

Instructor Note If you demonstrate this procedure, the results may vary because you may have added employees. A more realistic way of sharing a result set between two procedures would be to use a cursor variable as a parameter.

Oracle9i: Program with PL/SQL 13-20

PL/SQL Tables and Records in Packages
CREATE OR REPLACE PACKAGE emp_package IS TYPE emp_table_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; PROCEDURE read_emp_table (p_emp_table OUT emp_table_type); END emp_package; / CREATE OR REPLACE PACKAGE BODY emp_package IS PROCEDURE read_emp_table (p_emp_table OUT emp_table_type) IS i BINARY_INTEGER := 0; BEGIN FOR emp_record IN (SELECT * FROM employees) LOOP p_emp_table(i) := emp_record; i:= i+1; END LOOP; END read_emp_table; END emp_package; /
13-21 Copyright © Oracle Corporation, 2001. All rights reserved.

Passing Tables of Records to Procedures or Functions Inside a Package Invoke the READ_EMP_TABLE procedure from an anonymous PL/SQL block, using iSQL*Plus. DECLARE v_emp_table emp_package.emp_table_type; BEGIN emp_package.read_emp_table(v_emp_table); DBMS_OUTPUT.PUT_LINE('An example: '||v_emp_table(4).last_name); END; /

To invoke the READ_EMP_TABLE procedure from another procedure or any PL/SQL block outside the package, the actual parameter referring to the OUT parameter P_EMP_TABLE must be prefixed with its package name. In the example above, the V_EMP_TABLE variable is declared of the EMP_TABLE_TYPE type with the package name added as a prefix. Instructor Note In the anonymous block above, EMP_PACKAGE.EMP_TABLE_TYPE refers to the user-defined packaged table that is shown in the slide.
Oracle9i: Program with PL/SQL 13-21

Summary

In this lesson, you should have learned how to:

• • • • •

Overload subprograms Use forward referencing Use one-time-only procedures Describe the purity level of package functions Identify the persistent state of packaged objects

13-22

Copyright © Oracle Corporation, 2001. All rights reserved.

Summary Overloading is a feature that enables you to define different subprograms with the same name. It is logical to give two subprograms the same name in situations when the processing in both the subprograms is the same, but the parameters passed to them varies. PL/SQL allows for a special subprogram declaration called a forward declaration. Forward declaration enables you to define subprograms in logical or alphabetical order, define mutually recursive subprograms, and group subprograms in a package. A one-time-only procedure is executed only when the package is first invoked within the other user session. You can use this feature to initialize variables only once per session. You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time that the user disconnects.

Oracle9i: Program with PL/SQL 13-22

Practice 13 Overview

This practice covers the following topics:

• •

Using overloaded subprograms Creating a one-time-only procedure

13-23

Copyright © Oracle Corporation, 2001. All rights reserved.

Practice 13 Overview In this practice you create a package containing an overloaded function. You also create a one-timeonly procedure within a package to populate a PL/SQL table.

Oracle9i: Program with PL/SQL 13-23

Practice 13 1. Create a package called OVER_LOAD. Create two functions in this package; name each function PRINT_IT. The function accepts a date or character string and prints a date or a number, depending on how the function is invoked. Note: • To print the date value, use DD-MON-YY as the input format, and FmMonth,dd yyyy as the output format. Make sure you handle invalid input. • To print out the number, use 999,999.00 as the input format. a. Test the first version of PRINT_IT with the following set of commands:

VARIABLE display_date VARCHAR2(20) EXECUTE :display_date := over_load.print_it (TO_DATE('08-MAR-01')) PRINT display_date

b. Test the second version of PRINT_IT with the following set of commands: VARIABLE g_emp_sal NUMBER EXECUTE :g_emp_sal := over_load.print_it('33,600') PRINT g_emp_sal

2. Create a new package, called CHECK_PACK, to implement a new business rule. a. Create a procedure called CHK_DEPT_JOB to verify whether a given combination of department ID and job is a valid one. In this case valid means that it must be a combination that currently exists in the EMPLOYEES table. Note: • Use a PL/SQL table to store the valid department and job combination. • The PL/SQL table needs to be populated only once. • Raise an application error with an appropriate message if the combination is not valid. Test your CHK_DEPT_JOB package procedure by executing the following command: EXECUTE check_pack.chk_dept_job(50,'ST_CLERK') What happens? Test your CHK_DEPT_JOB package procedure by executing the following command: EXECUTE check_pack.chk_dept_job(20,'ST_CLERK') What happens, and why?
Oracle9i: Program with PL/SQL 13-24

b.

c.

Oracle Supplied Packages

Copyright © Oracle Corporation, 2001. All rights reserved.

Schedule:

Timing
50 minutes 50 minutes 100 minutes

Topic
Lecture Practice Total

Objectives

After completing this lesson, you should be able to do the following: • Write dynamic SQL statements using DBMS_SQL and EXECUTE IMMEDIATE

•

Describe the use and application of some Oracle server-supplied packages:
– DBMS_DDL – DBMS_JOB – DBMS_OUTPUT – UTL_FILE – UTL_HTTP and UTL_TCP

14-2

Copyright © Oracle Corporation, 2001. All rights reserved.

Lesson Aim In this lesson, you learn how to use some of the Oracle server supplied packages and to take advantage of their capabilities.

Instructor Note In this lesson, only a few of the many Oracle server supplied packages and their procedures, functions, and parameters are explained.
Oracle9i: Program with PL/SQL 14-2

Using Supplied Packages

Oracle-supplied packages:

• • •

Are provided with the Oracle server Extend the functionality of the database Enable access to certain SQL features normally restricted for PL/SQL

14-3

Copyright © Oracle Corporation, 2001. All rights reserved.

Using Supplied Packages Packages are provided with the Oracle server to allow either PL/SQL access to certain SQL features, or to extend the functionality of the database. You can take advantage of the functionality provided by these packages when creating your application, or you may simply want to use these packages as ideas when you create your own stored procedures. Most of the standard packages are created by running catproc.sql.

Instructor Note The catproc.sql script is found in the $ORACLE_HOME/rdbms/admin directory. Other packages may have to be created in the SYS schema by running corresponding scripts located in the directory $ORACLE_HOME/rdbms/admin. The scripts to create supplied packages have prefix DBMS_.
Oracle9i: Program with PL/SQL 14-3

Using Native Dynamic SQL

Dynamic SQL:

• • • • •

Is a SQL statement that contains variables that can change during runtime Is a SQL statement with placeholders and is stored as a character string Enables general-purpose code to be written Enables data-definition, data-control, or sessioncontrol statements to be written and executed from PL/SQL Is written using either DBMS_SQL or native dynamic SQL

14-4

Copyright © Oracle Corporation, 2001. All rights reserved.

Using Native Dynamic SQL (Dynamic SQL) You can write PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program but rather are stored in character strings that are input to, or built by, the program. That is, the SQL statements can be created dynamically at run time by using variables. For example, you use dynamic SQL to create a procedure that operates on a table whose name is not known until run time, or to write and execute a data definition language (DDL) statement (such as CREATE TABLE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL, such statements cannot be executed statically. In Oracle8, and earlier, you have to use DBMS_SQL to write dynamic SQL. In Oracle 8i, you can use DBMS_SQL or native dynamic SQL. The EXECUTE IMMEDIATE statement can perform dynamic single-row queries. Also, this is used for functionality such as objects and collections, which are not supported by DBMS_SQL. If the statement is a multirow SELECT statement, you use OPEN-FOR, FETCH, and CLOSE statements.

Oracle9i: Program with PL/SQL 14-4

Execution Flow

SQL statements go through various stages:

• • • •

Parse Bind Execute Fetch

Note: Some stages may be skipped.

14-5

Copyright © Oracle Corporation, 2001. All rights reserved.

Steps to Process SQL Statements All SQL statements have to go through various stages. Some stages may be skipped. Parse Every SQL statement must be parsed. Parsing the statement includes checking the statement's syntax and validating the statement, ensuring that all references to objects are correct, and ensuring that the relevant privileges to those objects exist. Bind After parsing, the Oracle server knows the meaning of the Oracle statement but still may not have enough information to execute the statement. The Oracle server may need values for any bind variable in the statement. The process of obtaining these values is called binding variables. Execute At this point, the Oracle server has all necessary information and resources, and the statement is executed. Fetch In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched. You can fetch queries, but not the DML statements. Instructor Note Do not go into too much detail when discussing this topic. Processing of SQL statements is covered in more detail in other courses.
Oracle9i: Program with PL/SQL 14-5

Using the DBMS_SQL Package
The DBMS_SQL package is used to write dynamic SQL in stored procedures and to parse DDL statements. Some of the procedures and functions of the package include:
– OPEN_CURSOR – PARSE – BIND_VARIABLE – EXECUTE – FETCH_ROWS – CLOSE_CURSOR

14-6

Copyright © Oracle Corporation, 2001. All rights reserved.

Using the DBMS_SQL Package Using DBMS_SQL, you can write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. DBMS_SQL can issue data definition language statements in PL/SQL. For example, you can choose to issue a DROP TABLE statement from within a stored procedure. The operations provided by this package are performed under the current user, not under the package owner SYS. Therefore, if the caller is an anonymous PL/SQL block, the operations are performed according to the privileges of the current user; if the caller is a stored procedure, the operations are performed according to the owner of the stored procedure. Using this package to execute DDL statements can result in a deadlock. The most likely reason for this is that the package is being used to drop a procedure that you are still using.

Oracle9i: Program with PL/SQL 14-6

Components of the DBMS_SQL Package The DBMS_SQL package uses dynamic SQL to access the database.
Function or Procedure Description OPEN_CURSOR PARSE Opens a new cursor and assigns a cursor ID number Parses the DDL or DML statement: that is, checks the statement’s syntax and associates it with the opened cursor (DDL statements are immediately executed when parsed) Binds the given value to the variable identified by its name in the parsed statement in the given cursor Executes the SQL statement and returns the number of rows processed Retrieves a row for the specified cursor (for multiple rows, call in a loop) Closes the specified cursor

BIND_VARIABLE EXECUTE FETCH_ROWS CLOSE_CURSOR

Instructor Note DBMS_SQL has many more procedures and functions than are shown here. The procedures and functions in the table should show the main stages of dynamic SQL statements. If you want to cover DBMS_SQL in more detail, start by showing the data dictionary information on DBMS_SQL. SELECT text FROM all_source WHERE name ='DBMS_SQL' ORDER BY LINE; or Use DESC DBMS_SQL to view the description of the procedures and functions in the package. The above SELECT statement returns 1050 rows.

Oracle9i: Program with PL/SQL 14-7

Using DBMS_SQL
CREATE OR REPLACE PROCEDURE delete_all_rows (p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER) IS cursor_name INTEGER; BEGIN cursor_name := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor_name, 'DELETE FROM '||p_tab_name, DBMS_SQL.NATIVE ); p_rows_del := DBMS_SQL.EXECUTE (cursor_name); DBMS_SQL.CLOSE_CURSOR(cursor_name); END; /

Use dynamic SQL to delete rows
VARIABLE deleted NUMBER EXECUTE delete_all_rows('employees', :deleted) PRINT deleted

14-8

Copyright © Oracle Corporation, 2001. All rights reserved.

Example of a DBMS_SQL Package In the slide, the table name is passed into the DELETE_ALL_ROWS procedure by using an IN parameter. The procedure uses dynamic SQL to delete rows from the specified table. The number of rows that are deleted as a result of the successful execution of the dynamic SQL are passed to the calling environment through an OUT parameter. How to Process Dynamic DML 1. Use OPEN_CURSOR to establish an area in memory to process a SQL statement. 2. Use PARSE to establish the validity of the SQL statement. 3. Use the EXECUTE function to run the SQL statement. This function returns the number of row processed. 4. Use CLOSE_CURSOR to close the cursor. Instructor Note If you want to demonstrate this code, remember to do a ROLLBACK in order to undo the deletion. The sample code contains DELETE command on another test table that is created in the script. Demonstration: 14_dmldynam.sql, 14_ddldynam.sql, and 14_seldynam.sql Purpose: The first two scripts create a table to perform either a DROP TABLE or a DELETE command on it. The last script accepts three parameters and selects data from the table. Use EMPLOYEES, JOB, and MANAGER as the values when prompted. The values are not casesensitive.
Oracle9i: Program with PL/SQL 14-8

Using the EXECUTE IMMEDIATE Statement
Use the EXECUTE IMMEDIATE statement for native dynamic SQL with better performance.
EXECUTE IMMEDIATE dynamic_string [INTO {define_variable [, define_variable] ... | record}] [USING [IN|OUT|IN OUT] bind_argument [, [IN|OUT|IN OUT] bind_argument] ... ];

• •

INTO is used for single-row queries and specifies the variables or records into which column values are retrieved. USING is used to hold all bind arguments. The default parameter mode is IN.

14-9

Copyright © Oracle Corporation, 2001. All rights reserved.

Using the EXECUTE IMMEDIATE Statement Syntax Definition
Parameter dynamic_string define_variable record bind_argument Description A string expression that represents a dynamic SQL statement (without terminator) or a PL/SQL block (with terminator) A variable that stores the selected column value A user-defined or %ROWTYPE record that stores a selected row An expression whose value is passed to the dynamic SQL statement or PL/SQL block

You can use the INTO clause for a single-row query, but you must use OPEN-FOR, FETCH, and CLOSE for a multirow query. Note: The syntax shown in the slide is not complete. The other clauses of the statement are discussed in the Advanced PL/SQL course. Instructor Note To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. However, to process a multirow query (SELECT statement), you must use the OPEN-FOR, FETCH, and CLOSE statements. OPEN-FOR, FETCH, and CLOSE are not covered in this course, because they use cursor variables.
Oracle9i: Program with PL/SQL 14-9

Using the EXECUTE IMMEDIATE Statement (continued) In the EXECUTE IMMEDIATE statement: • The INTO clause specifies the variables or record into which column values are retrieved. It is used only for single-row queries. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause. The RETURNING INTO clause specifies the variables into which column values are returned. It is used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause). For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNING INTO clause. The USING clause holds all bind arguments. The default parameter mode is IN. For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

•

•

At run time, bind arguments replace corresponding placeholders in the dynamic string. Thus, every placeholder must be associated with a bind argument in the USING clause or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL). Dynamic SQL supports all the SQL data types. For example, define variables and bind arguments can be collections, LOBs, instances of an object type, and REFs. As a rule, dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause. You can execute a dynamic SQL statement repeatedly, using new values for the bind arguments. However, you incur some overhead because EXECUTE IMMEDIATE reparses the dynamic string before every execution.

Oracle9i: Program with PL/SQL 14-10

Dynamic SQL Using EXECUTE IMMEDIATE
CREATE PROCEDURE del_rows (p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER) IS BEGIN EXECUTE IMMEDIATE 'delete from '||p_table_name; p_rows_deld := SQL%ROWCOUNT; END; / VARIABLE deleted NUMBER EXECUTE del_rows('test_employees',:deleted) PRINT deleted

14-11

Copyright © Oracle Corporation, 2001. All rights reserved.

Dynamic SQL Using EXECUTE IMMEDIATE This is the same dynamic SQL as seen with DBMS_SQL, using the Oracle8i statement EXECUTE IMMEDIATE. The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes the dynamic SQL statement.

Instructor Note If you run this example, remember to use ROLLBACK to undo the deletion.
Oracle9i: Program with PL/SQL 14-11

Using the DBMS_DDL Package
The DBMS_DDL Package:

• •

Provides access to some SQL DDL statements from stored procedures Includes some procedures:
– ALTER_COMPILE (object_type, owner, object_name)

DBMS_DDL.ALTER_COMPILE('PROCEDURE','A_USER','QUERY_EMP')

– ANALYZE_OBJECT (object_type, owner, name, method)
DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE')

Note: This package runs with the privileges of calling user, rather than the package owner SYS.
14-12 Copyright © Oracle Corporation, 2001. All rights reserved.

Using the DBMS_DDL package This package provides access to some SQL DDL statements, which you can use in PL/SQL programs. DBMS_DDL is not allowed in triggers, in procedures called from Forms Builder, or in remote sessions. This package runs with the privileges of calling user, rather than the package owner SYS. Practical Uses • You can recompile your modified PL/SQL program units by using DBMS_DDL.ALTER_COMPILE. The object type must be either procedure, function, package, package body, or trigger. You can analyze a single object, using DBMS_DDL.ANALYZE_OBJECT. (There is a way of analyzing more than one object at a time, using DBMS_UTILITY.) The object type should be TABLE, CLUSTER, or INDEX. The method must be COMPUTE, ESTIMATE, or DELETE. This package gives developers access to ALTER and ANALYZE SQL statements through PL/SQL environments.

•

•

Instructor Note You can check the column LAST_DDL_TIME in USER_OBJECTS and LAST_ANALYZED in USER_TABLES to monitor the results of these operations.
Oracle9i: Program with PL/SQL 14-12

Using DBMS_JOB for Scheduling

DBMS_JOB Enables the scheduling and execution of PL/SQL programs:

• • • • •

Submitting jobs Executing jobs Changing execution parameters of jobs Removing jobs Suspending Jobs

14-13

Copyright © Oracle Corporation, 2001. All rights reserved.

Scheduling Jobs by Using DBMS_JOB The package DBMS_JOB is used to schedule PL/SQL programs to run. Using DBMS_JOB, you can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when PL/SQL programs should run, remove PL/SQL programs from the schedule, and suspend PL/SQL programs from running. It can be used to schedule batch jobs during nonpeak hours or to run maintenance programs during times of low usage.

Instructor Note In the init.ora file, the JOB_QUEUE_PROCESSES parameter must be set to greater than zero (at least 1), because this parameter enables job queue processing in the background.
Oracle9i: Program with PL/SQL 14-13

DBMS_JOB Subprograms

Available subprograms include: • SUBMIT

• • • • • • •

REMOVE CHANGE WHAT NEXT_DATE INTERVAL BROKEN RUN

14-14

Copyright © Oracle Corporation, 2001. All rights reserved.

DBMS_JOB Subprograms
Subprogram SUBMIT REMOVE CHANGE Description Submits a job to the job queue Removes a specified job from the job queue Alters a specified job that has already been submitted to the job queue (you can alter the job description, the time at which the job w ill be run, or the interval betw een executions of the job) Alters the job description for a specified job Alters the next execution time for a specified job Alters the interval between executions for a specified job Disables job execution (if a job is m arked as broken, the Oracle server does not attempt to execute it) Forces a specified job to run

WHAT NEXT_DATE INTERVAL BROKEN RUN

Oracle9i: Program with PL/SQL 14-14

Submitting Jobs

You can submit jobs by using DBMS_JOB.SUBMIT. Available parameters include: • JOB OUT BINARY_INTEGER

• • • •

WHAT IN VARCHAR2 NEXT_DATE IN DATE DEFAULT SYSDATE INTERVAL IN VARCHAR2 DEFAULT 'NULL' NO_PARSE IN BOOLEAN DEFAULT FALSE

14-15

Copyright © Oracle Corporation, 2001. All rights reserved.

DBMS_JOB.SUBMIT Parameters The DBMS_JOB.SUBMIT procedure adds a new job to the job queue. It accepts five parameters and returns the number of a job submitted through the OUT parameter JOB. The descriptions of the parameters are listed below.
P a ra m e ter JOB WHAT NEXT_DATE INTERVAL NO_PARSE M od e OUT IN IN IN IN D e scr ip t io n U n iq u e id e n tifier o f th e jo b P L /S Q L c o d e to e x e cu te a s a jo b N ex t ex e cu tio n d a te o f th e jo b D ate fu n c tio n to c o m p u te th e n ex t e x e cu tio n d a te o f a jo b B o o le an fla g th a t in d ic ates w h eth e r to p a rse th e jo b at jo b su b m issio n (th e d e fa u lt is fa lse )

Note: An exception is raised if the interval does not evaluate to a time in the future.

Oracle9i: Program with PL/SQL 14-15

Submitting Jobs
Use DBMS_JOB.SUBMIT to place a job to be executed in the job queue.
VARIABLE jobno NUMBER BEGIN DBMS_JOB.SUBMIT ( job => :jobno, what => 'OVER_PACK.ADD_DEPT(''EDUCATION'',2710);', next_date => TRUNC(SYSDATE + 1), interval => 'TRUNC(SYSDATE + 1)' ); COMMIT; END; / PRINT jobno

14-16

Copyright © Oracle Corporation, 2001. All rights reserved.

Example The block of code in the slide submits the ADD_DEPT procedure of the OVER_PACK package to the job queue. The job number is returned through the JOB parameter. The WHAT parameter must be enclosed in single quotation marks and must include a semicolon at the end of the text string. This job is submitted to run every day at midnight. Note: In the example, the parameters are passed using named notation. The transactions in the submitted job are not committed until either COMMIT is issued, or DBMS_JOB.RUN is executed to run the job. COMMIT in the slide commits the transaction.

Instructor Note You can demonstrate this code with the 14_16s.sql file.

Oracle9i: Program with PL/SQL 14-16

Changing Job Characteristics • • • •

DBMS_JOB.CHANGE: Changes the WHAT, NEXT_DATE, and INTERVAL parameters DBMS_JOB.INTERVAL: Changes the INTERVAL parameter DBMS_JOB.NEXT_DATE: Changes the next execution date DBMS_JOB.WHAT: Changes the WHAT parameter

14-17

Copyright © Oracle Corporation, 2001. All rights reserved.

Changing Jobs After Being Submitted The CHANGE, INTERVAL, NEXT_DATE, and WHAT procedures enable you to modify job characteristics after a job is submitted to the queue. Each of these procedures takes the JOB parameter as an IN parameter indicating which job is to be changed. Example The following code changes job number 1 to execute on the following day at 6:00 a.m. and every four hours after that. BEGIN DBMS_JOB.CHANGE(1, NULL, TRUNC(SYSDATE+1)+6/24, ’SYSDATE+4/24'); END; / Note: Each of these procedures can be executed on jobs owned by the username to which the session is connected. If the parameter what, next_date, or interval is NULL, then the last values assigned to those parameters are used.

Instructor Note You can demonstrate this code with the 14_17n.sql file.
Oracle9i: Program with PL/SQL 14-17

Running, Removing, and Breaking Jobs • • •

DBMS_JOB.RUN: Runs a submitted job immediately DBMS_JOB.REMOVE: Removes a submitted job from the job queue DBMS_JOB.BROKEN: Marks a submitted job as broken, and a broken job will not run

14-18

Copyright © Oracle Corporation, 2001. All rights reserved.

Running, Removing, and Breaking Jobs The DBMS_JOB.RUN procedure executes a job immediately. Pass the job number that you want to run immediately to the procedure. EXECUTE DBMS_JOB.RUN(1) The DBMS_JOB.REMOVE procedure removes a submitted job from the job queue. Pass the job number that you want to remove from the queue to the procedure. EXECUTE DBMS_JOB.REMOVE(1) The DBMS_JOB.BROKEN marks a job as broken or not broken. Jobs are not broken by default. You can change a job to the broken status. A broken job will not run. There are three parameters for this procedure. The JOB parameter identifies the job to be marked as broken or not broken. The BROKEN parameter is a Boolean parameter. Set this parameter to FALSE to indicate that a job is not broken, and set it to TRUE to indicate that it is broken. The NEXT_DATE parameter identifies the next execution date of the job. EXECUTE DBMS_JOB.BROKEN(1, TRUE)

Oracle9i: Program with PL/SQL 14-18

Viewing Information on Submitted Jobs •

Use the DBA_JOBS dictionary view to see the status of submitted jobs.
SELECT job, log_user, next_date, next_sec, broken, what FROM DBA_JOBS;

•

Use the DBA_JOBS_RUNNING dictionary view to display jobs that are currently running.

14-19

Copyright © Oracle Corporation, 2001. All rights reserved.

Viewing Information on Submitted Jobs The DBA_JOBS and DBA_JOBS_RUNNING dictionary views display information about jobs in the queue and jobs that have run. To be able to view the dictionary information, users should be granted the SELECT privilege on SYS.DBA_JOBS. The query shown in the slide displays the job number, the user who submitted the job, the scheduled date for the job to run, the time for the job to run, and the PL/SQL block executed as a job. Use the USER_JOBS data dictionary view to display information about jobs in the queue for you. This view has the same structure as the DBA_JOBS view.

Instructor Note You may want to describe these views in iSQL*Plus. The columns are fairly descriptive of the information held in them. Refer students to the reference material for more in-depth descriptions. You can demonstrate this code with the 14_19s.sql file.
Oracle9i: Program with PL/SQL 14-19

Using the DBMS_OUTPUT Package

The DBMS_OUTPUT package enables you to output messages from PL/SQL blocks. Available procedures include: • PUT

• • • • •

NEW_LINE PUT_LINE GET_LINE GET_LINES ENABLE/DISABLE

14-20

Copyright © Oracle Corporation, 2001. All rights reserved.

Using the DBMS_OUTPUT Package The DBMS_OUTPUT package outputs values and messages from any PL/SQL block.
Function or Procedure PUT NEW_LINE PUT_LINE GET_LINE GET_LINES ENABLE/DISABLE Description Appends text from the procedure to the current line of the line output buffer Places an end_of_line marker in the output buffer Combines the action of PUT and NEW_LINE Retrieves the current line from the output buffer into the procedure Retrieves an array of lines from the output buffer into the procedure Enables or disables calls to the DBMS_OUTPUT procedures

Practical Uses • • You can output intermediary results to the window for debugging purposes. This package enables developers to closely follow the execution of a function or procedure by sending messages and values to the output buffer.
Oracle9i: Program with PL/SQL 14-20

Interacting with Operating System Files •

UTL_FILE Oracle-supplied package:
– Provides text file I/O capabilities – Is available with version 7.3 and later

•

The DBMS_LOB Oracle-supplied package:
– Provides read-only operations on external BFILES – Is available with version 8 and later – Enables read and write operations on internal LOBs

14-21

Copyright © Oracle Corporation, 2001. All rights reserved.

Interacting with Operating System Files Two Oracle-supplied packages are provided. You can use them to access operating system files. With the Oracle-supplied UTL_FILE package, you can read from and write to operating system files. This package is available with database version 7.3 and later and the PL/SQL version 2.3 and later. With the Oracle-supplied package DBMS_LOB, you can read from binary files on the operating system. This package is available from the database version 8.0 and later. This package is discussed later in the lesson “Manipulating Large Objects.”

Oracle9i: Program with PL/SQL 14-21

What Is the UTL_FILE Package?

• • •

Extends I/O to text files within PL/SQL Provides security for directories on the server through the init.ora file Is similar to standard operating system I/O
– Open files – Get text – Put text – Close files – Use the exceptions specific to the UTL_FILE package

14-22

Copyright © Oracle Corporation, 2001. All rights reserved.

The UTL_FILE Package The UTL_FILE package provides text file I/O from within PL/SQL. Client-side security implementation uses normal operating system file permission checking. Server-side security is implemented through restrictions on the directories that can be accessed. In the init.ora file, the initialization parameter UTL_FILE_DIR is set to the accessible directories desired. UTL_FILE_DIR = directory_name For example, the following initialization setting indicates that the directory /usr/ngreenbe/my_app is accessible to the fopen function, assuming that the directory is accessible to the database server processes. This parameter setting is case-sensitive on casesensitive operating systems. UTL_FILE_DIR = /user/ngreenbe/my_app The directory should be on the same machine as the database server. Using the following setting turns off database permissions and makes all directories that are accessible to the database server processes also accessible to the UTL_FILE package. UTL_FILE_DIR = * Using the procedures and functions in the package, you can open files, get text from files, put text into files, and close files. There are seven exceptions declared in the package to account for possible errors raised during execution. Instructor Note UTL_FILE_DIR = * is not recommended. Your init.ora file needs to include the parameter UTL_FILE_DIR. If it does not, edit the init.ora file and add the parameter. Then restart the database by using the SHUTDOWN and STARTUP commands.
Oracle9i: Program with PL/SQL 14-22

File Processing Using the UTL_FILE Package

Open the text file

Get lines from the text file Put lines into the text file

Yes More No lines to process? Close the text file

14-23

Copyright © Oracle Corporation, 2001. All rights reserved.

File Processing Using the UTL_FILE Package Before using the UTL_FILE package to read from or write to a text file, you must first check whether the text file is open by using the IS_OPEN function. If the file is not open, you open the file with the FOPEN function. You then either read the file or write to the file until processing is done. At the end of file processing, use the FCLOSE procedure to close the file. Note: A summary of the procedures and functions within the UTL_FILE package is listed on the next page.

Instructor Note Reading or writing to a file in PL/SQL or SQL is the same as reading or writing to files in other thirdgeneration languages.
Oracle9i: Program with PL/SQL 14-23

UTL_FILE Procedures and Functions • • • • • • •

Function FOPEN Function IS_OPEN Procedure GET_LINE Procedure PUT, PUT_LINE, PUTF Procedure NEW_LINE Procedure FFLUSH Procedure FCLOSE, FCLOSE_ALL

14-24

Copyright © Oracle Corporation, 2001. All rights reserved.

The UTL_FILE Package: Procedures and Functions
Function or Procedure FOPEN IS_OPEN GET_LINE Description A function that opens a file for input or output and returns a file handle used in subsequent I/O operations A function that returns a Boolean value whenever a file handle refers to an open file A procedure that reads a line of text from the opened file and places the text in the output buffer parameter (the maximum size of an input record is 1,023 bytes unless you specify a larger size in the overloaded version of FOPEN) A procedure that writes a text string stored in the buffer parameter to the opened file (no line terminator is appended by put; use new_line to terminate the line, or use PUT_LINE to write a complete line with a terminator) A formatted put procedure with two format specifiers: %s and \n (use %s to substitute a value into the output string. \n is a new line character) Procedure that terminates a line in an output file

PUT, PUT_LINE

PUTF

NEW_LINE FFLUSH FCLOSE FCLOSE_ALL

Procedure that writes all data buffered in memory to a file Procedure that closes an opened file Procedure that closes all opened file handles for the session Note: The maximum size of an input record is 1,023 bytes unless you specify a larger size in the overloaded version of FOPEN.
Oracle9i: Program with PL/SQL 14-24

Exceptions Specific to the UTL_FILE Package • • • • • • •
INVALID_PATH INVALID_MODE INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR WRITE_ERROR INTERNAL_ERROR

14-25

Copyright © Oracle Corporation, 2001. All rights reserved.

Exceptions to the UTL_FILE Package The UTL_FILE package declares seven exceptions that are raised to indicate an error condition in the operating system file processing.
Exception Name INVALID_PATH INVALID_MODE INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR WRITE_ERROR INTERNAL_ERROR Description The file location or filename was invalid. The OPEN_MODE parameter in FOPEN was invalid. The file handle was invalid. The file could not be opened or operated on as requested. An operating system error occurred during the read operation. An operating system error occurred during the write operation. An unspecified error occurred in PL/SQL.

Note: These exceptions must be prefaced with the package name. UTL_FILE procedures can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

Oracle9i: Program with PL/SQL 14-25

The FOPEN and IS_OPEN Functions

FUNCTION FOPEN (location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2) RETURN UTL_FILE.FILE_TYPE;

FUNCTION IS_OPEN (file_handle IN FILE_TYPE) RETURN BOOLEAN;

14-26

Copyright © Oracle Corporation, 2001. All rights reserved.

FOPEN Function Parameters Syntax Definitions
Is the operating-system-specific string that specifies the directory or area in which to open the file filename Is the name of the file, including the extension, without any pathing information open_mode Is string that specifies how the file is to be opened; Supported values are: ‘r’ read text (use GET_LINE) ‘w’ write text (PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH) ‘a’ append text (PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH) The return value is the file handle that is passed to all subsequent routines that operate on the file. Where location

IS_OPEN Function The function IS_OPEN tests a file handle to see if it identifies an opened file. It returns a Boolean value indicating whether the file has been opened but not yet closed. Note: For the full syntax, refer to Oracle9i Supplied PL/SQL Packages and Types Reference.
Oracle9i: Program with PL/SQL 14-26

Using UTL_FILE sal_status.sql CREATE OR REPLACE PROCEDURE sal_status (p_filedir IN VARCHAR2, p_filename IN VARCHAR2) IS v_filehandle UTL_FILE.FILE_TYPE; CURSOR emp_info IS SELECT last_name, salary, department_id FROM employees ORDER BY department_id; v_newdeptno employees.department_id%TYPE; v_olddeptno employees.department_id%TYPE := 0; BEGIN v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,'w'); UTL_FILE.PUTF (v_filehandle,'SALARY REPORT: GENERATED ON %s\n', SYSDATE); UTL_FILE.NEW_LINE (v_filehandle); FOR v_emp_rec IN emp_info LOOP v_newdeptno := v_emp_rec.department_id; ...

14-27

Copyright © Oracle Corporation, 2001. All rights reserved.

Using UTL_FILE Example The SAL_STATUS procedure creates a report of employees for each department and their salaries. This information is sent to a text file by using the UTL_FILE procedures and functions. The variable v_filehandle uses a type defined in the UTL_FILE package. This package defined type is a record with a field called ID of the BINARY_INTEGER datatype. TYPE file_type IS RECORD (id BINARY_INTEGER); The contents of file_type are private to the UTL_FILE package. Users of the package should not reference or change components of this record. The names of the text file and the location for the text file are provided as parameters to the program. EXECUTE sal_status('C:\UTL_FILE', 'SAL_RPT.TXT') Note: The file location shown in the above example is defined as value of UTL_FILE_DIR in the init.ora file as follows: UTL_FILE_DIR = C:\UTL_FILE. When reading a complete file in a loop, you need to exit the loop using the NO_DATA_FOUND exception. UTL_FILE output is sent synchronously. DBMS_OUTPUT procedures do not produce output until the procedure is completed. Instructor Note You can demonstrate this code with the 14_27s.sql and 14_27n.sql files. Run the script 14_27s.sql first. You can demonstrate the output generated in the file instructor.txt after you run 14_27n.sql.
Oracle9i: Program with PL/SQL 14-27

Using UTL_FILE sal_status.sql ... IF v_newdeptno v_olddeptno THEN UTL_FILE.PUTF (v_filehandle, 'DEPARTMENT: %s\n', v_emp_rec.department_id); END IF; UTL_FILE.PUTF (v_filehandle,' EMPLOYEE: %s earns: %s\n', v_emp_rec.last_name, v_emp_rec.salary); v_olddeptno := v_newdeptno; END LOOP; UTL_FILE.PUT_LINE (v_filehandle, '*** END OF REPORT ***'); UTL_FILE.FCLOSE (v_filehandle); EXCEPTION WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR (-20001, 'Invalid File.'); WHEN UTL_FILE.WRITE_ERROR THEN RAISE_APPLICATION_ERROR (-20002, 'Unable to write to file'); END sal_status; /

14-28

Copyright © Oracle Corporation, 2001. All rights reserved.

Using UTL_FILE (continued) The output for this report in the sal_rpt.txt file is as follows: SALARY REPORT: GENERATED ON 08-MAR-01 DEPARTMENT: 10 EMPLOYEE: Whalen earns: 4400 DEPARTMENT: 20 EMPLOYEE: Hartstein earns: 13000 EMPLOYEE: Fay earns: 6000 DEPARTMENT: 30 EMPLOYEE: Raphaely earns: 11000 EMPLOYEE: Khoo earns: 3100 ... DEPARTMENT: 100 EMPLOYEE: Greenberg earns: 12000 ... DEPARTMENT: 110 EMPLOYEE: Higgins earns: 12000 EMPLOYEE: Gietz earns: 8300 EMPLOYEE: Grant earns: 7000 *** END OF REPORT ***
Oracle9i: Program with PL/SQL 14-28

The UTL_HTTP Package
The UTL_HTTP package:

• •

Enables HTTP callouts from PL/SQL and SQL to access data on the Internet Contains the functions REQUEST and REQUEST_PIECES which take the URL of a site as a parameter, contact that site, and return the data obtained from that site Requires a proxy parameter to be specified in the above functions, if the client is behind a firewall Raises INIT_FAILED or REQUEST_FAILED exceptions if HTTP call fails Reports an HTML error message if specified URL is not accessible
Copyright © Oracle Corporation, 2001. All rights reserved.

• • •
14-29

The UTL_HTTP Package UTL_HTTP is a package that allows you to make HTTP requests directly from the database. The UTL_HTTP package makes hypertext transfer protocol (HTTP) callouts from PL/SQL and SQL. You can use it to access data on the Internet or to call Oracle Web Server Cartridges. By coupling UTL_HTTP with the DBMS_JOBS package, you can easily schedule reoccurring requests be made from your database server out to the Web. This package contains two entry point functions: REQUEST and REQUEST_PIECES. Both functions take a string universal resource locator (URL) as a parameter, contact the site, and return the HTML data obtained from the site. The REQUEST function returns up to the first 2000 bytes of data retrieved from the given URL. The REQUEST_PIECES function returns a PL/SQL table of 2000-byte pieces of the data retrieved from the given URL. If the HTTP call fails, for a reason such as that the URL is not properly specified in the HTTP syntax then the REQUEST_FAILED exception is raised. If initialization of the HTTP-callout subsystem fails, for a reason such as a lack of available memory, then the INIT_FAILED exception is raised. If there is no response from the specified URL, then a formatted HTML error message may be returned. If REQUEST or REQUEST_PIECES fails by returning either an exception or an error message, then verify the URL with a browser, to verify network availability from your machine. If you are behind a firewall, then you need to specify proxy as a parameter, in addition to the URL. This package is covered in more detail in the course Administering Oracle9i Application Server. For more information, refer to Oracle9i Supplied PL/SQL Packages Reference.
Oracle9i: Program with PL/SQL 14-29

Using the UTL_HTTP Package

SELECT UTL_HTTP.REQUEST('http://www.oracle.com', 'edu-proxy.us.oracle.com') FROM DUAL;

14-30

Copyright © Oracle Corporation, 2001. All rights reserved.

Using the UTL_HTTP Package The SELECT statement and the output in the slide show how to use the REQUEST function of the UTL_HTTP package to retrieve contents from the URL www.oracle.com. The second parameter to the function indicates the proxy because the client being tested is behind a firewall. The retrieved output is in HTML format. You can use the function in a PL/SQL block as shown below. The function retrieves up to 100 pieces of data, each of a maximum 2000 bytes from the URL. The number of pieces and the total length of the data retrieved are printed. DECLARE x UTL_HTTP.HTML_PIECES; BEGIN x := UTL_HTTP.REQUEST_PIECES('http://www.oracle.com/',100, 'edu-proxy.us.oracle.com'); DBMS_OUTPUT.PUT_LINE(x.COUNT || ' pieces were retrieved.'); DBMS_OUTPUT.PUT_LINE('with total length '); IF x.COUNT < 1 THEN DBMS_OUTPUT.PUT_LINE('0'); ELSE DBMS_OUTPUT.PUT_LINE((2000*(x.COUNT - 1))+LENGTH(x(x.COUNT))); END IF; END; /

Oracle9i: Program with PL/SQL 14-30

Using the UTL_TCP Package
The UTL_TCP Package:

• • • •

Enables PL/SQL applications to communicate with external TCP/IP-based servers using TCP/IP Contains functions to open and close connections, to read or write binary or text data to or from a service on an open connection Requires remote host and port as well as local host and port as arguments to its functions Raises exceptions if the buffer size is too small, when no more data is available to read from a connection, when a generic network error occurs, or when bad arguments are passed to a function call

14-31

Copyright © Oracle Corporation, 2001. All rights reserved.

Using the UTL_TCP Package The UTL_TCP package enables PL/SQL applications to communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols. The package contains functions such as: OPEN_CONNECTION: This function opens a TCP/IP connection with the specified remote and local host and port details. The remote host is the host providing the service. The remote port is the port number on which the service is listening for connections. The local host and port numbers represent those of the host providing the service. The function returns a connection of PL/SQL record type. CLOSE_CONNECTION: This procedure closes an open TCP/IP connection. It takes the connection details of a previously opened connection as parameter. The procedure CLOSE_ALL_CONNECTIONS closes all open connections. READ_BINARY()/TEXT()/LINE(): This function receives binary, text, or text line data from a service on an open connection. WRITE_BINARY()/TEXT()/LINE(): This function transmits binary, text, or text line message to a service on an open connection. Exceptions are raised when buffer size for the input is too small, when generic network error occurs, when no more data is available to read from the connection, or when bad arguments are passed in a function call. This package is discussed in detail in the course Administering Oracle9i Application Server. For more information, refer to Oracle 9i Supplied PL/SQL Packages Reference.
Oracle9i: Program with PL/SQL 14-31

Oracle-Supplied Packages

Other Oracle-supplied packages include:

• • • • •

DBMS_ALERT DBMS_APPLICATION_INFO DBMS_DESCRIBE DBMS_LOCK DBMS_SESSION

• • •

DBMS_SHARED_POOL DBMS_TRANSACTION DBMS_UTILITY

14-32

Copyright © Oracle Corporation, 2001. All rights reserved.

Using Oracle-Supplied Packages

Package DBMS_ALERT

Description Provides notification of database events

DBMS_APPLICATION_INFO Allows application tools and application developers to inform the database of the high level of actions they are currently performing DBMS_DESCRIBE DBMS_LOCK DBMS_SESSION DBMS_SHARED_POOL DBMS_TRANSACTION DBMS_UTILITY Returns a description of the arguments for a stored procedure Requests, converts, and releases userlocks, which are managed by the RDBMS lock management services Provides access to SQL session information Keeps objects in shared memory Controls logical transactions and improves the performance of short, nondistributed transactions Analyzes objects in a particular schema, checks whether the server is running in parallel mode, and returns the time

Oracle9i: Program with PL/SQL 14-32

Oracle-Supplied Packages

The following list summarizes and provides a brief description of the packages supplied with Oracle9i.
Built-in Name CALENDAR DBMS_ALERT Description Provides calendar maintenance functions Supports asynchronous notification of database events. Messages or alerts are sent on a COMMIT command. Message transmittal is one way, but one sender can alert several receivers. Is used to register an application name with the database for auditing or performance tracking purposes Provides message queuing as part of the Oracle server; is used to add a message (of a predefined object type) onto a queue or dequeue a message Is used to perform administrative functions on a queue or queue table for messages of a predefined object type Is used to embed the equivalent of the SQL commands ALTER, COMPILE, and ANALYZE within your PL/SQL programs A PL/SQL API to the PL/SQL debugger layer, Probe, in the Oracle server Is used to build and administer deferred remote procedure calls (use of this feature requires the Replication Option) Is used to describe the arguments of a stored procedure Is used to maintain the Trusted Servers list, which is used in conjunction with the list at the central authority to determine whether a privileged database link from a particular server can be accepted Is used to administer heterogeneous services by registering or dropping distributed external procedures, remote libraries, and non-Oracle systems (you use dbms_hs to create or drop some initialization variables for non-Oracle systems) Enables heterogeneous services to establish security for distributed external procedures Enables heterogeneous services to send pass-through SQL statements to non-Oracle systems Is used to schedule administrative procedures that you want performed at periodic intervals; is also the interface for the job queue Is used to schedule administrative procedures that you want performed at periodic intervals Provides general purpose routines for operations on Oracle large objects (LOBs) data types: BLOB, CLOB (read only) and BFILES (read-only)

DBMS_APPLICATION_INFO DBMS_AQ

DBMS_AQADM DBMS_DDL

DBMS_DEBUG DBSM_DEFER DBMS_DEFER_QUERY DBMS_DEFER_SYS DBMS_DESCRIBE DBMS_DISTRIBRUTED_ TRUST_ADMIN

DBMS_HS

DBMS_HS_EXTPROC DBMS_HS_PASSTHROUGH DBMS_IOT

DBMS_JOB DBMS_LOB

Oracle9i: Program with PL/SQL 14-33

Oracle Supplied Packages (continued)

B u ilt-in N am e DB M S _ L O CK DB M S _ L O GM N R DB M S _ L O GM N R _ D DB M S _ O F FL I N E _ O G DB M S _ O F FL I N E _ S NA P S H OT DB M S _ O L AP DB M S _ O R AC L E _ T R AC E _ AG E N T DB M S _ O R AC L E _ T R AC E _ US E R DB M S _ O U TP U T DB M S _ P C LX U T I L DB M S _ P I PE DB M S _ P R OF I L E R DB M S _ R A ND O M DB M S _ R E CT I F I E R _D I F F DB M S _ R E FR E S H

D escrip tion Is used to request, convert, and release locks through O racle L ock M anagem ent services P rovides functions to initialize and run the log reader Q ueries the dictionary tables of the current database, and creates a text based file containing their contents P rovides public A P Is for offline instantiation of m aster groups P rovides public A P Is for offline instantiation of snapshots P rovides procedures for sum m aries, dim ensions, and query rew rites P rovides client callable interfaces to the O racle T R A C E instrum entation w ithin the O racle7 server P rovides public access to the O racle7 release server O racle T R A C E instrum entation for the calling user A ccum ulates inform ation in a buffer so that it can be retrieved out later P rovides intrapartition parallelism for creating partitionw ise local indexes P rovides a D B M S pipe service that enables m essages to be sent betw een sessions P rovides a Probe P rofiler A P I to profile existing P L/S Q L applications and identify perform ance bottlenecks P rovides a built-in random num ber generator P rovides A P Is used to detect and resolve data inconsistencies betw een tw o replicated sites Is used to create groups of snapshots that can be refreshed together to a transactionally consistent point in tim e; requires the D istributed option P rovides data corruption repair procedures P rovides routines to adm inister and update the replication catalog and environm ent; requires the R eplication option Is used to create users w ith the privileges needed by the sym m etric replication facility; requires the R eplication option Instantiates deploym ent tem plates; requires the R eplication option C ontrols the m aintenance and definition of refresh group tem plates; requires the R eplication option P rovides routines to generate shadow tables, triggers, and packages for table replication M aintains plans, consum er groups, and plan directives; it also provides sem antics so that you m ay group together changes to the plan schem a

DB M S _ R E PA I R DB M S _ R E PC A T DB M S _ R E PC A T _ A D MI N

DB M S _ R E PC A T _ IN S T A T I AT E DB M S _ R E PC A T _ R G T DB M S _ R E PU T I L DB M S _ R E SO U R C E _ MA N A G E R

Oracle9i: Program with PL/SQL 14-34

Oracle Supplied Packages (continued)
Built-in Name DBMS_RESOURCE_ MANAGER_PRIVS DBMS_RLS DBMS_ROWID Description Maintains privileges associated with resource consumer groups Provides row-level security administrative interface Is used to get information about ROWIDs, including the data block number, the object number, and other components Enables programmatic use of the SQL ALTER SESSION statement as well as other session-level commands Is used to keep objects in shared memory, so that they are not aged out with the normal LRU mechanism Is used to refresh one or more snapshots that are not part of the same refresh group and purge logs; use of this feature requires the Distributed option Provides segment space information not available through standard views Provides tablespace and segment space administration not available through standard SQL Is used to write stored procedure and anonymous PL/SQL blocks using dynamic SQL; also used to parse any DML or DDL statement Provides language facilities that help your application interact with the Oracle server Provides a mechanism for users to view and modify optimizer statistics gathered for database objects Provides routines to start and stop PL/SQL tracing Provides procedures for a programmatic interface to transaction management Checks whether if the transportable set is self-contained Provides functionality for managing procedures, reporting errors, and other information Is used to debug external procedures on platforms with debuggers that can attach to a running process Provides the interface for procedures and functions associated with management of stored outlines Handles index-table operations Provides functions implementing spatial index creation and maintenance for spatial objects Provides functions implementing geometric operations on spatial objects Provides functions for migrating spatial data from release 7.3.3 and 7.3.4 to 8.1.x Provides functions for selecting parameters that determine the behavior of the spatial indexing scheme used in the Spatial Cartridge
Oracle9i: Program with PL/SQL 14-35

DBMS_SESSION DBMS_SHARED_POOL DBMS_SNAPSHOT

DBMS_SPACE DBMS_SPACE_ADMIN DSMS_SQL

DBMS_STANDARD DBMS_STATS DBMS_TRACE DBMS_TRANSACTION DBMS_TTS DBMS_UTILITY DEBUG_EXTPROC OUTLN_PKG PLITBLM SDO_ADMIN SDO_GEOM SDO_MIGRATE SDO_TUNE

Oracle Supplied Packages (continued)
Built-in Name STANDARD TIMESERIES Description Declares types, exceptions, and subprograms that are available automatically to every PL/SQL program Provides functions that perform operations, such as extraction, retrieval, arithmetic, and aggregation, on time series data Provides scale-up and scale-down functions Provides administrative tools procedures Enables PL/SQL programs to use collection locators to query and update Enables your PL/SQL programs to read and write operating system (OS) text files and provides a restricted version of standard OS stream file I/O Enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges Provides functions for converting COBOL numeric data into Oracle numbers and Oracle numbers into COBOL numeric data Provides SQL functions for RAW data types that concatenate, obtain substring, and so on, to and from RAW data types Enables a PL/SQL program to access an object by providing a reference to the object Provides analytical and conversion functions for visual information retrieval

TIMESCALE TSTOOLS UTL_COLL UTL_FILE

UTL_HTTP

UTL_PG

UTL_RAW

UTL_REF VIR_PKG

Oracle9i: Program with PL/SQL 14-36

Summary

In this lesson, you should have learned how to:

• • •

Take advantage of the preconfigured packages that are provided by Oracle Create packages by using the catproc.sql script Create packages individually.

14-37

Copyright © Oracle Corporation, 2001. All rights reserved.

DBMS Packages and the Scripts to Execute Them

DBMS_ALERT DBMS_APPLICATION_INFO DBMS_DDL DBMS_LOCK DBMS_OUTPUT DBMS_PIPE DBMS_SESSION DBMS_SHARED_POOL DBMS_SQL DBMS_TRANSACTION

dbmsalrt.sql dbmsutil.sql dbmsutil.sql dbmslock.sql dbmsotpt.sql dbmspipe.sql dbmsutil.sql dbmsspool.sql dbmssql.sql dbmsutil.sql

DBMS_UTILITY dbmsutil.sql Note: For more information about these packages and scripts, refer to Oracle9i Supplied PL/SQL Packages and Types Reference.
Instructor Note

Point out to the students that these script files often have useful comments embedded within them that supplement the documentation. See the dbmspipe.sql script for an example.
Oracle9i: Program with PL/SQL 14-37

Practice 14 Overview

This practice covers using: • DBMS_SQL for dynamic SQL

• • •

DBMS_DDL to analyze a table DBMS_JOB to schedule a task UTL_FILE to generate text reports

14-38

Copyright © Oracle Corporation, 2001. All rights reserved.

Practice 14 Overview In this practice, you use DBMS_SQL to implement a procedure to drop a table. You also use the EXECUTE IMMEDIATE command to drop a table. You use DBMS_DDL to analyze objects in your schema, and you can schedule the analyze procedure through DBMS_JOB.

In this practice, you also write a PL/SQL program that generates customer statuses into a text file.

Oracle9i: Program with PL/SQL 14-38

Practice 14 1. a. Create a DROP_TABLE procedure that drops the table specified in the input parameter. Use the procedures and functions from the supplied DBMS_SQL package.

b. To test the DROP_TABLE procedure, first create a new table called EMP_DUP as a copy of the EMPLOYEES table. c. Execute the DROP_TABLE procedure to drop the EMP_DUP table. 2. a. Create another procedure called DROP_TABLE2 that drops the table specified in the input parameter. Use the EXECUTE IMMEDIATE statement. b. Repeat the test outlined in steps 1-b and 1-c. 3. a. Create a procedure called ANALYZE_OBJECT that analyzes the given object that you specified in the input parameters. Use the DBMS_DDL package, and use the COMPUTE method. b. Test the procedure using the EMPLOYEES table. Confirm that the ANALYZE_OBJECT procedure has run by querying the LAST_ANALYZED column in the USER_TABLES data dictionary view.

If you have time: 4. a. Schedule ANALYZE_OBJECT by using DBMS_JOB. Analyze the DEPARTMENTS table, and schedule the job to run in five minutes time from now. (To start the job in five minutes from now, set the parameter NEXT_DATE = 5/(24*60) = 1/288.) b. Confirm that the job has been scheduled by using USER_JOBS. 5. Create a procedure called CROSS_AVGSAL that generates a text file report of employees who have exceeded the average salary of their department. The partial code is provided for you in the file lab14_5.sql. a. Your program should accept two parameters. The first parameter identifies the output directory. The second parameter identifies the text file name to which your procedure writes. b. Your instructor will inform you of the directory location. When you invoke the program, name the second parameter sal_rptxx.txt where xx stands for your user number, such as 01, 15, and so on. c. Add an exception handling section to handle errors that may be encountered from using the UTL_FILE package. Sample output from this file follows: EMPLOYEES OVER THE AVERAGE SALARY OF THEIR DEPARTMENT: REPORT GENERATED ON 26-FEB-01
Hartstein Raphaely Marvis ... *** END OF REPORT *** 20 30 40 $13,000.00 $11,000.00 $6,500.00

Oracle9i: Program with PL/SQL 14-39

Instructor Note You can locate the directory name UTL_FILE, where the students write their files as follows:

Use Telnet to connect to the server provided by the ESS group. Log in to the server operating system using the username and password supplied by ESS. Type the command ls to list the contents and observe that the directory UTL_FILE is listed. Type the command pwd to display the present working directory. Give this value to the students to use as a parameter in their practice.

Oracle9i: Program with PL/SQL 14-40

Manipulating Large Objects

Copyright © Oracle Corporation, 2001. All rights reserved.

Schedule:

Timing
70 minutes 45 minutes 115 minutes

Topic
Lecture Practice Total

Objectives

After completing this lesson, you should be able to do the following: • Compare and contrast LONG and large object (LOB) 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

15-2

Copyright © Oracle Corporation, 2001. All rights reserved.

Lesson Aim Databases have long been used to store large objects. However, the mechanisms built into databases have never been as useful as the new large object (LOB) data types provided in Oracle8. This lesson describes the characteristics of the new data types, comparing and contrasting them with earlier data types. Examples, syntax, and issues regarding the LOB types are also presented. Note: A LOB is a data type and should not be confused with an object type.

Instructor Note LOBs are available with Oracle8. They are not part of any option.
Oracle9i: Program with PL/SQL 15-2

What Is a LOB?
LOBs are used to store large unstructured data such as text, graphic images, films, and sound waveforms.

“Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated to the proposition that all men are created equal.”

Text (CLOB)

Photo (BLOB)
Copyright © Oracle Corporation, 2001. All rights reserved.

Movie (BFILE)

15-3

Overview A LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and so on. Structured data such as a customer record may be a few hundred bytes, but even small amounts of multimedia data can be thousands of times larger. Also, multimedia data may reside on operating system (OS) files, which may need to be accessed from a database. There are four large object data types: • BLOB represents a binary large object, such as a video clip. • CLOB represents a character large object. • • • NCLOB represents a multibyte character large object. BFILE represents a binary file stored in an operating system binary file outside the database. The BFILE column or attribute stores a file locator that points to the external file. LOBs are characterized in two ways, according to their interpretation by the Oracle server (binary or character) and their storage aspects. LOBs can be stored internally (inside the database) or in host files. There are two categories of LOBs: Internal LOBs (CLOB, NCLOB, BLOB) are stored in the database. External files (BFILE) are stored outside the database.

• •

The Oracle9i Server performs implicit conversion between CLOB and VARCHAR2 data types. The other implicit conversions between LOBs are not possible. For example, if the user creates a table T with a CLOB column and a table S with a BLOB column, the data is not directly transferable between these two columns. BFILEs can be accessed only in read-only mode from an Oracle server.
Oracle9i: Program with PL/SQL 15-3

Contrasting LONG and LOB Data Types

LONG and LONG RAW Single LONG column per table Up to 2 GB SELECT returns data Data stored in-line Sequential access to data

LOB Multiple LOB columns per table Up to 4 GB SELECT returns locator Data stored in-line or out-of-line Random access to data

15-4

Copyright © Oracle Corporation, 2001. All rights reserved.

LONG and LOB Data Types LONG and LONG RAW data types were previously used for unstructured data, such as binary images, documents, or geographical information. These data types are superseded by the LOB data types. Oracle 9i provides a LONG-to-LOB API to migrate from LONG columns to LOB columns. It is beneficial to discuss LOB functionality in comparison to the older types. In the bulleted list below, LONGs refers to LONG and LONG RAW, and LOBs refers to all LOB data types: • • A table can have multiple LOB columns and object type attributes. A table can have only one LONG column. The maximum size of LONGs is 2 gigabytes; LOBs can be up to 4 gigabytes.

• LOBs return the locator; LONGs return the data. • LOBs store a locator in the table and the data in a different segment, unless the data is less than 4,000 bytes; LONGs store all data in the same data block. In addition, LOBs allow data to be stored in a separate segment and tablespace, or in a host file. • LOBs can be object type attributes; LONGs cannot. • LOBs support random piecewise access to the data through a file-like interface; LONGs are restricted to sequential piecewise access. The TO_LOB function can be used to covert LONG and LONG RAW values in a column to LOB values. You use this in the SELECT list of a subquery in an INSERT statement.

Oracle9i: Program with PL/SQL 15-4

Anatomy of a LOB

The LOB column stores a locator to the LOB's value.

LOB locator

LOB column of a table

LOB value

15-5

Copyright © Oracle Corporation, 2001. All rights reserved.

Components of a LOB There are two distinct parts of a LOB: • • LOB value: The data that constitutes the real object being stored. LOB locator: A pointer to the location of the LOB value stored in the database.

Regardless of where the value of the LOB is stored, a locator is stored in the row. You can think of a LOB locator as a pointer to the actual location of the LOB value. A LOB column does not contain the data; it contains the locator of the LOB value. When a user creates an internal LOB, the value is stored in the LOB segment and a locator to the out-ofline LOB value is placed in the LOB column of the corresponding row in the table. External LOBs store the data outside the database, so only a locator to the LOB value is stored in the table. To access and manipulate LOBs without SQL DML, you must create a LOB locator. Programmatic interfaces operate on the LOB values, using these locators in a manner similar to operating system file handles. Instructor Note An internal LOB’s value is stored in-line with the other row data if the size of the LOB value is less than 4,000 bytes. When the LOB value is larger than 4,000 bytes, the LOB value is automatically moved out of the row. When you are creating a table with a LOB column, the default storage is ENABLE STORAGE IN ROW. If you do not want the LOB value stored in the row, even if the size is less than 4,000 bytes, use the storage clause option DISABLE STORAGE IN ROW.
Oracle9i: Program with PL/SQL 15-5

Internal LOBs

The LOB value is stored in the database.

“Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated to the proposition that all men are created equal.”

CLOB

BLOB

15-6

Copyright © Oracle Corporation, 2001. All rights reserved.

Features of Internal LOBs The internal LOB is stored inside the Oracle server. A BLOB, NCLOB, or CLOB can be one of the following: • • • • • • • An attribute of a user-defined type A column in a table A bind or host variable A PL/SQL variable, parameter, or result Concurrency mechanisms Redo logging and recovery mechanisms Transactions with commit or rollbacks

Internal LOBs can take advantage of Oracle features such as:

The BLOB data type is interpreted by the Oracle server as a bitstream, similar to the LONG RAW data type. The CLOB data type is interpreted as a single-byte character stream. The NCLOB data type is interpreted as a multiple-byte character stream, based on the byte length of the database national character set.

Oracle9i: Program with PL/SQL 15-6

Managing Internal LOBs •

To interact fully with LOB, file-like interfaces are provided in:
– – – – – PL/SQL package DBMS_LOB Oracle Call Interface (OCI) Oracle Objects for object linking and embedding (OLE) Pro*C/C++ and Pro*COBOL precompilers JDBC

•

The Oracle server provides some support for LOB management through SQL.

15-7

Copyright © Oracle Corporation, 2001. All rights reserved.

How to Manage LOBs Use the following method to manage an internal LOB: 1. Create and populate the table containing the LOB data type. 2. Declare and initialize the LOB locator in the program. 3. Use SELECT FOR UPDATE to lock the row containing the LOB into the LOB locator. 4. Manipulate the LOB with DBMS_LOB package procedures, OCI calls, Oracle Objects for OLE, Oracle precompilers, or JDBC using the LOB locator as a reference to the LOB value. You can also manage LOBs through SQL. 5. Use the COMMIT command to make any changes permanent.

Instructor Note OCI: Oracle Call Interface Oracle Objects for OLE (OO4O): Oracle Objects for object linking and embedding JDBC: Java Database Connectivity

Oracle9i: Program with PL/SQL 15-7

What Are BFILEs?
The BFILE data type supports an external or file-based large object as: • Attributes in an object type

•

Column values in a table

Movie (BFILE)
15-8 Copyright © Oracle Corporation, 2001. All rights reserved.

What Are BFILEs? BFILEs are external large objects (LOBs) stored in operating system files outside of the database tablespaces. The Oracle SQL data type to support these large objects is BFILE. The BFILE data type stores a locator to the physical file. A BFILE can be in GIF, JPEG, MPEG, MPEG2, text, or other formats. The External LOBs may be located on hard disks, CDROMs, photo CDs, or any such device, but a single LOB cannot extend from one device to another. The BFILE data type is available so that database users can access the external file system. The Oracle9i server provides for: • Definition of BFILE objects • • Association of BFILE objects to corresponding external files Security for BFILEs

The rest of the operations required to use BFILEs are possible through the DBMS_LOB package and the Oracle Call Interface. BFILEs are read-only, so they do not participate in transactions. Any support for integrity and durability must be provided by the operating system. The user must create the file and place it in the appropriate directory, giving the Oracle process privileges to read the file. When the LOB is deleted, the Oracle server does not delete the file. The administration of the actual files and the OS directory structures to house the files is the responsibility of the database administrator (DBA), system administrator, or user. The maximum size of an external large object is operating system dependent but cannot exceed four gigabytes. Note: BFILEs are available in the Oracle8 database and in later releases.
Oracle9i: Program with PL/SQL 15-8

Securing BFILEs

User

Access permissions

Movie (BFILE)
15-9 Copyright © Oracle Corporation, 2001. All rights reserved.

Securing BFILEs Unauthenticated access to files on a server presents a security risk. The Oracle9i Server can act as a security mechanism to shield the operating system from unsecured access while removing the need to manage additional user accounts on an enterprise computer system. File Location and Access Privileges The file must reside on the machine where the database exists. A time-out to read a nonexistent BFILE is based on the operating system value. You can read a BFILE in the same way as you read an internal LOB. However, there could be restrictions related to the file itself, such as: • • • Access permissions File system space limits Non-Oracle manipulations of files

• OS maximum file size The Oracle9i RDBMS does not provide transactional support on BFILEs. Any support for integrity and durability must be provided by the underlying file system and the OS. Oracle backup and recovery methods support only the LOB locators, not the physical BFILEs.

Oracle9i: Program with PL/SQL 15-9

A New Database Object: DIRECTORY

User DIRECTORY LOB_PATH = '/oracle/lob/'

Movie (BFILE)
15-10 Copyright © Oracle Corporation, 2001. All rights reserved.

A New Database Object: DIRECTORY A DIRECTORY is a nonschema database object that provides for administration of access and usage of BFILEs in an Oracle9i Server. A DIRECTORY specifies an alias for a directory on the file system of the server under which a BFILE is located. By granting suitable privileges for these items to users, you can provide secure access to files in the corresponding directories on a user-by-user basis (certain directories can be made read-only, inaccessible, and so on). Further, these directory aliases can be used while referring to files (open, close, read, and so on) in PL/SQL and OCI. This provides application abstraction from hard-coded path names, and gives flexibility in portably managing file locations. The DIRECTORY object is owned by SYS and created by the DBA (or a user with CREATE ANY DIRECTORY privilege). Directory objects have object privileges, unlike any other nonschema object. Privileges to the DIRECTORY object can be granted and revoked. Logical path names are not supported. The permissions for the actual directory are operating system dependent. They may differ from those defined for the DIRECTORY object and could change after the creation of the DIRECTORY object.

Oracle9i: Program with PL/SQL 15-10

Guidelines for Creating DIRECTORY Objects • •
Do not create DIRECTORY objects on paths with database files. Limit the number of people who are given the following system privileges:
– CREATE ANY DIRECTORY – DROP ANY DIRECTORY

• •

All DIRECTORY objects are owned by SYS. Create directory paths and properly set permissions before using the DIRECTORY object so that the Oracle server can read the file.

15-11

Copyright © Oracle Corporation, 2001. All rights reserved.

Guidelines for Creating Directory Objects To associate an operating system file to a BFILE, you should first create a DIRECTORY object that is an alias for the full pathname to the operating system file. Create DIRECTORY objects by using the following guidelines: • Directories should point to paths that do not contain database files, because tampering with these files could corrupt the database. Currently, only the READ privilege can be given for a DIRECTORY object. • The system privileges CREATE ANY DIRECTORY and DROP ANY DIRECTORY should be used carefully and not granted to users indiscriminately. • DIRECTORY objects are not schema objects; all are owned by SYS. • Create the directory paths with appropriate permissions on the OS prior to creating the DIRECTORY object. Oracle does not create the OS path. If you migrate the database to a different operating system, you may need to change the path value of the DIRECTORY object. The DIRECTORY object information that you create by using the CREATE DIRECTORY command is stored in the data dictionary views DBA_DIRECTORIES and ALL_DIRECTORIES.

Oracle9i: Program with PL/SQL 15-11

Managing BFILEs • • • • • • •

Create an OS directory and supply files. Create an Oracle table with a column that holds the BFILE data type. Create a DIRECTORY object. Grant privileges to read the DIRECTORY object to users. Insert rows into the table by using the BFILENAME function. Declare and initialize a LOB locator in a program. Read the BFILE.

15-12

Copyright © Oracle Corporation, 2001. All rights reserved.

How to Manage BFILEs Use the following method to manage the BFILE and DIRECTORY objects: 1. Create the OS directory (as an Oracle user) and set permissions so that the Oracle server can read the contents of the OS directory. Load files into the the OS directory. 2. Create a table containing the BFILE data type in the Oracle server. 3. Create the DIRECTORY object. 4. Grant the READ privilege to it. 5. Insert rows into the table using the BFILENAME function and associate the OS files with the corresponding row and column intersection. 6. Declare and initialize the LOB locator in a program. 7. Select the row and column containing the BFILE into the LOB locator. 8. Read the BFILE with an OCI or a DBMS_LOB function, using the locator as a reference to the file.

Instructor Note There is a GRANT READ privilege so users can read from the directory created. This privilege is checked by the DBMS_LOB package.
Oracle9i: Program with PL/SQL 15-12

Preparing to Use BFILEs •
Create or modify an Oracle table with a column that holds the BFILE data type.
ALTER TABLE employees ADD emp_video BFILE;

•

Create a DIRECTORY object by using the CREATE DIRECTORY command.
CREATE DIRECTORY dir_name AS os_path;

•

Grant privileges to read the DIRECTORY object to users.
GRANT READ ON DIRECTORY dir_name TO user|role|PUBLIC;

15-13

Copyright © Oracle Corporation, 2001. All rights reserved.

Preparing to Use BFILEs In order to use a BFILE within an Oracle table, you need to have a table with a column of BFILE type. For the Oracle server to access an external file, the server needs to know the location of the file on the operating system. The DIRECTORY object provides the means to specify the location of the BFILEs. Use the CREATE DIRECTORY command to specify the pointer to the location where your BFILEs are stored. You need the CREATE ANY DIRECTORY privilege. Syntax Definition: CREATE DIRECTORY dir_name AS os_path; dir_name is the name of the directory database object Where: os_path is the location of the BFILEs The following commands set up a pointer to BFILEs in the system directory /$HOME/LOG_FILES and give users the privilege to read the BFILEs from the directory. CREATE OR REPLACE DIRECTORY log_files AS '/$HOME/LOG_FILES'; GRANT READ ON DIRECTORY log_files TO PUBLIC;

In a session, the number of BFILEs that can be opened in one session is limited by the parameter SESSION_MAX_OPEN_FILES. This parameter is set in the init.ora file. Its default value is 10. Instructor Note To delete a created directory, use the DROP DIRECTORY command.
Oracle9i: Program with PL/SQL 15-13

The BFILENAME Function

Use the BFILENAME function to initialize a BFILE column.
FUNCTION BFILENAME (directory_alias IN VARCHAR2, filename IN VARCHAR2) RETURN BFILE;

15-14

Copyright © Oracle Corporation, 2001. All rights reserved.

The BFILENAME Function BFILENAME is a built-in function that initializes a BFILE column to point to an external file. Use the BFILENAME function as part of an INSERT statement to initialize a BFILE column by associating it with a physical file in the server file system. You can use the UPDATE statement to change the reference target of the BFILE. A BFILE can be initialized to NULL and updated later by using the BFILENAME function. Syntax Definitions
Where: directory_alias is the name of the DIRECTORY database object

filename is the name of the BFILE to be read Example UPDATE employees SET emp_video = BFILENAME('LOG_FILES', 'King.avi') WHERE employee_id = 100;

Once physical files are associated with records using SQL DML, subsequent read operations on the BFILE can be performed using the PL/SQL DBMS_LOB package and OCI. However, these files are read-only when accessed through BFILEs, and so they cannot be updated or deleted through BFILEs. Instructor Note You can demonstrate this code with the code example 15_14n.sql file. This script file contains additional statements to disable and enable triggers on EMPLOYEES table.
Oracle9i: Program with PL/SQL 15-14

Loading BFILEs
CREATE OR REPLACE PROCEDURE load_emp_bfile (p_file_loc IN VARCHAR2) IS v_file BFILE; v_filename VARCHAR2(16); CURSOR emp_cursor IS SELECT first_name FROM employees WHERE department_id = 60 FOR UPDATE; BEGIN FOR emp_record IN emp_cursor LOOP v_filename := emp_record.first_name || '.bmp'; v_file := BFILENAME(p_file_loc, v_filename); DBMS_LOB.FILEOPEN(v_file); UPDATE employees SET emp_video = v_file WHERE CURRENT OF emp_cursor; DBMS_OUTPUT.PUT_LINE('LOADED FILE: '||v_filename || ' SIZE: ' || DBMS_LOB.GETLENGTH(v_file)); DBMS_LOB.FILECLOSE(v_file); END LOOP; END load_emp_bfile; /

15-15

Copyright © Oracle Corporation, 2001. All rights reserved.

Loading BFILEs Example Load a BFILE pointer to an image of each employee into the EMPLOYEES table by using the DBMS_LOB package. The images are .bmp files stored in the /home/LOG_FILES directory. Executing the procedure yields the following results: EXECUTE load_emp_bfile('LOG_FILES')

Instructor Note Although the DIRECTORY object, represented by the DIRECTORY_ALIAS parameter to BFILENAME(), need not already be defined before BFILENAME() is called by a SQL or PL/SQL program, the DIRECTORY object and operating system file must exist by the time you actually use the BFILE locator. For example, when the BFILE locator is used as a parameter to one of the following operations: OCILobFileOpen(), DBMS_LOB.FILEOPEN(), OCILOBOPEN(), DBMS_LOB.OPEN() You can demonstrate this code with the 15_15s.sql and 15_15n.sql files. Run the 15_15s.sql script first.
Oracle9i: Program with PL/SQL 15-15

Loading BFILEs
Use the DBMS_LOB.FILEEXISTS function to vefiry that the file exists in the operating system. The function returns 0 if the file does not exist, and returns 1 if the file does exist.
CREATE OR REPLACE PROCEDURE load_emp_bfile (p_file_loc IN VARCHAR2) IS v_file BFILE; v_filename VARCHAR2(16); v_file_exists BOOLEAN; CURSOR emp_cursor IS ... BEGIN FOR emp_record IN emp_cursor LOOP v_filename := emp_record.first_name || '.bmp'; v_file := BFILENAME (p_file_loc, v_filename); v_file_exists := (DBMS_LOB.FILEEXISTS(v_file) = 1); IF v_file_exists THEN DBMS_LOB.FILEOPEN (v_file); ...

15-16

Copyright © Oracle Corporation, 2001. All rights reserved.

Using DBMS_LOB.FILEEXISTS This function finds out whether a given BFILE locator points to a file that actually exists on the server's file system. This is the specification for the function: Syntax Definitions FUNCTION DBMS_LOB.FILEEXISTS (file_loc IN BFILE) RETURN INTEGER;
Where: file_loc RETURN INTEGER is name of the BFILE locator returns 0 if the physical file does not exist returns 1 if the physical file exists

If the FILE_LOC parameter contains an invalid value, one of three exceptions may be raised. In the example in the slide, the output of the DBMS_LOB.FILEEXISTS function is compared with value 1 and the result is returned to the BOOLEAN variable V_FILE_EXISTS.
Exception Nam e NOEXIST_DIRECTORY NOPRIV_DIRECTORY INVALID_DIRECTORY Description T he directory does not exist. You do not have privileges for the directory. T he directory was invalidated after the file was opened.

Oracle9i: Program with PL/SQL 15-16

Migrating from LONG to LOB

The Oracle9i server allows migration of LONG columns to LOB columns.

•

Data migration consists of the procedure to move existing tables containing LONG columns to use LOBs.
ALTER TABLE [.] MODIFY ( {CLOB | BLOB | NCLOB}

•

Application migration consists of changing existing LONG applications for using LOBs.

15-17

Copyright © Oracle Corporation, 2001. All rights reserved.

Migrating from LONG to LOB Oracle9i Server supports the LONG-to-LOB migration using API. Data migration: Where existing tables that contain LONG columns need to be moved to use LOB columns. This can be done using the ALTER TABLE command. In Oracle8i, an operator named TO_LOB had to be used to copy a LONG to a LOB. In Oracle9i, this operation can be performed using the syntax shown in the slide. You can use the syntax shown to: • • Modify a LONG column to a CLOB or an NCLOB column Modify a LONG RAW column to a BLOB column

The constraints of the LONG column (NULL and NOT-NULL are the only allowed constraints) are maintained for the new LOB columns. The default value specified for the LONG column is also copied to the new LOB column. For example, if you had a table with the following definition: CREATE TABLE Long_tab (id NUMBER, long_col LONG); you can change the LONG_COL column in table LONG_TAB to the CLOB data type as follows: ALTER TABLE Long_tab MODIFY ( long_col CLOB ); For limitations on the LONG-to-LOB migration, refer to Oracle9i Application Developer’s Guide - Large Objects. Application Migration: Where the existing LONG applications change for using LOBs. You can use SQL and PL/SQL to access LONGs and LOBs. This API is provided for both OCI and PL/SQL.
Oracle9i: Program with PL/SQL 15-17

Migrating From LONG to LOB • •
Implicit conversion: LONG (LONG RAW) or a VARCHAR2(RAW) variable to a CLOB (BLOB) variable, and vice versa Explicit conversion:
– TO_CLOB() converts LONG, VARCHAR2, and CHAR to CLOB – TO_BLOB() converts LONG RAW and RAW to BLOB

•

Function and Procedure Parameter Passing:
– CLOBs and BLOBs as actual parameters – VARCHAR2, LONG, RAW, and LONG RAW are formal parameters, and vice versa

•

LOB data is acceptable in most of the SQL and PL/SQL operators and built-in functions

15-18

Copyright © Oracle Corporation, 2001. All rights reserved.

Migrating from LONG to LOB (continued) With the new LONG-to-LOB API introduced in Oracle9i, data from CLOB and BLOB columns can be referenced by regular SQL and PL/SQL statements. Implicit assignment and parameter passing: The LONG-to-LOB migration API supports assigning a CLOB (BLOB) variable to a LONG (LONG RAW) or a VARCHAR2(RAW) variable, and vice versa. Explicit conversion functions: In PL/SQL, the following two new explicit conversion functions have been added in Oracle9i to convert other data types to CLOB and BLOB as part of LONG-to-LOB migration: • TO_CLOB() converts LONG, VARCHAR2, and CHAR to CLOB • TO_BLOB() converts LONG RAW and RAW to BLOB TO_CHAR() is enabled to convert a CLOB to a CHAR type. Function and procedure parameter passing: This allows all the user-defined procedures and functions to use CLOBs and BLOBs as actual parameters where VARCHAR2, LONG, RAW, and LONG RAW are formal parameters, and vice versa. Accessing in SQL and PL/SQL built-in functions and operators: A CLOB can be passed to SQL and PL/SQL VARCHAR2 built-in functions, behaving exactly like a VARCHAR2. Or the VARCHAR2 variable can be passed into DBMS_LOB APIs acting like a LOB locator. These details are discussed in detail later in this lesson. For more information, refer to “Migrating from LONGs to LOBs” in Oracle9i Application Developer’s Guide - Large Objects (LOBs).
Oracle9i: Program with PL/SQL 15-18

The DBMS_LOB Package • • • •

Working with LOB often requires the use of the Oracle-supplied package DBMS_LOB. DBMS_LOB provides routines to access and manipulate internal and external LOBs. Oracle9i enables retrieving LOB data directly using SQL, without using any special LOB API. In PL/SQL you can define a VARCHAR2 for a CLOB and a RAW for BLOB.

15-19

Copyright © Oracle Corporation, 2001. All rights reserved.

The DBMS_LOB Package In releases prior to Oracle9i, you need to use the DBMS_LOB package for retrieving data from LOBs. To create the DBMS_LOB package, the dbmslob.sql and prvtlob.plb scripts must be executed as SYS. The catproc.sql script executes the scripts. Then users can be granted appropriate privileges to use the package. The package does not support any concurrency control mechanism for BFILE operations. The user is responsible for locking the row containing the destination internal LOB before calling any subprograms that involve writing to the LOB value. These DBMS_LOB routines do not implicitly lock the row containing the LOB. Two constants are used in the specification of procedures in this package: LOBMAXSIZE and FILE_READONLY. These constants are used in the procedures and functions of DBMS_LOB; for example, you can use them to achieve the maximum possible level of purity so that they can be used in SQL expressions. Using the DBMS_LOB Routines Functions and procedures in this package can be broadly classified into two types: mutators or observers. Mutators can modify LOB values, whereas observers can only read LOB values. • Mutators: APPEND, COPY, ERASE, TRIM, WRITE, FILECLOSE, FILECLOSEALL, and FILEOPEN • Observers: COMPARE, FILEGETNAME, INSTR, GETLENGTH, READ, SUBSTR, FILEEXISTS, and FILEISOPEN
Oracle9i: Program with PL/SQL 15-19

The DBMS_LOB Package • • •

Modify LOB values: APPEND, COPY, ERASE, TRIM, WRITE, LOADFROMFILE Read or examine LOB values: GETLENGTH, INSTR, READ, SUBSTR Specific to BFILEs: FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME, FILEISOPEN, FILEOPEN

15-20

Copyright © Oracle Corporation, 2001. All rights reserved.

The DBMS_LOB Package (continued)
APPEND COPY ERASE LOADFROMFILE TRIM WRITE GETLENGTH INSTR READ SUBSTR FILECLOSE FILECLOSEALL FILEEXISTS FILEGETNAME FILEISOPEN FILEOPEN Append the contents of the source LOB to the destination LOB Copy all or part of the source LOB to the destination LOB Erase all or part of a LOB Load BFILE data into an internal LOB Trim the LOB value to a specified shorter length Write data to the LOB from a specified offset Get the length of the LOB value Return the matching position of the nth occurrence of the pattern in the LOB Read data from the LOB starting at the specified offset Return part of the LOB value starting at the specified offset Close the file Close all previously opened files Check if the file exists on the server Get the directory alias and file name Check if the file was opened using the input BFILE locators Open a file

Oracle9i: Program with PL/SQL 15-20

The DBMS_LOB Package • • •

NULL parameters get NULL returns. Offsets:
– BLOB, BFILE: Measured in bytes – CLOB, NCLOB: Measured in characters

There are no negative values for parameters.

15-21

Copyright © Oracle Corporation, 2001. All rights reserved.

Using the DBMS_LOB Routines All functions in the DBMS_LOB package return NULL if any input parameters are NULL . All mutator procedures in the DBMS_LOB package raise an exception if the destination LOB /BFILE is input as NULL. Only positive, absolute offsets are allowed. They represent the number of bytes or characters from the beginning of LOB data from which to start the operation. Negative offsets and ranges observed in SQL string functions and operators are not allowed. Corresponding exceptions are raised upon violation. The default value for an offset is 1, which indicates the first byte or character in the LOB value. Similarly, only natural number values are allowed for the amount (BUFSIZ) parameter. Negative values are not allowed.

Instructor Note The offset indicates the starting point when reading from or writing to a large object. If the LOB is a CLOB or NCLOB, this is the nth character; if it is a BLOB or BFILE, this is the nth byte.

Oracle9i: Program with PL/SQL 15-21

DBMS_LOB.READ and DBMS_LOB.WRITE

PROCEDURE READ ( lobsrc IN BFILE|BLOB|CLOB , amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW|VARCHAR2 ) PROCEDURE WRITE ( lobdst IN OUT BLOB|CLOB, amount IN OUT BINARY_INTEGER, offset IN INTEGER := 1, buffer IN RAW|VARCHAR2 ) -- RAW for BLOB

15-22

Copyright © Oracle Corporation, 2001. All rights reserved.

DBMS_LOB.READ Call the READ procedure to read and return piecewise a specified AMOUNT of data from a given LOB, starting from OFFSET. An exception is raised when no more data remains to be read from the source LOB. The value returned in AMOUNT will be less than the one specified, if the end of the LOB is reached before the specified number of bytes or characters could be read. In the case of CLOBs, the character set of data in BUFFER is the same as that in the LOB. PL/SQL allows a maximum length of 32767 for RAW and VARCHAR2 parameters. Make sure the allocated system resources are adequate to support these buffer sizes for the given number of user sessions. Otherwise, the Oracle server raises the appropriate memory exceptions. Note: BLOB and BFILE return RAW; the others return VARCHAR2. DBMS_LOB.WRITE Call the WRITE procedure to write piecewise a specified AMOUNT of data into a given LOB, from the user-specified BUFFER, starting from an absolute OFFSET from the beginning of the LOB value. Make sure (especially with multibyte characters) that the amount in bytes corresponds to the amount of buffer data. WRITE has no means of checking whether they match, and will write AMOUNT bytes of the buffer contents into the LOB. Instructor Note These two procedures are given as examples of the routines used from DBMS_LOB. Depending on the audience, you can go through more of the routines using ALL_SOURCE: SELECT text FROM ALL_SOURCE WHERE name = 'DBMS_LOB';
Oracle9i: Program with PL/SQL 15-22

Adding LOB Columns to a Table

ALTER TABLE employees ADD (resume CLOB, picture BLOB);

15-23

Copyright © Oracle Corporation, 2001. All rights reserved.

Adding LOB Columns to a Table LOB columns are defined by way of SQL data definition language (DDL), as in the ALTER TABLE statement in the slide. The contents of a LOB column is stored in the LOB segment, whereas the column in the table contains only a reference to that specific storage area, called the LOB locator. In PL/SQL you can define a variable of type LOB, which contains only the value of the LOB locator.

Oracle9i: Program with PL/SQL 15-23

Populating LOB Columns
Insert a row into a table with LOB columns:
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, resume, picture) VALUES (405, 'Marvin', 'Ellis', 'MELLIS', SYSDATE, 'AD_ASST', 4000, EMPTY_CLOB(),NULL);

Initialize a LOB column using the EMPTY_BLOB() function:
UPDATE employees SET resume = 'Date of Birth: 8 February 1951', picture = EMPTY_BLOB() WHERE employee_id = 405;

15-24

Copyright © Oracle Corporation, 2001. All rights reserved.

Populating LOB Columns You can insert a value directly into a LOB column by using host variables in SQL or in PL/SQL, 3GL-embedded SQL, or OCI. You can use the special functions EMPTY_BLOB and EMPTY_CLOB in INSERT or UPDATE statements of SQL DML to initialize a NULL or non-NULL internal LOB to empty. These are available as special functions in Oracle SQL DML, and are not part of the DBMS_LOB package. Before you can start writing data to an internal LOB using OCI or the DBMS_LOB package, the LOB column must be made nonnull, that is, it must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value to empty by using the function EMPTY_BLOB in the VALUES clause of an INSERT statement. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB. The result of using the function EMPTY_CLOB() or EMPTY_BLOB() means that the LOB is initialized, but not populated with data. To populate the LOB column, you can use an update statement. You can use an INSERT statement to insert a new row and populate the LOB column at the same time. When you create a LOB instance, the Oracle server creates and places a locator to the out-of-line LOB value in the LOB column of a particular row in the table. SQL, OCI, and other programmatic interfaces operate on LOBs through these locators.

Oracle9i: Program with PL/SQL 15-24

Populating LOB Columns (continued) The EMPTY_B/CLOB() function can be used as a DEFAULT column constraint, as in the example below. This initializes the LOB columns with locators. CREATE TABLE emp_hiredata (employee_id NUMBER(6), first_name VARCHAR2(20), last_name VARCHAR2(25), resume CLOB DEFAULT EMPTY_CLOB(), picture BLOB DEFAULT EMPTY_BLOB());

Oracle9i: Program with PL/SQL 15-25

Updating LOB by Using SQL

UPDATE CLOB column
UPDATE employees SET resume = 'Date of Birth: 1 June 1956' WHERE employee_id = 170;

15-26

Copyright © Oracle Corporation, 2001. All rights reserved.

Updating LOB by Using SQL You can update a LOB column by setting it to another LOB value, to NULL, or by using the empty function appropriate for the LOB data type (EMPTY_CLOB() or EMPTY_BLOB()). You can update the LOB using a bind variable in embedded SQL, the value of which may be NULL, empty, or populated. When you set one LOB equal to another, a new copy of the LOB value is created. These actions do not require a SELECT FOR UPDATE statement. You must lock the row prior to the update only when updating a piece of the LOB.

Oracle9i: Program with PL/SQL 15-26

Updating LOB by Using DBMS_LOB in PL/SQL
DECLARE lobloc CLOB; -- serves as the LOB locator text VARCHAR2(32767):='Resigned: 5 August 2000'; amount NUMBER ; -- amount to be written offset INTEGER; -- where to start writing BEGIN SELECT resume INTO lobloc FROM employees WHERE employee_id = 405 FOR UPDATE; offset := DBMS_LOB.GETLENGTH(lobloc) + 2; amount := length(text); DBMS_LOB.WRITE (lobloc, amount, offset, text ); text := ' Resigned: 30 September 2000'; SELECT resume INTO lobloc FROM employees WHERE employee_id = 170 FOR UPDATE; amount := length(text); DBMS_LOB.WRITEAPPEND(lobloc, amount, text); COMMIT; END;
15-27 Copyright © Oracle Corporation, 2001. All rights reserved.

Updating LOB by Using DBMS_LOB in PL/SQL In the example in the slide, the LOBLOC variable serves as the LOB locator, and the AMOUNT variable is set to the length of the text you want to add. The SELECT FOR UPDATE statement locks the row and returns the LOB locator for the RESUME LOB column. Finally, the PL/SQL package procedure WRITE is called to write the text into the LOB value at the specified offset. WRITEAPPEND appends to the existing LOB value. The example shows how to fetch a CLOB column in releases before Oracle9i. In those releases, it was not possible to fetch a CLOB column directly into a character column. The column value needed to be bound to a LOB locator, which is accessed by the DBMS_LOB package. An example later in this lesson shows that you can directly fetch a CLOB column by binding it to a character variable. Note: In versions prior to Oracle9i, Oracle did not allow LOBs in the WHERE clause of UPDATE and SELECT. Now SQL functions of LOBs are allowed in predicates of WHERE. An example is shown later in this lesson.

Instructor Note EMPTY_B/CLOB() is not the same as NULL, and an IS NULL test on a LOB initialized EMPTY returns FALSE.
Oracle9i: Program with PL/SQL 15-27

Selecting CLOB Values by Using SQL

SELECT employee_id, last_name , resume -- CLOB FROM employees WHERE employee_id IN (405, 170);

15-28

Copyright © Oracle Corporation, 2001. All rights reserved.

Selecting CLOB Values by Using SQL It is possible to see the data in a CLOB column by using a SELECT statement. It is not possible to see the data in a BLOB or BFILE column by using a SELECT statement in iSQL*Plus. You have to use a tool that can display binary information for a BLOB, as well as the relevant software for a BFILE; for example, you can use Oracle Forms.

Instructor Note Use the iSQL*Plus command SET LONG n to display more data, if the information in the LOB column is truncated.
Oracle9i: Program with PL/SQL 15-28

Selecting CLOB Values by Using DBMS_LOB • •

DBMS_LOB.SUBSTR(lob_column, no_of_chars, starting) DBMS_LOB.INSTR (lob_column, pattern)
SELECT DBMS_LOB.SUBSTR (resume, 5, 18), DBMS_LOB.INSTR (resume,' = ') FROM employees WHERE employee_id IN (170, 405);

15-29

Copyright © Oracle Corporation, 2001. All rights reserved.

Selecting CLOB Values by Using DBMS_LOB DBMS_LOB.SUBSTR Use DBMS_LOB.SUBSTR to display part of a LOB. It is similar in functionality to the SQL function SUBSTR. DBMS_LOB.INSTR Use DBMS_LOB.INSTR to search for information within the LOB. This function returns the numerical position of the information. Note: Starting with Oracle9i, you can also use SQL functions SUBSTR and INSTR to perform the operations shown in the slide.

Instructor Note This works in iSQL*Plus because the LOB column is a CLOB and not a BLOB or BFILE. You can use DBMS_LOB.INSTR to do searches for information in the WHERE clause of a SELECT statement. You may not want do this for performance reasons. Unlike a LONG, where you can only select data and it is not possible to do any form of manipulation or searching, DBMS_LOB.INSTR/SUBSTR highlights a distinct difference between usage of LONGs and that of LOBs.
Oracle9i: Program with PL/SQL 15-29

Selecting CLOB Values in PL/SQL

DECLARE text VARCHAR2(4001); BEGIN SELECT resume INTO text FROM employees WHERE employee_id = 170; DBMS_OUTPUT.PUT_LINE('text is: '|| text); END; /

15-30

Copyright © Oracle Corporation, 2001. All rights reserved.

Selecting CLOB Values in PL/SQL The slide shows the code for accessing CLOB values that can be implicitly converted to VARCHAR2 in Oracle9i. The value of the column RESUME, when selected into a VARCHAR2 variable TEXT, is implicitly converted. In prior releases, to access a CLOB column, first you must to retrieve the CLOB column value into a CLOB variable and specify the amount and offset size. Then you use the DBMS_LOB package to read the selected value. The code using DBMS_LOB is as follows: DECLARE rlob clob; text VARCHAR2(4001); amt number := 4001; offset number := 1; BEGIN SELECT resume INTO rlob FROM employees WHERE employee_id = 170; DBMS_LOB.READ(rlob, amt, offset, text); DBMS_OUTPUT.PUT_LINE('text is: '|| text); END; /

Oracle9i: Program with PL/SQL 15-30

Removing LOBs
Delete a row containing LOBs:
DELETE FROM employees WHERE employee_id = 405;

Disassociate a LOB value from a row:
UPDATE employees SET resume = EMPTY_CLOB() WHERE employee_id = 170;

15-31

Copyright © Oracle Corporation, 2001. All rights reserved.

Removing LOBs A LOB instance can be deleted (destroyed) using appropriate SQL DML statements. The SQL statement DELETE deletes a row and its associated internal LOB value. To preserve the row and destroy only the reference to the LOB, you must update the row, by replacing the LOB column value with NULL or an empty string, or by using the EMPTY_B/CLOB() function. Note: Replacing a column value with NULL and using EMPTY_B/CLOB are not the same. Using NULL sets the value to null, using EMPTY_B/CLOB ensures there is nothing in the column. A LOB is destroyed when the row containing the LOB column is deleted when the table is dropped or truncated, or implicitly when all the LOB data is updated. You must explicitly remove the file associated with a BFILE using operating system commands. To erase part of an internal LOB, you can use DBMS_LOB.ERASE.

Oracle9i: Program with PL/SQL 15-31

Temporary LOBs •

Temporary LOBs:
– – – – – Provide an interface to support creation of LOBs that act like local variables Can be BLOBs, CLOBs, or NCLOBs Are not associated with a specific table Are created using DBMS_LOB.CREATETEMPORARY procedure Use DBMS_LOB routines

• •

The lifetime of a temporary LOB is a session. Temporary LOBs are useful for transforming data in permanent internal LOBs.

15-32

Copyright © Oracle Corporation, 2001. All rights reserved.

Temporary LOBs Temporary LOBs provide an interface to support the creation and deletion of LOBs that act like local variables. Temporary LOBs can be BLOBs, CLOBs, or NCLOBs. Features of temporary LOBs: • • • Data is stored in your temporary tablespace, not in tables. Temporary LOBs are faster than persistent LOBs because they do not generate any redo or rollback information. Temporary LOBs lookup is localized to each user’s own session; only the user who creates a temporary LOB can access it, and all temporary LOBs are deleted at the end of the session in which they were created. You can create a temporary LOB using DBMS_LOB.CREATETEMPORARY.

•

Temporary LOBs are useful when you want to perform some transformational operation on a LOB, for example, changing an image type from GIF to JPEG. A temporary LOB is empty when created and does not support the EMPTY_B/CLOB functions. Use the DBMS_LOB package to use and manipulate temporary LOBs. Instructor Note Temporary LOBs are introduced in Oracle8i. The default lifetime of a temporary LOB is a session. It can also be cleaned up at the end of the call; this is specified at the time of creation.
Oracle9i: Program with PL/SQL 15-32

Creating a Temporary LOB
PL/SQL procedure to create and test a temporary LOB:
CREATE OR REPLACE PROCEDURE IsTempLOBOpen (p_lob_loc IN OUT BLOB, p_retval OUT INTEGER) IS BEGIN -- create a temporary LOB DBMS_LOB.CREATETEMPORARY (p_lob_loc, TRUE); -- see if the LOB is open: returns 1 if open p_retval := DBMS_LOB.ISOPEN (p_lob_loc); DBMS_OUTPUT.PUT_LINE ('The file returned a value ....' || p_retval); -- free the temporary LOB DBMS_LOB.FREETEMPORARY (p_lob_loc); END; /

15-33

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating a Temporary LOB The example in the slide shows a user-defined PL/SQL procedure, IsTempLOBOpen, that creates a temporary LOB. This procedure accepts a LOB locator as input, creates a temporary LOB, opens it, and tests whether the LOB is open. The IsTempLOBOpen procedure uses the procedures and functions from the DBMS_LOB package as follows: • • • The CREATETEMPORARY procedure is used to create the temporary LOB. The ISOPEN function is used to test whether a LOB is open: this function returns the value 1 if the LOB is open. The FREETEMPORARY procedure is used to free the temporary LOB; memory increases incrementally as the number of temporary LOBs grows, and you can reuse temporary LOB space in your session by explicitly freeing temporary LOBs.

Oracle9i: Program with PL/SQL 15-33

Summary

In this lesson, you should have learned how to:

• • •

Identify four built-in types for large objects: BLOB, CLOB, NCLOB, and BFILE Describe how LOBs replace LONG and LONG RAW Describe two storage options for LOBs:
– – The Oracle server (internal LOBs) External host files (external LOBs)

• •
15-34

Use the DBMS_LOB PL/SQL package to provide routines for LOB management Use temporary LOBs in a session

Copyright © Oracle Corporation, 2001. All rights reserved.

Summary There are four LOB data types: • • • • A BLOB is a binary large object. A CLOB is a character large object. A NCLOB stores multibyte national character set data. A BFILE is a large object stored in a binary file outside the database.

LOBs can be stored internally (in the database) or externally (in an operating system file). You can manage LOBs by using the DBMS_LOB package and its procedures. Temporary LOBs provide an interface to support the creation and deletion of LOBs that act like local variables.

Oracle9i: Program with PL/SQL 15-34

Practice 15 Overview

This practice covers the following topics:

• • •

Creating object types, using the new data types CLOB and BLOB Creating a table with LOB data types as columns Using the DBMS_LOB package to populate and interact with the LOB data

15-35

Copyright © Oracle Corporation, 2001. All rights reserved.

Practice 15 Overview In this practice you create a table with both BLOB and CLOB columns. Then, you use the DBMS_LOB package to populate the table and manipulate the data.

Oracle9i: Program with PL/SQL 15-35

Practice 15 1. Create a table called PERSONNEL by executing the script file lab15_1.sql. The table contains the following attributes and data types:
Column Name ID last_name review picture Data type NUMBER VARCHAR2 CLOB BLOB Length 6 35 N/A N/A

2. Insert two rows into the PERSONNEL table, one each for employees 2034 and 2035. Use the empty function for the CLOB, and provide NULL as the value for the BLOB. 3. Examine and execute the script lab15_3.sql. The script creates a table named REVIEW_TABLE. This table contains annual review information for each employee. The script also contains two statements to insert review details for two employees. 4. Update the PERSONNEL table. a. Populate the CLOB for the first row, using the following subquery in a SQL UPDATE statement: SELECT ann_review FROM review_table WHERE employee_id = 2034; b. Populate the CLOB for the second row, using PL/SQL and the DBMS_LOB package. Use the following SELECT statement to provide a value. SELECT ann_review FROM review_table WHERE employee_id = 2035;

Oracle9i: Program with PL/SQL 15-36

Practice 15 (continued) If you have time 5. Create a procedure that adds a locator to a binary file into the PICTURE column of the COUNTRIES table. The binary file is a picture of the country. The image files are named after the country IDs. You need to load an image file locator into all rows in Europe region (REGION_ID = 1) in the COUNTRIES table. The DIRECTORY object name that stores a pointer to the location of the binary files is called COUNTRY_PIC. This object is already created for you. a. Use the command below to add the image column to the COUNTRIES table (or use lab15_5_add.sql) ALTER TABLE countries ADD (picture BFILE); b. Create a PL/SQL procedure called load_country_image that reads a locator into your picture column. Have the program test to see if the file exists, using the function DBMS_LOB.FILEEXISTS. If the file is not existing, your procedure should display a message that the file can not be opened. Have your program report information about the load to the screen. Invoke the procedure by passing the name of the directory object COUNTRY_PIC as parameter. Note that you should pass the directory object in single quotation marks.

c.

Sample output follows:

Instructor Note The class needs to add a BFILE column to the COUNTRIES table to complete question 5 of Practice 15. The script is stored in lab\lab15_5_add.sql. This practice question 5 requires the instructor to run the file lab\lab15_5_setup.sql. The script creates the DIRECTORY objects and grants READ permissions to these objects. Edit the file and fill in the database connection for the classroom. Also, fill in the file location on the server to identify where the COUNTRY_PIC and LOG_FILES are located. The steps in this script must be followed before the students can complete question 5 of Practice 15. After running the above script, you can verify that the directory alias is created by querying the ALL_DIRECTORIES data dictionary view as follows: SELECT directory_name, directory_path FROM all_directories;

Oracle9i: Program with PL/SQL 15-37

Oracle9i: Program with PL/SQL 15-38

Creating Database Triggers

Copyright © Oracle Corporation, 2001. All rights reserved.

Schedule:

Timing
60 minutes 60 minutes 120 minutes

Topic
Lecture Practice Total

Objectives

After completing this lesson, you should be able to do the following:

• • • • •

Describe different types of triggers Describe database triggers and their use Create database triggers Describe database trigger firing rules Remove database triggers

16-2

Copyright © Oracle Corporation, 2001. All rights reserved.

Lesson Aim In this lesson, you learn how to create and use database triggers.

Oracle9i: Program with PL/SQL 16-2

Types of Triggers
A trigger:

• • •

Is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database Executes implicitly whenever a particular event takes place Can be either:
– Application trigger: Fires whenever an event occurs with a particular application – Database trigger: Fires whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database

16-3

Copyright © Oracle Corporation, 2001. All rights reserved.

Types of Triggers Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application. An example of an application that uses triggers extensively is one developed with Oracle Forms Developer. Database triggers execute implicitly when a data event such as DML on a table (an INSERT, UPDATE, or DELETE triggering statement), an INSTEAD OF trigger on a view, or data definition language (DDL) statements such as CREATE and ALTER are issued, no matter which user is connected or which application is used. Database triggers also execute implicitly when some user actions or database system actions occur, for example, when a user logs on, or the DBA shut downs the database. Note: Database triggers can be defined on tables and on views. If a DML operation is issued on a view, the INSTEAD OF trigger defines what actions take place. If these actions include DML operations on tables, then any triggers on the base tables are fired. Database triggers can be system triggers on a database or a schema. With a database, triggers fire for each event for all users; with a schema, triggers fire for each event for that specific user. This course covers creating database triggers. Creating database triggers based on system events is discussed in the lesson “More Trigger Concepts.” Instructor Note Database administration type triggers have been introduced in Oracle8i. Some of the triggers that can be created include user actions of logging on and off, and database shutdown and startup. These types of triggers are covered in a later lesson.
Oracle9i: Program with PL/SQL 16-3

Guidelines for Designing Triggers •
Design triggers to:
– Perform related actions – Centralize global operations

•

Do not design triggers:
– Where functionality is already built into the Oracle server – That duplicate other triggers

• •

Create stored procedures and invoke them in a trigger, if the PL/SQL code is very lengthy. The excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications.

16-4

Copyright © Oracle Corporation, 2001. All rights reserved.

Guidelines for Designing Triggers • Use triggers to guarantee that when a specific operation is performed, related actions are performed. • Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement. • Do not define triggers to duplicate or replace the functionality already built into the Oracle database. For example do not define triggers to implement integrity rules that can be done by using declarative constraints. An easy way to remember the design order for a business rule is to: – Use built-in constraints in the Oracle server such as, primary key, foreign key and so on – Develop a database trigger or develop an application such as a servlet or Enterprise JavaBean (EJB) on your middle tier – Use a presentation interface such as Oracle Forms, dynamic HTML, Java ServerPages (JSP) and so on, if you cannot develop your business rule as mentioned above, which might be a presentation rule. • The excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications. Only use triggers when necessary, and beware of recursive and cascading effects. • If the logic for the trigger is very lengthy, create stored procedures with the logic and invoke them in the trigger body. • Note that database triggers fire for every user each time the event occurs on which the trigger is created.
Oracle9i: Program with PL/SQL 16-4

Database Trigger: Example
Application
INSERT INTO EMPLOYEES . . .;

EMPLOYEES table

CHECK_SAL trigger

…

16-5

Copyright © Oracle Corporation, 2001. All rights reserved.

Example of a Database Trigger In this example, the database trigger CHECK_SAL checks salary values whenever any application tries to insert a row into the EMPLOYEES table. Values that are out of range according to the job category can be rejected, or can be allowed and recorded in an audit table.

Instructor Note (for page 16-4) • • Recursive trigger: This is a trigger that contains a DML operation changing the very same table. Cascading trigger: The action of one trigger cascades to another trigger, causing this second trigger to fire. The Oracle server allows up to 32 triggers to cascade at any one time. However, the number of cascading triggers can be limited by changing the value of the OPEN_CURSORS database initialization parameter, which is set to 50 by default.
Oracle9i: Program with PL/SQL 16-5

Creating DML Triggers

A triggering statement contains:

• • • • • •

Trigger timing
– For table: BEFORE, AFTER – For view: INSTEAD OF

Triggering event: INSERT, UPDATE, or DELETE Table name: On table, view Trigger type: Row or statement WHEN clause: Restricting condition Trigger body: PL/SQL block

16-6

Copyright © Oracle Corporation, 2001. All rights reserved.

Database Trigger Before coding the trigger body, decide on the values of the components of the trigger: the trigger timing, the triggering event, and the trigger type.
Part Trigger timing Description When the trigger fires in relation to the triggering event Possible Values BEFORE AFTER INSTEAD OF

Triggering event

Which data manipulation operation on the INSERT UPDATE table or view causes the trigger to fire DELETE How many times the trigger body executes What action the trigger performs Statement Row Complete PL/SQL block

Trigger type Trigger body

If multiple triggers are defined for a table, be aware that the order in which multiple triggers of the same type fire is arbitrary. To ensure that triggers of the same type are fired in a particular order, consolidate the triggers into one trigger that calls separate procedures in the desired order.

Oracle9i: Program with PL/SQL 16-6

DML Trigger Components

Trigger timing: When should the trigger fire? • BEFORE: Execute the trigger body before the triggering DML event on a table. • AFTER: Execute the trigger body after the triggering DML event on a table. • INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.

16-7

Copyright © Oracle Corporation, 2001. All rights reserved.

BEFORE Triggers This type of trigger is frequently used in the following situations: • To determine whether that triggering statement should be allowed to complete. (This situation enables you to eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the triggering action.) To derive column values before completing a triggering INSERT or UPDATE statement.

•

• To initialize global variables or flags, and to validate complex business rules. AFTER Triggers This type of trigger is frequently used in the following situations: • To complete the triggering statement before executing the triggering action. • To perform different actions on the same triggering statement if a BEFORE trigger is already present. INSTEAD OF Triggers This type of trigger is used to provide a transparent way of modifying views that cannot be modified directly through SQL DML statements because the view is not inherently modifiable. You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEAD OF trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.

Oracle9i: Program with PL/SQL 16-7

DML Trigger Components

Triggering user event: Which DML statement causes the trigger to execute? You can use any of the following: • INSERT

• •

UPDATE DELETE

16-8

Copyright © Oracle Corporation, 2001. All rights reserved.

The Triggering Event The triggering event or statement can be an INSERT, UPDATE, or DELETE statement on a table. • When the triggering event is an UPDATE statement, you can include a column list to identify which columns must be changed to fire the trigger. You cannot specify a column list for an INSERT or for a DELETE statement, because they always affect entire rows. . . . UPDATE OF salary . . . • The triggering event can contain one, two, or all three of these DML operations. . . . INSERT or UPDATE or DELETE . . . INSERT or UPDATE OF job_id . . .

Oracle9i: Program with PL/SQL 16-8

DML Trigger Components

Trigger type: Should the trigger body execute for each row the statement affects or only once?

• •

Statement: The trigger body executes once for the triggering event. This is the default. A statement trigger fires once, even if no rows are affected at all. Row: The trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows.

16-9

Copyright © Oracle Corporation, 2001. All rights reserved.

Statement Triggers and Row Triggers You can specify that the trigger will be executed once for every row affected by the triggering statement (such as a multiple row UPDATE) or once for the triggering statement, no matter how many rows it affects. Statement Trigger A statement trigger is fired once on behalf of the triggering event, even if no rows are affected at all. Statement triggers are useful if the trigger action does not depend on the data from rows that are affected or on data provided by the triggering event itself: for example, a trigger that performs a complex security check on the current user. Row Trigger A row trigger fires each time the table is affected by the triggering event. If the triggering event affects no rows, a row trigger is not executed. Row triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself.

Oracle9i: Program with PL/SQL 16-9

DML Trigger Components

Trigger body: What action should the trigger perform? The trigger body is a PL/SQL block or a call to a procedure.

16-10

Copyright © Oracle Corporation, 2001. All rights reserved.

Trigger Body The trigger action defines what needs to be done when the triggering event is issued. The PL/SQL block can contain SQL and PL/SQL statements, and can define PL/SQL constructs such as variables, cursors, exceptions, and so on. You can also call a PL/SQL procedure or a Java procedure. Additionally, row triggers use correlation names to access the old and new column values of the row being processed by the trigger. Note: The size of a trigger cannot be more than 32 K.

Instructor Note The Java procedure needs to be encapsulated in a PL/SQL wrapper. To call a Java procedure, you use the CALL statement. The syntax for the CALL statement to call a PL/SQL procedure is shown in a later lesson. Before creating any triggers, run the catproc.sql script while connected as SYS. This script automatically runs all of the scripts required for, or used within, the procedural extensions to the Oracle server.
Oracle9i: Program with PL/SQL 16-10

Firing Sequence
Use the following firing sequence for a trigger on a table, when a single row is manipulated: DML statement
INSERT INTO departments (department_id, department_name, location_id) VALUES (400, 'CONSULTING', 2400);

Triggering action

BEFORE statement trigger

…

BEFORE row trigger AFTER row trigger AFTER statement trigger

16-11

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating Row or Statement Triggers Create a statement trigger or a row trigger based on the requirement that the trigger must fire once for each row affected by the triggering statement, or just once for the triggering statement, regardless of the number of rows affected. When the triggering data manipulation statement affects a single row, both the statement trigger and the row trigger fire exactly once. Example This SQL statement does not differentiate statement triggers from row triggers, because exactly one row is inserted into the table using this syntax.

Oracle9i: Program with PL/SQL 16-11

Firing Sequence
Use the following firing sequence for a trigger on a table, when many rows are manipulated:
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 30;

BEFORE statement trigger BEFORE row trigger AFTER row trigger

...

BEFORE row trigger AFTER row trigger ... AFTER statement trigger

16-12

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating Row or Statement Triggers (continued) When the triggering data manipulation statement affects many rows, the statement trigger fires exactly once, and the row trigger fires once for every row affected by the statement. Example The SQL statement in the slide above causes a row-level trigger to fire a number of times equal to the number of rows that satisfy the WHERE clause, that is, the number of employees reporting to department 30.

Oracle9i: Program with PL/SQL 16-12

Syntax for Creating DML Statement Triggers
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name trigger_body

Note: Trigger names must be unique with respect to other triggers in the same schema.

16-13

Copyright © Oracle Corporation, 2001. All rights reserved.

Syntax for Creating a Statement Trigger trigger name timing Is the name of the trigger Indicates the time when the trigger fires in relation to the triggering event: BEFORE AFTER Identifies the data manipulation operation that causes the trigger to fire: INSERT UPDATE [OF column] DELETE Indicates the table associated with the trigger Is the trigger body that defines the action performed by the trigger, beginning with either DECLARE or BEGIN, ending with END, or a call to a procedure

event

table/view_name trigger body

Trigger names must be unique with respect to other triggers in the same schema. Trigger names do not need to be unique with respect to other schema objects, such as tables, views, and procedures. Using column names along with the UPDATE clause in the trigger improves performance, because the trigger fires only when that particular column is updated and thus avoids unintended firing when any other column is updated.
Oracle9i: Program with PL/SQL 16-13

Creating DML Statement Triggers
Example:
CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON employees BEGIN IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN RAISE_APPLICATION_ERROR (-20500,'You may insert into EMPLOYEES table only during business hours.'); END IF; END; /

16-14

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating DML Statement Triggers You can create a BEFORE statement trigger in order to prevent the triggering operation from succeeding if a certain condition is violated. For example, create a trigger to restrict inserts into the EMPLOYEES table to certain business hours, Monday through Friday. If a user attempts to insert a row into the EMPLOYEES table on Saturday, the user sees the message, the trigger fails, and the triggering statement is rolled back. Remember that the RAISE_APPLICATION_ERROR is a server-side built-in procedure that returns an error to the user and causes the PL/SQL block to fail. When a database trigger fails, the triggering statement is automatically rolled back by the Oracle server.

Instructor Note The time specified in the trigger in the slide is based on the database server system time. Hence, if you are in a different time zone from the database server, your DML may not work even if your system clock is within the time specified in the code.
Oracle9i: Program with PL/SQL 16-14

Testing SECURE_EMP

INSERT INTO employees (employee_id, last_name, first_name, email, hire_date, job_id, salary, department_id) VALUES (300, 'Smith', 'Rob', 'RSMITH', SYSDATE, 'IT_PROG', 4500, 60);

16-15

Copyright © Oracle Corporation, 2001. All rights reserved.

Example Insert a row into the EMPLOYEES table during nonbusiness hours. When the date and time are out of the business timings specified in the trigger, you get the error message as shown in the slide.

Instructor Note Note that the row might be inserted if you are in a different timezone from the database server. The trigger fires even if your system clock is within these business hours.

Oracle9i: Program with PL/SQL 16-15

Using Conditional Predicates
CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT OR UPDATE OR DELETE ON employees BEGIN IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18') THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502,'You may delete from EMPLOYEES table only during business hours.'); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20500,'You may insert into EMPLOYEES table only during business hours.'); ELSIF UPDATING ('SALARY') THEN RAISE_APPLICATION_ERROR (-20503,'You may update SALARY only during business hours.'); ELSE RAISE_APPLICATION_ERROR (-20504,'You may update EMPLOYEES table only during normal hours.'); END IF; END IF; END;
Copyright © Oracle Corporation, 2001. All rights reserved.

16-16

Combining Triggering Events You can combine several triggering events into one by taking advantage of the special conditional predicates INSERTING, UPDATING, and DELETING within the trigger body. Example Create one trigger to restrict all data manipulation events on the EMPLOYEES table to certain business hours, Monday through Friday.

Oracle9i: Program with PL/SQL 16-16

Creating a DML Row Trigger
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new] FOR EACH ROW [WHEN (condition)] trigger_body

16-17

Copyright © Oracle Corporation, 2001. All rights reserved.

Syntax for Creating a Row Trigger trigger_name timing Is the name of the trigger Indicates the time when the trigger fires in relation to the triggering event: BEFORE AFTER INSTEAD OF Identifies the data manipulation operation that causes the trigger to fire: INSERT UPDATE [OF column] DELETE Indicates the table associated with the trigger Specifies correlation names for the old and new values of the current row (The default values are OLD and NEW) Designates that the trigger is a row trigger Specifies the trigger restriction; (This conditional predicate must be enclosed in parenthesis and is evaluated for each row to determine whether or not the trigger body is executed.) Is the trigger body that defines the action performed by the trigger, beginning with either DECLARE or BEGIN, ending with END, or a call to a procedure
Oracle9i: Program with PL/SQL 16-17

event

table_name REFERENCING FOR EACH ROW WHEN

trigger body

Creating DML Row Triggers

CREATE OR REPLACE TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP')) AND :NEW.salary > 15000 THEN RAISE_APPLICATION_ERROR (-20202,'Employee cannot earn this amount'); END IF; END; /

16-18

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating a Row Trigger You can create a BEFORE row trigger in order to prevent the triggering operation from succeeding if a certain condition is violated. Create a trigger to allow only certain employees to be able to earn a salary of more than 15,000. If a user attempts to do this, the trigger raises an error. UPDATE employees SET salary = 15500 WHERE last_name = 'Russell';

Instructor Note Alternatively, the value 15,000 in the program could be a value in a table that can be retrieved into the program, or a global variable.
Oracle9i: Program with PL/SQL 16-18

Using OLD and NEW Qualifiers
CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_emp_table (user_name, timestamp, id, old_last_name, new_last_name, old_title, new_title, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary ); END; /

16-19

Copyright © Oracle Corporation, 2001. All rights reserved.

Using OLD and NEW Qualifiers Within a ROW trigger, reference the value of a column before and after the data change by prefixing it with the OLD and NEW qualifier.
Data Operation INSERT UPDATE DELETE Old Value NULL Value before update Value before delete New Value Inserted value Value after update NULL

• • •

The OLD and NEW qualifiers are available only in ROW triggers. Prefix these qualifiers with a colon (:) in every SQL and PL/SQL statement. There is no colon (:) prefix if the qualifiers are referenced in the WHEN restricting condition.

Note: Row triggers can decrease the performance if you do a lot of updates on larger tables. Instructor Note If you run the above code and get a compilation error, check whether you have a table called AUDIT_EMP_TABLE; if not, run 16_addtabs.sql to create this table. Point out to the students that the OLD and NEW are host variable created implicitly. The same rules apply to these host variables in PL/SQL bodies as in anonymous PL/SQL blocks.
Oracle9i: Program with PL/SQL 16-19

Using OLD and NEW Qualifiers: Example Using Audit_Emp_Table
INSERT INTO employees (employee_id, last_name, job_id, salary, ...) VALUES (999, 'Temp emp', 'SA_REP', 1000, ...); UPDATE employees SET salary = 2000, last_name = 'Smith' WHERE employee_id = 999;

SELECT user_name, timestamp, ... FROM audit_emp_table

16-20

Copyright © Oracle Corporation, 2001. All rights reserved.

Using OLD and NEW Qualifiers: Example Using AUDIT_EMP_TABLE Create a trigger on the EMPLOYEES table to add rows to a user table, AUDIT_EMP_TABLE, logging a user’s activity against the EMPLOYEES table. The trigger records the values of several columns both before and after the data changes by using the OLD and NEW qualifiers with the respective column name. There is additional column COMMENTS in the AUDIT_EMP_TABLE that is not shown in this slide.

Oracle9i: Program with PL/SQL 16-20

Restricting a Row Trigger
CREATE OR REPLACE TRIGGER derive_commission_pct BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.job_id = 'SA_REP') BEGIN IF INSERTING THEN :NEW.commission_pct := 0; ELSIF :OLD.commission_pct IS NULL THEN :NEW.commission_pct := 0; ELSE :NEW.commission_pct := :OLD.commission_pct + 0.05; END IF; END; /

16-21

Copyright © Oracle Corporation, 2001. All rights reserved.

Example To restrict the trigger action to those rows that satisfy a certain condition, provide a WHEN clause. Create a trigger on the EMPLOYEES table to calculate an employee’s commission when a row is added to the EMPLOYEES table, or when an employee’s salary is modified. The NEW qualifier cannot be prefixed with a colon in the WHEN clause because the WHEN clause is outside the PL/SQL blocks. Instructor Note To assign values to columns using the NEW qualifier, create BEFORE ROW triggers. If you attempt to create an AFTER ROW trigger for the above code, you get a compilation error: CREATE OR REPLACE TRIGGER derive_commission_pct AFTER INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.job_id = 'SA_REP') BEGIN IF INSERTING THEN :NEW.commission_pct := 0; ELSIF :OLD.commission_pct IS NULL THEN :NEW.commission_pct := 0; ELSE :NEW.commission_pct := :OLD.commission_pct * (:NEW.salary/:OLD.salary); END IF; END; / CREATE OR REPLACE TRIGGER derive_commission_pct* ERROR at line 1: ORA-04084: cannot change NEW values for this trigger type
Oracle9i: Program with PL/SQL 16-21

INSTEAD OF Triggers
Application
INSERT INTO my_view . . .;

INSTEAD OF Trigger

INSERT TABLE1

MY_VIEW

UPDATE TABLE2

16-22

Copyright © Oracle Corporation, 2001. All rights reserved.

INSTEAD OF Triggers Use INSTEAD OF triggers to modify data in which the DML statement has been issued against an inherently nonupdatable view. These triggers are called INSTEAD OF triggers because, unlike other triggers, the Oracle server fires the trigger instead of executing the triggering statement. This trigger is used to perform an INSERT, UPDATE, or DELETE operation directly on the underlying tables. You can write INSERT, UPDATE, or DELETE statements against a view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place. Why Use INSTEAD OF Triggers? A view cannot be modified by normal DML statements if the view query contains set operators, group functions, clauses such as GROUP BY, CONNECT BY, START, the DISTINCT operator, or joins. For example, if a view consists of more than one table, an insert to the view may entail an insertion into one table and an update to another. So, you write an INSTEAD OF trigger that fires when you write an insert against the view. Instead of the original insertion, the trigger body executes, which results in an insertion of data into one table and an update to another table. Note: If a view is inherently updateable and has INSTEAD OF triggers, the triggers take precedence. INSTEAD OF triggers are row triggers. The CHECK option for views is not enforced when insertions or updates to the view are performed by using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check. Instructor Note You can update a subset of join views.
Oracle9i: Program with PL/SQL 16-22

Creating an INSTEAD OF Trigger
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF event1 [OR event2 OR event3] ON view_name [REFERENCING OLD AS old | NEW AS new] [FOR EACH ROW] trigger_body

16-23

Copyright © Oracle Corporation, 2001. All rights reserved.

Syntax for Creating an INSTEAD OF Trigger trigger_name INSTEAD OF event Is the name of the trigger. Indicates that the trigger belongs to a view Identifies the data manipulation operation that causes the trigger to fire: INSERT UPDATE [OF column] DELETE Indicates the view associated with trigger Specifies correlation names for the old and new values of the current row (The defaults are OLD and NEW) Designates the trigger to be a row trigger; INSTEAD OF triggers can only be row triggers: if this is omitted, the trigger is still defined as a row trigger. Is the trigger body that defines the action performed by the trigger, beginning with either DECLARE or BEGIN, and ending with END or a call to a procedure

view_name REFERENCING FOR EACH ROW trigger body

Note: INSTEAD OF triggers can be written only for views. BEFORE and AFTER options are not valid.
Oracle9i: Program with PL/SQL 16-23

Creating an INSTEAD OF Trigger Example: The following example creates two new tables, NEW_EMPS and NEW_DEPTS, based on the EMPLOYEES and DEPARTMENTS tables respectively. It also creates an EMP_DETAILS view from the EMPLOYEES and DEPARTMENTS tables. The example also creates an INSTEAD OF trigger, NEW_EMP_DEPT. When a row is inserted into the EMP_DETAILS view, instead of inserting the row directly into the view, rows are added into the NEW_EMPS and NEW_DEPTS tables, based on the data in the INSERT statement. Similarly, when a row is modified or deleted through the EMP_DETAILS view, corresponding rows in the NEW_EMPS and NEW_DEPTS tables are affected. CREATE TABLE new_emps AS SELECT employee_id, last_name, salary, department_id, email, job_id, hire_date FROM employees; CREATE TABLE new_depts AS SELECT d.department_id, d.department_name, d.location_id, sum(e.salary) tot_dept_sal FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_id, d.department_name, d.location_id; CREATE VIEW emp_details AS SELECT e.employee_id, e.last_name, e.salary, e.department_id, e.email, e.job_id, d.department_name, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id; CREATE OR REPLACE TRIGGER new_emp_dept INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_details FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO new_emps VALUES (:NEW.employee_id, :NEW.last_name, :NEW.salary, :NEW.department_id, :NEW.email, :New.job_id, SYSDATE); UPDATE new_depts SET tot_dept_sal = tot_dept_sal + :NEW.salary WHERE department_id = :NEW.department_id; ELSIF DELETING THEN DELETE FROM new_emps WHERE employee_id = :OLD.employee_id; UPDATE new_depts SET tot_dept_sal = tot_dept_sal - :OLD.salary WHERE department_id = :OLD.department_id;

Oracle9i: Program with PL/SQL 16-24

Creating an INSTEAD OF Trigger (continued) Example: ELSIF UPDATING ('salary') THEN UPDATE new_emps SET salary = :NEW.salary WHERE employee_id = :OLD.employee_id; UPDATE new_depts SET tot_dept_sal = tot_dept_sal + (:NEW.salary - :OLD.salary) WHERE department_id = :OLD.department_id; ELSIF UPDATING ('department_id') THEN UPDATE new_emps SET department_id = :NEW.department_id WHERE employee_id = :OLD.employee_id; UPDATE new_depts SET tot_dept_sal = tot_dept_sal - :OLD.salary WHERE department_id = :OLD.department_id; UPDATE new_depts SET tot_dept_sal = tot_dept_sal + :NEW.salary WHERE department_id = :NEW.department_id; END IF; END; / Note: This example is explained in the next page by using graphics.

Instructor Note Demonstration: 16_inst_of.sql Purpose: Creates the two new tables, a view and the trigger. The script shows the data in the view and the tables. A direct INSERT into the view implicitly executes the trigger, which INSERTs into the NEW_EMPS table and UPDATEs the NEW_DEPTS table. The trigger is not complete. It is programmed to handle the INSERT and UPDATE of the SALARY or DEPARTMENT_ID, and a DELETE. Any other form of UPDATE is not programmed.
Oracle9i: Program with PL/SQL 16-25

Creating an INSTEAD OF Trigger
INSERT into EMP_DETAILS that is based on EMPLOYEES and DEPARTMENTS tables

1

INSERT INTO emp_details(employee_id, ... ) VALUES(9001,'ABBOTT',3000,10,'abbott.mail.com','HR_MAN');

INSTEAD OF INSERT into EMP_DETAILS

…

16-26

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating an INSTEAD OF Trigger You can create an INSTEAD OF trigger in order to maintain the base tables on which a view is based. Assume that an employee name will be inserted using the view EMP_DETAILS that is created based on the EMPLOYEES and DEPARTMENTS tables. Create a trigger that results in the appropriate INSERT and UPDATE to the base tables. The slide in the next page explains how an INSTEAD OF TRIGGER behaves in this situation.

Oracle9i: Program with PL/SQL 16-26

Creating an INSTEAD OF Trigger
INSERT into EMP_DETAILS that is based on EMPLOYEES and DEPARTMENTS tables

1

INSERT INTO emp_details(employee_id, ... ) VALUES(9001,'ABBOTT',3000,10,'abbott.mail.com','HR_MAN');

INSTEAD OF INSERT into EMP_DETAILS

… 2
INSERT into NEW_EMPS

3

UPDATE NEW_DEPTS

…

…

16-27

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating an INSTEAD OF Trigger Because of the INSTEAD OF TRIGGER on the view EMP_DETAILS, instead of inserting the new employee record into the EMPLOYEES table: • • A row is inserted into the NEW_EMPS table. The TOTAL_DEPT_SAL column of the NEW_DEPTS table is updated. The salary value supplied for the new employee is added to the existing total salary of the department to which the new employee has been assigned.

Oracle9i: Program with PL/SQL 16-27

Differentiating Between Database Triggers and Stored Procedures
Triggers Defined with CREATE TRIGGER Data dictionary contains source code in USER_TRIGGERS Implicitly invoked COMMIT, SAVEPOINT, and ROLLBACK are not allowed Procedures Defined with CREATE PROCEDURE Data dictionary contains source code in USER_SOURCE Explicitly invoked COMMIT, SAVEPOINT, and ROLLBACK are allowed

16-28

Copyright © Oracle Corporation, 2001. All rights reserved.

Database Triggers and Stored Procedures There are differences between database triggers and stored procedures:
Database Trigger Invoked implicitly COMMIT, ROLLBACK, and SAVEPOINT statements are not allowed within the trigger body. It is possible to commit or rollback indirectly by calling a procedure, but it is not recommended because of side effects to transactions. Stored Procedure Invoked explicitly COMMIT, ROLLBACK, and SAVEPOINT statements are permitted within the procedure body.

Triggers are fully compiled when the CREATE TRIGGER command is issued and the P code is stored in the data dictionary. If errors occur during the compilation of a trigger, the trigger is still created.

Oracle9i: Program with PL/SQL 16-28

Differentiating Between Database Triggers and Form Builder Triggers
INSERT INTO EMPLOYEES . . .;

EMPLOYEES table

CHECK_SAL trigger

…

BEFORE INSERT row

16-29

Copyright © Oracle Corporation, 2001. All rights reserved.

Differences between a Database Trigger and a Form Builder Trigger Database triggers are different from Form Builder triggers.
Database Trigger Executed by actions from any database tool or application Form Builder Trigger Executed only within a particular Form Builder application

Always triggered by a SQL DML, DDL, or a Can be triggered by navigating from field to field, by certain database action pressing a key, or by many other actions Is distinguished as either a statement or row Is distinguished as a statement or row trigger trigger Upon failure, causes the triggering statement Upon failure, causes the cursor to freeze and may to roll back cause the entire transaction to roll back Fires independently of, and in addition to, Form Builder triggers Executes under the security domain of the author of the trigger Fires independently of, and in addition to, database triggers Executes under the security domain of the Form Builder user

Oracle9i: Program with PL/SQL 16-29

Managing Triggers

Disable or reenable a database trigger:
ALTER TRIGGER trigger_name DISABLE | ENABLE

Disable or reenable all triggers for a table:
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

Recompile a trigger for a table:
ALTER TRIGGER trigger_name COMPILE

16-30

Copyright © Oracle Corporation, 2001. All rights reserved.

Trigger Modes: Enabled or Disabled • • When a trigger is first created, it is enabled automatically. The Oracle server checks integrity constraints for enabled triggers and guarantees that triggers cannot compromise them. In addition, the Oracle server provides read-consistent views for queries and constraints, manages the dependencies, and provides a two-phase commit process if a trigger updates remote tables in a distributed database. Disable a specific trigger by using the ALTER TRIGGER syntax, or disable all triggers on a table by using the ALTER TABLE syntax. Disable a trigger to improve performance or to avoid data integrity checks when loading massive amounts of data by using utilities such as SQL*Loader. You may also want to disable the trigger when it references a database object that is currently unavailable, owing to a failed network connection, disk crash, offline data file, or offline tablespace.

• •

Compile a Trigger • Use the ALTER TRIGGER command to explicitly recompile a trigger that is invalid. • When you issue an ALTER TRIGGER statement with the COMPILE option, the trigger recompiles, regardless of whether it is valid or invalid.

Oracle9i: Program with PL/SQL 16-30

DROP TRIGGER Syntax

To remove a trigger from the database, use the DROP TRIGGER syntax:
DROP TRIGGER trigger_name;

Example:
DROP TRIGGER secure_emp;

Note: All triggers on a table are dropped when the table is dropped.

16-31

Copyright © Oracle Corporation, 2001. All rights reserved.

Removing Triggers When a trigger is no longer required, you can use a SQL statement in iSQL*Plus to drop it.

Oracle9i: Program with PL/SQL 16-31

Trigger Test Cases • • • • •

Test each triggering data operation, as well as nontriggering data operations. Test each case of the WHEN clause. Cause the trigger to fire directly from a basic data operation, as well as indirectly from a procedure. Test the effect of the trigger upon other triggers. Test the effect of other triggers upon the trigger.

16-32

Copyright © Oracle Corporation, 2001. All rights reserved.

Testing Triggers • • Ensure that the trigger works properly by testing a number of cases separately. Take advantage of the DBMS_OUTPUT procedures to debug triggers. You can also use the Procedure Builder debugging tool to debug triggers. Using Procedure Builder is discussed in Appendix F, “Creating Program Units by Using Procedure Builder.”

Instructor Note When writing new application code with procedures and functions, you should verify the USER_TRIGGERS data dictionary view for any existing triggers. The newly added procedures and functions may fire these triggers implicitly, which is not intended. Hence, test the procedures and functions, and drop any triggers that are not required.
Oracle9i: Program with PL/SQL 16-32

Trigger Execution Model and Constraint Checking
1. Execute all BEFORE STATEMENT triggers. 2. Loop for each row affected:
a. Execute all BEFORE ROW triggers. b. Execute all AFTER ROW triggers.

3. Execute the DML statement and perform integrity constraint checking. 4. Execute all AFTER STATEMENT triggers.

16-33

Copyright © Oracle Corporation, 2001. All rights reserved.

Trigger Execution Model A single DML statement can potentially fire up to four types of triggers: BEFORE and AFTER statement and row triggers. A triggering event or a statement within the trigger can cause one or more integrity constraints to be checked. Triggers can also cause other triggers to fire (cascading triggers). All actions and checks done as a result of a SQL statement must succeed. If an exception is raised within a trigger and the exception is not explicitly handled, all actions performed because of the original SQL statement are rolled back. This includes actions performed by firing triggers. This guarantees that integrity constraints can never be compromised by triggers. When a trigger fires, the tables referenced in the trigger action may undergo changes by other users' transactions. In all cases, a read-consistent image is guaranteed for modified values the trigger needs to read (query) or write (update).

Instructor Note The example in the following slide demonstrates the constraint verification procedure. You can demonstrate this concept by using the file 16_trig_constr.sql.
Oracle9i: Program with PL/SQL 16-33

Trigger Execution Model and Constraint Checking: Example
UPDATE employees SET department_id = 999 WHERE employee_id = 170; -- Integrity constraint violation error CREATE OR REPLACE TRIGGER constr_emp_trig AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO departments VALUES (999, 'dept999', 140, 2400); END; / UPDATE employees SET department_id = 999 WHERE employee_id = 170; -- Successful after trigger is fired

16-34

Copyright © Oracle Corporation, 2001. All rights reserved.

Trigger Execution Model and Constraint Checking: Example The example in the slide explains a situation in which the integrity constraint can be taken care of by using a trigger. Table EMPLOYEES has a foreign key constraint on the DEPARTMENT_ID column of the DEPARTMENTS table. In the first SQL statement, the DEPARTMENT_ID of the employee with EMPLOYEE_ID 170 is modified to 999. Because such a department does not exist in the DEPARTMENTS table, the statement raises the exception -2292 for the integrity constraint violation. A trigger CONSTR_EMP_TRIG is created that inserts a new department 999 into the DEPARTMENTS table. When the UPDATE statement that modifies the department of employee 170 to 999 is issued, the trigger fires. Then, the foreign key constraint is checked. Because the trigger inserted the department 999 into the DEPARTMENTS table, the foreign key constraint check is successful and there is no exception. This process works with Oracle8i and later releases. The example described in the slide produces a run-time error in releases prior to Oracle8i.

Oracle9i: Program with PL/SQL 16-34

A Sample Demonstration for Triggers Using Package Constructs
AUDIT_EMP_TRIG DML into EMPLOYEES table FOR EACH ROW Increment variables
VAR_PACK package

1

2
AUDIT_EMP_TAB AFTER STATEMENT Copy and then reset variables

3 4
AUDIT_TABLE

16-35

Copyright © Oracle Corporation, 2001. All rights reserved.

A Sample Demonstration The following pages of PL/SQL subprograms are an example of the interaction of triggers, packaged procedures, functions, and global variables. The sequence of events: 1. Issue an INSERT, UPDATE, or DELETE command that can manipulate one or many rows. 2. AUDIT_EMP_TRIG, the AFTER ROW trigger, calls the packaged procedure to increment the global variables in the package VAR_PACK. Because this is a row trigger, the trigger fires once for each row that you updated. 3. When the statement has finished, AUDIT_EMP_TAB, the AFTER STATEMENT trigger, calls the procedure AUDIT_EMP. 4. This procedure assigns the values of the global variables into local variables using the packaged functions, updates the AUDIT_TABLE, and then resets the global variables. Instructor Note The slide presents a simplified version of the demonstration. First run the script 16_drop_trg.sql. The following demonstration scripts contain a complete explanation about the demonstration as described in the next few pages. Demonstration: 16_comp_ex_spec.sql and 16_comp_ex_body.sql Purpose: To create the VAR_PACK package, the AUDIT_EMP procedure, and the AUDIT_EMP_TRIG trigger. If you wish to complement the code by running an example, insert a row into AUDIT_TABLE with your user name and EMPLOYEES as the table name, and then write an INSERT, UPDATE, or DELETE command to see the triggers working.
Oracle9i: Program with PL/SQL 16-35

After Row and After Statement Triggers
CREATE OR REPLACE TRIGGER audit_emp_trig AFTER UPDATE or INSERT or DELETE on EMPLOYEES FOR EACH ROW BEGIN IF DELETING THEN var_pack.set_g_del(1); ELSIF INSERTING THEN var_pack.set_g_ins(1); ELSIF UPDATING ('SALARY') THEN var_pack.set_g_up_sal(1); ELSE var_pack.set_g_upd(1); END IF; END audit_emp_trig; / CREATE OR REPLACE TRIGGER audit_emp_tab AFTER UPDATE or INSERT or DELETE on employees BEGIN audit_emp; END audit_emp_tab; /

16-36

Copyright © Oracle Corporation, 2001. All rights reserved.

AFTER Row and AFTER Statement Triggers The trigger AUDIT_EMP_TRIG is a row trigger that fires after every row manipulated. This trigger invokes the package procedures depending on the type of DML performed. For example, if the DML updates salary of an employee, then the trigger invokes the procedure SET_G_UP_SAL. This package procedure inturn invokes the function G_UP_SAL. This function increments the package variable GV_UP_SAL that keeps account of the number of rows being changed due to update of the salary. The trigger AUDIT_EMP_TAB will fire after the statement has finished. This trigger invokes the procedure AUDIT_EMP, which is on the following pages. The AUDIT_EMP procedure updates the AUDIT_TABLE table. An entry is made into the AUDIT_TABLE table with the information such as the user who performed the DML, the table on which DML is performed, and the total number of such data manipulations performed so far on the table (indicated by the value of the corresponding column in the AUDIT_TABLE table). At the end, the AUDIT_EMP procedure resets the package variables to 0.

Instructor Note This is not the only way to implement this business requirement, but it does show the way packages, triggers, procedures, and packaged variables can interact.
Oracle9i: Program with PL/SQL 16-36

Demonstration: VAR_PACK Package Specification var_pack.sql CREATE OR REPLACE PACKAGE var_pack IS -- these functions are used to return the -- values of package variables FUNCTION g_del RETURN NUMBER; FUNCTION g_ins RETURN NUMBER; FUNCTION g_upd RETURN NUMBER; FUNCTION g_up_sal RETURN NUMBER; -- these procedures are used to modify the -- values of the package variables PROCEDURE set_g_del (p_val IN NUMBER); PROCEDURE set_g_ins (p_val IN NUMBER); PROCEDURE set_g_upd (p_val IN NUMBER); PROCEDURE set_g_up_sal (p_val IN NUMBER); END var_pack; /

16-37

Copyright © Oracle Corporation, 2001. All rights reserved.

Demonstration: VAR_PACK Package Body var_pack_body.sql CREATE OR REPLACE PACKAGE BODY var_pack IS gv_del NUMBER := 0; gv_ins NUMBER gv_upd NUMBER := 0; gv_up_sal NUMBER FUNCTION g_del RETURN NUMBER IS BEGIN RETURN gv_del; END; FUNCTION g_ins RETURN NUMBER IS BEGIN RETURN gv_ins; END; FUNCTION g_upd RETURN NUMBER IS BEGIN RETURN gv_upd; END; FUNCTION g_up_sal RETURN NUMBER IS BEGIN RETURN gv_up_sal; END; (continued on the next page)
Oracle9i: Program with PL/SQL 16-37

:= 0; := 0;

VAR_PACK Package Body (continued) PROCEDURE set_g_del (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_del := p_val; ELSE gv_del := gv_del +1; END IF; END set_g_del; PROCEDURE set_g_ins (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_ins := p_val; ELSE gv_ins := gv_ins +1; END IF; END set_g_ins; PROCEDURE set_g_upd (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_upd := p_val; ELSE gv_upd := gv_upd +1; END IF; END set_g_upd; PROCEDURE set_g_up_sal (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_up_sal := p_val; ELSE gv_up_sal := gv_up_sal +1; END IF; END set_g_up_sal; END var_pack; /

Oracle9i: Program with PL/SQL 16-38

Demonstration: Using the AUDIT_EMP Procedure
CREATE OR REPLACE PROCEDURE audit_emp IS v_del NUMBER := var_pack.g_del; v_ins NUMBER := var_pack.g_ins; v_upd NUMBER := var_pack.g_upd; v_up_sal NUMBER := var_pack.g_up_sal; BEGIN IF v_del + v_ins + v_upd != 0 THEN UPDATE audit_table SET del = del + v_del, ins = ins + v_ins, upd = upd + v_upd WHERE user_name=USER AND tablename='EMPLOYEES' AND column_name IS NULL; END IF; IF v_up_sal != 0 THEN UPDATE audit_table SET upd = upd + v_up_sal WHERE user_name=USER AND tablename='EMPLOYEES' AND column_name = 'SALARY'; END IF; -- resetting global variables in package VAR_PACK var_pack.set_g_del (0); var_pack.set_g_ins (0); var_pack.set_g_upd (0); var_pack.set_g_up_sal (0); END audit_emp;

16-39

Copyright © Oracle Corporation, 2001. All rights reserved.

Updating the AUDIT_TABLE with the AUDIT_EMP Procedure The AUDIT_EMP procedure updates the AUDIT_TABLE and calls the functions in the package VAR_PACK that reset the package variables, ready for the next DML statement.

Oracle9i: Program with PL/SQL 16-39

Summary
Procedure
xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx

Package

Trigger

Procedure A declaration

Procedure B definition Procedure A definition
Local variable

16-40

Copyright © Oracle Corporation, 2001. All rights reserved.

Develop different types of procedural database constructs depending on their usage.
Construct Procedure Package Trigger Usage PL/SQL programming block that is stored in the database for repeated execution Group of related procedures, functions, variables, cursors, constants, and exceptions PL/SQL programming block that is executed implicitly by a data manipulation statement

Oracle9i: Program with PL/SQL 16-40

Practice 16 Overview

This practice covers the following topics:

• •

Creating statement and row triggers Creating advanced triggers to add to the capabilities of the Oracle database

16-41

Copyright © Oracle Corporation, 2001. All rights reserved.

Practice 16 Overview You create statement and row triggers in this practice. You create procedures that will be invoked from the triggers.

Oracle9i: Program with PL/SQL 16-41

Practice 16 1. Changes to data are allowed on tables only during normal office hours of 8:45 a.m. until 5:30 p.m., Monday through Friday. Create a stored procedure called SECURE_DML that prevents the DML statement from executing outside of normal office hours, returning the message, “You may only make changes during normal office hours.” 2. a. Create a statement trigger on the JOBS table that calls the above procedure. b. Test the procedure by temporarily modifying the hours in the procedure and attempting to insert a new record into the JOBS table. (Example: replace 08:45 with 16:45; This attempt results in an error message) After testing, reset the procedure hours asspecified in question 1 and recreate the procedure as in question 1 above. If you have time: 3. Employees should receive an automatic increase in salary if the minimum salary for a job is increased. Implement this requirement through a trigger on the JOBS table. a. Create a stored procedure named UPD_EMP_SAL to update the salary amount. This procedure accepts two parameters: the job ID for which salary has to be updated, and the new minimum salary for this job ID. This procedure is executed from the trigger on the JOBS table. b. Create a row trigger named UPDATE_EMP_SALARY on the JOBS table that invokes the procedure UPD_EMP_SAL, when the minimum salary in the JOBS table is updated for a specified job ID. c. Query the EMPLOYEES table to see the current salary for employees who are programmers.

d. e.

Increase the minimum salary for the Programmer job from 4,000 to 5,000. Employee Lorentz (employee ID 107) had a salary of less than 4,500. Verify that her salary has been increased to the new minimum of 5,000.

Instructor Note If you encounter errors while performing practice question 3d, verify that you do not have the triggers trig_constr_emp and constr_emp_trig in your schema. These triggers are created and dropped when you run the demo 16_trig_constr.sql and code example 16_34s.sql. Students did not create these triggers earlier.
Oracle9i: Program with PL/SQL 16-42

More Trigger Concepts

Copyright © Oracle Corporation, 2001. All rights reserved.

Schedule:

Timing
50 minutes 45 minutes 95 minutes

Topic
Lecture Practice Total

Objectives

After completing this lesson, you should be able to do the following:

• • •

Create additional database triggers Explain the rules governing triggers Implement triggers

17-2

Copyright © Oracle Corporation, 2001. All rights reserved.

Lesson Aim In this lesson, you learn how to create more database triggers and learn the rules governing triggers. You also learn many applications of triggers.

Instructor Note This lesson contains several sections with discrete concepts that do not tie in with one another. Towards the end of this lesson, there are many practical examples of triggers. You do not need to go through each example. Gauge the level of detail to the audience requirements.
Oracle9i: Program with PL/SQL 17-2

Creating Database Triggers • •

Triggering user event:
– CREATE, ALTER, or DROP – – – Logging on or off Shutting down or starting up the database A specific error (or any error) being raised

Triggering database or system event:

17-3

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating Database Triggers Before coding the trigger body, decide on the components of the trigger. Triggers on system events can be defined at the database or schema level. For example, a database shutdown trigger is defined at the database level. Triggers on data definition language (DDL) statements, or a user logging on or off, can also be defined at either the database level or schema level. Triggers on DML statements are defined on a specific table or a view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table. Triggering events that can cause a trigger to fire: • • • • A data definition statement on an object in the database or schema A specific user (or any user) logging on or off A database shutdown or startup A specific or any error that occurs

Instructor Note The trigger concepts described above were introduced in Oracle8i and are intended primarily for use by DBAs.
Oracle9i: Program with PL/SQL 17-3

Creating Triggers on DDL Statements
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name timing [ddl_event1 [OR ddl_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body

17-4

Copyright © Oracle Corporation, 2001. All rights reserved.

Create Trigger Syntax
DDL_Event CREATE ALTER DROP Possible Values Causes the Oracle server to fire the trigger whenever a CREATE statement adds a new database object to the dictionary Causes the Oracle server to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary Causes the Oracle server to fire the trigger whenever a DROP statement removes a database object in the data dictionary

The trigger body represents a complete PL/SQL block. You can create triggers for these events on DATABASE or SCHEMA. You also specify BEFORE or AFTER for the timing of the trigger. DDL triggers fire only if the object being created is a cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, or user. Instructor Note The Oracle server fires the trigger in the existing user transaction.

Oracle9i: Program with PL/SQL 17-4

Creating Triggers on System Events

CREATE [OR REPLACE] TRIGGER trigger_name timing [database_event1 [OR database_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body

17-5

Copyright © Oracle Corporation, 2001. All rights reserved.

Create Trigger Syntax
Database_event AFTER SERVERERROR AFTER LOGON Possible Values Causes the Oracle server to fire the trigger whenever a server error message is logged Causes the Oracle server to fire the trigger whenever a user logs on to the database

BEFORE LOGOFF Causes the Oracle server to fire the trigger whenever a user logs off the database AFTER STARTUP Causes the Oracle server to fire the trigger whenever the database is opened BEFORE SHUTDOWN Causes the Oracle server to fire the trigger whenever the database is shut down

You can create triggers for these events on DATABASE or SCHEMA except SHUTDOWN and STARTUP, which apply only to the DATABASE. Instructor Note For each of these triggering events, the Oracle server opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction). An autonomous transaction scope is an independent transaction that can be committed without affecting the existing transaction. This was introduced in Oracle8i. Autonomous transactions are not covered in this course.
Oracle9i: Program with PL/SQL 17-5

LOGON and LOGOFF Trigger Example

CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id, log_date, action) VALUES (USER, SYSDATE, 'Logging on'); END; / CREATE OR REPLACE TRIGGER logoff_trig BEFORE LOGOFF ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id, log_date, action) VALUES (USER, SYSDATE, 'Logging off'); END; /

17-6

Copyright © Oracle Corporation, 2001. All rights reserved.

LOGON and LOGOFF Trigger Example You can create this trigger to monitor how often you log on and off, or you may want to write a report that monitors the length of time for which you are logged on. When you specify ON SCHEMA, the trigger fires for the specific user. If you specify ON DATABASE, the trigger fires for all users. Instructor Note Sample LOG_TRIG_TABLE: DESCRIBE log_trig_table

SELECT * FROM log_trig_table;

Oracle9i: Program with PL/SQL 17-6

CALL Statements

CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new] [FOR EACH ROW] [WHEN condition] CALL procedure_name CREATE OR REPLACE TRIGGER log_employee BEFORE INSERT ON EMPLOYEES CALL log_execution /

17-7

Copyright © Oracle Corporation, 2001. All rights reserved.

CALL Statements A CALL statement enables you to call a stored procedure, rather than coding the PL/SQL body in the trigger itself. The procedure can be implemented in PL/SQL, C, or Java. The call can reference the trigger attributes :NEW and :OLD as parameters as in the following example: CREATE TRIGGER salary_check BEFORE UPDATE OF salary, job_id ON employees FOR EACH ROW WHEN (NEW.job_id 'AD_PRES') CALL check_sal(:NEW.job_id, :NEW.salary) / Note: There is no semicolon at the end of the CALL statement. In the example above, the trigger calls a procedure check_sal. The procedure compares the new salary with the salary range for the new job ID from the JOBS table. Instructor Note Code example 17_7n.sql creates a procedure check_sal that compares the new salary with the range of the salary allowed for that job ID. The script also contains the above code for the trigger and statements to fire the trigger. The trigger is disabled at the end of the script of the next page. If you receive compilation errors while creating the trigger shown in the slide, re-create the LOG_EXECTION procedure by running the script demo\09_logexec.sql.
Oracle9i: Program with PL/SQL 17-7

Reading Data from a Mutating Table
UPDATE employees SET salary = 3400 WHERE last_name = 'Stiles';
CHECK_SALARY trigger

EMPLOYEES table

Failure

… …
Triggered table/ mutating table

3400

BEFORE UPDATE row

Trigger event

17-8

Copyright © Oracle Corporation, 2001. All rights reserved.

Rules Governing Triggers Reading and writing data using triggers is subject to certain rules. The restrictions apply only to row triggers, unless a statement trigger is fired as a result of ON DELETE CASCADE. Mutating Table A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action. A table is not considered mutating for STATEMENT triggers. The triggered table itself is a mutating table, as well as any table referencing it with the FOREIGN KEY constraint. This restriction prevents a row trigger from seeing an inconsistent set of data.

Instructor Note Before Oracle8i, it was not possible to change data in the primary key, foreign key, or unique key columns of a constraining table. This rule no longer applies in Oracle8i.

Oracle9i: Program with PL/SQL 17-8

Mutating Table: Example
CREATE OR REPLACE TRIGGER check_salary BEFORE INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW WHEN (NEW.job_id 'AD_PRES') DECLARE v_minsalary employees.salary%TYPE; v_maxsalary employees.salary%TYPE; BEGIN SELECT MIN(salary), MAX(salary) INTO v_minsalary, v_maxsalary FROM employees WHERE job_id = :NEW.job_id; IF :NEW.salary < v_minsalary OR :NEW.salary > v_maxsalary THEN RAISE_APPLICATION_ERROR(-20505,'Out of range'); END IF; END; /

17-9

Copyright © Oracle Corporation, 2001. All rights reserved.

Mutating Table: Example The CHECK_SALARY trigger in the example, attempts to guarantee that whenever a new employee is added to the EMPLOYEES table or whenever an existing employee’s salary or job ID is changed, the employee’s salary falls within the established salary range for the employee’s job. When an employee record is updated, the CHECK_SALARY trigger is fired for each row that is updated. The trigger code queries the same table that is being updated. Hence, it is said that the EMPLOYEES table is mutating table.

Oracle9i: Program with PL/SQL 17-9

Mutating Table: Example

UPDATE employees SET salary = 3400 WHERE last_name = 'Stiles';

17-10

Copyright © Oracle Corporation, 2001. All rights reserved.

Mutating Table: Example (continued) Try to read from a mutating table. If you restrict the salary within a range between the minimum existing value and the maximum existing value you get a run-time error. The EMPLOYEES table is mutating, or in a state of change; therefore, the trigger cannot read from it. Remember that functions can also cause a mutating table error when they are invoked in a DML statement.

Instructor Note A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement. A mutating table cannot be changed, because the resulting DML could change data that is in an inconsistent state.
Oracle9i: Program with PL/SQL 17-10

Implementing Triggers

You can use trigger for:

• • • • • • •

Security Auditing Data integrity Referential integrity Table replication Computing derived data automatically Event logging

17-11

Copyright © Oracle Corporation, 2001. All rights reserved.

Implementing Triggers Develop database triggers in order to enhance features that cannot otherwise be implemented by the Oracle server or as alternatives to those provided by the Oracle server.
Feature Security Auditing Data integrity Referential integrity Table replication Derived data Event logging Enhancement The Oracle server allows table access to users or roles. Triggers allow table access according to data values. The Oracle server tracks data operations on tables. Triggers track values for data operations on tables. The Oracle server enforces integrity constraints. Triggers implement complex integrity rules. The Oracle server enforces standard referential integrity rules. Triggers implement nonstandard functionality. The Oracle server copies tables asynchronously into snapshots. Triggers copy tables synchronously into replicas. The Oracle server computes derived data values manually. Triggers compute derived data values automatically. The Oracle server logs events explicitly. Triggers log events transparently.
Oracle9i: Program with PL/SQL 17-11

Controlling Security Within the Server

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO clerk; -- database role GRANT clerk TO scott;

17-12

Copyright © Oracle Corporation, 2001. All rights reserved.

Controlling Security Within the Server Develop schemas and roles within the Oracle server to control the security of data operations on tables according to the identity of the user. • • • Base privileges upon the username supplied when the user connects to the database. Determine access to tables, views, synonyms, and sequences. Determine query, data manipulation, and data definition privileges.

Instructor Note There is no code example script file for the example in the slide.

Oracle9i: Program with PL/SQL 17-12

Controlling Security with a Database Trigger
CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT OR UPDATE OR DELETE ON employees DECLARE v_dummy VARCHAR2(1); BEGIN IF (TO_CHAR (SYSDATE, 'DY') IN ('SAT','SUN')) THEN RAISE_APPLICATION_ERROR (-20506,'You may only change data during normal business hours.'); END IF; SELECT COUNT(*) INTO v_dummy FROM holiday WHERE holiday_date = TRUNC (SYSDATE); IF v_dummy > 0 THEN RAISE_APPLICATION_ERROR(-20507, 'You may not change data on a holiday.'); END IF; END; /

17-13

Copyright © Oracle Corporation, 2001. All rights reserved.

Controlling Security With a Database Trigger Develop triggers to handle more complex security requirements. • • • Base privileges on any database values, such as the time of day, the day of the week, and so on. Determine access to tables only. Determine data manipulation privileges only.

Instructor Note The code example script file for the example in the slide contains additional statements that create the HOLIDAY table and insert values into the table.

Oracle9i: Program with PL/SQL 17-13

Using the Server Facility to Audit Data Operations

AUDIT INSERT, UPDATE, DELETE ON departments BY ACCESS WHENEVER SUCCESSFUL;

The Oracle server stores the audit information in a data dictionary table or operating system file.

17-14

Copyright © Oracle Corporation, 2001. All rights reserved.

Auditing Data Operations You can audit data operations within the Oracle server. Database auditing is used to monitor and gather data about specific database activities. The DBA can gather statistics about which tables are being updated, how many I/Os are performed, how many concurrent users connect at peak time, and so on. • Audit users, statements, or objects. • Audit data retrieval, data manipulation, and data definition statements. • Write the audit trail to a centralized audit table. • Generate audit records once per session or once per access attempt. • Capture successful attempts, unsuccessful attempts, or both. • Enable and disable dynamically. Executing SQL through PL/SQL program units may generate several audit records because the program units may refer to other database objects. Instructor Note The data dictionary view SYS.AUD$ contains all information regarding audit. Access the view as SYSTEM user. AUDIT statement_opt|system_priv[, ...] [BY user[, ...]] [BY [SESSION|ACCESS]] [WHENEVER [NOT] SUCCESSFUL]; Examples: AUDIT ROLE; AUDIT ROLE WHENEVER SUCCESSFUL; AUDIT ROLE WHENEVER NOT SUCCESSFUL; AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake;
Oracle9i: Program with PL/SQL 17-14

Auditing by Using a Trigger
CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON employees FOR EACH ROW BEGIN IF (audit_emp_package.g_reason IS NULL) THEN RAISE_APPLICATION_ERROR (-20059, 'Specify a reason for the data operation through the procedure SET_REASON of the AUDIT_EMP_PACKAGE before proceeding.'); ELSE INSERT INTO audit_emp_table (user_name, timestamp, id, old_last_name, new_last_name, old_title, new_title, old_salary, new_salary, comments) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary, audit_emp_package.g_reason); END IF; END; CREATE OR REPLACE TRIGGER cleanup_audit_emp AFTER INSERT OR UPDATE OR DELETE ON employees BEGIN audit_emp_package.g_reason := NULL; END;

17-15

Copyright © Oracle Corporation, 2001. All rights reserved.

Audit Data Values Audit actual data values with triggers. You can: • • • • • Audit data manipulation statements only Write the audit trail to a user-defined audit table Generate audit records once for the statement or once for each row Capture successful attempts only Enable and disable dynamically

Using the Oracle server, you can perform database auditing. Database auditing cannot record changes to specific column values. If the changes to the table columns need to be tracked and column values need to be stored for each change, use application auditing. Application auditing can be done either through stored procedures or database triggers, as shown in the example in the slide. Instructor Note The user makes a call to the AUDIT_EMP_PACKAGE and sets the variable G_REASON. During a DML operation, the trigger checks the reason for the data manipulation operation. If G_REASON is not set, an error is raised; if it is set, the DML operation is successful, and it is recorded in the AUDIT_EMP_TABLE. If the AUDIT_EMP_TABLE is not already existing, run the script file demo\09_addtabs.sql. The second trigger clears the variable G_REASON of a value. The code example script file for this example contains additional statements that you can use to create the package used by the trigger and the statements to test the trigger.
Oracle9i: Program with PL/SQL 17-15

Enforcing Data Integrity Within the Server

ALTER TABLE employees ADD CONSTRAINT ck_salary CHECK (salary >= 500);

17-16

Copyright © Oracle Corporation, 2001. All rights reserved.

Enforcing Data Integrity within the Server You can enforce data integrity within the Oracle server and develop triggers to handle more complex data integrity rules. The standard data integrity rules are not null, unique, primary key, and foreign key. Use these rules to: • • • Provide constant default values Enforce static constraints Enable and disable dynamically

Example The code sample in the slide ensures that the salary is at least $500.

Oracle9i: Program with PL/SQL 17-16

Protecting Data Integrity with a Trigger
CREATE OR REPLACE TRIGGER check_salary BEFORE UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.salary < OLD.salary) BEGIN RAISE_APPLICATION_ERROR (-20508, 'Do not decrease salary.'); END; /

17-17

Copyright © Oracle Corporation, 2001. All rights reserved.

Protecting Data Integrity with a Trigger Protect data integrity with a trigger and enforce nonstandard data integrity checks. • • • • Provide variable default values. Enforce dynamic constraints. Enable and disable dynamically. Incorporate declarative constraints within the definition of a table to protect data integrity.

Example The code sample in the slide ensures that the salary is never decreased.

Instructor Note The code example script file for this example contains additional statements to test the trigger.

Oracle9i: Program with PL/SQL 17-17

Enforcing Referential Integrity Within the Server

ALTER TABLE employees ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE;

17-18

Copyright © Oracle Corporation, 2001. All rights reserved.

Enforcing Referential Integrity within the Server Incorporate referential integrity constraints within the definition of a table to prevent data inconsistency and enforce referential integrity within the server. • • • Restrict updates and deletes. Cascade deletes. Enable and disable dynamically.

Example When a department is removed from the DEPARTMENTS parent table, cascade the deletion to the corresponding rows in the EMPLOYEES child table.

Instructor Note Do not run the code example for the code in the slide. The code produces an error message because the foreign key constraint mentioned in the code is already existing in the table.

Oracle9i: Program with PL/SQL 17-18

Protecting Referential Integrity with a Trigger
CREATE OR REPLACE TRIGGER cascade_updates AFTER UPDATE OF department_id ON departments FOR EACH ROW BEGIN UPDATE employees SET employees.department_id=:NEW.department_id WHERE employees.department_id=:OLD.department_id; UPDATE job_history SET department_id=:NEW.department_id WHERE department_id=:OLD.department_id; END; /

17-19

Copyright © Oracle Corporation, 2001. All rights reserved.

Protecting Referential Integrity with a Trigger Develop triggers to implement referential integrity rules that are not supported by declarative constraints. • • • • • Cascade updates. Set to NULL for updates and deletions. Set to a default value on updates and deletions. Enforce referential integrity in a distributed system. Enable and disable dynamically.

Example Enforce referential integrity with a trigger. When the value of DEPARTMENT_ID changes in the DEPARTMENTS parent table, cascade the update to the corresponding rows in the EMPLOYEES child table. For a complete referential integrity solution using triggers, a single trigger is not enough. Instructor Note The code example script file for this page contains additional statements that temporarily disable constraints on the EMPLOYEES and JOB_HISTORY tables. The script file also tests the triggers, rolls back the transaction, and re-enables the triggers.
Oracle9i: Program with PL/SQL 17-19

Replicating a Table Within the Server

CREATE SNAPSHOT emp_copy AS SELECT * FROM employees@ny;

17-20

Copyright © Oracle Corporation, 2001. All rights reserved.

Creating a Snapshot A snapshot is a local copy of a table data that originates from one or more remote master tables. An application can query the data in a read-only table snapshot, but cannot insert, update, or delete rows in the snapshot. To keep a snapshot's data current with the data of its master, the Oracle server must periodically refresh the snapshot. When this statement is used in SQL, replication is performed implicitly by the Oracle server by using internal triggers. This has better performance over using user-defined PL/SQL triggers for replication. Copying Tables with Server Snapshots Copy a table with a snapshot. • • • • Copy tables asynchronously, at user-defined intervals. Base snapshots on multiple master tables. Read from snapshots only. Improve the performance of data manipulation on the master table, particularly if the network fails.

Alternatively, you can replicate tables using triggers. Example In San Francisco, create a snapshot of the remote EMPLOYEES table in New York. Instructor Note Replication of snapshots or materialized views is done within the database kernel and is faster than a trigger. There are no code example script files that you can use to demonstrate replication.
Oracle9i: Program with PL/SQL 17-20

Replicating a Table with a Trigger
CREATE OR REPLACE TRIGGER emp_replica BEFORE INSERT OR UPDATE ON employees FOR EACH ROW BEGIN /*Only proceed if user initiates a data operation, NOT through the cascading trigger.*/ IF INSERTING THEN IF :NEW.flag IS NULL THEN INSERT INTO employees@sf VALUES(:new.employee_id, :new.last_name,..., 'B'); :NEW.flag := 'A'; END IF; ELSE /* Updating. */ IF :NEW.flag = :OLD.flag THEN UPDATE employees@sf SET ename = :NEW.last_name, ..., flag = :NEW.flag WHERE employee_id = :NEW.employee_id; END IF; IF :OLD.flag = 'A' THEN :NEW.flag := 'B'; ELSE :NEW.flag := 'A'; END IF; END IF; END;

17-21

Copyright © Oracle Corporation, 2001. All rights reserved.

Replicating a Table with a Trigger Replicate a table with a trigger. • • • • Copy tables synchronously, in real time. Base replicas on a single master table. Read from replicas, as well as write to them. Impair the performance of data manipulation on the master table, particularly if the network fails.

Maintain copies of tables automatically with snapshots, particularly on remote nodes. Example In New York, replicate the local EMPLOYEES table to San Francisco.

Instructor Note If the network were to fail and you used the snapshot method, the Oracle server capabilities ensure that the users on the main node are not affected, and that the snapshot is maintained asynchronously. If you used the trigger method of replication, the user is not able to work because the trigger would not be able to write to the remote database.

Oracle9i: Program with PL/SQL 17-21

Computing Derived Data Within the Server

UPDATE departments SET total_sal=(SELECT SUM(salary) FROM employees WHERE employees.department_id = departments.department_id);

17-22

Copyright © Oracle Corporation, 2001. All rights reserved.

Computing Derived Data within the Server Compute derived values in a batch job. • • • Compute derived column values asynchronously, at user-defined intervals. Store derived values only within database tables. Modify data in one pass to the database and calculate derived data in a second pass.

Alternatively, you can use triggers to keep running computations of derived data. Example Keep the salary total for each department within a special TOTAL_SALARY column of the DEPARTMENTS table.

Instructor Note The code example script file for this topic contains additional statements that you can use to add the TOTAL_SAL column to the DEPARTMENTS table and then drop it from the table.
Oracle9i: Program with PL/SQL 17-22

Computing Derived Values with a Trigger
CREATE OR REPLACE PROCEDURE increment_salary (p_id IN departments.department_id%TYPE, p_salary IN departments.total_sal%TYPE) IS BEGIN UPDATE departments SET total_sal = NVL (total_sal, 0)+ p_salary WHERE department_id = p_id; END increment_salary; CREATE OR REPLACE TRIGGER compute_salary AFTER INSERT OR UPDATE OF salary OR DELETE ON employees FOR EACH ROW BEGIN IF DELETING THEN increment_salary(:OLD.department_id,(-1*:OLD.salary)); ELSIF UPDATING THEN increment_salary(:NEW.department_id,(:NEW.salary-:OLD.salary)); ELSE increment_salary(:NEW.department_id,:NEW.salary);--INSERT END IF; END;
17-23 Copyright © Oracle Corporation, 2001. All rights reserved.

Computing Derived Data Values with a Trigger Compute derived values with a trigger. • • • Compute derived columns synchronously, in real time. Store derived values within database tables or within package global variables. Modify data and calculate derived data in a single pass to the database.

Example Keep a running total of the salary for each department within the special TOTAL_SALARY column of the DEPARTMENTS table.

Instructor Note The code example script file for this topic contains additional statements that you can use to add the TOTAL_SAL column to the DEPARTMENTS table and then drop it from the table. The script also contains statements to test the trigger and roll back the transaction at the end.

Oracle9i: Program with PL/SQL 17-23

Logging Events with a Trigger
CREATE OR REPLACE TRIGGER notify_reorder_rep BEFORE UPDATE OF quantity_on_hand, reorder_point ON inventories FOR EACH ROW DECLARE v_descrip product_descriptions.product_description%TYPE; v_msg_text VARCHAR2(2000); stat_send number(1); BEGIN IF :NEW.quantity_on_hand :s' USING my_sal; ... END; Any bind arguments in the query are evaluated only when the cursor variable is opened. Thus, to fetch rows from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values. Fetching from the Cursor Variable The FETCH statement returns a row from the result set of a multirow query, assigns the values of select-list items to corresponding variables or fields in the INTO clause, increments the count kept by %ROWCOUNT, and advances the cursor to the next row. Use the following syntax: FETCH {cursor_variable | :host_cursor_variable} INTO {define_variable[, define_variable]... | record}; Continuing the example, fetch rows from cursor variable EMP_CV into define variables MY_ENAME and MY_SAL: LOOP FETCH emp_cv INTO my_ename, my_sal; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible variable or field in the INTO clause. You can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set. If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR. Closing the Cursor Variable The CLOSE statement disables a cursor variable. After that, the associated result set is undefined. Use the following syntax: CLOSE {cursor_variable | :host_cursor_variable}; In this example, when the last row is processed, close cursor variable EMP_CV: LOOP FETCH emp_cv INTO my_ename, my_sal; EXIT WHEN emp_cv%NOTFOUND; -- process row END LOOP; CLOSE emp_cv; -- close cursor variable If you try to close an already-closed or never-opened cursor variable, PL/SQL raises INVALID_CURSOR.
Oracle9i: Program with PL/SQL D-7

An Example of Fetching
DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec employees%ROWTYPE; sql_stmt VARCHAR2(200); my_job VARCHAR2(10) := 'ST_CLERK'; BEGIN sql_stmt := 'SELECT * FROM employees WHERE job_id = :j'; OPEN emp_cv FOR sql_stmt USING my_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- process record END LOOP; CLOSE emp_cv; END; /
D-8 Copyright © Oracle Corporation, 2001. All rights reserved.

An Example of Fetching The example in the preceding slide shows that you can fetch rows from the result set of a dynamic multirow query into a record. First you must define a REF CURSOR type, EmpCurTyp. Next you define a cursor variable emp_cv, of the type EmpcurTyp. In the executable section of the PL/SQL block, the OPEN-FOR statement associates the cursor variable EMP_CV with the multirow query, sql_stmt. The FETCH statement returns a row from the result set of a multirow query and assigns the values of select-list items to EMP_REC in the INTO clause. When the last row is processed, close the cursor variable EMP_CV.

Oracle9i: Program with PL/SQL D-8

Index

A actual parameter 2-6 anonymous blocks 1-7 application trigger 9-3 aUTHID CURRENT_USE 4-5 B BEFORE statement trigger 9-14 BEGIN 1-7 benefits 2-26 BFILE 8-12 BFILENAME 8-12 binding 7-5 C CALL statement 10-7 CLOSE_CONNECTION 7-31 CREATE PROCEDURE 2-5 CREATE ANY DIRECTORY 8-13 D database trigger 9-3, 10-11 DBA_JOB 7-19 DBA_JOBS_RUNNING 7-19 DBMS_DDL 7-12 DBMS_JOB 7-13 DBMS_JOB.BROKEN 7-18 DBMS_JOB.REMOVE 7-18 DBMS_JOB.RUN 7-18 DBMS_LOB 7-21, 8-12 data dictionary view 4-9, 4-11 data type 3-4 DBMS_OUTPUT 4-16 DBMS_SQL 7-6 DECLARE 1-7 definer's-rights 4-4 DEPTREE 11-8 DIRECTORY 8-10 DROP PROCEDURE 2-25 dynamic SQL 7-4

Oracle9i: Program with PL/SQL Index-3

E EMPTY_BLOB 8-24 EMPTY_CLOB 8-24 END 1-7 environments 1-13 EXCEPTION 1-7, 2-21 EXECUTE 4-3, 7-11 external large object 8-8 F fetch 7-5 FILE_LOCATOR 8-16 file_type 7-27 formal parameter 2-6 forward declaration 6-8 function 3-3, 8-12 H host variable 2-14 I IDEPTREE 11-8 IMMEDIATE 7-11 INSTEAD OF 9-22 INSTEAD OF 9-7 internal 8-6 invoke a procedure 2-9 IS_OPEN 7-26 L LOB 8-3, 8-5, 8-32 LOB locator 8-5 local dependencies 11-5 locator 8-12 LONG 8-4 LONG-to-LOB 8-17 M migration 8-17 modularization 1-6 modules 1-6 mutating table 10-8 N NEW 9-19

Oracle9i: Program with PL/SQL Index-4

O object privilege 4-3 OCI 8-10 OLD 9-19 one-time-only procedure 6-10 OPEN_CONNECTION 7-31 Oracle Internet Platform 1-4 overload 6-3 OLD and NEW qualifiers 9-19 P package 4-16, 5-3, 7-22, 8-9, 8-19 package body 5-11 package specification 5-8 parameter mode 2-8, 3-8 Parsing 7-5 persistent state 6-14 PL/SQL block 2-4 PL/SQL construct 1-5 PROCEDURE 2-5, 2-25 procedures and functions within the 7-23 purity level 6-11 R recompile a PL/SQL object 11-22 remote dependencies 11-12 row trigger 9-18 READ 8-12 REPLACE 2-4 REQUEST 7-29 REQUEST_PIECES 7-29 RETURN 3-4 row trigger 9-9 S schedule batch job 7-13 security mechanism 8-9 SESSION_MAX_OPEN_FILE 8-13 SHOW ERROR 4-11

Oracle9i: Program with PL/SQL Index-5

signature 11-13 SQL*Plus 1-4 statement trigger 9-9, 9-14 SUBMIT 7-15 submit PL/SQL program 7-13 subprogram 1-6 system event 10-3 system privileges 4-3 T temporary 8-32 time stamp 11-13, 11-20 TO_BLOB 8-18 TO_CLOB 8-18 trigger action 9-10 trigger name 9-13 trigger timing 9-6 trigger type 9-6 triggering event 9-8 U user-defined PL/SQL function 3-13 USER_DEPENDENCIES 11-7 USER_ERRORS 4-11 USER_JOBS 7-19 USER_OBJECTS 4-7 USER_SOURCE 4-9 USER_TRIGGER 10-28 UTL_FILE 7-21 UTL_FILE_DIR 7-22 UTL_HTTP 7-29 UTL_TCP 7-31

Oracle9i: Program with PL/SQL Index-6