Oracle Course - PL/SQL

Module No. 1
Advanced PL/SQL

PSBDMF! Supinfo Training Program

Supinfo Laboratory of Oracle Technologies

Thibault Blanchard
01/01/1601 01:00
Page Name: 101
[Link]

Higher School of Computer Science

23, Château Landon Street
75010PARIS
[Link]
 Advanced PL/SQL - Version 1.2

1 OVERVIEW OF PL/SQL___________________________________________________________ 7
1.1 The complete solution from Oracle______________________________________________ 7
1.2 Programs in PL/SQL _______________________________________________ 7
1.2.1 Program models in PL/SQL _________________________________ 7
1.2.2 Structure of an anonymous PL/SQL block ___________________________________ 7
1.2.3 Structure of a PL/SQL subprogram _________________________________ 8
1.2.4 Advantages of subprograms
1.3 Development Environments______________________________________ 8
1.3.1 SQL*Plus and Procedure Builder
1.3.2 Develop functions and procedures with SQL*Plus
1.3.3 Develop using Oracle Procedure Builder __________________________ 9
1.4 Function and Procedure Calls ________________________________________ 9
2 USAGE OF PROCEDURE BUILDER
2.1 Procedure Builder_______________________________________________________ 10
2.1.1 The components of Procedure Builder _________________________________ 10
2.1.2 Develop program units and stored program units___ 10
2.1.3 The object browser ______________________________________________ 10
2.1.4 The program unit editor _____________________________________ 11
2.1.5 The stored program units editor_______________________________ 12
2.2 Use of Procedure Builder
2.2.1 Creation of a customer program unit ______________________________ 12
2.2.2 Creation of a server program unit _____________________________ 12
2.2.3 Transfer programs between the client and the server_____________________ 12
2.3 The PL/SQL interpreter____________________________________________________ 12
2.4 The TEXT_IO package_____________________________________________________ 13
3 CREATION OF PROCEDURES__________________________________________________ 14
3.1 Overview of Procedures __________________________________________________ 14
3.2 Create procedures____________________________________________________ 14
3.2.1 The syntax for creating procedures __________________________________ 14
3.2.2 The parameter modes of the procedure ______________________________ 14
3.2.3 Develop stored procedures __________________________________ 15
3.2.4 Develop a procedure using SQL*Plus _________________________ 15
3.2.5 Develop a procedure using procedure builder ___________________ 15
3.3 Procedures and parameters ________________________________________________ 16
3.3.1 Creation of procedures with parameters ____________________________ 16
3.3.2 The IN parameter ___________________________________________________ 17
3.3.3 The OUT parameter _________________________________________________ 17
3.3.4 The IN OUT parameter_______________________________________________ 18
3.3.5 Passing parameters _____________________________________________ 20
3.4 The subprograms ___________________________________________________ 21
3.4.1 Declare subprograms_______________________________________ 21
3.4.2 Invoke a procedure from an anonymous block _________________________ 22
3.4.3 Invoke a procedure from a stored procedure ____________________ 22
3.5 Exception Management __________________________________________________ 22
3.5.1 Exceptions handled_________________________________________________ 22
3.5.2 Unhandled exceptions _____________________________________________ 23
3.6 Delete procedures _______________________________________________ 23
3.7 Delete server procedures_________________________________________ 23
3.8 Delete customer procedures __________________________________________ 23

Supinfo Laboratory of Oracle Technologies Page 2 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

4 CREATION OF FUNCTIONS
4.1 The functions___________________________________________________________ 24
4.1.1 Overview of stored functions________________________________________ 24
4.1.2 Syntax for Creating Functions __________________________________ 24
4.1.3 Function Creation________________________________________________ 24
4.2 Functions in SQL*Plus _____________________________________________ 25
4.2.1 Creation of stored functions _______________________________________ 25
4.2.2 Execute functions______________________________________________ 25
4.3 Functions in Procedure Builder ______________________________________ 26
4.3.1 Function Creation________________________________________________ 26
4.3.2 Example of function creation ______________________________________ 26
4.3.3 Execute functions
4.4 User-defined functions in SQL __________________________ 27
4.4.1 Advantages of functions in SQL expressions ______________________ 27
4.4.2 Locations from which to call the functions _______________________________ 27
4.4.3 Function calls: restrictions _______________________________________ 27
4.5 Delete functions _________________________________________________ 28
4.5.1 Remove server functions ______________________________________ 28
4.5.2 Delete client functions ______________ 28
4.6 Procedure or function?__________________________________________________ 28
4.6.1 Summary ______________________________________________________ 28
4.6.2 Comparison between procedures and functions _____________________________ 28
4.6.3 The advantages of stored procedures and functions ______________________ 29
5 CREATION OF PACKAGES ____________________________________________________ 30
5.1 The packages___________________________________________________________ 30
5.1.1 Overview of packages_______________________________________________ 30
5.1.2 The components of a package______________________________________ 30
5.1.3 Referencing the objects of a package____________________________________ 31
5.2 Create packages _____________________________________________________ 31
5.2.1 Development of a package ________________________________________ 31
5.2.2 Creation of package specifications_________________________________ 32
5.2.3 Declaration of public elements________________________________________ 32
5.2.4 Package specification creation: example __________________________ 32
5.2.5 Creation of the package body ________________________________________ 32
5.2.6 Public and private elements ___________________________________________ 33
5.2.7 Package Body Creation: Example________________________________ 34
5.2.8 Directives for developing packages _______________________________ 34
5.2.9 Global variables ______________________________________________ 34
5.3 Manipulating packages __________________________________________________ 35
5.3.1 Execute a public procedure of a package __________________________ 35
5.3.2 Invoke package elements ___________________________________ 35
5.3.3 Referencing a public variable from a standalone procedure _________ 36
5.3.4 Delete packages ____________________________________________ 37
5.3.5 Advantages of packages____________________________________________ 37
6 SUPPLEMENTS ON THE PACKAGES __________________________________________ 39
6.1 The overload ___________________________________________________________ 39
6.2 The early declarations _______________________________________________ 40
6.3 Creation of a one-time procedure ___________________________________ 41
6.4 Restrictions on package functions in SQL ____________________________ 41
6.5 Invoke a function from a user-defined package in an SQL order____ 42
6.6 The persistent state ________________________________________________________ 42

Page 3 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

6.6.1 The variable packages___________________________________________ 42

6.6.2 The cursor packages ___________________________________________ 43
6.6.3 The PL/SQL table and record packages______________________________ 44
7 PACKAGES PROVIDED BY ORACLE ____________________________________________ 46
7.1 The packages provided by Oracle ___________________________________________ 46
7.2 The DBMS_PIPE package__________________________________________________ 46
7.2.1 The composition of DBMS_PIPE_______________________________________ 46
7.2.2 The functions of DBMS_PIPE ________________________________________ 46
7.2.3 Example of DBMS_PIPE usage __________________________________ 46
7.3 Dynamic SQL ______________________________________________________ 47
7.3.1 Définition ________________________________________________________ 47
7.3.2 The execution flow _________________________________________________ 47
7.3.3 The DBMS_SQL package ____________________________________________ 48
7.3.4 Use of DMBS_SQL ___________________________________________ 48
7.3.5 EXECUTE IMMEDIATE _____________________________________________ 49
7.3.6 49
7.3.7 Use of EXECUTE IMMEDIATE __________________________________ 50
7.4 The other available packages __________________________________________ 50
7.4.1 The DBMS_DDL package ____________________________________________ 50
7.4.2 The DBMS_JOB package ____________________________________________ 50
7.4.3 The DBMS_OUTPUT package. ________________________________________ 51
7.4.4 Other packages provided by Oracle___________________________________ 51
8 DATABASE TRIGGER CREATION ________________________________ 52
8.1 Database triggers __________________________________________ 52
8.1.1 Overview of triggers ________________________________________________ 52
8.1.2 Design directives for triggers __________________________________ 52
8.1.3 Example of a database trigger ________________________________ 52
8.1.4 Creation of triggers ________________________________________________ 53
8.2 The components of a trigger_____________________________________________ 53
8.2.1 The synchronization of the trigger_________________________________________ 53
8.2.2 The triggering event ___________________________________________ 53
8.2.3 The type of trigger __________________________________________________ 54
8.2.4 The body of the trigger _________________________________________________ 54
8.3 The triggering sequence ___________________________________________ 54
8.4 Creation of Statement Trigger
8.4.1 Syntax for creating Statement triggers ______________________________ 54
8.4.2 Creation with SQL*Plus ____________________________________________ 55
8.4.3 Creation with Procedure Builder ______________________________________ 55
8.4.4 SECURE_EMP Test _____________________________________________ 56
8.4.5 Use of conditional attributes _____________________________________ 57
8.5 Creation of Row Triggers
8.5.1 Syntax for creating a Row Trigger __________________________________ 57
8.5.2 Creation with SQL*Plus
8.5.3 Creation with Procedure Builder ______________________________________ 59
8.5.4 Use of the qualifiers OLD and NEW ________________________________ 59
8.5.5 Values of OLD and NEW ____________________________________________ 59
8.5.6 Restricting a row trigger________________________________________ 60
8.6 Trigger INSTEAD OF_____________________________________________________ 60
8.6.1 Interest of INSTEAD OF triggers______________________________________ 60
8.6.2 Creation of an INSTEAD OF trigger ____________________________________ 60
8.7 Difference between triggers and stored procedures _______________________ 61
8.8 Management of triggers _____________________________________________________ 61
8.8.1 Activation of triggers ______________________________________________ 61

Supinfo Laboratory of Oracle Technologies Page 4 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

8.8.2 Deletion of triggers

8.9 Trigger tests _________________________________________________________ 62
8.9.1 Cases to test ______________________________________________________ 62
8.9.2 Trigger execution model and constraint verification __________________ 62
8.10 Interactions ____________________________________________________________ 63
8.10.1 A typical demonstration_____________________________________________ 63
8.10.2 The audit table____________________________________________________ 63
8.10.3 The triggers ______________________________________________________ 63
8.10.4 VAR_PACK package specifications _________________________________ 64
8.10.5 Procedure________________________________________________________ 65
9 COMPLEMENTS ON TRIGGERS ___________________________________________ 66
9.1 Creating triggers on user events __________________________ 66
9.2 Creating triggers on system events ___________________________ 66
9.3 Examples of Log On and Log Off triggers _________________________________ 67
9.4 The CALL statement _____________________________________________________ 67
9.5 The tables under modification _______________________________________ 68
9.5.1 Reading data from a table under modification________________ 68
9.5.2 Example of a table under modification _____________________________ 68
9.6 Functions of triggers ___________________________________________________ 69
9.6.1 Secure the server ________________________________________________ 69
9.6.2 Audit
9.6.3 Ensure data integrity _______________________________________ 71
9.6.4 Ensure Referential Integrity________________________________________ 71
9.6.5 Duplicate the tables ________________________________________________ 72
9.6.6 Use derived data ________________________________________ 73
9.6.7 Manage event logs with triggers ___________________________ 74
9.6.8 Advantages of Database Triggers_____________________________ 75
10 MANAGING SUBPROGRAMS AND TRIGGERS _____________________________ 76
Privileges
10.1.1 System Privileges ________________________________________________ 76
10.1.2 Object privileges___________________________________________________ 76
10.2 Granting access to data __________________________________________ 76
10.3 Specify user rights_________________________________________ 77
10.4 Managing stored PL/SQL objects
10.5 Information about the object __________________________________________________ 78
10.5.1 USER_OBJECTS__________________________________________________ 78
10.5.2 List all the procedures and functions ________________________________ 78
10.6 Procedure Text____________________________________________________ 78
10.6.1 The view of the USER_SOURCE data dictionary ______________________ 78
10.6.2 List the code of all procedures and functions ________________________ 79
10.6.3 List the code of stored procedures with procedure builder ______________ 79
10.7 Text of a trigger _______________________________________________________ 79
10.7.1 USER_TRIGGERS ________________________________________________ 79
10.7.2 List the trigger codes ___________________________________________ 80
10.8 The parameters _________________________________________________________ 80
10.8.1 DESCRIBE in SQL*Plus __________________________________________ 80
10.8.2 The .DESCRIBE command in Procedure Builder________________________ 81
10.9 Compilation Errors___________________________________________________ 81
10.9.1 Detect compilation errors with the stored program editor _______ 81
10.9.2 USER_ERRORS __________________________________________________ 81

Page 5 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

10.9.3 Detecting compilation errors: example____________________________ 82

10.10 Debugging Information ____________________________________________ 83
10.10.1 Debugging using the DBMS_OUTPUT package________________________ 83
10.10.2 Debug the subprograms using procedure builder _______________ 84
10.10.3 Creating breakpoints ______________________________________________ 84
10.10.4 Use debugging levels ____________________________________ 85
10.10.5 Monitor the execution of programs__________________________________ 85
11 MANAGING DEPENDENCIES___________________________________________________ 87
11.1 Dependent and referenced objects___________________________________________ 87
11.1.1 Understanding Dependencies
11.1.2 Direct and indirect dependencies_________________________________ 87
11.1.3 Local dependencies____________________________________________ 87
11.1.4 Remote dependencies __________________________________________ 87
11.1.5 A scenario of local dependencies __________________________________ 88
11.2 Visualize the dependencies _______________________________________________ 88
11.2.1 Display direct dependencies with USER_DEPENDENCIES ____________ 88
11.2.2 Display direct and indirect dependencies___________________________ 89
11.2.3 Display the dependencies____________________________________________ 89
11.3 A scenario of local name dependencies ________________________________ 89
11.4 Remote Dependencies __________________________________________________ 90
11.4.1 Recompilation of dependencies ______________________________________ 90
11.4.2 Concept of remote dependencies____________________________________ 90
11.4.3 Remote dependency scenario in Timestamp mode ____________________ 91
11.4.4 The signature mode ______________________________________________ 91
11.5 Manual recompilation__________________________________________________ 91
11.5.1 Recompiling a PL/SQL program ____________________________________ 91
11.5.2 Recompilation failure _____________________________________________ 92
11.5.3 Successful recompilation ______________________________________________ 92
11.5.4 Recompile the procedures___________________________________________ 92
11.6 Packages and dependencies ________________________________________________ 92
12 MANIPULATING LARGE OBJECTS____________________________________________ 94
12.1 The LOBs ______________________________________________________________ 94
12.1.1 What is a LOB _______________________________________________ 94
12.1.2 Opposition between LONG and LOB data types______________________ 94
12.1.3 Anatomy of a LOB ________________________________________________ 94
12.2 Internal and external LOBs ________________________________________________ 95
12.2.1 The internal LOBs _________________________________________________ 95
12.2.2 Managing internal LOBs ____________________________________________ 95
12.2.3 External LOBs_________________________________________________ 95
12.2.4 Define the BFILEs _________________________________________________ 95
12.3 The DBMS_LOB package __________________________________________________ 96
12.3.1 Use of DBMS_LOB ___________________________________________ 96
12.3.2 Functions and Procedures of DBMS_LOB ________________________________ 96
12.3.3 DBMS_LOB READ and WRITE ________________________________________ 96
12.3.4 Example of creating a table with LOBs ____________________________ 97
12.4 Manipulating LOBs in SQL
12.4.1 Insertion using SQL____________________________________________ 97
12.4.2 Updating LOBs using SQL__________________________________ 98
12.4.3 Update using DBMS_LOB____________________________________ 98
12.4.4 Select CLOB values _______________________________________ 99
12.4.5 Example of LOB deletion____________________________________ 101
12.5 Temporary LOBs __________________________________________________ 102

Supinfo Laboratory of Oracle Technologies Page 6 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

1 OVERVIEW OF PL/SQL
1.1 The complete solution from Oracle
The Oracle solution consists of several tools that recognize and submit SQL and PL/SQL orders to
server to be executed. These tools have their own command language.

Figure 1: Architecture of the complete Oracle solution

1.2 The programs in PL/SQL

1.2.1 PL/SQL Program Models

Programs written in PL/SQL all follow a predetermined block structure (See Module No. 4)
Basic notion of PL/SQL $1.3 "The structures of PL/SQL programs". The different models of
programs (anonymous blocks, stored procedures and functions, triggers, packages...) are therefore constructed
following the same model.
All blocks of a PL/SQL program can be separated and nested within each other. So a
A block can represent a small part of another block which is itself a part of the program's code.

1.2.2 Structure of an anonymous PL/SQL block

An anonymous block is a block that has no names. These blocks are declared at the place where they will be used.
executed in an application. They are passed to the PL/SQL engine during program execution.

[DECLARE]
[optional declarative section]
BEGIN
<mandatory executable section>
[EXCEPTION]
[optional exception handling section]
END;

The keywords DECLARE, BEGIN, and EXCEPTION are not followed by a semicolon, only END and the others.
PL/SQL statements require a semicolon.

Page 7 / 102 Supinfo Laboratory of Oracle Technologies

March 7, 2003 [Link]
 Advanced PL/SQL - Version 1.2

1.2.3 Structure of a PL/SQL subprogram

A PL/SQL subprogram is a named block that can take parameters and be invoked in
other blocks. There are two types of subprograms: procedures and functions.

Header
IS|AS
[optional declarative section]
BEGIN
<mandatory executable section>
[EXCEPTION]
<optional exception handling section>
END;

The Header section determines how the subroutine will be called or invoked. This section
also determine the type of the subroutine (procedure or function), the list of parameters if there are any,
RETURN clause that applies only to functions.
The keyword IS is mandatory. The keyword DECLARE should not be used because the declaration section is located between
the IS and the BEGIN.
For the rest of the code, it behaves the same way as for anonymous PL/SQL blocks.

1.2.4 Advantages of subprograms

Stored procedures and functions have advantages in addition to the modular development of
applications. The sub-programs help improve:
• maintenance
Modification of online routines without interfering with other users
Modification of a routine to act on multiple applications
Modification of a routine to eliminate duplicate tests
• data integrity and security:
Control of indirect access to database objects by users who do not
not having security privileges
Ensures that the related actions are executed together, or not at all, by centralizing
the activity of linked tables in a single directory
• the performances :
Avoid re-scanning the rows for different users by leveraging the SQL zone.
shared
Avoid traversing the PL/SQL block during execution by traversing it during the
compilation
Reduces the number of calls to the database and decreases network traffic by sending the
packet orders

1.3 Development environments

1.3.1 SQL*Plus and Procedure Builder

PL/SQL is not strictly speaking an Oracle product. It is a technology used by the Oracle server.
and by certain Oracle development tools, PL/SQL blocks are passed and processed by a engine
PL/SQL. This engine can be included in the development tool or in the Oracle server.
The two main development tools are SQL*Plus and Procedure Builder.
SQL*Plus uses the Oracle server's PL/SQL engine while Procedure Builder uses the PL/SQL engine of
the client tool or the Oracle server engine.

Supinfo Laboratory of Oracle Technologies Page 8 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

1.3.2 Develop functions and procedures with SQL*Plus

There are two ways to write PL/SQL blocks in SQL*Plus. One can store them in the SQL*Plus buffer.
and then execute it from SQL*Plus or store them in a SQL*Plus script and then execute it
file using the EXECUTE command.

1.3.3 Develop using Oracle Procedure Builder

Procedure Builder is a tool that can be used to create, execute, and debug PL/SQL programs.
used in your applications or on the Oracle server through its graphical interface.
The Procedure Builder development environment has an integrated editor with which it is possible to
create and edit subprograms. It is possible to compile, test, and debug your code with this tool.

Figure 1: The Procedure Builder interface

1.4 Calling Functions and Procedures

Stored procedures and functions can be called from various environments: SQL*Plus:
Oracle Developer, Oracle Discoverer, WebDB, another stored procedure and many Oracle tools and
precompilation applications.
To launch a procedure or function from SQL*Plus, you must use the EXECUTE command followed by the name.
the file in which it is stored. With Oracle Discoverer and Oracle Developer, it is enough to specify the name
the script file.
To execute a procedure inside another, you simply need to provide the name of the procedure to the
moment wanted.

Example:

CREATE OR REPLACE PROCEDURE leave_emp

(v_id IN [Link]%TYPE)
IS
BEGIN
DELETE FROM emp
WHERE empno = v_id;
exec_proc;
END leave_emp;
This code creates a procedure that deletes the rows whose EMPNO corresponds to V_ID
entered as parameter. After the deletion, we call the procedure named EXEC_PROC.
The procedure creation structure will be explained further in this module.

Page 9 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

2 USE OF PROCEDURE BUILDER

2.1 Procedure Builder
2.1.1 The components of Procedure Builder

Procedure Builder is an integrated development environment that allows you to edit, compile, and test.
debugging PL/SQL program units for both client and server with a single tool. All of these
Features are possible thanks to the various components integrated into Procedure Builder.

Component Usage
Object Explorer Allows to manage sets and perform operations on them.
debugging
PL/SQL Interpreter Allows debugging of PL/SQL code and evaluating code
Real-time PL/SQL
Unit editor of Allows you to create and edit PL/SQL source code
programs
Unit editor of Allows you to create and edit PL/SQL source code.
stored programs server
Base trigger editor Allows you to create and edit database triggers
of data

2.1.2 Develop program units and units of

stored programs

Procedure Builder allows the development of PL/SQL subprograms that can be used in
client or server applications. Program units are PL/SQL subprograms that are
used with client applications, such as Oracle Developer. Stored program units are
PL/SQL subprograms that can be used with all applications, client or server.
The PL/SQL code is lost when closing Procedure Builder unless the code is saved on the server.
in the PL/SQL library or if we export it to a file.
There are several ways to develop PL/SQL code in Procedure Builder:
For client-side code, we can create the program unit using the program unit editor.
how to drag and drop a server-side program unit to the client using the explorer
of object.
For server-side code, we can create the program unit using the units editor.
stored programs or drag and drop a program unit from the server side to the server
using the object explorer.

2.1.3 The object browser

The object explorer is a browser that allows you to find and work with program units.
clients and servers as well as libraries and triggers. It is possible to develop and reduce the hierarchy,
copy and paste, search for an object and drag-and-drop PL/SQL program units between the client side and
server.

Supinfo Laboratory of Oracle Technologies Page 10 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Figure 1: Overview of the object explorer

Several important elements can be distinguished in the browser window:

The location indicator (1): it indicates the current location in the hierarchy
The sub-object indicator (2): allows you to expand or collapse nodes to show or hide the
Information about objects. The different classes of objects are represented by different icons.
The type (3) icon: indicates the type of the object, followed by the name of the object. In the example, the icon indicates
LOG_EXECUTION is a PL/SQL block. If you double-click on the icon, Procedure Builder opens the editor.
of program unit with the code of this object.
The name of the object (4): displays the name of the object
The search field (5): allows searching for objects

2.1.4 The program units editor

The program unit editor allows you to edit, compile, and browse warnings and errors during
the development of PL/SQL client subprograms.

Figure 2: Overview of the program unit editor

Page 11 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

Zone (1) contains all the different buttons used to create and debug program units.
The name of the program is displayed in area (2) and the
the source code of the procedure is located in area (3).
To bring the code of a subprogram into the source code panel, you need to select the name from the list.
dropdown of the Name field.

2.1.5 The stored program unit editor

The stored program unit editor appears as the editor described earlier, except that
In this case, the backup operation sends the source code to the PL/SQL compiler of the server.

2.2 Use of Procedure Builder

2.2.1 Creation of a client program unit

To create a client-side program unit, you need to select the object Program Unit or a sub-object in
the object explorer. Then click on Create to bring up the New Program Unit dialog.
In this one, we choose the name of the program as well as its type and then confirm to display the unit editor.
of the program. The editor contains the skeleton of the PL/SQL model. The cursor is automatically positioned
on the line following the BEGIN. Once the code is written, we compile it by clicking the Compile button. The
Error messages generated during compilation are displayed in the compilation message panel.
When an error is selected, the cursor moves to the location of the error in the program window.
When the program has compiled successfully, the message Successfully Compiled is displayed in the line of
editor's status.
The program units residing in the hierarchy are lost if one exits Procedure Builder. They must be
export to a file, save them in a PL/SQL library or store them in the database for
not to lose them.

2.2.2 Creation of a server program unit

To create a server-side program unit, the procedure is the same as for a program unit.
client side but this time you must select the Stored Program Unit object in the Database Objects node
the tree structure.

2.2.3 Transfer programs between the client and the server

With Procedure Builder, it is also possible to copy program units created on the client into
program units stored on the server (and vice versa). This can be done by moving the unit of
program towards the destination stored program unit in the hierarchy
The PL/SQL code stored on the server is processed by the PL/SQL engine on the server side, so an SQL query
content within a program unit does not need to be transferred between a client application and the
server.
The program units stored on the server are potentially accessible to all applications.
but depending on the user's security privileges.

2.3 The PL/SQL Interpreter

The PL/SQL interpreter is a Procedure Builder tool that allows for the direct execution of units of
PL/SQL program.

Supinfo Oracle Technologies Laboratory Page 12 / 104

[Link] 03/07/2003
 Advanced PL/SQL - Version 1.2

Figure 3: The PL/SQL interpreter

The interpreter is composed of three windows: the first displays the source code of the program, the second
contains the same information as the object explorer and finally the last one allows executing sub
programs, Procedure Builder commands, and SQL queries.
To execute subprograms, you must enter the program name at the PL/SQL prompt, specify the ...
parameters if needed, and add a semicolon.

PL/SQL> program_name [parameter1|parameter2,…];

To execute an SQL statement, simply enter the statement and place a semicolon at the end.

2.4 The TEXT_IO package

TEXT_IO is a package, part of Procedure Builder and can only be used by functions and
client-side procedures. It allows managing text input/output. This package includes a procedure
PUT_LINE which writes information to the PL/SQL interpreter window. This procedure accepts a single
parameter that corresponds to the text we want to display.

Example:

This text will be displayed

This text will be displayed

This package is very useful for debugging client procedures. However, to debug
A server procedure requires using the package provided by Oracle DBMS_OUTPUT, as TEXT_IO will produce
compilation errors on the server. The DBMS_OUTPUT package does not display messages in the
PL/SQL interpreter window when executing a procedure from Procedure Builder.

Page 13 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

3 CREATION OF PROCEDURES
3.1 Overview of Procedures
A procedure is a named PL/SQL block that can take parameters (also called arguments).
and to be invoked.
As previously described, a procedure consists of a header, a declarative section, a section
executable, and an optional exception handling section.
Procedures facilitate the reuse and manipulation of code because once saved, a procedure can
to be used by several other applications. If the definition changes, only the procedure is affected which
simplifies maintenance.

3.2 Create procedures

3.2.1 The syntax for creating procedures

To create a procedure, we use the expression CREATE PROCEDURE which defines the actions that will be
executed by the PL/SQL block. This expression can define a list of parameters.

CREATE [OR REPLACE] PROCEDURE procedure_name

(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
…)
IS|AS
PL/SQL Block

The PL/SQL block starts with a BEGIN or the declaration of local variables and ends with END or
ENDprocedure_name.It is impossible to reference host variables or variable of
substitution.
The REPLACE option indicates that if the procedure already exists, it will be deleted and replaced by the one created by
the request.

Definitions of syntax:
Parameters Description
regarding
Procedure Name of the procedure
e_name
Parameter Name of the PL/SQL variable that is passed, returned to the calling environment,
er or the two depending on the chosen mode
Mode Type of the argument:
IN (default)
OUT
IN OUT
Datatype Data type of the argument
Block Body of the procedure defining the actions to be taken
PL/SQL

3.2.2 The parameter modes of the procedure

The parameters of the procedure allow for transferring values to and from the calling environment. The
parameters each have three modes: IN, OUT, and IN OUT.

Supinfo Laboratory of Oracle Technologies Page 14 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Type of Description
parameter
re
IN (by A constant value is passed from the calling environment to the
defect procedure
OUT A value is passed from the procedure to the calling environment
IN OUT A constant value is passed from the calling environment to the
a procedure and a different value may be returned to the environment
using the same parameter.

3.2.3 Develop stored procedures

To develop a stored procedure, you must first choose a development environment such as
Procedure Builder or SQL*Plus. Then you need to enter the code using the syntax defined earlier.
In Procedure Builder, we use the program unit editor, and in SQL*Plus, we enter the text in a
text editor then we save it as a script file.
Finally, you need to compile the enp-code (pseudo-code). With Procedure Builder, you just have to click on Save and
In SQL*Plus, you need to execute the script file.

3.2.4 Develop a procedure using SQL*Plus

To create a procedure with SQL*Plus, you first need to type the text of the CREATE statement.
PROCEDURE in a text editor and then save it as a script file. Next, to compile it into p-code, it
just execute it from SQL*Plus. If the terminal returns one or more compilation errors, the
The SHOW ERRORS command allows them to be displayed. When the script is compiled without errors, it can be
executed from the Oracle server environment.
A script file with the CREATE PROCEDURE (or CREATE FUNCTION) statement allows you to make some
changes directly in the file if there are compilation errors or to make modifications
subsequent. If a procedure has been compiled and it returns compilation errors, it will not be able to
to be invoked correctly. It is therefore necessary to ensure that the compilation runs smoothly before invoking it.
When executed, the CREATE PROCEDURE (or CREATE FUNCTION) command stores the source code
in the data dictionary even if the procedure contains compilation errors. If one wants to perform
For changes, it is best to use the OR REPLACE option or you have to do a DROP of the
procedure.

3.2.5 Develop a procedure using procedure builder

[Link] Creation of a client procedure

Thanks to the integrated PL/SQL engine in the Procedure Builder application, it is possible to develop
client-side procedures. To do this, you need to select the node Program Units in the tree structure of the explorer.
select an object then click on Create to display the dialog box for creating a new unit of
program.
We enter the name of the procedure by selecting the typeProcedure (which is the one selected by default).
After validation, the program editor window appears with the name of the procedure and the keywords IS,
BEGIN and END. The cursor is automatically positioned at the line following BEGIN.
Once the source code is entered, we click on Compile. The error messages generated during the compilation
are displayed in the compilation message window. When an error message is selected in
the window, the cursor automatically positions itself at the location of the error in the source code. If the
compilation occurs correctly, a message indicating this is displayed in the editor window.
program.
We can then save the code in a file by selecting Export from the File menu.
In Procedure Builder, the keywords CREATE and CREATE OR REPLACE cannot be used.
Page 15 / 102 Supinfo Laboratory of Oracle Technologies
07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

[Link] Creation of a server procedure

One can also use the PL/SQL engine of the server to develop server-side applications. For
first you need to log in (FileÆ Connect to the database using your username and
his password. Then we expand the node Database Objects in the object explorer to do
to display the name of our schema to develop it in turn. We then select the nœudStored
Program Units of this scheme and click on Create to be able to enter the source code of this procedure.
The rest of the creation of stored procedures proceeds as described earlier for the procedures.
Once the source code is compiled, click on Save to save the procedure on the server.

[Link] Navigating Compilation Errors

Procedure Builder displays compilation errors in a separate panel that allows the developer to
easily debug your code. When an error is selected in this panel, the cursor is placed
automatically at the location of the error in the source code. Once the error is resolved, we recompile the
procedure to ensure the success of the correction.

3.3 Procedures and parameters

3.3.1 Creating procedures with parameters

Procedures can take external parameters into account during their execution. These parameters
can be passed as input (the value is used in the procedure itself), as output (the value is sent
to the calling environment) or both.

IN OUT IN OUT
By default Must be specified Must be specified
The value has passed in The value is The value has gone down.
the subprogram sent back to program then a value
the environment different is returned to
caller the calling environment
The formal parameter acts It is a variable It is an initialized variable.
like a constant uninitialized
The current parameter can Must be a Must be a variable
to be an expression variable
literal, constant or a
initialized variable

Supinfo Laboratory of Oracle Technologies Page 16 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

3.3.2 The IN parameter

Example:

SQL> CREATE OR REPLACE PROCEDURE raise_salary

2 (v_id in [Link]%TYPE)
3 IS
4 BEGIN
5 UPDATE emp
6 SET sal = sal * 1.10
7 WHERE empno = v_id;
8 END raise_salary;
9/
Procedure created.

SQL> EXECUTE raise_salary (7369)

PL/SQL procedure successfully completed.

The example shows a procedure using an IN parameter. When the RAISE_SALARY procedure is
called, the parameter is used as the employee number to execute the UPDATE statement.
To call a procedure with a parameter in SQL*Plus, the EXECUTE command is used:
SQL> EXECUTE raise_salary (7569)
To call a procedure from Procedure Builder, a direct call is used. To do this, enter the name of
the procedure and the current parameter at the prompt of the Procedure Builder interpreter:
PL/SQL> raise_salary (7369)
IN parameters are passed as constants, so if we try to modify the value of a parameter
In, an error will occur.

3.3.3 The OUT parameter

[Link] Use of the OUT parameter

The OUT parameter allows returning values obtained inside the procedure to the environment
caller. Since the default value for parameters is IN, it is necessary to explicitly specify OUT when
one wants to return a value.

Example:

SQL> CREATE OR REPLACE PROCEDURE query_emp

1 (v_id IN [Link]%TYPE,
2 v_name OUT [Link]%TYPE,
3 OUT [Link]%TYPE
4 v_comm OUT [Link]%TYPE
5 IS
6 BEGIN
7 SELECT name
8 INTO v_name
9 FROM emp
10 WHERE empno = v_id;
11 END query_emp;
12 /
Æ This script creates a procedure that accepts an external parameter and returns three.
parameters

For a procedure with one or more OUT parameters to work, it is necessary to declare as many variables.
hosts than values returned by the query. These variables must be of the same type as the values
returned. Then these variables preceded by two points (:) will be passed as parameters to the procedure.

Page 17 / 102 Supinfo Laboratory of Oracle Technologies

July 3, 2003 [Link]
 Advanced PL/SQL - Version 1.2

[Link] The OUT parameter and SQL*Plus

To execute the procedure query_emp in SQL*Plus, we first create three variables using the
variable command. Then the procedure is called by specifying an input value and the three variables.
preceded by two colons (:) for the OUT parameters. To see the values returned in the variables, we
use the PRINT command.

SQL> VARIABLE g_name VARCHAR2(15)

SQL> VARIABLE g_sal NUMBER
SQL> VARIABLE g_comm NUMBER

SQL> EXECUTE query_emp(7654,:g_name,:g_sal,:g_comm)

PL/SQL procedure completed successfully.

SQL> PRINT g_name

G_NAME
---------------
MARTIN
Æ This example displays the value of the variable g_name returned to the calling environment.
by the procedure query_emp.

To display multiple variables at the same time, simply specify all the names in the list.
PRINT. The PRINT command as well as the VARIABLE command are specific commands for
SQL*Plus.
When using the VARIABLE command to define host variables, it is not necessary to
specify a size for NUMBER type variables. A host variable of type CHAR or VARCHAR2 has a
default size of one, unless a value is specified in parentheses. To avoid creating errors it
It is necessary to ensure that the variables can hold the returned values.

[Link] The OUT parameter and procedure builder

With Procedure Builder, you also need to declare variables. The command that allows you to declare them is
.CREATE. For this command, you need to specify the data type, the variable name, and its size.
The procedure is then called as in SQL*Plus to store the returned values in the
defined variables. Then to display these values, we use the PUT_LINE procedure from the TEXT_IO package.

Example:

PL/SQL> .CREATE CHAR g_name LENGTH 10

PL/SQL> .CREATE NUMBER g_sal PRECISION 4
PL/SQL> .CREATE NUMBER g_comm PRECISION 4
PL/SQL> QUERY_EMP (7654, :g_name, :g_sal,
+> :g_comm);
:g_name || ' wins ' ||
TO_CHAR(:g_sal) || ' and a commission of '
TO_CHAR(:g_comm)
MARTIN earns 1250 and a commission of 1400
Æ This example displays the values of the variables modified by the query_emp procedure.

3.3.4 The IN OUT parameter

[Link] Create a procedure using IN OUT

The IN OUT parameter allows you to pass a value to the procedure and return a different value.
the calling environment. The returned value can either be the original, if the value is not modified by the
procedure, or any other value defined in the procedure. An IN OUT parameter behaves like
an initialized variable.

Example:
Supinfo Laboratory of Oracle Technologies Page 18 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

SQL> CREATE OR REPLACE PROCEDURE format_phone

2 IN OUT VARCHAR2
3 IS
4 BEGIN
5 v_phone_no := '(' || SUBSTR(v_phone_no,1,3) ||
6 ')' || SUBSTR(v_phone_no,4,3) ||
7 '-' || SUBSTR(v_phone_no,7);
8 END format_phone;
9/
The example shows the creation of the FORMAT_PHONE procedure that uses an IN OUT parameter to do
a format conversion on this parameter.
To run a procedure with an IN OUT parameter, you need to first create and initialize a
host variable.

[Link] Usage with SQL*Plus

To invoke the FORMAT_PHONE procedure, created earlier, in SQL*Plus we will create a variable
hosted by the VARIABLE command then initialized by a PL/SQL script.

Example:

SQL> VARIABLE g_phone_no VARCHAR2(15)

SQL> BEGIN :g_phone_no := '8006330575'; END;
2 /

PL/SQL procedure completed successfully.

SQL> PRINT g_phone_no

G_PHONE_NO
--------------------------------
8006330575

SQL> EXECUTE format_phone (:g_phone_no)

PL/SQL procedure completed successfully.

SQL> PRINT g_phone_no

G_PHONE_NO
--------------------------------
(800)633-0575
Æ The FORMAT_PHONE procedure modifies the format on the variable.
g_phone_no

[Link] Usage with Procedure Builder

In Procedure Builder, the method to invoke the FORMAT_PHONE procedure is similar to that of
SQL*Plus but the syntax is slightly different.

Page 19 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

Example:

.CREATE CHAR g_phone_no LENGTH 15

PL/SQL> BEGIN
8006330575
END;
PL/SQL> FORMAT_PHONE (:g_phone_no);
PL/SQL> TEXT_IO.PUT_LINE (:g_phone_no);
(800) 633-0575

3.3.5 Passing parameters

[Link] Methods for passing parameters

When a procedure has multiple arguments, there are several methods to specify the
parameter values: by position, by name association and by combination.

Method Description
By The values are listed in the order in which the parameters are declared.
position
By The values are listed in arbitrary order, associating each with the name
associates of the corresponding parameter using a special syntax (=>).
on of
name
By It is a combination of the two previous methods: the initial values
combined are listed by position and the rest uses the special syntax of the method by
son name association.

[Link] The DEFAULT option for parameters

When declaring parameters, one can specify a DEFAULT option following the data type.

Variable_name[IN|OUT|IN OUT]data type[DEFAULTvalue]

This option allows the user to not specify parameters when a procedure demands them. If the
parameters are not specified, the procedure will run with the value defined in the DEFAULT option.

Example:

SQL> CREATE OR REPLACE PROCEDURE add_dept

1 (v_name IN [Link]%TYPE DEFAULT 'unknown',
2 v_loc IN [Link]%TYPE DEFAULT 'unknown')
3 IS
4 BEGIN
5 INSERT INTO dept
6 VALUES (dept_deptno.NEXTVAL, v_name, v_loc);
7 END add_dept;
8/
Æ This query creates a procedure ADD_DEPT that allows adding a new
department at the DEPT table without necessarily specifying a name and a location.

Supinfo Laboratory of Oracle Technologies Page 20 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

[Link] Example of using parameters

Example:

SQL> BEGIN
2 add_dept;
3 add_dept ( 'TRAINING', 'NEW YORK');
4 add_dept ( v_loc => 'DALLAS', v_name => 'EDUCATION');
5 add_dept ( v_loc => 'BOSTON');
6 END;
7 /
PL/SQL procedure completed successfully.

SQL> SELECT * FROM dept;

DEPARTMENT NUMBER LOC

------ -------------- -------------
... ... ...
41 unknown unknown
42 TRAINING NEW YORK
43 EDUCATION DALLAS
44 unknown BOSTON
Æ This anonymous block illustrates the different ways to call a function containing
parameters. It can be seen that when a parameter is missing, it is replaced by the
value set in the DEFAULT: "unknown".
The first call of the procedure works because a DEFAULT value has been set.

3.4 Subprograms
3.4.1 Declare subprograms

A subprogram can be declared in any PL/SQL block. This is an alternative to creating

single-use procedures to call them from other procedures.
The subprogram must be declared in the declarative section of the block and must be the last element of this.
section, after all the other elements of the program. If a variable is declared after the end of the sub
This program will create a compilation error.

Example:

CREATE OR REPLACE PROCEDURE LEAVE_EMP2

(v_id IN [Link]%TYPE)
IS
PROCEDURE log_exec
IS
BEGIN
INSERT INTO log_table (user_id, log_date)
VALUES (user, sysdate);
END log_exec;
BEGIN
DELETE FROM emp
WHERE empno = v_id;
log_exec;
END leave_emp2;
Æ This query creates a procedure that accepts a parameter. This procedure includes a
subroutine that is called in the executable section of the block.

Page 21 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

3.4.2 Invoke a procedure from an anonymous block

The procedures can be called from any tool or language that supports PL/SQL.
To call a procedure from an anonymous block, you must specify its name, passing the parameters.
possible ones in parentheses, followed by a semicolon.

Example:

DECLARE
v_id NUMBER := 7900;
BEGIN
raise_salary(v_id); --call the procedure
COMMIT;
...
END;
Æ This anonymous block calls the RAISE_SALARY procedure defined earlier with it
giving the parameter v_id initialized in the declarative section.

3.4.3 Invoke a procedure from a stored procedure

Procedures can also be called from stored procedures. To do this, it is necessary to use
the name of the procedure as for the call from anonymous blocks.

Example:
SQL> CREATE OR REPLACE PROCEDURE process_emps
2 IS
3 CURSOR emp_cursor IS
4 SELECT employee number
5 FROM emp;
6 BEGIN
7 FOR emp_rec IN emp_cursor
8 LOOP
9 raise_salary(emp_rec.empno); --call the procedure
10 END LOOP;
11 COMMIT;
12 END process_emps;
13 /
Æ The PROCESS_EMPS procedure uses a cursor to process all the data from the
table EMP and pass the number of each employee to the RAISE_SALARY procedure, which
increase the salary by 10%.

3.5 Exception management

3.5.1 Exceptions handled

When developing procedures that will be called from other procedures, it is important to take into account
count the effects that handled and unhandled exceptions can have on the transaction and on the procedure
appellant.
A transaction groups all the data manipulation orders performed since the last
COMMIT. To control it, we can use the transaction control commands, COMMIT, ROLLBACK.
and SAVEPOINT.
In a procedure calling another procedure, it is important to pay attention to how the raised exceptions are handled.
affecting the transaction and whose exception is propagated.
When an exception is raised in a called program, the exception handling section takes
automatically the control of the block. If the exception is handled in this section, the block ends
correctly and control is returned to the calling program. Any DML statement executed before the exception
do not rise, remain in the transaction

Supinfo Laboratory of Oracle Technologies Page 22 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

3.5.2 Unhandled exceptions

When the exception is not handled by the exception handling section, any DML statement executed in the
the called program block is implicitly rolled back (ROLLBACK), the block ends and control is returned to
the exception handling section of the calling program.
If the exception is handled by the calling procedure, all DML statements executed in this block are
preserved in the transaction.
If the exception is not handled by the calling procedure, all DML statements executed in this block are
implicitly rolled back (ROLLBACK), the block ends and the exception is propagated to the calling environment.

3.6 Delete procedures

When a procedure is no longer used, one can use an SQL command in SQL*Plus or in the interpreter of
Procedure Builder to delete (DROP). The deletion method differs slightly depending on the type of
the procedure (client or server).
In SQL*Plus, you can only delete server procedures, whereas in Procedure Builder the
server and client procedures can be deleted.

3.7 Remove server procedures

[Link] With SQL*Plus

In SQL*Plus, the DROP PROCEDURE command is used to delete the desired server procedure.
DROP PROCEDURE procedure_name

Example:

SQL> DROP PROCEDURE raise_salary;

Procedure deleted.
Æ This order allows you to remove the RAISE_SALARY procedure created earlier.

It is impossible to perform a ROLLBACK after having deleted a procedure because a command of

The definition of data (DDL) cannot be undone.

[Link] With Procedure Builder

To delete a server procedure in Procedure Builder, you first need to connect to the database.
data. In the Object Explorer, we expand the Database Object node to display the different
available schemas. Then we develop the schema of the procedure owner and the Stored node.
Program Units (Stored Program Units) to display all existing procedures.
then select the procedure to delete and click on Delete in the object explorer. A message
A confirmation appears, then we click on Yes to permanently delete it.
To delete a procedure from the server, you can also click on Drop in the program editor.
stored.

3.8 Delete client procedures

To delete a client procedure with Procedure Builder, expand the node Program Units, then you
select the procedure we want to delete. We then click on Delete in the object explorer and
we click on Yes when the confirmation dialog appears.
If the procedure code has been exported to a text file and you want to delete it, you must go through the
operating system.

Page 23 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

4 FUNCTION CREATION
4.1 Functions
4.1.1 Overview of Stored Functions

A stored function is a named PL/SQL block that can accept parameters and be called from the
the same way as procedures. Generally, a function is used to calculate a value. The
procedures and functions have a similar structure except that a function must return a value to
the calling environment.
Like procedures, functions are composed of four parts: a header, a declaration section,
an executable part and an optional error handling part. A function must have a RETURN clause
in the header and at least one RETURN in the executable part.
Functions facilitate reuse and maintenance. Once validated, functions are stored in the
database as a database object and can thus be reused in many
applications. If the definition changes, only the function is affected, which allows for simple maintenance.
Functions can be called in an SQL expression or in a PL/SQL expression. In a
SQL expression, the function must adhere to certain syntax rules to control side effects.
In a PL/SQL expression, the function identifier behaves like a variable whose value
depends on the parameter that is passed to it.

4.1.2 Syntax for creating functions

To create a function, we use the CREATE FUNCTION command, in which we can specify a list.
of parameters. In this command, we must define the value that will be returned to the calling environment and
define the actions performed by the standard PL/SQL block.

CREATE [OR REPLACE] FUNCTION function_name

(parameter1[mode1]datatype1,
parameter2[mode2]datatype2,
. . .)
RETURNdatatype
IS|AS
PL/SQL Block;

Parameter Description
e
Function_ Function name
name
Argument Name of the PL/SQL variable whose value is passed to the function
Mode The parameter type; only the IN parameter must be declared
Datatype Parameter data type
RETURN Data type of the RETURN value that must be returned by the
datatype function
PL/SQL Body of the procedure defining the actions taken by the
Block function

The REPLACE option indicates that if the function already exists, it will be replaced by the new version created by
the request.
The data type of the RETURN must not have a specified size.
The PL/SQL block starts either with a BEGIN or with a section for declaring local variables and then
It must end with END or ENDfunction_name. There must be at least one RETURN expression.
(variable). It is impossible to reference host variables or substitution variables in the
PL/SQL block of a stored function.

Supinfo Laboratory of Oracle Technologies Page 24 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

4.1.3 Function Creation

The method of creating a function is similar to that of creating a procedure. First, one chooses a
development environment (Procedure Builder or SQL*Plus) in which the syntax of
creation. Then we compile the code to obtain dup-code.
The use of multiple RETURN statements in a PL/SQL block is allowed, but during compilation
only one will be taken into account and therefore only one value will be returned during execution. We use
usually multiple RETURNs when the block contains an IF condition.

4.2 Functions in SQL*Plus

4.2.1 Creation of stored functions

To create a stored function from SQL*Plus, we type the expression CREATE FUNCTION in a
text editor, then we save this file as a script file (*.sql). This script file must then be
executed in SQL*Plus to compile the code. If the compilation produces errors, we use the
command SHOW ERRORS to correct them. Once the compilation is done without errors, we call the
function from an Oracle Server environment.
A script file containing the CREATE FUNCTION statement allows us to modify the query if there are any
compilation or execution errors or to make later changes. It is impossible to call a
function containing compilation or execution errors.
The execution of the CREATE FUNCTION command stores the source code in the data dictionary
even if the function contains compilation errors. Therefore, it is necessary to drop the function (DROP) or else
use the OR REPLACE syntax if you want to make changes to a function.

Example:

SQL> CREATE OR REPLACE FUNCTION get_sal

2 (v_id IN [Link]%TYPE)
3 RETURN NUMBER
4 IS
5 v_salary [Link]%TYPE :=0;
6 BEGIN
7 SELECT salary
8 INTO v_salary
9 FROM emp
10 WHERE empno = v_id;
11 RETURN (v_salary);
12 END get_sal;
13 /

Function created.
Æ This query creates a function that accepts an IN parameter and returns a value of
type NUMBER corresponding to the salary of the employee whose number was provided in
parameter.

4.2.2 Execute functions

Functions are called within PL/SQL expressions. For the function to execute properly
you need to create a host variable to store the returned value. At runtime, the host variable is
initialized with the value returned by the RETURN.
A function can accept multiple IN parameters but it must return only one value.

Example:

Page 25 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

SQL> START get_salary.sql

Function created.

SQL> VARIABLE g_salary number

SQL> EXECUTE :g_salary := get_sal(7934)
PL/SQL procedure completed successfully.

SQL> PRINT g_salary

G_SALARY
----------
1300
Æ This example creates a host variable in which the returned value is stored.
function using the parameter (employee number). The EXECUTE command allows here to
execute a PL/SQL expression using SQL*Plus

4.3 Functions in Procedure Builder

4.3.1 Function Creation

Since there is a PL/SQL engine in the client tool of Procedure Builder, it is possible to develop ...
client-side functions. With Procedure Builder, you can also use the PL/SQL engine of the server to
develop server-side functions.
The drag-and-drop feature of Procedure Builder allows for easy movement of functions between the
client and the server.

4.3.2 Example of function creation

To create a function with Procedure Builder, select the Program Units node in the explorer.
Then we click on Create. We choose a name for the function in the creation dialog box.
of a new program unit. In the example, we choose Tax. We then choose the type Function and then we
click on OK to open the program editor window. Then we type the code before
click on Compile.

FUNCTION tax
(v_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (v_value * .08);
END tax;
Æ This code creates a function that calculates the tax on a given salary as an argument.

If the compilation is done without errors, the message Successfully Compiled is displayed. Otherwise, the errors are
reported on the error display panel.
It is necessary to avoid using OUT and IN OUT parameters with functions because they are designed to return
a unique value.

4.3.3 Execute functions

To execute a function in Procedure Builder, you must first create a host variable to store it.
returned value. For this, we use the CREATE syntax at the prompt of the PL/SQL interpreter.
We then create a PL/SQL expression calling the TAX function by passing a value as an argument.
digital. The two points (:) indicate that one is referring to a host variable.
We observe the result of the function using the PUT_LINE procedure of the TEXT_IO package.

Example:
Supinfo Laboratory of Oracle Technologies Page 26 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

PL/SQL> .CREATE NUMBER x PRECISION 4

:x := tax(1000);
PL/SQL> TEXT_IO.PUT_LINE (TO_CHAR(:x));
80

4.4 User-defined functions in SQL

4.4.1 Advantages of functions in SQL expressions

SQL expressions can reference user-defined PL/SQL functions.

User-defined functions can be used wherever a SQL function can be used.
User-defined functions allow for complex calculations, clumsy or not
available in SQL more easily.
They also increase data independence by performing complex data analyses.
server level in Oracle rather than having it done by the applications.
They increase the efficiency of queries by executing the functions in the query rather than in
the application.
They also allow for the manipulation of new types of data (for example, latitudes and
longitudes) by encoding the strings and using functions to act on these strings.

Example:

SQL> SELECT employee number

2 FROM emp;

Employee Number SAL TAX(SAL)

---------- ---------- ---------- ----------
7369 SMITH 800 64
7499 ALLEN 1600 128
7521 WARD 1250 100
…
7902 FORD 3000 240
7934 MILLER 1300 104

14 line(s) selected.
Æ This SQL order uses the TAX function in its SELECT list

4.4.2 Locations from which to call functions

User-defined PL/SQL functions can be called from any SQL expression in

which a predefined function can be used.
That is to say that one can use a PL/SQL function in the list of a SELECT order, in the conditions of
WHERE and HAVING clauses, in the CONNECT BY, START WITH, ORDER BY and GROUP BY clauses, in
the VALUES clause of an INSERT statement and in the SET clause of an UPDATE statement.

4.4.3 Function calls: restrictions

In order to be called from an SQL expression, a user-defined function must comply with
certain conditions :
• Only stored functions can be used. Stored procedures cannot be.
called.
• A user-defined function used in SQL must be a SINGLE-ROW function and not a
group function.
• These functions only accept IN parameters, not OUT or IN OUT.

Page 27 / 102 Supinfo Laboratory of Oracle Technologies

July 3, 2003 [Link]
 Advanced PL/SQL - Version 1.2

• The returned data types must be valid SQL data types: CHAR, VARCHAR2,
DATE or NUMBER. The data types specific to PL/SQL (BOOLEAN, RECORD, TABLE) do not
cannot be used.
• The function must not modify the database, therefore the INSERT, UPDATE or
DELETE statements are prohibited in a function called from SQL.
• The parameters of a PL/SQL function called from a SQL expression must use the notation by
The position by name is not supported in SQL.
• One must own the function or have the EXECUTE privilege on it in order to be able to call it.
from an SQL order.
• Stored PL/SQL functions cannot be called from the CHECK clause of a
The CREATE or ALTER TABLE command cannot be used to specify a default value for a
column.
• The called functions must not invoke another subprogram that does not comply with these.
rules.
The use of PL/SQL functions in SQL statements has been available since PL/SQL 2.1. Tools using a
Older versions of PL/SQL do not support this feature.

4.5 Remove functions

4.5.1 Delete server functions

When a stored function is no longer used, it can be deleted using an SQL command in SQL*Plus.
or by using the Procedure Builder interpreter panel to perform a DROP.
To delete a function on the server side in SQL*Plus, the DROP FUNCTION command is used.
DROP FUNCTION function_name

Example:

SQL> DROP FUNCTION get_sal;

Function deleted.
Æ This query deletes the function get_sal.

DROP FUNCTION is a DDL command, so it is auto-committed and cannot be rolled back by a ROLLBACK.

To delete a stored procedure using Procedure Builder, you must first connect to the database.
data. We then expand the Database Objects node, followed by the schema of the function owner. We
select the function we want to delete and then click on Drop in the object explorer. We must
then confirm the deletion in the warning window that appears.

4.5.2 Remove client functions

To remove a function on the client side, we use Procedure Builder. To do this, we develop the node Program.
In Unitspuis, we select the function we want to delete. We then click on 'Delete' in the explorer.
of objects by confirming the deletion in the dialog box that appears.
If the source code of the function has been exported to a text file and we want to delete it from the client, we need to
use the features of the operating system.

4.6 Procedure or function?

4.6.1 Summary

Procedures are created to store a series of actions to be executed later. A procedure can
accept or not parameters, which are not limited in number and can be transferred to and from
the calling environment. A procedure does not necessarily return a value.
Functions are created to compute a value; they must return a value to the environment.
caller. A function can accept or not accept parameters that are passed from the environment. A
A function can only return a single value and cannot accept OUT or IN OUT parameters.
Supinfo Oracle Technologies Laboratory Page 28 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

However, if one declares it, there will be no compilation errors, but it is advisable to never do so.
to use.

4.6.2 Comparison between procedures and functions

Procedure Function
Executes like a PL/SQL request Is called in a
expression
No data type RETURN Must have a data type
RETURN
Can return one, none or Only return one
multiple values value

A procedure containing a single OUT parameter can be rewritten as a function using the
OUT parameter for the returned value.

4.6.3 The advantages of stored procedures and functions

In addition to providing modular development of applications, stored procedures and functions have
other advantages:
• Performance improvement
Avoid reparsing the lines for different users by utilizing the shared SQL area.
Avoid PL/SQL parsing at runtime by parsing during compilation.
Reduce the number of calls to the database and decrease network traffic by sending the
orders by batches.
• Improvement of maintenance
Ability to modify routines online without interfering with other users
Possibility to modify a routine to affect all applications.
Possibility to modify a single routine to avoid duplicate testing
• Improvement of data integrity and security
Indirect control of access to database objects by users who do not own
no security privileges
Ensuring that the related actions are executed together, or not at all, by centralizing.
the activity of linked tables.

Page 29 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

5 PACKAGE CREATION
5.1 The packages
5.1.1 Overview of packages

Packages are groups that include types, elements, and PL/SQL sub-programs.
logically associated. For example, a Human Resources package could contain the procedures
hiring and firing, commission and bonus functions and tax exemption variables.
Generally, a package consists of two parts stored separately in the database: the
specification and the body.
The specification is the interface for applications. It declares types, variables, constants,
exceptions, cursors, and subprograms that can be used in the package.
The body defines the sliders and sub-programs as well as what has been defined in the specification.
The package itself cannot be called, receive parameters, or be nested. However, a package
has the same format as a subroutine. Once written and compiled, the content can be shared by multiple
applications.
When an element of a package is called for the first time, the entire package is loaded into memory. By
Consequently, the following calls to the element in question do not require writing or reading to the disk.

5.1.2 The components of a package

Figure 1: Structure of a package

On the diagram, we distinguish the declaration areas: for public variables (1), procedures
public (2), private procedures (3), package-specific private variables (4) and variables
specific locales to the procedure (5).
The package is created in two parts: first, we define the specification and then we create the body of the package.
The public elements of a package are those that are declared in the specification and that are defined in the
body. The private members of a package are those that are defined only within the body.

Carried Description Location in the package

and of
the element
ent
Public Can be used from anywhere Declared in the specification of
what environment of the Server package and can be defined in the
Supinfo Laboratory of Oracle Technologies Page 30 / 104
[Link] March 7, 2003
 Advanced PL/SQL - Version 1.2

Oracle. package body

Private May be used by others Declared and defined in the body of
elements were part of the same package
package only

The Oracle Server stores the specification and body of the package separately in the database, which
allows changing the definition of an element of the package body without Oracle having to disable others
objects of the schema that call upon or refer to this element.

5.1.3 Referencing the objects of a package

The variables defined in the package do not all have the same visibility, meaning that depending on where one is
we will not be able to use certain variables.

Figure 2: Scope of the elements of a package

The elements of a package can have local or global visibility.

A variable with local visibility is a variable defined in a subprogram of the package. This
the variable will not be visible to external users.
Private variables (4) are local variables for the package. These are defined in the body of the
package. These variables are accessible only by other objects in the same package but are not
visible by sub-programs or objects outside the package.
Local variables (5) are local variables for procedures. These are defined at the level of the
function or procedure. These variables are accessible only by the subprogram in which
They are declared. They are not visible to any other sub-program, even those contained within the
same package.
A variable with global visibility is a variable defined in the package specification. This
variable can be used and modified outside the package and is visible to external users.

5.2 Create packages

5.2.1 Development of a package

To develop a package, first choose a development environment: SQL*Plus or

Procedure Builder.
Then we tape the syntax. Under Procedure Builder, we use the program units editor and under
In SQL*Plus, we use a text editor and then save the code as a script file.

Page 31 / 102 Supinfo Laboratory of Oracle Technologies

March 7, 2003 [Link]
 Advanced PL/SQL - Version 1.2

Finally, we compile the package code. The source code is compiled into p-code. To compile the package under
In Procedure Builder, you just need to click on Save, under SQL*Plus you run the created script file. Once the code
compiled source, it is stored in the data dictionary.
To facilitate further development, it is advisable to save in separate text files the
CREATE PACKAGE code: one file for the specification and one for the body.
A package specification can exist without being associated with a body, but the opposite is not true.
A package's body must always be associated with a specification.
If the code of an existing procedure has been incorporated into the package, it is advisable to remove it.
independent procedure using the DROP command.

5.2.2 Creation of the package specifications

To create the specification of a package, the CREATE PACKAGE command is used. All must be specified
Public structures in the package specification. The REPLACE option can be specified if the specification
the package already exists. If necessary, we initialize the variables with a constant or a formula in the
specification, otherwise the variable is implicitly initialized to NULL.

CREATE [OR REPLACE] PACKAGE package_name

IS|AS
public type and item declarations
subprogram specifications
ENDpackage_name;

Parameter Description
Package_name Package name
Public type and item Declare the variables, the constants, the cursors,
declarations exceptions
Subprogram Declare the PL/SQL subprograms
specifications

5.2.3 Declaration of public elements

In the package specification, public variables, public procedures, and functions are declared.
public. Public functions and procedures are routines that can be called multiple times.
taken over by other elements of the same package, or from outside the package.
To declare public elements, we specify the type of the element followed by its name, in the section of
specification.

CREATE [OR REPLACE] PACKAGE package_name

IS|AS
TYPEname [parameter]
ENDpackage_name;

5.2.4 Creation of package specification: example

Example:

SQL> CREATE OR REPLACE PACKAGE comm_package IS

2 PROCEDURE reset_comm
3 NUMBER
4 END comm_package;
5 /
Æ This request creates a package declaring

Supinfo Laboratory of Oracle Technologies Page 32 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

5.2.5 Creation of the package body

To create the body of the package, the CREATE PACKAGE BODY command is used. In the body of the package
We define all the public and private elements of the package. We can specify the REPLACE option to remove.
and replace an already existing version.

CREATE [OR REPLACE] PACKAGE BODY package_name

IS|AS
private type and item declarations
subprogram bodies
ENDpackage_name;

Parameter Description
Package_name Package name
Private type and item Declaration of variables, constants, cursors, exceptions
declarations or types
Subprogram bodies Definition of public and private PL/SQL subprograms

The order in which the subprograms are defined is very important. Variables must be declared first.
that another variable or a sub-program refers to it. Sub-programs must be declared or defined.
private before being called from other sub-programs. Most often, all the variables and sub-
private programs are declared first in the package body and then the sub-
public programs.

5.2.6 Public and private elements

To clarify and modularize the code of public procedures and functions as much as possible, one can define
private functions and procedures whose visibility is limited to the package.
To create private functions and procedures, you need to enter the classic syntax of this sub-
program as explained earlier in this course.
When coding the package body, the definition of a private subprogram must be before that of a sub-
public program.

Page 33 / 102 Supinfo Laboratory of Oracle Technologies

03/07/2003 [Link]
 Advanced PL/SQL - Version 1.2

5.2.7 Creating Package Bodies: Example

Example:

SQL> CREATE OR REPLACE PACKAGE BODY comm_package

2 IS
3 FUNCTION validate_comm
4 (v_comm IN NUMBER)
5 RETURN BOOLEAN
6 IS
7 v_max_comm NUMBER;
8 BEGIN
9 SELECT max(comm)
10 INTO v_max_comm
11 FROM emp;
12 IF v_comm > v_max_comm
13 THEN RETURN(FALSE);
14 ELSE RETURN(TRUE);
15 END IF;
16 END validate_comm;
17 PROCEDURE reset_comm
18 (v_comm IN NUMBER)
19 IS
20 l_comm NUMBER := 10;
21 BEGIN
22 IF validate_comm(v_comm)
23 THEN l_comm := v_comm;
24 ELSE
25 RAISE_APPLICATION_ERROR
26 (-20210,’Invalid commission’);
27 END IF;
28 END reset_comm;
29 END comm_package;
30 /
Æ This package defines a function that validates the commission, it must not be
higher than the highest touched by the employees. It also defines a procedure that
call the previous function to reset and validate the found value.

5.2.8 Directives for developing packages

We need to try to make packages as general as possible in order to be able to reuse them in
future applications. It is also important to avoid creating packages that duplicate functions provided by Oracle.
The package specification defines the structure of the application, so it is essential to always define the specification first.
the body of the package.
The package specification should only contain the elements that need to be visible to other users.
package. This way, other developers will not be able to misuse it by basing their code on it.
inappropriate elements.
To reduce the need for recompilation when the code changes, it is necessary to place the smallest possible amount.
elements in the package specification. Changes in the package body do not require
the recompilation of dependent elements, while changes in the package specification
requires recompilation of all stored sub-programs referencing the package.

5.2.9 Global variables

One can also create package specifications that do not require package bodies. These
package details consist solely of the declaration and initialization of public variables. The
Public (global) variables are variables that will exist for the duration of the user's session.

Supinfo Laboratory of Oracle Technologies Page 34 / 104

[Link] 03/07/2003
 Advanced PL/SQL - Version 1.2

Example 1:

SQL> CREATE OR REPLACE PACKAGE global_vars IS

2 mile_2_kilo CONSTANT NUMBER := 1.6093;
3 kilo_2_mile CONSTANT NUMBER := 0.6214;
4 yard_2_meter CONSTANT NUMBER := 0.9144;
5 CONSTANT NUMBER := 1.0936;
6 END global_vars;
7 /
Æ This example shows a package containing only conversion rates in the form of
global variables.

Example 2:

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE

20 miles = 32.18688 km
20 miles = 32.186 km

PL/SQL procedure completed successfully.

Æ This example shows that a package body is not required for this
package specification can run correctly.

5.3 Manipulating packages

5.3.1 Execute a public procedure of a package

A public procedure is declared in the package specification and defined in the package body.
Therefore, it is possible to call it directly from the SQL*Plus environment. To do this, we use
the EXECUTE command by specifying the name of the package, the procedure, and the arguments, if any are needed.
to provide.

SQL> EXECUTE package_name.procedure_name(parameters)

Example :

SQL> EXECUTE comm_package.reset_comm(15)

Æ This SQL order calls the RESET_COMM procedure from the COMM_PACKAGE package in
giving an argument. This order can be executed at any time during the
work session.

5.3.2 Invoking package elements

Once the package is stored in the database, it is possible to call on a package element.
from the package itself or outside the package. This characteristic depends on the type of the element: if
it is private only the first case is possible, if it is public both cases are possible.
When calling a procedure or function within the same package, it is not necessary to
Specify the package name.

Page 35 / 102 Supinfo Laboratory of Oracle Technologies

March 7, 2003 [Link]
 Advanced PL/SQL - Version 1.2

Example 1:

CREATE OR REPLACE PACKAGE BODY comm_package IS

...
PROCEDURE reset_comm
(v_comm IN NUMBER)
IS
l_comm NUMBER := 10;
BEGIN
IF validate_comm(v_comm)
THEN l_comm := v_comm;
ELSE
RAISE_APPLICATION_ERROR
(-20210, ’Invalid commission’);
END IF;
END reset_comm;
END comm_package;
Æ This procedure calls the VALIDATE_COMM function which is in the same package.
COMM_PACKAGE so its name is not specified.

When calling a procedure or function from outside a package, it is necessary to specify the name of the
subprogram as well as the package name. If the package is in a different schema, it must be specified.
the name of the schema before the name of the package. We can also call a package located on another
database. To do this, we add an at symbol followed by the name of the database.

Example 2:

SQL> EXECUTE comm_package.reset_comm(15)

Æ This order executes the RESET_COMM procedure located in the package
COMM_PACKAGE.

Example 3:

SQL> EXECUTE scott.comm_package.reset_comm(15)

Æ This order executes the RESET_COMM procedure located in the package
COMM_PACKAGE itself located on the SCOTT schema

Example 4:

SQL> EXECUTE comm_package.reset_comm@ny(15)

Æ This order executes the RESET_COMM procedure located in the package
COMM_PACKAGE located on the NY database

5.3.3 Referencing a public variable from a procedure

autonomous

Public (global) variables are visible outside of the package, so it is possible to reference them in
independent procedures. To do this, we specify its name as well as the name of the package from which it originates.

Supinfo Laboratory of Oracle Technologies Page 36 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Example:

SQL> CREATE PROCEDURE meter_to_yard

2 (v_meter IN NUMBER,
3 v_yard OUT NUMBER)
4 IS
5 BEGIN
6 v_yard := v_meter * global_vars.meter_2_yard;
7 END meter_to_yard;
8/

SQL> VARIABLE yard NUMBER

SQL> EXECUTE meter_to_yard (1, :yard)
PL/SQL procedure completed successfully.
SQL> PRINT yard
YARD
---------
1.0936
Æ This example creates a procedure for converting meters to yards using a
global variable of the package GLOBAL_VARS. This function is then called to convert
one meter.

Independent procedures can appeal to all types of data that can be declared in the
specification of a package (variables, cursors, constants or exceptions). Each time, it is necessary to specify the name
of the package in addition to the name of the element.

5.3.4 Remove packages

When a package is no longer used, it can be deleted using an SQL command in SQL*Plus or in
Procedure Builder. In SQL*Plus, we use the commands DROP PACKAGE BODY and DROP PACKAGE.

DROP PACKAGE BODY package_name

DROP PACKAGE package_name

In Procedure Builder, click on DROP when the procedure is selected in the object explorer.
As a package consists of two distinct parts, one can choose to delete the entire package or
It is fine to remove only the body and keep the package specification. It is impossible to remove.
only the specification since we cannot have a package body not related to a specification.

5.3.5 Advantages of packages

The use of packages is an alternative to creating independent functions and procedures and offers
many advantages.
This brings great modularity as it encapsulates logically related program structures in
a module named. Each package is easy to understand and the connection between the packages is simple, clear
and well defined.
We also obtain a simpler application model. All we need at the beginning are
information about the interface in the package specification. It is possible to compile and code a
package specification without body. Then the stored subprograms referencing the package
can be compiled. It is not mandatory to have the entire package body when developing a
application.
Packages allow concealing information from other users. Indeed, one can decide whether a
element will be public (visible and accessible to everyone) or private (hidden and inaccessible outside the package). The
The package caches the definition of private elements and so if the package definition changes, only this one is
affected, and the changes are transparent to the application. Moreover, the integrity of the package is protected by
hiding the implementation details from users
Packages add features to the database: public variables and cursors are
stored in memory for the duration of the session. Therefore they can be shared by everyone

Page 37 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

sub-programs executing in the environment. Moreover, they allow for data to be maintained between the
transactions without having to store them in the database.
The performance is improved because when we call a package for the first time, it is fully
loaded into memory which avoids subsequent disk accesses when the package is called again.
Packages allow for the overriding of procedures and functions, which means that one can create
multiple subprograms with the same name in the same package, each taking parameters of
types or different numbers.

Supinfo Laboratory of Oracle Technologies Page 38 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

6 COMPLEMENTS ON PACKAGES
6.1 The overload
Overloading allows using the same name for different subprograms within a single package.
The different sub-programs are distinguished by the name, the number, or the order of the arguments of the sub-
program. Sometimes the processing in two sub-programs is the same, so in this case it makes sense to
give them the same name. The PL/SQL engine determines which procedure it is by analyzing the
formal parameters. Only local subprograms and package subprograms can be
overloaded.
The overloading feature has some restrictions; one cannot overload:
Two sub-programs whose initial parameters differ only by name or mode of
parameter.
Two sub-programs whose initial parameters differ only by the data type and these
types belong to the same family (number types and decimal types belong to the same family)
Two subprograms whose initial parameters differ only by the subtype and these sub-
types are based on types from the same family (VARCHAR and STRING are subtypes of
VARCHAR2
Two functions that only differ in the type of data returned, even if these types are of the same family.
different
When calling a subprogram, the compiler tries to find a package declaration.
corresponding to the called subprogram. The search first takes place in the current field of vision.
then extends to the nested fields of vision if necessary, it stops when the compiler has found one or
multiple sub-program declarations where the name matches that of the call. If it finds
several programs having the same name at the same level of visibility, the compiler analyzes the number,
the order and the type of data to find the correspondence between the initial parameters and the parameters
current.

Example 1:

CREATE OR REPLACE PACKAGE over_pack

IS
PROCEDURE add_dept
(v_deptno IN [Link]%TYPE,
unknown
v_loc IN [Link]%TYPE DEFAULT 'unknown');
PROCEDURE add_dept
(v_name IN [Link]%TYPE DEFAULT 'unknown',
v_loc IN [Link]%TYPE DEFAULT 'unknown');
END over_pack;
CREATE OR REPLACE PACKAGE BODY over_pack
IS
PROCEDURE add_dept
(v_deptno IN [Link]%TYPE,
v_name IN [Link]%TYPE DEFAULT 'unknown',
v_loc IN [Link]%TYPE DEFAULT 'unknown')
IS
BEGIN
INSERT INTO dept
VALUES (v_deptno, v_name, v_loc);
END add_dept;
PROCEDURE add_dept
(v_name IN [Link]%TYPE DEFAULT 'unknown',
v_loc IN [Link]%TYPE DEFAULT 'unknown')
IS
BEGIN
INSERT INTO dept
VALUES (dept_deptno.NEXTVAL,v_name,v_loc);
END add_dept;
END over_pack;
Æ We create a package and its specification. This package consists of two procedures with the same name.
but with a different number of arguments.
Page 39 / 102 Supinfo Laboratory of Oracle Technologies
March 7, 2003 [Link]
 Advanced PL/SQL - Version 1.2

If you call the ADD_DEPT procedure by explicitly specifying a department number, the engine
PL/SQL uses the first procedure. If no department number is specified, it will use the
second version.

Example 2:

EXECUTE OVER_PACK.ADD_DEPT (76, 'MARKETING', 'ATLANTA')

EXECUTE OVER_PACK.ADD_DEPT ('SUPPORT', 'ORLANDO')
Æ The first command will execute the first procedure of the package while the
second will launch the second version.

6.2 The early declarations

PL/SQL does not allow forward references, meaning that an identifier must be declared before
be able to use it. Therefore, a subroutine must be declared before it can be called.

Example 1:

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;
Æ In this example, the reference is illegal because the CALC_RATING procedure does not
still declared.

To correct an illegal reference issue, it is enough to reverse the order of the two references to the procedure.
However, this technique does not always work, for example if all the procedures call each other.
they or if one wants to define the procedures in alphabetical order.
To solve this problem, there is a special subprogram declaration in PL/SQL called
preliminary declaration. It is actually the specification of a subprogram finished with a semicolon. One can
use forward declarations for:
Define subprograms in alphabetical or logical order
Define mutually recursive programs (these are programs that call each other)
others directly or indirectly
Group sub-programs into a package

Supinfo Laboratory of Oracle Technologies Page 40 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Example 2:

CREATE OR REPLACE PACKAGE BODY forward_pack

IS

PROCEDURE calc_rating(...); early declaration

PROCEDURE award_bonus(. . .)
IS -- defined subprograms
BEGIN -- in alphabetical order
calc_rating(. . .);
...
END;

PROCEDURE calc_rating(. . .)
IS
BEGIN
...
END;

END forward_pack;
Æ The anticipatory declaration allows the use of the CALC_RATING procedure before that.
that it may not be defined.

When using forward declarations, it is important to ensure that the list of initial parameters appears.
in the forward declaration and in the body of the sub-program.
The body of the subprogram can be placed anywhere after the forward declaration but it must be located
in the same program unit.
The use of forward declarations allows grouping several related sub-programs within a package. The
Specifications of the sub-program will be found in the package specification and the bodies of sub-.
programs will be found in the body of the package, from where they will be invisible to the applications. From this
Packages allow for masking the details of the implementation.

6.3 Creation of a one-time use procedure

A one-time procedure is a procedure that is executed only once, when the package is
executed in the user's session.

Example:

CREATE OR REPLACE PACKAGE taxes

IS
tax NUMBER;
... -- declarations of public functions/procedures
END taxes;

CREATE OR REPLACE PACKAGE BODY taxes

IS
... -- declarations of private variables
... definition of private/public procedures/functions
BEGIN
SELECT rate_value
INTO tax
FROM tax_rates
WHERE TAX
END taxes;
Æ In this example, the value of TAX is set during the first execution of the package.
TAXES. The value taken by TAX then corresponds to the value contained in the table.
TAX RATES

6.4 Restrictions on package functions in SQL

Page 41 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

When the Oracle server executes a SQL statement calling a stored function, it must know if the function
has side effects or not. Side effects are all changes made to a
database tables or on public package variables (those declared in the specification)
The side effects can delay the execution of a request by producing results.
dependents on the order (but nevertheless indeterminate) or by requiring the package to maintain variables
state of the package beyond the user's session (which is prohibited). Therefore, the restrictions
the following apply to functions called in SQL orders:
The function must not modify the database tables, so it cannot execute
of the INSERT, UPDATE, or DELETE type
Functions that read or write package variable values cannot be executed at
distance or in parallel
Only functions called from SELECT, VALUES, or SET clauses can write the
package variable values
The function cannot call a subroutine that violates one of the previous rules. From
Even a function cannot refer to a view that violates one of these rules.

6.5 Invoke a function from a user-defined package

in a SQL order
In SQL, PL/SQL functions are called in the same way as predefined SQL functions:
the name of the package from which the function comes is specified followed by the name of the function and the arguments in between
parentheses if necessary.

Example:

SQL> SELECT taxes_pack.tax(sal), sal, ename

2 FROM empty
3 /
TAXES_PACK.TAX(SAL) SALT NAME
------------------- --------- ----------
400 5000 KING
228 2850 BLAKE
196 2450 CLARK
238 2975 JONES
100 1250 MARTIN
128 1600 ALLEN
120 1500 TURNER
76 950 JAMES
100 1250 WARD
240 3000 FORD
64 800 SMITH
240 3000 SCOTT
88 1100 ADAMS
104 1300 MILLER
14 rows selected.
Æ The TAX function from the TAXES_PACKAGE is called in the SELECT order.

6.6 The persistent state

6.6.1 Variable packages

It is possible to keep track of the state of a variable or a package cursor. This state is maintained
throughout the entire duration of the user's session, from the moment he first refers to the
variable at the moment he disconnects.
The Oracle server keeps a record of the successive values of package variables over the course of the
session. Each time a value is initialized or modified, it keeps track of the old and the new one.
value. When the user logs out, the value of the variable is released.
Example:

CREATE OR REPLACE PACKAGE comm_package IS

Supinfo Laboratory of Oracle Technologies Page 42 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

g_comm NUMBER := 10; --initialized to 10

PROCEDURE reset_comm (v_comm IN NUMBER);
END comm_package;
CREATE OR REPLACE PACKAGE BODY comm_package IS
FUNCTION validate_comm (v_comm IN NUMBER) RETURN BOOLEAN
IS v_max_comm NUMBER;
BEGIN
... - validates the commission so that it is not higher
... - at the current maximum salary
END validate_comm;
PROCEDURE reset_comm (v_comm IN NUMBER)
IS BEGIN
... - call VALIDATE_COMM. If we try to reset the
... - commission at an amount greater than the maximum,
... - an error is raised (RAISE_APPLICATION_ERROR)
END reset_comm;
END comm_package;
Æ This example shows the different values of the variables during execution of the
package

6.6.2 Cursor Packages

It is possible to create cursor packages. Creating such a package is useful to simplify the FETCH.
of a defined number of lines through public procedures.
Example 1:

SQL> CREATE OR REPLACE PACKAGE pack_cur

2 IS
3 CURSOR c1 IS SELECT empno FROM emp
4 ORDER BY empno DESC;
5 PROCEDURE proc1_3rows;
6 PROCEDURE proc4_6rows;
7 END pack_cur;
8 /

SQL> CREATE OR REPLACE PACKAGE BODY pack_cur

2 IS
3 v_empno NUMBER;
4 PROCEDURE proc1_3rows IS
5 BEGIN
6 OPEN c1;
7 LOOP
8 FETCH c1 INTO v_empno;
9 DBMS_OUTPUT.PUT_LINE('Id :' || (v_empno));
10 EXIT WHEN c1%ROWCOUNT >= 3;
11 END LOOP;
12 END proc1_3rows;
13 PROCEDURE proc4_6rows IS
14 BEGIN
15 LOOP
16 FETCH c1 INTO v_empno;
17 DBMS_OUTPUT.PUT_LINE('Id :' || (v_empno));
18 EXIT WHEN c1%ROWCOUNT >= 6;
19 END LOOP;
20 CLOSE c1;
21 END proc4_6rows;
22 END pack_cur;
23 /
Æ We create a package consisting of a cursor and two procedures. These procedures
execute FETCH a specific number of rows.

The cursor and the procedures being defined as public elements, it is possible to call each
procedure independently from an SQL order.

Example 2:
Page 43 / 102 Supinfo Laboratory of Oracle Technologies
July 3, 2003 [Link]
 Advanced PL/SQL - Version 1.2

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE pack_cur.proc1_3rows
7934
Id : 7902
Id : 7900
SQL> EXECUTE pack_cur.proc4_6rows
Id : 7876
Id : 7844
7839
Æ We execute here the procedures from the previous package independently.

6.6.3 PL/SQL table and record packages

We can also create packages made up of tables and PL/SQL records.

Example:

CREATE OR REPLACE PACKAGE emp_package IS

TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE read_emp_table
(emp_table OUT emp_table_type);
END emp_package;

CREATE OR REPLACE PACKAGE BODY emp_package IS

PROCEDURE read_emp_table
(emp_table OUT emp_table_type)
IS
I BINARY_INTEGER := 0;
BEGIN
FOR emp_record IN (SELECT * FROM emp)
LOOP
emp_table(i) := emp_record;
i:= i+1;
END LOOP;
END read_emp_table;
END emp_package;
Æ This package creates a record table. This table is filled in the procedure
READ_EMP_TABLE.

To call the READ_EMP_TABLE procedure, an anonymous PL/SQL block is used in which a

variable of the type defined in the package specification that will be initialized by the loop of the procedure.

Supinfo Laboratory of Oracle Technologies Page 44 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Example 2:

SQL> DECLARE
2 emp_table emp_package.emp_table_type;
3 BEGIN
4 emp_package.read_emp_table(emp_table);
5 dbms_output.put_line('An example: ' || emp_table(4).ename);
6 END;
7 /
Æ This PL/SQL block displays the name of the 4th field of the record table. This table has been
initialized thanks to the READ_EMP_TABLE procedure of the EMP_PACKAGE package.

Page 45 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

7 PACKAGES PROVIDED BY ORACLE

7.1 The packages provided by Oracle
The packages provided by Oracle are included in the Oracle server and allow access to certain
SQL function in a PL/SQL block or to extend the database's functionalities.
One can directly take advantage of the features provided by these packages when creating
applications, or to draw inspiration from them to create our own stored procedures.

7.2 The DBMS_PIPE package

7.2.1 The composition of DBMS_PIPE

The DBMS_PIPE package consists of several procedures and functions, the main ones being;
PACK_MESSAGE

Function or Description
Procedure
PACK_MESS Compress an element into the local message buffer that will be sent by
AGE the SEND_MESSAGE function. The element must be of type VARCHAR2,
(Procedure) NUMBER or DATE
SEND_MESS Send a message contained in the local message buffer to the pipe
AGE desired
(Function)
RECEIVE_ME Retrieve a message from the chosen pipe and place it in the message buffer.
MESSAGE local in order to be decompressed by the UNPACK_MESSAGE procedure
(Function)
UNPACK_ME Uncompress an element from the local message buffer. This element must be
MESSAGE of type VARCHAR2, NUMBER or DATE.
(Procedure)

DBMS_PIPE has many other procedures and functions than those listed here. To see all
the functions and procedures of the package can use the information from the data dictionary regarding
DBMS_PIPE. As sys:

SELECT text
FROM all_source
WHERE name = 'DBMS_PIPE'
ORDER BY LINE;

7.2.2 The functions of DBMS_PIPE

The DBMS_PIPE package has several functions:

It allows two or more sessions connected to the same instance to communicate through a pipe that
the operation is similar to UNIX pipes.
Pipes operate asynchronously.
Depending on the security parameters, the pipes can be private or public.
Once the information from the buffer is read by a user, it is deleted from the buffer and is not
available for other readers on the same pipe.
To send a message, you first need to make one or more calls to PACK_MESSAGE to construct the
message. Then we call SEND_MESSAGE to send the message to the named pipe.
To receive a message from a pipe, you must first call RECEIVE_MESSAGE and then
called UNPACK_MESSAGE to access the different elements of the message.

Supinfo Laboratory of Oracle Technologies Page 46 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

7.2.3 Example of using DBMS_PIPE

CREATE OR REPLACE PROCEDURE send_message

(v_message IN VARCHAR2)
IS s INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE(v_message);
s := DBMS_PIPE.SEND_MESSAGE('DEMO_PIPE');
IF s <> 0
THEN RAISE_APPLICATION_ERROR (-20200,
Error
END IF;
END send_message;

CREATE OR REPLACE PROCEDURE receive_message

IS s INTEGER;
VARCHAR2(50);
BEGIN
s := DBMS_PIPE.RECEIVE_MESSAGE('DEMO_PIPE');
IF s <> 0
THEN RAISE_APPLICATION_ERROR (-20201,
'Error '||TO_CHAR(s)||' reading pipe.';
END IF;
DBMS_PIPE.UNPACK_MESSAGE(v_message);
DBMS_OUTPUT.PUT_LINE(v_message);
END receive_message;
Æ Here we define two procedures: one for sending a message and the other for receiving one.
one.

7.3 Dynamic SQL

7.3.1 Definition

Oracle allows the writing of PL/SQL blocks using dynamic SQL. Dynamic SQL statements are
orders that are not embedded in the program's source code, they are stored in strings of
characters that can be used as input by programs or constructed by them. By
example we use a dynamic SQL order to create a procedure that acts on a table whose name is not
not known before execution, or to write and execute a DDL statement in PL/SQL..
In Oracle8 and earlier versions, it is necessary to use the DBMS_SQL package to write SQL statements.
dynamics. This package can also be used in Oracle8i as well as EXECUTE IMMEDIATE. If
the expression is a SELECT involving multiple rows, we can use DBMS_SQL or expressions
OPEN-FOR, FETCH and CLOSE.
The DBMS_SQL package allows you to write stored procedures and anonymous PL/SQL blocks using
Dynamic SQL. With this package, it is possible to execute DDL commands within a PL/SQL block.
for example we will be able to perform a DROP TABLE from a stored procedure.
The operations provided by this package are executed under the current user's account and not under
that of the owner of the SYS package. So if the caller is an anonymous PL/SQL block, the operations are
executed based on the privileges of the current user, if the caller is a stored procedure the
operations are executed according to the privileges of the function owner.
The use of this package to execute DDL commands can lead to a deadlock. The most common reason is
It is likely, for example, that one tries to eliminate a procedure that is still being used.

7.3.2 The flow of execution

SQL statements are executed in a certain order: they are first parsed, then bound, executed, and finally
"fetcher". Not all of these steps are mandatory.
Parsing involves traversing the entire SQL order to check the syntax of the expression and
to validate orders by ensuring that all references to objects are correct and that privileges
appropriate ones exist for these objects.

Page 47 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

Once the server has parsed the lines, it knows the meaning of the expressions used but does not have
enough information to execute the request. Oracle may need to retrieve the values of
variables of the request. The method to retrieve them constitutes the binding phase.
Then, since it has enough information, Oracle can execute the query.
During the 'FETCH' phase, the rows are selected and then ordered (if the query requests it), and
Each "FETCH" returns a different result row until the last row has been "fetched".

7.3.3 The DBMS_SQL package

The DBMS_SQL package uses dynamic SQL to access the database. It is composed of, among other things,
others, the following functions and procedures: OPEN_CURSOR, PARSE, BIND_VARIABLE, EXECUTE,
FETCH_ROWS, CLOSE_CURSOR.

Function or Description
Procedure
OPEN_CUR Open a new cursor and assign it an identifier number (ID)
SOR
PARSE Parse DDL and DML commands: check the syntax of the expressions and associate them.
with the cursor open (DDL statements are executed during parsing)
BIND_VARIA Read the value assigned to the variable defined by its name in the expression.
BLE cursor open parsing.
EXECUTE Execute the SQL query and return the number of rows processed
FETCH_RO Return a row from the open cursor (if there are multiple rows, it is necessary to use)
WS a loop)
CLOSE_CUR Close the specified cursor
SOR

The DBMS_SQL package has many other functions and procedures than those listed here, for
know all the functions and procedures of the package using the information contained in the
data dictionary:

SELECT text
FROM all_source
WHERE DBMS_SQL
ORDER BY LINE;

7.3.4 Use of DMBS_SQL

To process a dynamic DML order, you must first use OPEN_CURSOR to establish a place in
memory to process the SQL command. We then use PARSE to go through the command and check its validity. The
The EXECUTE function executes the SQL command and returns the number of rows processed. Finally, we close the cursor.
thanks to CLOSE_CURSOR

Supinfo Laboratory of Oracle Technologies Page 48 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Example:

CREATE OR REPLACE PROCEDURE delete_all_rows

(tab_name IN VARCHAR2,
rows_del OUT NUMBER)
IS
cursor_name INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE ( cursor_name, 'delete FROM ' || tab_name,
DBMS_SQL.NATIVE );
rows_del := DBMS_SQL.EXECUTE (cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

SQL> VARIABLE deleted NUMBER

SQL> EXECUTE delete_all_rows('emp', :deleted)
PL/SQL procedure completed successfully.
SQL> PRINT deleted
DELETED
---------
14
Æ This procedure deletes all rows from the specified table and returns the
number of lines deleted.

7.3.5 EXECUTE IMMEDIATE

To write dynamic SQL statements with Oracle, you can also use the EXECUTE expression.
IMMEDIATE. This expression parses and executes the SQL command immediately.

EXECUTE IMMEDIATE dynamic_string

[INTO {define_variable
[,define_variable] ... |record}]
[USING [IN|OUT|IN OUT] bind_argument
[, [IN|OUT|IN OUT]bind_argument] ... ];

Param Description
to be
Dynam It is a string representing an SQL order or a PL/SQL block
ic_strin
g
Define It is a variable storing the value returned by the SELECT
_variable
the
Record It is a record defined by the user or by %ROWTYPE storing the result of
SELECT
Bind a It is an expression whose value is passed to the SQL expression or block.
argue Dynamic PL/SQL
nt

The INTO clause can only be used for a single-row query. If the query involves
multiple lines must use OPEN-FOR, FETCH and CLOSE. Comment: Thank you for the
reminder of times we would have
forgotten.
7.3.6

Page 49 / 102 Supinfo Laboratory of Oracle Technologies

March 7, 2003 [Link]
 Advanced PL/SQL - Version 1.2

7.3.7 Use of EXECUTE IMMEDIATE

CREATE PROCEDURE del_rows

(v_table_name IN VARCHAR2,
rows_deld OUT NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'delete from ' || v_table_name;
SQL%ROWCOUNT;
END;

SQL> VARIABLE deleted NUMBER

SQL> EXECUTE del_rows('emp', :deleted)
PL/SQL procedure completed successfully.
SQL> PRINT deleted
DELETED
---------
14
Æ Here we create the same procedure as before using EXECUTE IMMEDIATE.
DBMS_SQL package location

7.4 The other available packages

7.4.1 The DBMS_DDL package

The DBMS_DDL package provides access to certain DDL commands specific to SQL usable in PL/SQL.
package cannot be used in triggers, procedures called from Form Builder or in
remote sessions.
DBMS_DDL provides the ability to recompile modified procedures, functions, and packages using the
ALTER_COMPILE procedure.
DBMS_DDL.ALTER_COMPILE(object_type, owner, object_name)
It is also possible to analyze a single object using the ANALYZE_OBJECT procedure. One can
also analyze several objects at the same time using DBMS_UTILITY.
DBMS_DDL.ANALYZE_OBJECT(object, owner, object_name)
Thanks to this package, developers have access to the ALTER and ANALYZE commands, which are SQL commands.
in a PL/SQL environment.

7.4.2 The DBMS_JOB package

This package allows you to schedule tasks, execute procedures, and modify already scheduled jobs.
for example, to force them to execute immediately instead of at the scheduled time. We can see all the jobs
already defined through the USER_JOBS view of the data dictionary.
To add a job to the queue, we use the SUBMIT procedure:
DBMS_JOB.SUBMIT(jobno, order, date)
JOBNO is an OUT parameter, it will contain the number assigned to the job created so that the user can refer to it.
later reference.
ORDER represents the action to be executed.
DATE corresponds to the date on which the job will be executed. By default, this value is SYSDATE.

Example:

SQL> VARIABLE jobno NUMBER

SQL> EXECUTE DBMS_JOB.SUBMIT (:jobno, -
> OVER_PACK.ADD_DEPT('EDUCATION','ZURICH');
> SYSDATE)
SQL> COMMIT;
SQL> PRINT jobno
Æ This request adds a job that executes immediately (SYSDATE).

Supinfo Laboratory of Oracle Technologies Page 50 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

To force the execution of a JOB that is in the queue, use RUN by indicating the JOB number.
execute.
DBMS_JOB.RUN (jobno)

7.4.3 The DBMS_OUTPUT package.

The DBMS_OUTPUT package allows outputting messages and values from PL/SQL blocks. It is
composed among others of the following procedures: PUT, NEW_LINE, PUT_LINE, GET_LINE, GET_LINES and
ENABLE/DISABLE.

Function or Description
Procedure
PUT Add the text of the procedure to the current line of the buffer.
exit
NEW_LINE Place an End_Of_Line marker in the output buffer
PUT_LINE Combine the action of PUT and NEW_LINE
GET_LINE Retrieve the current line from the output buffer in the procedure
GET_LINES Retrieve a series of lines from the output buffer in the procedure
ENABLE/DISABLE Enable or disable calls to the DBMS_OUTPUT procedure

This package allows developers to precisely track the execution of a function or procedure.
by sending messages and values to the output buffer. It is a valuable aid for debugging because it
allows tracking intermediate results during execution.
If we use this package under SQL*Plus, we must ensure that the display on the output terminal
(SERVEROUTPUT) is well set to ON.

7.4.4 Other packages provided by Oracle

Here are some other Oracle packages whose operation will not be detailed in this course:

Package Description
DBMS_ALERT Provides notifications about database events
data
DBMS_APPLICATION_INFO Allows application tools and developers
applications to inform the database of the level of
actions that she is currently performing
DBMS_DESCRIBE Return a description of the arguments of a procedure
stored
DBMS_LOCK Request, convert and release the USERLOCKS, which are managed
by the RDBMS lock management services
DBMS_SESSION Give access to the information of the current SQL session
DBMS_SHARED_POOL Keep the objects in shared memory
DBMS_TRANSACTION Control logical transactions and improve performance
short and undelivered transactions
DBMS_UTILITY Analyze the objects of a particular schema, check if the server
turn to parallel mode or not and return the time
UTL_FILE Add file reading and writing functionalities

Page 51 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

8 DATABASE TRIGGER CREATION

8.1 Database triggers
8.1.1 Overview of triggers

Triggers are PL/SQL blocks that are executed implicitly whenever a particular event occurs.
Triggers can be set up either on a database or on an application.
Database triggers are executed implicitly when an INSERT, UPDATE, or order
DELETE (triggering orders) is executed on the table associated with the trigger. Triggers are executed something
whether the user is logged in or the application used. Database triggers are also executed.
implicitly for user actions or actions of the database system. For example
when the user logs in or when a DBA stops the database.
Application triggers are executed implicitly when a particular event occurs in a
application. An example of applications using triggers: applications developed with Form Builder.
Database triggers can be defined on tables or on views. If a DML operation is
performed on a view, the INSTEAD OF trigger defines the actions that will take place. If these actions include
DML operations on tables, all triggers on the base table(s) are triggered.

8.1.2 Design directives for triggers

The use of triggers ensures that when a specific operation is executed, the related actions are
executed implicitly and transparently for the user.
Database triggers are used for global and centralized operations that must be
triggered by commands independent of the user or the application that issued the command.
It is better not to define a trigger to reproduce or replace existing functionalities.
in the Oracle database. For example, one should not define a trigger implementing rules
integrity measures that can be implemented using constraints.
The excessive use of triggers can lead to complex interdependencies, which can make it difficult to
maintenance of large applications. Triggers should only be used when necessary and care should be taken with
recursive and cascading effects they can produce.
A recursive trigger is a trigger containing a DML operation that modifies the same table.
A cascading trigger is a trigger whose action causes the activation of a second trigger and so on.
The Oracle server allows the cascading execution of a maximum of 32 triggers at the same time, but we
can limit the number of cascading triggers by changing the value of the database initialization parameter
OPEN_CURSORS data, which is set to 50 by default.

8.1.3 Example of database trigger

Supinfo Oracle Technologies Laboratory Page 52 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Figure 1: Example of a database trigger.

The diagram shows a database trigger that checks that the inserted salary falls within the range.
defined. The values that do not comply with the salary rank based on the type of employment may be either rejected,
they should be inserted and reported in an audit table.

8.1.4 Creation of triggers

Before coding the body of the trigger, one must decide on the parameters of its components: the synchronization of
trigger, the triggering event and the type of trigger.

Component Description Values

Possible
Synchronization of Defines when the trigger is activated BEFORE
trigger in relation to the triggering effect AFTER
INSTEAD OF
Event Define what manipulation operation of INSERT
triggering data on the table or the view leads to the UPDATE
trigger activation DELETE
Type of trigger Defines how many times the body of the trigger is Statement
executed Row
Trigger body Define the actions that will be executed by the PL/SQL Block
trigger complete

8.2 The components of a trigger

8.2.1 The synchronization of the trigger

The trigger synchronization defines when the trigger will be activated. It can take three values:
BEFORE, AFTER, INSTEAD OF.
A BEFORE trigger executes the trigger body before the DML event that triggers on the table. The triggers
BEFORE are used when the action of the trigger determines whether the triggering order is allowed to execute or
This situation allows for the elimination of unnecessary executions of the triggering expression and any potential
ROLLBACK for cases where an exception is raised during the triggering action. It is also used for
modify the values of the columns before performing a triggering order of type INSERT or UPDATE.
An AFTER trigger executes the trigger body after the triggering DML event has occurred on the
The AFTER triggers are used when you want the triggering action to be completed before executing
the action of the trigger. It is also useful when one wants to execute several actions on a triggering order
already having a BEFORE trigger.
An INSTEAD OF trigger executes the body of the trigger instead of the triggering order. This trigger provides a way
to modify views that cannot be directly modified by DML commands because views are
not modifiable by nature. Thus, it is possible to write INSERT, UPDATE, and DELETE statements on the view and
the INSTEAD OF trigger will act transparently to execute the action coded directly on the underlying tables
adjacent to the view.

8.2.2 The triggering event

The events triggering the triggers can be the DML orders INSERT, UPDATE, and DELETE.
When the triggering event is an UPDATE statement, it is possible to define a list of columns for
identifier the cell(s) that need to be modified to trigger the event. It is impossible to specify a list of
columns for INSERT and DELETE statements because they pertain to an entire row.

. . . UPDATE OF salary . . .

The triggering event can consist of multiple DML operations:

Page 53 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

. . . INSERT or UPDATE or DELETE . . .

. . . INSERT or UPDATE OF job . . .

8.2.3 The type of trigger

The type of trigger represents the number of times the body of the trigger will be executed when the event
Triggering occurs. It can take two values: Statement and Row.
A trigger statement executes only once for the triggering event even if no row exists
concerned. This value is used for the default value. Statement triggers are useful if the action of
trigger does not depend on the data of the affected rows or the data provided by the event
triggering itself. For example, a trigger that executes a complex security check on
the current user.
A Row trigger is a trigger whose body executes once for each row affected by the event.
triggering. If the triggering event does not affect any row, the body of the trigger is not executed. The
Row triggers are useful when the action of the trigger depends on the data of the affected rows or data.
provided directly by the triggering event.

8.2.4 The body of the trigger

The body of the trigger defines all the actions that will be executed when triggered. The body is
consists of a PL/SQL block or a call to a procedure. The PL/SQL block may contain SQL statements.
In PL/SQL, define PL/SQL elements such as variables, cursors, exceptions, and others.

8.3 The triggering sequence

The triggering sequence corresponds to the order in which the request and the trigger are executed. This
The sequence depends on the option of the chosen trigger type (BEFORE, AFTER, INSTEAD OF) and the type.
the trigger (Row or Statement).
When the trigger is of type Statement, the trigger is executed only once. If the triggering is of
BEFORE type, the trigger will execute first and then allow the DML request to take place. If it is a
trigger AFTER the query executes and then the trigger takes over to perform its actions. If the trigger
is of type INSTEAD OF the body of the trigger is executed instead of the DML statement.
For a Row type trigger, the trigger executes once for each row processed. For a BEFORE trigger, the body
the trigger executes first and then the query executes on the first returned row, then the trigger
executes once again, then the request executes on the next line and so on until there are no more
no more lines returned. For an AFTER trigger, the approach is the same, except that the body of the trigger
executes after each line processed. When the query returns only one line, the sequence of
The triggering is identical to that of a Statement trigger.

8.4 Creation of Statement trigger

8.4.1 Syntax for creating Statement triggers

The syntax for creating a Statement trigger is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name

timing
event1[ORevent2ORevent3]
ONtable_name
trigger_body

Trigger_name Indicate the name of the trigger that will be created

Timing Indicate the moment when the trigger will be activated in relation to
the triggering event:
BEFORE

Supinfo Laboratory of Oracle Technologies Page 54 / 104

[Link] 03/07/2003
 Advanced PL/SQL - Version 1.2

AFTER
INSTEAD OF
Event Identify the data manipulation operation that triggers the trigger:
INSERT
UPDATE [OF column]
DELETE
Table/view_name Specify the table or view that will be associated with the trigger
Trigger_body The body of the trigger defining the actions that will be executed by it
The body starts with DECLARE or BEGIN and ends with END
where is a call to a procedure.

8.4.2 Creation with SQL*Plus

A BEFORE trigger can prevent the execution of certain queries if a condition is not met.
We will thus create a trigger to restrict INSERT orders on the EMP table to business hours between
Monday and Friday.

Example:

SQL> CREATE OR REPLACE TRIGGER secure_emp

2 BEFORE INSERT ON emp
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN ('SUN','MON')) OR
5 (TO_CHAR(sysdate,'HH24') NOT BETWEEN
6 '08' AND '18')
7 THEN RAISE_APPLICATION_ERROR (-20500,
Insertion in EMP impossible outside of working hours.
9 END IF;
10 END;
11 /

Trigger created.
Æ If a user tries to insert a row into the EMP table outside of business hours
(Saturday for example) he will see the error message, the trigger fails and the request
trigger is canceled (ROLLBACK).

RAISE_APPLICATION_ERROR is a built-in server procedure that displays a message on the terminal and
because of the trigger failure. When a database trigger fails, the triggering event is
automatically canceled (ROLLBACK) by the Oracle server.

8.4.3 Creation with Procedure Builder

To create a database trigger using Procedure Builder, you must first connect to the database.
of data. In the object explorer, click on the Database Objects node and then select
the database trigger editor in the program menu to display the following window:

Page 55 / 102 Supinfo Laboratory of Oracle Technologies

March 7, 2003 [Link]
 Advanced PL/SQL - Version 1.2

Figure 1: Overview of the trigger editor window

In the upper part of the window, select the owner of the assigned table, the name of this table.
thanks to the dropdown menu. We then choose the moment of the trigger and the type of triggering order in
checking the appropriate boxes. We give a name to our trigger as well as its type (Statement in our
example). In the lower part of the window, we enter the body code of the trigger and then click on Save
to compile it using the PL/SQL engine of the server. Once the compilation is successfully completed the code
your trigger is stored in the database and activated automatically. If the compilation returns some
errors, these are displayed in a separate window.
It is impossible to create INSTEAD OF triggers in older versions of Procedure Builder.

8.4.4 SECURE_EMP Test

To test the SECURE_EMP trigger, we will try to insert a row into the EMP table outside of hours.
work.

Supinfo Laboratory of Oracle Technologies Page 56 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Example:

SQL> INSERT INTO emp (empno, ename, deptno)

2 VALUES (7777, 'BAUWENS', 40);
INSERT INTO emp (empno, ename, deptno)
*
ERROR at line 1:
ORA-20500: Insertion into EMP not possible outside of working hours.
ORA-06512: at 'SCOTT.SECURE_EMP', line 5
ORA-04088: error while executing trigger 'SCOTT.SECURE_EMP'
Æ This DML INSERT order produces an error due to the trigger when trying to insert some
lines outside of normal working hours.

8.4.5 Use of conditional attributes

It is possible to combine several events triggering a single statement using attributes.

conditional INSERTING, UPDATING and DELETING within the body of the trigger. These attributes are included
in IF-THEN-ELSE type conditional expressions.
For example, we can create a trigger that will restrict all data manipulation operations on the
table EMP during working hours between Monday and Friday.

Example:

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF (TO_CHAR (sysdate,'DY') IN ('SUN','MON')) OR
(TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18')
THEN
IF DELETING
THEN RAISE_APPLICATION_ERROR (-20502,
Removal of EMP impossible outside of working hours.
ELSE IF INSERTING
THEN RAISE_APPLICATION_ERROR (-20500,
Insertion in EMP impossible outside of working hours.
ELSIF UPDATING ('SAL')
THEN RAISE_APPLICATION_ERROR (-20503,
Update of SAL not possible outside working hours.
ELSE
RAISE_APPLICATION_ERROR (-20504,
Update of EMP impossible outside of working hours.
END IF;
END IF;
END;
Æ This trigger restricts DML orders on the EMP table using conditional attributes.

8.5 Creation of Row Triggers

8.5.1 Syntax for creating a Row Trigger

The syntax for creating a Row Trigger is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name

timing
event1[ORevent2ORevent3]
ONtable_name
[REFERENCING OLD ASold| NEW ASnew]
FOR EACH ROW
[WHENcondition]
trigger_body

Page 57 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

Trigger name Please indicate the name of the trigger that will be created

Timing Indicate the moment the trigger will be activated in relation to

the triggering event:
BEFORE
AFTER
INSTEAD OF
Event Identify the data manipulation operation that triggers the
trigger :
INSERT
UPDATE [OF column]
DELETE
Table name Indicate the table or view that will be associated with the trigger.
REFERENCING Specify the names of the reciprocal links for the old and the new
value of the current row (the default values are OLD and NEW)
FOR EACH Indicate that the trigger is a Row trigger
ROW
WHEN Specify the trigger restriction (the conditional attribute is evaluated for
each line to determine if the trigger body is executed or
non)
Trigger_body The body of the trigger defining the actions that will be executed by
this one. The body begins with DECLARE or BEGIN and ends with
END or is a call to a procedure.

8.5.2 Creation with SQL*Plus

One can create a BEFORE Row trigger to prevent the triggering operation from executing on each
line concerned when a certain condition is not met. For example, we can create a Row trigger
who will only allow certain employees to earn a salary over 5000.

Example;

SQL> CREATE OR REPLACE TRIGGER DERIVE_COMMISSION_PCT

2 BEFORE INSERT OR UPDATE OF sal ON emp
3 FOR EACH ROW
4 BEGIN
5 IF NOT (:[Link] IN ('MANAGER', 'PRESIDENT'))
6 AND :[Link] > 5000
7 THEN
8 RAISE_APPLICATION_ERROR
9 The employee cannot earn that much.
10 END IF;
11 END;
Æ This trigger prevents users from inserting a salary higher than 5000 for all JOB
except for the MANAGER and PRESIDENT.

If the user tries to insert a salary greater than 5000, the trigger returns an error:

Example:

SQL> UPDATE EMP SET SAL = 6500 WHERE ENAME = 'MILLER'

2/
UPDATE EMP SET SAL = 6500 WHERE ENAME = 'MILLER'
*
ERROR at line 1:
ORA-20202: The employee cannot earn that much.
ORA-06512: at "SCOTT.DERIVE_COMMISSION_PCT", line 5
ORA-04088: error occurred while executing the trigger
SCOTT.DERIVE_COMMISSION_PCT
Æ The trigger produces an error and thus the EMP table is not modified.

Supinfo Laboratory of Oracle Technologies Page 58 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

8.5.3 Creation with Procedure Builder

One can also create a Row trigger BEFORE using Procedure Builder. To do this, one connects to the
database and we click on the node Database Objects in the object explorer. Then we select
the database trigger editor in the Program menu (see § 8.4.3, Figure 1). Then select the
owner and the corresponding table in the drop-down lists. We choose the moment of triggering.
(BEFORE) and the triggering events by checking the appropriate boxes. In the For Each part
Select Row to indicate to Procedure Builder that we are developing a Row Trigger. We can fill in the
sectionReferencing in order to modify the reciprocal links by specifying a different name for the OLD and the
NEW. You can also specify a conditional attribute by filling out the When field. These two options
are only available when creating a Row Trigger. In the lower part of the window, we enter
the body code of the trigger then we click on Save to have it compiled by the server's PL/SQL engine.
Once properly compiled, the code is stored in the database and activated.

8.5.4 Use of the qualifiers OLD and NEW

When using a Row Trigger, it records the values of several columns before and after the
modification of data using the qualifiers OLD and NEW associated with the respective column name
For the example, we will use a table named AUDIT_EMP_TABLE. This table consists of the columns
user_name, timestamp, id, old_last_name, new_last_name, old_title, new_title, old_salary, new_salary). This
The table is only meant to provide a more concrete example of the use of qualifiers, so it is empty.
(NULL).

Example:

SQL> CREATE OR REPLACE TRIGGER audit_emp_values

2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp_table (user_name,
6 timestamp
7 old_title
8 VALUES (USER, SYSDATE, :[Link], :[Link],
9 :[Link], :[Link], :[Link],
10 :[Link], :[Link] );
11 END;
12 /
Æ This request creates a trigger that, for every DML order on the EMP table, will insert a row
of the audit by recording the old and new value of the name, title, and salary.

8.5.5 Values of OLD and NEW

In a Row Trigger, it is possible to reference the values of a column before and after the change.
due to a DML order by prefixing its name with the qualifier OLD or NEW.
According to the DML operation performed, the OLD and NEW values will not be the same:

Operation Value of OLD Value of NEW

INSERT NULL Inserted Value
UPDATE Value before the update Value after the update
DELETE Value before deletion

The OLD and NEW qualifiers are only available for Row type triggers.
These qualifiers must be prefixed with colons (:) in any SQL or PL/SQL statement using them.
One must not use a colon (:) prefix when the qualifiers are used in the condition of
restrictionWHEN.

Page 59 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

8.5.6 Restrict a row trigger

Thanks to the WHEN clause, it is possible to restrict the action of the trigger to rows that conform to certain
conditions.

Example:

SQL> CREATE OR REPLACE TRIGGER derive_commission_pct

2 BEFORE INSERT OR UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN ([Link] = 'SALESMAN')
5 BEGIN
6 IF INSERTING
7 THEN :[Link] := 0;
8 ELSIF :[Link] IS NULL
9 THEN :[Link] := 0;
10 ELSE :[Link] := :[Link] * (:[Link]/:[Link]);
11 END IF;
12 END;
13 /
Æ This trigger is used to calculate the new commission when a line is added or
modified in the EMP table.

If one wants to assign values to columns using the qualifier NEW, one must create a Row trigger.
BEFORE. If we try to compile the code of the example using an AFTER trigger, an error will occur.
error

8.6 Trigger INSTEAD OF

8.6.1 Interest of INSTEAD OF triggers

INSTEAD OF triggers are used to modify data on which a DML command has been executed.
that they are part of a non-updatable view. These triggers execute the operations INSERT, UPDATE and
DELETE directly on the underlying tables of the view. When writing an INSERT, UPDATE, or
DELETE on a view, the INSTEAD OF trigger acts transparently in the background to execute the correct actions.
replacement actions.
These triggers are called INSTEAD OF because; unlike other triggers, the Oracle server triggers the
trigger instead of executing the triggering expression. The INSTEAD OF type is a Row trigger.
If a view consists of more than one table, an insertion on it may result in an insertion into a
table and an update in another one. So we need to write an INSTEAD OF trigger that is triggered when
the execution of an INSERT order. Instead of the original INSERT order, the body of the trigger is executed which
results in an insertion into one table and an update in the other.
When a view is updatable and has an INSTEAD OF trigger, the trigger will take priority in execution.
and therefore will execute the body of the trigger for each DML order.
INSTEAD OF triggers are only available in the Oracle Enterprise edition.

8.6.2 Creation of an INSTEAD OF trigger

The syntax for creating an INSTEAD OF trigger is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF
event1[ORevent2ORevent3]
ONview_name
[REFERENCING OLD ASold| NEW ASnew]
[FOR EACH ROW]
trigger_body

Trigger_name Indicate the name of the trigger

Supinfo Oracle Technologies Laboratory Page 60 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

INSTEAD OF Indicate that the trigger will be on a view

Event Specify the type of data manipulation operation that will trigger
the trigger. This value can be:
INSERT
UPDATE [OF column]
DELETE
View Name Indicate the view associated with the trigger
REFERENCING Specify the names of the correlation links for the new and old ones
values. (By default, these values are OLD and NEW)
[FOR EACH Designate the trigger as a Row trigger. INSTEAD OF triggers do not
ROW there can only be Row triggers so this clause is optional.
Trigger_body The body of the trigger defines the actions that will be executed by the trigger.
This block can be a PL/SQL block with a DECLARE or BEGIN clause.
and the keyword END or a call to a procedure

INSTEAD OF triggers can only be written for views. The BEFORE and AFTER options are not
not available and we cannot specify a WHEN clause with an INSTEAD OF trigger.

8.7 Difference between triggers and stored procedures

There are several differences and similarities between database triggers and procedures.
stored:

Triggers Procedures
Use CREATE TRIGGER Use CREATE PROCEDURE
The data dictionary contains the source The data dictionary contains the source
and the lep-code and the code
Implicitly called Called explicitly
COMMIT, SAVEPOINT and ROLLBACK do not COMMIT, SAVEPOINT and ROLLBACK
are not allowed can be used

Triggers are fully compiled when the CREATE TRIGGER command is executed and the code is
stored in the data dictionary. As a result, triggering the trigger does not require
plus the opening of a shared cursor to perform the trigger action. Instead, the trigger is executed
directly.
If errors occur during the compilation of a trigger, it will still be created. It is therefore better
to use the OR REPLACE syntax to avoid having to delete the old trigger when it contains
errors.

8.8 Trigger Management

8.8.1 Activation of triggers

When a trigger is created, there are commands that allow you to manipulate them in order to activate them,
disable and recompile them.
When a trigger is created, it is automatically activated. For all activated triggers, the Oracle server checks
the integrity constraints and guarantees that no trigger can interfere with another. Furthermore, the server
provides logical views for queries and constraints, manages dependencies, and provides validation
COMMIT in two phases if a trigger updates remote tables of a distributed database.
However, if one wants to disable a trigger, in order to perform an operation that it prohibits for example, one
can use the ALTER TRIGGER command by specifying the name of the trigger. You can also disable all
the triggers of the database in a single command using the ALTER TABLE syntax.

ALTER TRIGGER trigger_name DISABLE | ENABLE

ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

Disabling a trigger improves the performance of DML commands and avoids the check for
the integrity of the data when handling a large amount of data. Disabling is useful
Page 61 / 102 Supinfo Laboratory of Oracle Technologies
07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

when it refers to a database object temporarily unavailable due to a

Network connection failed, a disk failure or a data file or a tablespace is offline.
To explicitly recompile an invalid trigger, we use the ALTER TRIGGER command. When
execute an ALTER TRIGGER command with the COMPILE option, the trigger is recompiled without losing its validity or
his disability.

ALTER TRIGGER trigger_name COMPILE

8.8.2 Deletion of triggers

When a trigger is no longer needed, one can use a SQL DROP command in SQL*Plus or in
the command interpreter of Procedure Builder to remove it.

DROP TRIGGER trigger_name

Example:

SQL> DROP TRIGGER secure_emp;

Trigger deleted.

8.9 Trigger tests

8.9.1 Cases to test

To ensure the proper functioning of a trigger, we must check the various possible cases.
• We test all triggering operations as well as non-triggering operations in order to
ensure that the trigger only executes for the intended cases.
• We also test each possible case for the WHEN clause.
• The trigger is activated directly from a basic manipulation operation.
of data as well as indirectly from a procedure.
• We also need to test the effect that this trigger has on all the others present in the database.
as well as the effect of other triggers on it.

8.9.2 Trigger Execution Model and Constraint Checking

All triggers, during execution, follow a predetermined model:

• Execution of all BEFORE type statement triggers
• A loop for each affected line:
Execution of all Row BEFORE type triggers
Execution of the DML order and verification of integrity constraints
Execution of all AFTER type Row triggers
• Deferred integrity constraint checking
• Execution of all AFTER type Statement triggers

A DML statement can therefore generate up to four types of triggers by itself: Statement and Row triggers.
of type BEFORE and AFTER. An event or a triggering order in a trigger can lead to the
verification of one or more integrity constraints. Triggers can also lead to the
triggering other triggers (cascading triggers).
All actions and checks performed following a SQL command must succeed. If an exception is raised
in the trigger and if this exception is not handled explicitly, all actions executed in by the
original SQL query are rolled back (ROLLBACK), including actions performed by triggers
triggering. This procedure ensures that integrity constraints will never be compromised by the
triggers.
When a trigger is activated, the tables designated in the trigger action may be in progress of
modification by transactions of other users. In all cases, a valid image for the values
modified is used by the trigger for read and write operations.

Supinfo Laboratory of Oracle Technologies Page 62 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

8.10 Interactions
8.10.1 A typical demonstration

To demonstrate the operation of the interaction between triggers, package procedures, and functions.
and the global variables we will use the triggers AUDIT_EMP_TRIG and AUDTI_EMP_TAB, the functions and
procedures included in the VAR_PACK package and the global variables defined in the package. The
The structure of these different elements is detailed further along in this chapter.
The sequence of events for the demonstration begins with the execution of a DML INSERT statement,
UPDATE or DELETE manipulating multiple rows. This statement triggers the AFTER row trigger,
AUDIT_EMP_TRIG, which calls the package procedure that increments the package global variables
VAR_PACK. Since this trigger is a Row trigger, it executes once for each row returned and therefore the variables
global correspond to the number of lines returned.
Once the request is completed, the AFTER trigger Statement; AUDIT_EMP_TAB, calls the procedure
AUDIT_EMP which assigns the values of global variables to local variables using the functions of
package, then updates the AUDIT_TABLE and finally resets the global variables. Comment: Not found
a clear description of this
table. A schema or other
might be necessary? I
8.10.2 The audit table add the creation script of
times that!

CREATE TABLE audit_table

(
user_name VARCHAR2(30)
tablenameVARCHAR2(30)
column_nameVARCHAR2(30)
del NUMBER(4)
ins NUMBER(4),
upd NUMBER(
);
insert into audit_table (user_name, table_name)
values('SCOTT','EMP');
insert into audit_table (user_name, tablename, column_name)
values(SCOTT, EMP, SAL

8.10.3 The triggers

The demonstration uses two triggers: AUDIT_EMP_TRIG and AUDIT_EMP_TAB.

AUDIT_EMP_TRIG will be triggered after each manipulated row. The trigger increments the global variables.
which represent the number of rows that have been affected.
AUDIT_EMP_TAB is triggered when the query has completed. It invokes the AUDIT_EMP procedure which is
described in the following paragraph.

Page 63 / 102 Supinfo Laboratory of Oracle Technologies

March 7, 2003 [Link]
 Advanced PL/SQL - Version 1.2

CREATE OR REPLACE TRIGGER audit_emp_trig

AFTER UPDATE or INSERT or DELETE on EMP
FOR EACH ROW
BEGIN
IF DELETING THEN var_pack.set_g_del(1);
ELSIF INSERTING THEN var_pack.set_g_ins(1);
ELSE IF UPDATING ('SAL')
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 EMP
BEGIN
audit_emp;
END audit_emp_tab;

8.10.4 VAR_PACK package specifications

The VAR_PACK package defines all the functions and procedures for incrementing variables.
global based on the number of lines returned.

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;
/

CREATE OR REPLACE PACKAGE BODY var_pack

IS
delete NUMBER := 0;
NUMBER := 0;
gv_upd NUMBER := 0;
gv_up_sal NUMBER := 0;
FUNCTION g_del RETURN NUMBER
IS BEGIN RETURN gv_del; END g_del;
FUNCTION g_ins RETURN NUMBER
IS BEGIN RETURN gv_ins; END g_ins;
FUNCTION g_upd RETURN NUMBER
IS BEGIN RETURN gv_upd; END g_upd;
FUNCTION g_up_sal RETURN NUMBER
IS BEGIN RETURN gv_up_sal; END g_up_sal;
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;

Supinfo Laboratory of Oracle Technologies Page 64 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

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;
set_g_up_sal
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;

8.10.5 Procedure

The demonstration uses the AUDIT_EMP procedure. This procedure updates the AUDIT_TABLE.
calls the functions to reset the global variables so they can be reused with the
next DML order.

CREATE OR REPLACE PROCEDURE audit_emp

IS
v_del var_pack.g_del;
v_ins NUMBER := var_pack.g_ins;
v_upd 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 = NVL(del,0) + v_del,
ins = NVL(ins,0) + v_ins,
upd = NVL(upd,0) + v_upd
WHERE upper(user_name) user
AND upper(tablename) = 'EMP'
AND column_name IS NULL;
END IF;
IF v_up_sal != 0
THEN
UPDATE audit_table SET
upd = NVL(upd,0) + v_up_sal
WHERE user_name user
AND tablename = 'EMP'
AND column_name = 'SAL';
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;

Page 65 / 102 Supinfo Laboratory of Oracle Technologies

03/07/2003 [Link]
 Advanced PL/SQL - Version 1.2

9 COMPLEMENTS ON TRIGGERS

9.1 Creating triggers on user events

In addition to triggers on DML orders (INSERT, UPDATE, and DELETE), it is possible to create triggers
on user events. These events can be DDL statements (CREATE, ALTER, and DROP)
or a user login/logout. Unlike triggers on DML commands which relate to
a table or a view, triggers on user events are defined at the database level
data or at the schema level.
This type of trigger can be defined at the database level, it then triggers for all
users; or defined at the schema or table level, it only triggers when an event occurs.
involving the table or schema being executed. These triggers will only be triggered when the modified object is
un cluster, une fonction, un index, un package, une procédure, un rôle, une séquence, un synonyme, une table,
a tablespace, a trigger, a view or a user.
This type of trigger can be triggered when a data definition order is executed on an object.
database or schema and a connection or disconnection of a particular user or of everyone
database user.
The syntax for creating this type of triggers is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name

timing
[ddl_event1[ORddl_event2OR ...]]
ON {DATABASE|SCHEMA}
trigger_body

The trigger timing point in the syntax can be BEFORE or AFTER.

Although triggers can be defined at the database level, they will always be triggered
in the user's current transaction.

9.2 Creating triggers on system events

There are also triggers on database events and system events. These
triggers on system events can be defined at the database level or at the
schema. For example, a database extinction trigger is defined at the database level.
This type of trigger is activated when the database is stopped or started or when a particular error occurs.
any error occurs.

CREATE [OR REPLACE] TRIGGER trigger_name

timing
[database_event1[ORdatabase_event2OR ...]]
ON {DATABASE|SCHEMA}
trigger_body

There are five types of triggers on system events:

AFTER SERVERERROR: the Oracle server triggers the trigger every time an error message occurs.
is recorded
AFTER LOGON: the Oracle server triggers the trigger for each user connection on the
database.
BEFORE LOGOFF: the Oracle server triggers the trigger for each user disconnection
from the database
AFTER STARTUP: the Oracle server triggers the trigger when the database is started
BEFORE SHUTDOWN: the Oracle server triggers the event when the database is stopped
normally
For all these events, we can create triggers on the database or on a schema, except for
SHUTDOWN and STARTUP that can only apply to the database.
For each triggering system event, the Oracle server opens an autonomous transaction domain.
trigger the trigger and validate any other ongoing transaction without concerning yourself with the user's transactions.

Supinfo Laboratory of Oracle Technologies Page 66 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

An autonomous transaction domain is an independent transaction that can be validated without this.
affects other ongoing transactions.

9.3 Examples of Log On and Log Off triggers

Thanks to system event triggers, we can create a trigger to monitor the frequency of logins.
of users as well as the time they stay connected.

Example:

SQL> CREATE OR REPLACE TRIGGER LOGON_TRIG

2 AFTER logon ON SCHEMA
3 BEGIN
4 INSERT INTO log_trig_table
5 user_id
6 VALUES (user, sysdate, 'Logging on');
7 END;
SQL> CREATE OR REPLACE TRIGGER LOGOFF_TRIG
2 BEFORE logoff ON SCHEMA
3 BEGIN
4 INSERT INTO log_trig_table
5 user_id
6 VALUES (user, sysdate, 'Logging off');
7 END;
Æ These two commands insert into the LOG_TRIG_TABLE the connection date and that
user disconnection.

The example uses the LOG_TRIG_TABLE which has the following structure:

SQL> describe log_trig_table

Name Null? Type
------------------------------- -------- ----
USER_ID VARCHAR2(30)
LOG_DATE DATE
ACTION VARCHAR2(30)

9.4 The CALL statement

A trigger consists of four parts: the synchronization, the triggering event, the type, and the body.
This last part defines the actions that will be performed when the trigger is activated. It can be
consisting of a PL/SQL block or a call to a procedure.
When you want to call a procedure from a trigger, you use the CALL command:

CREATE [OR REPLACE] TRIGGER trigger_name

timing
event1[ORevent2ORevent3]
ONtable_name
[REFERENCING OLD ASold| NEW ASnew]
[FOR EACH ROW]
[WHENcondition]
CALL procedure_name

Example:

SQL> CREATE TRIGGER TEST3

2 BEFORE INSERT ON EMP
3 CALL LOG_EXECUTION
4 /
Æ This trigger calls the LOG_EXECUTION procedure before executing an INSERT command on
the EMP table.

Page 67 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

9.5 Tables under modification

9.5.1 Reading data in a table undergoing modifications

A table under modification is a table in which changes are being made thanks to some
DML commands UPDATE, INSERT or DELETE or a table that needs to be updated by the effects of a
DELETE CASCADE. A table is not considered to be in the process of modification when the trigger is of
type Statement.
Reading and writing in tables being modified are subject to certain rules. These
restrictions apply only to Row triggers or to triggers triggered following an ON statement
DELETE CASCADE.
A table on which a trigger acts is considered to be in the process of being modified, as well as every table.
referring to it as a FOREIGN KEY. This restriction prevents the Row trigger from seeing a set
inconsistent data.

9.5.2 Example of a table being modified

Example:

SQL> CREATE OR REPLACE TRIGGER check_salary

2 BEFORE INSERT OR UPDATE OF sal, job ON emp
3 FOR EACH ROW
4 WHEN ([Link] <> 'PRESIDENT')
5 DECLARE
6 v_minsalary [Link]%TYPE;
7 v_maxsalary [Link]%TYPE;
8 BEGIN
9 SELECT MIN(sal), MAX(sal)
10 INTO v_minsalary, v_maxsalary
11 FROM emp
12 WHERE job = :[Link];
13 IF :[Link] < v_minsalary OR
14 If [Link] is greater than v_maxsalary then

15 Out of range
16 END IF;
17 END;
18

SQL> UPDATE emp

2 SET salary = 1500
3 WHERE ename = 'SMITH';
*
ERROR at line 2:
ORA-04091: table EMP is mutating, trigger/function
may not see it
ORA-06512: at "CHECK_SALARY", line 5
ORA-04088: error during execution of trigger
CHECK_SALARY
Æ We create a trigger that checks whether the salary falls within the correct range based on the
Job type when adding an employee or modifying an existing employee.

When trying to restrict the salary to values within a range composed of

minimum and maximum existing values, an execution error occurs. The EMP table is considered
as it is being modified due to the use of the values it contains, so it is impossible to read
and to write in it for reasons of integrity of the data used for the trigger.

Supinfo Laboratory of Oracle Technologies Page 68 / 104

[Link] 03/07/2003
 Advanced PL/SQL - Version 1.2

9.6 Functions of triggers

9.6.1 Secure the server

[Link] Control the security of the server

The Oracle server allows access to tables for anyone with an account on the server. To
control security on the server we define schemes and roles in order to control operations
data on the tables based on the user's name.
The granted privileges are based on the username provided during the connection to the database.
One can determine access to tables, views, synonyms, and sequences as well as the privileges concerning the
queries, manipulation, and definition of data.

Example:

SQL> GRANT SELECT, INSERT, UPDATE, DELETE

2 ON emp
3 TO CLERK; -- database role
SQL> GRANT CLERK TO SCOTT;

[Link] Control security with a trigger

Access control to the tables through the trigger is no longer based on the user's name but on the
data values. This allows for the implementation of more complex security specifications.
The granted privileges are based on database values such as the time, the day of the
week and others. Security managed by a trigger allows determining access to tables only and the
data manipulation privileges.

Example:

SQL> CREATE OR REPLACE TRIGGER secure_emp

2 BEFORE INSERT OR UPDATE OR DELETE ON emp
3 DECLARE
4 v_dummy VARCHAR2(1);
5 BEGIN
6 IF TO_CHAR (sysdate, 'DY') IN ('SAT','SUN')
7 THEN RAISE_APPLICATION_ERROR (-20506,
8 Modification on the EMP table possible only during
9 working hours.
10 END IF;
11 SELECT COUNT(*) INTO v_dummy FROM holiday
12 WHERE holiday_date = TRUNC(sysdate);
13 IF v_dummy > 0 THEN RAISE_APPLICATION_ERROR (-20507,
14 It is impossible to modify the data during the holidays.
15 END IF;
16 END;
17 /
Æ This trigger only allows data modification during working hours and outside of
vacation.

9.6.2 Audit

[Link] Audit through system functions

The Oracle server keeps a record of data operations performed on tables thanks to functions
Predefined. The audited events are recorded in the data dictionary table.

Page 69 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

It is possible to audit data retrieval orders, data manipulation, and definition of

data. The audit logs are all written in the centralized audit table. A report can be generated.
Audit once per session or once per access attempt. The audit can be done on access attempts.
successful, failed attempts or both and it can be dynamically enabled and disabled.
To enable auditing on an object, the AUDIT command is used:

AUDITstatement_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;
AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake;
AUDIT DELETE ANY TABLE;

[Link] Audit through a trigger

Triggers keep track of the values for data operations on the tables.
Triggers can only audit data manipulation orders. All audit information
will be recorded in the user-defined audit table. It is possible to generate an audit report
once by order or once for each line. Only successful attempts are audited. As for
From the system functions, it is possible to dynamically enable and disable the triggers.
of audit.

Example:

SQL> CREATE OR REPLACE TRIGGER audit_emp_values

2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 IF audit_emp_package.g_reason IS NULL THEN
6 RAISE_APPLICATION_ERROR (-20059, 'Specify a reason
7 for the operation containing the procedure
8 SET_REASON before executing it.
9 ELSE
10 INSERT INTO audit_emp_table (user_name, timestamp, id,
11 old_last_name
12 old_salary
13 VALUES (user, sysdate, :[Link], :[Link],
14 [Link]
15 :[Link], :audit_emp_package.g_reason);
16 END IF;
17 END;
18 /
SQL> CREATE TRIGGER cleanup_audit_emp
2 AFTER INSERT OR UPDATE OR DELETE ON emp
3 BEGIN
4 audit_emp_package.g_reason := NULL;
5 END;
Æ The user calls AUDIT_EMP_PACKAGE by defining the variable G_REASON.
When executing a DML order, the trigger checks the reason for this order. If G_REASON is not
if not defined an error is raised, and if it is defined, the DML order executes correctly and
the event is recorded in the AUDIT_EMP_TABLE. The second trigger resets the
value of G_REASON.

Supinfo Laboratory of Oracle Technologies Page 70 / 104

[Link] March 7, 2003
 Advanced PL/SQL - Version 1.2

9.6.3 Ensure data integrity

[Link] Monitor the integrity of data on the server

The Oracle server allows the implementation of integrity constraints to ensure data integrity.
Data. The standard integrity constraints are: NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY.
Constraints allow for constant default values. It is also possible to put in
set static constraints. The constraints can be dynamically activated and deactivated.

Example:

SQL> ALTER TABLE emp ADD

2 CONSTRAINT ck_salary CHECK (sal >= 500);
Æ This constraint ensures that the salary will always be greater than 500.

[Link] Protect the integrity of data through a

trigger

The use of triggers to protect data integrity adds a higher level of complexity to the
level of integrity rules.
The use of triggers to protect data integrity allows specifying default values.
variables. This also allows for the establishment of dynamic constraints as well as to activate and
dynamically disable them.
To protect the integrity of the data, it is necessary to incorporate declarative constraints in the declaration of the
table.

Example:

SQL> CREATE OR REPLACE TRIGGER check_salary

2 BEFORE UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN ([Link] < [Link]) OR
5 ([Link] > [Link] * 1.1)
6 BEGIN
7 RAISE_APPLICATION_ERROR (-20508,
8 The salary should not be increased or decreased by
9 more than 10%.
10 END;
11 /
Æ Ensures that the salary will never be increased or decreased by more than 10%
all of a sudden.

9.6.4 Ensure referential integrity

[Link] Apply referential integrity on the server

The Oracle server allows the implementation of standard rules of referential integrity, that is to say the setting up
instead of PRIMARY KEY and FOREIGN KEY.
Referential integrity within the Oracle server allows for restricting the use of UPDATE and DELETE.
It also allows for cascading DELETEs. It is possible to set them up and to
dynamically disable.

Example:

SQL> ALTER TABLE emp

2 ADD CONSTRAINT emp_deptno_fk
Page 71 / 102 Supinfo Laboratory of Oracle Technologies
07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

3 FOREIGN KEY (deptno) REFERENCES dept(deptno)

4 ON DELETE CASCADE;
Æ When a department is removed from the parent table DEPT, the corresponding rows
the keys are removed from the child table EMP.

[Link] Protect referential integrity using a trigger

Using triggers to manage referential integrity allows the implementation of non-standard features.
standard on this integrity.
A trigger managing referential integrity allows for cascading UPDATES as well as defining a
default value and having a NULL value for UPDATE and DELETE. This type of trigger allows for
implementation of referential integrity in a distributed system.

Example:

SQL> CREATE OR REPLACE TRIGGER cascade_updates

2 AFTER UPDATE OF deptno ON dept
3 FOR EACH ROW
4 BEGIN
5 UPDATE emp
6 SET [Link] = :[Link]
7 WHERE [Link] = :[Link];
8 END;
9 /
Æ This trigger establishes referential integrity. When a DEPTNO value is modified
in the parent table, this change is reflected in the corresponding rows in the
child table EMP through an UPDATE. This example only works if it does not exist
referential integrity between the two tables in the definition of the tables.

9.6.5 Duplicate the tables

[Link] Duplicate a table from the server

The Oracle server is capable of asynchronously copying tables into snapshots. A snapshot
is a local copy of data from one or more master tables. The data of a
Table snapshots can be read, but it is impossible to perform INSERT, UPDATE, or DELETE.
Snapshots are therefore read-only. To keep the snapshot data up to date, the Oracle server must
regularly perform refreshes of this with the master tables.
It is possible to copy tables asynchronously, at intervals defined by the user. The
Snapshots can be based on multiple master tables. One can only read from snapshots.
Snapshots bring an improvement in performance when handling data on the table.
mistress, especially when the network is down.

Example:

SQL> CREATE SNAPSHOT emp_copy AS

2 SELECT * FROM emp@ny;
Æ This example creates a snapshot of the EMP table located in New York

[Link] Duplicate a table using a trigger

It is also possible to create table copies with a trigger. The tables duplicated by this method are
called replicas.
This trigger will copy tables synchronously in real time. Usually, replicas are based on
a single master table. It is possible to read from these replicas but unlike snapshots we
may also write there. The replicas degrade the performance of data manipulation on the table
master especially if the network is down because there is no synchronization between the master table and
Supinfo Laboratory of Oracle Technologies Page 72 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

he replied. Therefore, it is better to keep automatically updated copies with the master table
as is the case for snapshots.

Example:

SQL> CREATE OR REPLACE TRIGGER emp_replica

2 BEFORE INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 IF INSERTING THEN
6 IF :[Link] IS NULL THEN
7 INSERT INTO emp@sf VALUES (:[Link],
8 :[Link],...,'B');
9 :[Link] = 'A';
10 ELSE /* Updating. */
11 IF :[Link] = :[Link] THEN
12 UPDATE emp@sf SET ename = :[Link], ...
13 :[Link]
14 WHERE empno = :[Link];
15 END IF;
16 IF :[Link] = 'A' THEN :[Link] := 'B';
17 ELSE :[Link] := 'A';
18 END IF;
19 END IF;
20 END;
21 /
Æ We duplicate the EMP table located in New York.

In these two examples, if the network is taken offline and we had used the snapshot method, the
features of the Oracle server will ensure that users of the main node will not be affected
since the snapshot will be maintained asynchronously.
If the trigger method had been used, users would not be able to continue working since the trigger
will not be able to write to the remote database.

9.6.6 Use derived data

[Link] Calculate derived data on the server

The Oracle server calculates the derived values of the data manually.
The derived values of the columns are calculated asynchronously, at intervals defined by
the user. Derived values can only be stored in the database tables.
The method for calculating derived values is as follows: the data is modified during a first
traverse the database, then the derived data is calculated during a second pass.

Example:

SQL> UPDATE dept

2 SET total_sal = (SELECT SUM(salary)
3 FROM emp
4 WHERE [Link] = [Link]);
Æ The total salaries for the department are kept in a column TOTAL_SALARY
from the DEPT table

[Link] Calculate derived values using a trigger

A trigger automatically calculates the derived values during execution.

The derived values from the columns are calculated synchronously, in real time. The derived values
can be stored in the database tables or in the global variables of a package. The
data is modified then the values of the derived values are calculated in a single pass of the base of
data.
Page 73 / 102 Supinfo Laboratory of Oracle Technologies
07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

Example:

SQL> CREATE OR REPLACE PROCEDURE increment_salary

2 (v_id IN [Link]%TYPE,
3 v_salary IN dept.total_salary%TYPE
4 IS
5 BEGIN
6 UPDATE dept
7 SET total_sal = NVL(total_sal, 0) + v_salary
8 WHERE deptno = v_id;
9 END increment_salary;
10 /

SQL> CREATE OR REPLACE TRIGGER compute_salary

2 AFTER INSERT OR UPDATE OF sal OR DELETE ON emp
3 FOR EACH ROW
4 BEGIN
5 IF DELETING
6 THEN increment_salary(:[Link], -1 * :[Link]);
7 ELSIF UPDATING
8 THEN increment_salary(:[Link], :[Link]-:[Link]);
9 ELSE increment_salary(:[Link], :[Link]);--INSERT
10 END IF;
11 END;
12 /
Æ This trigger maintains a current total of salaries for each department in the column
TOTAL_SALARY from the DEPT table.

9.6.7 Manage event logs with triggers

The Oracle server manages event logs explicitly while triggers manage them indirectly.
transparent.
To record an event using the server's features, a request is submitted to
determine if the operation is necessary. Then we must carry out the operation, which may involve sending
message for example.
When a trigger is used, the operation is executed implicitly without the user noticing it. The
Data is modified and dependent operations are executed in a single step. With triggers.
Events are recorded as soon as the data has changed.

Example:

CREATE OR REPLACE TRIGGER notify_reorder_rep

BEFORE UPDATE OF amount_in_stock, reorder_point
ON inventory FOR EACH ROW
DECLARE
v_descrip [Link]%TYPE;
VARCHAR2(2000);
stat_send number(1);
BEGIN
IF :new.amount_in_stock <= :new.reorder_point THEN
SELECT descrip INTO v_descrip
FROM PRODUCT WHERE prodid = :new.product_id;
ALERT: INVENTORY LOW ORDER:
It has come to my personal attention that, due to recent
transactions, our inventory for product #
TO_CHAR(:new.product_id) || '-- ' || v_descrip ||
-- has fallen below acceptable levels.
'Yours,' user.
ELSIF :old.amount_in_stock < :new.amount_in_stock THEN NULL;
ELSE v_msg_text := 'Product #' || TO_CHAR(:new.product_id)
ordered.
DBMS_PIPE.PACK_MESSAGE(v_msg_text);
stat_send := DBMS_PIPE.SEND_MESSAGE('INV_PIPE');
END;
Supinfo Laboratory of Oracle Technologies Page 74 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Æ On the server, events are logged by executing a query on the

data and then by performing actions manually in order to send a message using
a pipe when the inventory for a particular product reaches a certain critical threshold. This trigger
use the package provided by Oracle DBMS_PIPE to send the message.

9.6.8 Advantages of Database Triggers

Database triggers provide enhanced data security by performing checks on

security based on values as well as enabling value-based auditing itself and not on
actions.
Database triggers provide better data integrity by allowing the implementation
dynamic data integrity constraints. They also allow the use of integrity constraints
complex referential and allow actions related to always execute together.
Triggers are used as an alternative to features provided by the Oracle server. But they are used
most often to obtain simpler or more complex features than those provided by the
server, or if the feature does not exist on the server.

Page 75 / 102 Supinfo Oracle Technologies Laboratory

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

10 MANAGING SUBPROGRAMS AND TRIGGERS

10.1 Privileges
10.1.1 System privileges

Privileges are the privileges that allow users to make modifications (creation,
object deletion) directly on the database.
To be able to create procedures and triggers, one needs the system privileges CREATE PROCEDURE and
CREATE TRIGGER.
One can modify, delete or execute (ALTER, DROP, EXECUTE) the subprograms and triggers that one
created without needing other privileges. In order to modify the sub-programs and triggers of others
Users must specify the ANY parameter when creating a TRIGGER or a PROCEDURE. There exists
also specific privileges allowing the user possessing them to delete, modify or
execute any subprogram or trigger: CREATE ANY [PROCEDURE | TRIGGER], DROP ANY
[PROCEDURE | TRIGGER] or EXECUTE ANY PROCEDURE.
The keyword PROCEDURE is used to refer to the rights on stored procedures, functions, and
packages.

10.1.2 Object privileges

Object privileges are privileges that users can grant to other users on
objects that they own.
If a PL/SQL subprogram or the trigger references objects located in other schemas, it is necessary that
we are explicitly allowed to execute it, this authorization is not valid if it is given by
the intermediary of a role.
By default, PL/SQL subprograms are executed under the security domain of the owner, so for
execute PL/SQL subprograms if we do not have the EXECUTE ANY system privilege, we need the
EXECUTE object privilege. This privilege is granted by the owner of the object.
Since triggers are executed from DML orders, it is not necessary to have privileges for them.
execute.

10.2 Granting access to data

All database users can grant access to the objects they are
owners to other users. This access can be direct or indirect.
Suppose a table EMP located in the personnel schema and a developer named Scott and a user.
final Green. We want to ensure that Green can only access the EMP through the procedure
QUERY_EMP, where Scott is the owner. To do this, we can provide direct or indirect access to Green.
Direct Access:
Direct access involves granting object privileges on the concerned object and creating a procedure that exploits
the object.
Since the personnel diagram, we grant object privileges on the EMP table to SCOTT.
SQL> GRANT select
2 ON emp
3 TO scott;
Scott created the QUERY_EMP procedure that queries the EMP table.
Indirect Access:
Indirect access consists of granting the EXECUTE object privilege on the procedure requiring access to the table.
from another user.
-SCOTT grants the EXECUTE object privilege to GREEN on the QUERY_EMP procedure
SQL> GRANT execute
2 ON query_emp
3 TO green
By default, the PL/SQL subprogram will be executed under the security domain of the owner. As
Scott has direct privileges on the EMP table and has created the QUERY_EMP procedure, Green can retrieve some.
information from the EMP table using the QUERY_EMP procedure
Supinfo Laboratory of Oracle Technologies Page 76 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

If Green has a table EMP in his schema, the QUERY_EMP procedure will not refer to it.
table, it will refer to the EMP table that the Scott schema can access. It can be an EMP table
personal or a public EMP table.

10.3 Specify user rights

If one wants to ensure that the procedure executes using the security context of the user running it and
not the owner's, we can specify the AUTHID CURRENT_USER parameter when creating the
procedure. Thus, at each execution of the procedure, the user's rights will be used as a priority.
By default, the procedure runs using the security context of the owner, but it can also be
explicitly force the procedure to always use the owner's security domain by specifying the
AUHTID parameter.

Example:

CREATE OR REPLACE PROCEDURE

query_emp
(v_id IN [Link]%TYPE,
OUT [Link]%TYPE
v_salary OUT [Link]%TYPE,
v_comm OUT [Link]%TYPE)
AUTHID CURRENT_USER
IS
BEGIN
SELECT employee_name, salary, commission

INTO v_name, v_salary, v_comm

FROM emp
WHERE empno=v_id;
END query_emp;

10.4 Manage stored PL/SQL objects

When a procedure is created, all the information concerning it is recorded in the dictionary of
data or in variables so that they can be accessed from an SQL editor.

Information Description Access method

stored
General Information about the object View USER_OBJECTS of
data dictionary
Procedure Object Explorer
Builder
Source code of Procedure text View USER_SOURCE of
procedure data dictionary
Stored program editor of
PB
Source code of Trigger text See USER_TRIGGERS
trigger data dictionary
Procedure Trigger Editor
Builder
Settings Mode: IN/OUT/IN OUT DESCRIBE command of
And type of data SQL*Plus
PB .DESCRIBE command
p-code Compiled object code Not accessible
Errors of Syntax errors Vue USER_ERRORS of
compilation PL/SQL data dictionary
SHOW_ERRORS command from
SQL*Plus
Procedure Builder Compiler
Information of Variables and messages Package provided by Oracle
debug of the debug DBMS_OUTPUT
Page 77 / 102 Supinfo Laboratory of Oracle Technologies
07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

During execution Specified by the user PL/SQL Procedure Interpreter

Builder

10.5 Information about the object

10.5.1 USER_OBJECTS

To obtain information about the objects stored in a database schema, the view is used.
USER_OBJECTS of the data dictionary. This view contains some of the following columns:

Column Description of the column

OBJECT_NAME Name of the object
OBJECT_ID Internal identifier of the object
OBJECT_TYPE Type of the object. It can be TABLE, PROCEDURE, FUNCTION,
PACKAGE, PACKAGE BODY, TRIGGER
CREATED Date of creation of the object
LAST_DDL_TIME Date of the last modification of the object
TIMESTAMP Date and time of the last recompilation
STATUS VALID or INVALID

You can also look in the ALL_OBJECTS and DBA_OBJECTS views, which additionally contain the name.
of the owner of the object in the OWNER column.

10.5.2 List all procedures and functions

To list all the procedures and functions of our schema, we perform a SELECT on the view by specifying the
type PROCEDURE and FUNCTION in the WHERE clause.

Example:

SQL> SELECT object_name, object_type

2 FROM user_objects
3 WHERE object_type in ('PROCEDURE', 'FUNCTION')
4 ORDER BY object_name
5 /

OBJECT_NAME OBJECT_TYPE
----------------- ---------------------
ADD_DEPT PROCEDURE
LEAVE_EMP PROCEDURE
LOG_EXECUTION PROCEDURE
PROCESS_EMPS PROCEDURE
QUERY_EMP PROCEDURE
RECEIVE_MESSAGE PROCEDURE
SEND_MESSAGE PROCEDURE
TAX FUNCTION
Æ Display the names of all the procedures and functions we have created

10.6 Procedure text

10.6.1 The view of the USER_SOURCE data dictionary

When a procedure is compiled, the source code is stored in a view of the data dictionary.
Thus, to examine the source code of the procedure, one can either look in the script file used for
its creation, either query the USER_SOURCE view.

Supinfo Laboratory of Oracle Technologies Page 78 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

Column Description of the column

NAME Name of the object
TYPE Type of object, for example PROCEDURE, FUNCTION, PACKAGE, PACKAGE
BODY
LINE Source code line number
TEXT Line of code text

This view of the data dictionary can be used under SQL*Plus or under Procedure Builder to
regenerate a script file creating the procedure in case the original source file is missing.
We can also use the ALL_SOURCE and DBA_SOURCE views which additionally contain the name of the
owner of the procedure in the OWNER column.

10.6.2 List the code of all procedures and functions

To list the source code of a procedure, a SELECT is performed on the view USER_SOURCE, specifying the
name of this procedure in the WHERE clause.

Example:

SQL> SELECT text

2 FROM user_source
3 WHERE name = 'QUERY_EMP'
4 ORDER BY line;

TEXT
--------------------------------------------------
PROCEDURE QUERY_EMP
(v_id IN [Link]%TYPE,
v_name OUT [Link]%TYPE,
OUT [Link]%TYPE
v_comm OUT [Link]%TYPE)
IS
BEGIN
SELECT ename, sal, comm
INTO v_name
FROM emp
WHERE empno = v_id;
END query_emp;
Æ This query displays the source code of the QUERY_EMP procedure.

10.6.3 List the stored procedure code with procedure

builder

To list the code of a procedure with Procedure Builder, the object explorer is used.
We connect to the database, select the objects from the database, and click the button.
Next, we choose the procedure owner's diagram and click again on the button.
Then we select the node of the stored programs and develop it as before. Finally
double-click on the stored procedure to open the stored program editor containing the
source code of the procedure.
Once the code is displayed, it is possible to export it to a text file by selecting Export from the menu.
The editor. The source code will be stored in a .pls file.

10.7 Text of a trigger

Page 79 / 102 Supinfo Laboratory of Oracle Technologies

July 3, 2003 [Link]
 Advanced PL/SQL - Version 1.2

10.7.1 USER_TRIGGERS

As with procedures, when a trigger is compiled, the source code is stored in the view.
USER_TRIGGERS of the data dictionary. This view is composed, among others, of the columns
following:

Column Description of the column

TRIGGER_NAME Trigger name
TRIGGER_TYPE Type : BEFORE, AFTER, INSTEAD OF
TRIGGERING_EVENT DML operation triggering the trigger
TABLE_NAME Name of the table affected by the trigger
REFERENCING_NAMES Name used by :OLD and :NEW
WHEN_CLAUSE WHEN clause used for triggering
STATUS Status of the trigger
TRIGGER_BODY Actions taken upon triggering

We can also use this view to regenerate the script file of a trigger via SQL*Plus or
the database trigger editor of Procedure Builder.
The ALL_TRIGGERS and DBA_TRIGGERS views also allow you to obtain information about the
triggers with the owner's name in the OWNER column.

10.7.2 List the trigger codes

To display the information of a specific trigger, a SELECT is performed on the view by specifying the name.
the trigger in the WHERE clause:

Example:

SQL> SELECT trigger_name, trigger_type,

2
3 when_clause, status, trigger_body
4 FROM user_triggers
5 WHERE trigger_name = 'DERIVE_COMMISSION_PCT';

TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT

------------------------- ---------------- ----------------
REFERENCING_NAMES
-------------------- -----------------------------------
WHEN_CLAUSE STATUS
----------------------------------- --------
TRIGGER_BODY
-----------------------------------------------------------
DERIVE_COMMISSION_PCT BEFORE EACH ROW INSERT OR UPDATE
EMP REFERENCING NEW AS NEW OLD AS OLD
ENABLED
BEGIN
IF NOT (:[Link] IN ('MANAGER', 'PRESIDENT'))
AND :[Link] > 5000
THEN
RAISE_APPLICATION_ERROR
(-20202, ’EMPLOYEE CANNOT EARN THIS AMOUNT’);
END IF;
END;
Æ Display the information about the trigger DERIVE_COMMISSION_PCT using the view
USER_TRIGGERS

Supinfo Laboratory of Oracle Technologies Page 80 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

10.8 The parameters

10.8.1 DESCRIBE in SQL*Plus

To display information about a function's parameters, we use the DESCRIBE command.

SQL*Plus. This command will display the name of arguments, their type, the parameter type as well as the
presence of a default value if there is one.

Example:

SQL> DESCRIBE QUERY_EMP

PROCEDURE QUERY_EMP
Argument Name Type In/Out Default?
------------------ ----------------- ------ --------
V_ID NUMBER(4) IN
V_NAME VARCHAR2(10) OUT
V_SALARY NUMBER(7,2) OUT
V_COMM NUMBER(7,2) OUT

SQL> DESCRIBE ADD_DEPT

PROCEDURE ADD_DEPT
Argument Name Type In/Out Default?
------------------ ------------------ ------ --------
V_NAME VARCHAR2(14) IN DEFAULT
V_LOC VARCHAR2(13) IN DEFAULT

SQL> DESCRIBE TAX

FUNCTION TAX RETURNS NUMBER
Argument Name Type In/Out Default?
------------------ ----------------- ------ --------
V_SALARY NUMBER IN
Æ These examples display information about different procedures and functions.

10.8.2 The .DESCRIBE command under procedure builder

Under Procedure Builder, to display information about the parameters of a function, one uses the
COMMAND .DESCRIBE in the PL/SQL command interpreter. The result will be displayed in the form of a
report and not in the form of a table like in SQL*Plus.

Example:

PL/SQL> .DESCRIBE PROCEDURE FORMAT_PHONE

Procedure Body; FORMAT_PHONE
Parameters:
IN OUT VARCHAR2
Compiled: YES
Open: NO
References:
Package Spec STANDARD
Referenced by:

Æ Information about the FORMAT_PHONE procedure via Procedure Builder

Page 81 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

10.9 Compilation Errors

10.9.1 Detecting compilation errors with the editor
stored program

When a procedure is recorded in Procedure Builder, it is compiled. If there are errors

produced during this compilation, they will be displayed in the compilation error panel, in the
base of the program editor window and the cursor moves automatically to the location of the
first error in the source code.
If no errors are detected, "Successfully Compiled" appears in the compilation panel.

10.9.2 USER_ERRORS

To view the compilation error text, one uses the USER_ERRORS view of the dictionary
data or the SQL*Plus command SHOW ERRORS. The USER_ERRORS view contains the columns
next:

Column Description of the column

NAME Name of the object
TYPE Type of the object, for example PROCEDURE, FUNCTION, PACKAGE,
PACKAGE BODY, TRIGGER
SEQUENCE Sequence number, for sorting
LINE Line number of the source code where the error occurred
POSITION Position in the line where the error occurred
TEXT Error message text

One can also obtain information about compilation errors through the ALL_ERRORS views.
DBA_ERRORS, which also include the names of the object owners.

10.9.3 Detecting compilation errors: example

Example 1:

SQL> CREATE OR REPLACE PROCEDURE log_execution

2 IS
3 BEGIN
4 INPUT INTO LOG_TABLE (user_id, log_date) -- error
5 VALUES (user, sysdate);
6 END;
7 /

Procedure created with compilation errors.

Æ This code produces an error but does not specify their nature.

To obtain information regarding errors, one can use the USER_ERRORS view in an order.
SELECT in order to display the information we need to correct the code.

Supinfo Laboratory of Oracle Technologies Page 82 / 104

[Link] 03/07/2003
 Advanced PL/SQL - Version 1.2

Example 2:

SQL> SELECT line|| '/'|| position POS,text

2 FROM user_errors
WHERE LOG_EXECUTION
4 ORDER BY line;
POS TEXT
---------- ----------------------------------------------
4/7 PLS-00103: Encountered the symbol 'INTO' when
expecting one of the following:
:= . ( @ % ;
5/1 PLS-00103: Encountered the symbol 'VALUES' when
expecting one of the following:
. ( , % from
The symbol 'VALUES' was ignored.
May 23 PLS-00103: Encountered the symbol ";" when
expecting one of the following:
. ( , % from
Æ This order provides complete information about the errors that occurred during the
compilation of the LOG_EXECUTION procedure.

It is also possible to view compilation errors using the SHOW ERRORS command.
SQL prompt of SQL*Plus

Example 3:

SQL> SHOW ERRORS PROCEDURE log_execution

Errors for PROCEDURE LOG_EXECUTION:

LINE/COL ERROR
-------- -------------------------------------------------
4/7 PLS-00103: Encountered the symbol 'INTO' when
expecting one of the following:
:= . ( @ % ;
5/1 PLS-00103: Encountered the symbol 'VALUES' when
expecting one of the following:
. ( , % from
The symbol 'VALUES' was ignored.
5/23 PLS-00103: Encountered the symbol ";" when
expecting one of the following:
. ( , % from
Æ Errors during the compilation of the LOG_EXECUTION procedure are displayed thanks to
to the command SHOW ERRORS.

The use of the SHOW ERRORS command without specifying arguments allows displaying errors of
compilation of the last compiled object.

10.10 Information on debugging

10.10.1 Debug using the DBMS_OUTPUT package

It is possible to use the procedures of the package included in the Oracle DBMS_OUTPUT server to display
values and messages from a PL/SQL block. This display is done by accumulating data in a
buffer then by allowing the sending of data from the buffer to the display terminal. To call upon the
procedures of this package are always prefixed with the name of the package DBMS_OUTPUT.
This package allows developers to closely track the execution process of a function or
from a procedure by sending messages and values. In SQL*Plus, it is preferable to use SET.
SERVEROUTPUT ON or OFF instead of the ENABLE or DISABLE procedures.
It is advisable to take certain actions to facilitate debugging:
display a message when a procedure starts, when it stops or a message indicating
that an operation has completed successfully.
display the values of a loop counter

Page 83 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

display the values of a variable before and after initialization

The buffer is not emptied until the block has completed correctly.

10.10.2 Debug the subprograms using procedure

builder

We can also execute debugging actions on client or server sub-programs in

Procedure Builder. To do this, we need to load the subprogram:
1. From the object explorer we choose ProgramÆ PL/SQL Interpreter in the menu
2. In the menu, select ViewÆ Navigator Pane
3. From the navigation panel, we expand the node Program Units or Database objects
4. Then click on the subprogram that you want to debug.

Figure n°1: Overview of the debugging window in Procedure Builder

10.10.3 Create breakpoints

In Procedure Builder, it is possible to create breakpoints in a program to interrupt

the execution of a program at a specific location in the source code. When the PL/SQL engine encounters a
breakpoint in a program, it suspends execution at the line preceding the breakpoint and hands over control to
the PL/SQL interpreter of Procedure Builder.
To create a breakpoint in a program, simply double-click on the line where you want it.
set up.
In the previous figure, we can see that line 00007 has been set as a breakpoint. The lines where
the breakpoints are called B(XX), where XX represents the breakpoint number in the
program starting with 01.
Breakpoints can only be created on lines containing an executable expression, it is
impossible to set a breakpoint on a variable declaration line in the DECLARE section, unless
that this variable is not initialized at the same time.

Supinfo Laboratory of Oracle Technologies Page 84 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

10.10.4 Use the debugging levels

To start the debugger, we run the program that we are debugging.

When a debugging action interrupts the execution of the program, the interpreter takes control and
establish what is called debugging levels. At a debugging level, it is possible to enter
commands and orders PL/SQL to inspect and modify the status of the interrupted program.

Figure n°2: State of variables at the breakpoint.

In the previous figure, the Stack node is expanded to see the values of the variables used in the
program at the breakpoint.

10.10.5 Monitor the execution of programs

In the Procedure Builder interpreter, there are buttons to control the execution of the
debugger. These buttons are available when setting breakpoints in a program. When one
run the program, it stops at the first breakpoint encountered and then you can use the object browser
to visualize the values of the variables. We can then choose the action to be performed using these buttons. We can
either continue until the next breakpoint, or continue execution without considering the breakpoints
following.

Figure No. 3: Overview of the control buttons.

We use the STEP INTO/OVER/OUT buttons to manage the execution of a program stopped at a breakpoint.
STEP INTO executes the program stopping at all breakpoints, STEP OVER executes the program
Without concerning itself with breakpoints and STEP OUT, it executes the program to the end when it is stopped at a.
breakpoint. Once the operation is executed, control is returned to the interpreter.
The GO button is used to run the program until it finishes correctly or encounters an error.
breakpoint.

Page 85 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

The RESET button allows you to return control to a higher level of debugging without continuing to
next breakpoint.

Supinfo Laboratory of Oracle Technologies Page 86 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

11 MANAGE DEPENDENCIES
11.1 Dependent and referenced objects
11.1.1 Understanding Dependencies

Some objects refer to other objects in their definitions and these objects are said to be dependent. By
For example, a stored procedure can contain a SELECT statement returning a column from a table.
Consequently, a stored procedure is called a dependent object while the table to which it refers
a reference is called a referenced object.
Among the dependent objects are views, procedures, functions, package specifications,
the package bodies and the database triggers.
The referenced objects can be a table, a view, a sequence, a synonym, a procedure, a
function or a package specification.
If the definition of a referenced object is modified, the dependent objects may not continue to function.
correctly. For example, if the table definition is modified, it is possible that the dependent procedure
can no longer function properly.
The Oracle server automatically records dependencies between objects. To manage dependencies,
All schema objects have a Status, which can be Valid or Invalid, recorded in the dictionary of
data. This status can be viewed using the USER_OBJECTS view of the data dictionary.
When the status of an object appears as valid, it means that it has been compiled and is usable.
immediately. If its status appears as invalid, the object must be compiled before being used.

11.1.2 Direct and indirect dependencies

Direct dependencies are dependencies for which the dependent object directly references.
to the referenced object, for example in the case of a procedure dependent on a table.
Indirect dependencies are dependencies for which the dependent object references another object.
through another object. For example, in the case of a procedure dependent on a view and which by
consequent is dependent on the underlying table.

11.1.3 Local dependencies

Local dependencies are dependencies for which the dependent object and the referenced object are
are found on the same node of the same database. These dependencies are managed automatically
by the Oracle server using the internal database table 'depends-on'.
In the case of local dependencies, the Oracle server implicitly recompiles any object whose status is
invalid during its next call. So if a referenced object changes, it leads to invalidation of
The dependent object, recompilation will be automatic.

11.1.4 Remote dependencies

Remote dependencies are dependencies for which the dependent object and the referenced object are
located on separate nodes. The Oracle server does not manage dependencies among schema objects.
distant apart if it involves dependencies between a local procedure and a remote procedure.
If the referenced object is to change, the stored local procedures and all their dependent objects
will be invalidated but will not be recompiled automatically during the next call.

Page 87 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

11.1.5 A scenario of local dependencies

Figure n 1: Diagram of local dependencies

The QUERY_EMP procedure directly refers to the EMP table, while the ADD_EMP procedure refers to
day the EMP table indirectly through the NEW_EMP view.
According to the changes made, the ADD_EMP procedure may or may not be invalidated.
Changes to procedures not related to ADD_EMP will have no consequences.
on the validity of this.
If a column is added to the EMP table, the ADD_EMP procedure will be invalidated and will be recompiled with
success provided that a list of columns is given in the INSERT order and that the added column does not
a NOT NULL constraint.

11.2 Visualize the dependencies

11.2.1 Show direct dependencies with
USER_DEPENDENCIES

To determine which objects in the database need to be manually recompiled, we display

the direct dependencies of the USER_DEPENDENCIES view of the data dictionary. This view is
composed of the following columns:

Column Description of the column

NAME Name of the dependent object
TYPE Type of dependent object (PROCEDURE, FUNCTION,
PACKAGE, PACKAGE_BODY, TRIGGER or VIEW
REFERENCED_OWNER Diagram where the referenced object is located
REFERENCED_NAME Name of the referenced object
REFERENCED_TYPE Type of the referenced object
REFERENCED_LINK_NAME Link to the database used to access the object
referenced

One can also visualize direct dependencies by examining the views of the data dictionary.
ALL_DEPENDENCIES and DBA_DEPENDENCIES which also contain the owner's name in the
column OWNER.
Example:

SQL> SELECT name, type, referenced_name, referenced_type

2 FROM user_dependencies
3 WHERE referenced_name IN ('EMP', 'NEW_EMP');

NAME TYPE REFERENCED_NAME

---------- ---------- ----------------- ---------------
QUERY_EMP PROCEDURE EMP TABLE
ADD_EMP PROCEDURE NEW_EMP VIEW
NEW_EMP VIEW EMP TABLE
Supinfo Laboratory of Oracle Technologies Page 88 / 104
[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

11.2.2 Display direct and indirect dependencies

To display direct and indirect dependencies, we will execute a script that creates the objects allowing us to do so.
to visualize the dependencies. This script creates a table DEPTREE_TEMPTAB that will contain information
on a specific referenced object. This table will be filled using the DEPTREE_FILL procedure.
procedure accepts three parameters: object_type, which is the type of the referenced object, object_owner, which is the
schema of the referenced object and object_name, which is the name of the referenced object.
The information regarding dependencies will be displayed using user views.
additional DEPTREE and IDEPTRE.

Example:

SQL> @UTDLTREE

SQL> EXECUTE deptree_fill ('TABLE', 'SCOTT', 'EMP')

PL/SQL procedure completed successfully.
Æ We execute the UTLDTREE script, then we fill the DEPTREE_TEMPTAB table thanks to the
script DEPTREE_FILL using information from the EMP table of SCOTT.

11.2.3 Show dependencies

We display a representation of dependencies by querying the DEPTREE view.

Example:

SQL> SELECT nested_level, type, name

2 FROM deptree
3 ORDER BY seq#;

NESTED_LEVEL TYPE NAME

------------ --------- ----
0 TABLE EMP
1 VIEW NEW_EMP
2 PROCEDURE ADD_EMP
1 PROCEDURE QUERY_EMP

One can also display an indented representation of the same information by making a query on
the IDEPTREE view, containing a single column, DEPENDENCIES.

Example:

SQL> SELECT *
2 FROM ideptree;

DEPENDENCIES
---------------------------------------------------
TABLE [Link]
VIEW SCOTT.NEW_EMP
PROCEDURE SCOTT.ADD_EMP
PROCEDURE SCOTT.QUERY_EMP

11.3 A scenario of local name dependencies

One must be cautious of cases where the creation of a table, a view, or a synonym leads to invalidation of a
dependent object because it interferes with the Oracle server hierarchy to resolve references to
names.
One must always anticipate the effect that a new object name will have on a dependent procedure.
For example, let's assume that the QUERY_EMP procedure refers to the public synonym EMP. However
We just created a new table named EMP in our own schema. If the table we created

Page 89 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

is a copy of the view (same columns), the procedure will be compiled correctly and will therefore be
still valid. However, if the table has different columns, recompilation of QUERY_EMP
will produce errors and therefore will remain INVALID.
Now, if we delete the EMP table, the dependent objects become invalid. If we have access to a
public object named EMP through a public synonym and that this object has the same structure, the object
recompiled will henceforth refer to the public EMP object.
Security dependencies can be monitored in the USER_TAB_PRIVS view of the data dictionary.

11.4 Remote Dependencies

11.4.1 Recompilation of dependencies

When a referenced object changes, the dependent object may become invalid. If the invalidity concerns a
local dependency, the object is recompiled implicitly. However, when it comes to a remote dependency
Recompilation does not happen automatically; it must be done explicitly.
We check the success of the explicit recompilation of remote dependent procedures and the recompilation.
implicit local dependent procedures by checking the status of these procedures in the view
USER_OBJECTS.
If an automatic recompilation of a dependent local procedure fails, its status remains
INVALID and the Oracle server produces a runtime error. So, to avoid interrupting the operation
from the procedure, it is strongly recommended to manually recompile the dependent local objects instead.
to rely on automatic compilation.

11.4.2 Concept of remote dependencies

The behavior of dependencies is guided by the mode chosen by the user: the verification of
TIMESTAMP (or timestamp marker) or SIGNATURE verification.
TIMESTAMP verification:
When a procedure is compiled or recompiled, a timestamp is assigned to it and recorded in
the data dictionary. The timestamp is a record of the time at which the procedure has
was created, modified or replaced. In addition, the compiled version of the procedure contains information about
each remote procedure it refers to, and in particular the schema of the remote procedure, the
package name
When a dependent procedure is used, Oracle compares the remote timestamps recorded at
from the compilation with the current time markers of the referenced remote procedures. Depending on the
As a result of this comparison, two situations can occur:
If the time markers match, the local and remote procedures execute without
compilation.
If a timestamp of the referenced remote procedures does not match, the local procedure
is invalidated and an error is returned to the calling environment. Additionally, all other procedures
locales that depend on the remote procedure with the new timestamp are invalidated. By
For example, if several local procedures call a remote procedure and the remote procedure has been
recompiled, when a local procedure executes and notices that the timestamp has changed, all the
Local procedures depending on the remote procedure are invalidated.
Time markers are compared only when a body order of a local procedure executes.
a remote procedure.
SIGNATURE Verification:
The Oracle server provides the ability to check remote dependencies using signatures. The
local procedures are not affected by signature verification since in this case recompilation
happens automatically.
The signature of a procedure contains the name of the package, the procedure or function, the types of
basic parameters and the parameter mode (IN, OUT, IN OUT). Only the type and mode of the parameters
are important, the name of the parameter has no impact on the signature.
The timestamp recorded in the calling program is compared with the current timestamp.
of the called remote program. If the markers match, the call proceeds normally. If they do not
they do not match, the Remote Procedure Calls (RPC) layer performs a simple test to compare the signature
to determine if the call is secure or not. If the signature has not been changed in an incompatible way
The execution continues, otherwise an error status is returned.
Supinfo Laboratory of Oracle Technologies Page 90 / 104
[Link] March 7, 2003
 Advanced PL/SQL - Version 1.2

11.4.3 Remote Dependency Scenario in Timestamp Mode

[Link] Automatic recompilation

When a distant object has been modified, it is strongly advised to manually recompile the local objects.
dependents rather than relying on the automatic mechanism of remote dependencies to avoid
production disruptions.
The remote dependency mechanism is different from the automatic mechanism of local dependencies already.
mentioned. The first time a remotely recompiled subroutine is called by a local subroutine, it
an execution error occurs and the local subroutine is invalidated. On the second call of the sub-
an implicit recompilation takes place.

[Link] Example

A local procedure that references a remote procedure is invalidated by the Oracle server if the
the remote procedure is recompiled after the procedure has been compiled. This is due to the fact that during the
compilation of the local procedure, the procedure records the timestamp corresponding to all the
dependent objects in the lep-code. So if the remote procedure is recompiled, the comparison of
time markers do not agree, which consequently leads to the invalidation of the procedure.
For example, we compile a remote procedure B, on which a procedure A depends, at 8 o'clock.
If procedure A is recompiled at 9 o'clock, it will still be considered valid by the Oracle server.
since the timestamp recorded in the procedure code will correspond to the marker
during the remote procedure at compilation to 8 hours.
If the procedure A is executed without recompiling it, the timestamp will correspond to a compilation of the
Procedure B prior to the last compilation and therefore will result in an invalidation of procedure A
since the comparison of time markers does not match. The recompilation mechanism of
local procedures will then recompile procedure A using the timestamp of the compilation of the
Procedure B at 8 o'clock and thus during the second execution of this the comparison of the markers of
the weather will be consistent.

11.4.4 The signature mode

To solve some problems posed by the time marker dependency model, one can use
the signature model. This allows the remote procedure to be recompiled without the local procedure
dependent may be affected.
The signature of a subroutine consists of the name of the subroutine, the data type of the
parameters, the mode of the parameters, the number of parameters and the data type of the returned value
by a function.
If a remote program is changed and then recompiled but the signature does not change, then the procedure
locale can execute the remote procedure. With the method of time markers, the local procedure
would have been invalidated since these markers no longer match.

11.5 Manual recompilation

11.5.1 Recompile a PL/SQL program

The recompilation of a PL/SQL program can be done automatically through a recompilation when
the execution can be done explicitly with an ALTER command with the parameter
COMPILE.

ALTER PROCEDURE [SCHEMA.]procedure_name COMPILE

Page 91 / 102 Supinfo Laboratory of Oracle Technologies
07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

ALTER FUNCTION [SCHEMA.]function_name COMPILE

ALTER PACKAGE [SCHEMA.]package_name COMPILE [PACKAGE]

ALTER PACKAGE [SCHEMA.]package_name COMPILE BODY

ALTER TRIGGER trigger_name [COMPILE [DEBUG]]

If a recompilation is successfully performed, the object becomes valid. Otherwise, the Oracle server returns a
error and the status of the object remains invalid.
When recompiling a PL/SQL object, the Oracle server first recompiles all the invalid objects of which it
depends.
The COMPILE PACKAGE option recompiles the body of the package as well as the specification without worrying about its
invalidity. The COMPILE BODY option recompiles only the body of the package. The recompilation of the specification
of a package causes the invalidity of all local objects dependent on this specification, such as the
procedures calling procedures or functions of the package. The body of the package also depends on the
specification.

11.5.2 Recompilation failure

The recompilation of dependent procedures and functions will fail if:

• The referenced object is deleted or renamed
• The data type of the referenced column has changed.
• The referenced column is deleted
• A referenced view is replaced by a view based on different columns.
• The parameter list of a referenced procedure is modified.

The success of a recompilation is based on exact dependency. If a referenced view is recreated, any object
Being dependent on the view must be recompiled. The success of a recompilation depends on the columns that the view
current contains and columns that the referenced object needs to execute. If the required columns do not
If it is not part of the new view, the object will remain invalid.

11.5.3 Successful recompilation

The recompilation of dependent procedures and functions will be successful if:

• Columns have been added to the table and none of the new columns are defined as NOT
NULL
• The data type of the referenced columns has not changed.
• A private table has been deleted, but a public table with the same name and the same
structure exists. The program will henceforth depend on the public table
• The body of a referenced PL/SQL procedure has been modified and recompiled without errors.

11.5.4 Recompile the procedures

During the recompilation of procedures, several factors can lead to its failure. To minimize
the issues related to dependencies must be:
• Declare records using the %ROWTYPE attribute.
• Declare the variables using the %TYPE attribute
• Execute queries using the SELECT * notation
• Specify a list of columns in the INSERT orders

11.6 Packages and dependencies

Supinfo Laboratory of Oracle Technologies Page 92 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

To simplify dependency management with packages, it is preferable to refer to a

procedure or function of a package from an independent procedure or function.
Thus, if the body of the package is modified while the specification remains the same, the independent procedure
referring to the package specification will remain valid.
On the other hand, if the specification changes, the external procedure referencing it will be invalid just like the
body of the package.
The reference to an external procedure from the body of a package does not provide any management improvement.
dependencies because the entire package body will then depend on the validity of the procedure. It is strongly
recommended to include this procedure in the package.
If the external procedure referenced in the package changes, then the body of the package will be invalidated while
the package specification will remain valid.

Page 93 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

MANIPULATE LARGE OBJECTS

12.1 The LOBs
12.1.1 What is a LOB

A LOB is a large object stored directly in the database. There are four types of LOBs.
(BLOB, CLOB, NCLOB, BFILE). BLOBs are binary Large Objects, CLOBs are Large
Character type objects, NCLOBs are fixed-size character Large Objects, BFILEs are
binary files stored outside of the database.
LOBs are characterized in two ways: their interpretation by the Oracle server (binary or character)
and their storage method. LOBs can be stored in the database or in files
hosts. There are two categories of LOBs:
Internal LOBs (CLOB, NCLOB, BLOB) are stored directly in the database.
External LOBs (BFILE) are stored outside the database.
The Oracle server will not convert data between types. For example, a user creates a table X
with a CLOB column and a table Y with a BLOB column, the data will not be directly
transferable between the two columns.
The BFILES are accessible in read-only mode from the Oracle server.

12.1.2 Opposition between LONG and LOB data types

The LONG and LONG RAW data types were previously used for unstructured data.
such as binary images, documents, or geographic information. These types of data are
now replaced by LOB data types. These data types are different from LONG and
LONG RAW because LOBs are not interchangeable. LOBs are not supported by the interface.
application programming LONG and vice versa.
Here is a list of the main differences between LONG and LOB:

LONG, LONG RAW LOB

One column per table Multiple columns per table
Up to 2 Giga Up to 4 Giga
A SELECT returns the data A SELECT returns the locator
The data is stored in the The data is stored in the database or in
base outside
Sequential access to data Random access to data

12.1.3 Anatomy of a LOB

A LOB is composed of two distinct parts: the locator and the value. The locator is a
indicator of where the value of the Lob is located in the database. The value represents the data
constituting the true value of the stored object.
In addition, a program accessing and manipulating LOBs requires the declaration of a pointer or a
LOB locator.
When a user creates an internal LOB, the value is stored in the LOB segment and the locator of
The offline value of the LOB is placed in the corresponding row column of the table. The external LOBs
store the data outside the database so the table just contains the locator of the value
LOB.
For an internal type LOB, the value of the LOB is directly stored in line with the other rows if the...
the size of the LOB is less than 4000 bits. When the value of the LOB exceeds 4000, it is
automatically moved out of line.
When creating a table that contains a LOB column, the default storage will be ENABLE STORAGE IN
ROW. If one does not want the values of the LOBs to be stored in the rows, even if their size is
less than 4000 bits, the DISABLE STORAGE IN ROW option is specified in the storage clause.

Supinfo Laboratory of Oracle Technologies Page 94 / 104

[Link] 03/07/2003
 Advanced PL/SQL - Version 1.2

12.2 Internal and external LOBs

12.2.1 The internal LOBs

Internal LOBs are LOBs whose value is stored in the Oracle server. Internal LOBs are
BLOBs, CLOBs, and NCLOBs. These can appear as an attribute of a type defined by
the user, as a column of a table, as a substitution or host variable or as a
result, parameter or PL/SQL variable.
Internal LOBs can take advantage of Oracle server features such as
mechanisms of cooperation and restoration.
The BLOB data type is interpreted by the Oracle server as a stream of bits, similar to a type of
LONG RAW data.
The CLOB type is interpreted as a stream of single-byte characters.
The NCLOB type is interpreted as a multibyte character stream with a fixed size, based on bit size.
defined by the national character of the database.

12.2.2 Manage internal LOBs

To fully interact with LOBs, interfaces are provided in the PL/SQL package DBMS_LOB and
in Oracle Call Interface.
The Oracle server also provides support for LOB management through SQL.
The general method for managing internal LOBs is as follows:
• We create and fill a table containing the LOB data type.
• We declare and initialize the LOB locator in the program.
• SELECT FOR UPDATE is used to lock the row containing the LOB.
• LOBs are manipulated using the procedures of the DBMS_LOB package or OCI calls.
the LOB locator as a reference to the value of the LOB
• The COMMIT command is used to validate changes.

12.2.3 External LOBs

External LOBs are LOBs whose value is stored outside of the database, the database of
data will only contain the file locator in the form of a DIRECTORY object containing the
access path to the directory. The BFILE data type is provided by the Oracle server to give a
access to external files for database users.
Oracle SQL allows you to define BFILE objects and associate BFILE objects with external files.
correspondent, to access the security of the BFILE.
A DIRECTORY type object specifies an alias for a directory located on the server. By granting the privilege
READ on this object, we can guarantee secure access, depending on the user, to the files in the directory
(certain directories may thus be read-only or inaccessible).

12.2.4 Define BFILEs

To define BFILEs, you must first create a directory in the operating system as
that the Oracle user is defined with permissions so that Oracle can read the contents of the directory.
Then place the desired files in the directory. On the Oracle server, a table is created containing the
data type BFILE and we also create a DIRECTORY object to which we grant the READ privilege. We
then insert the lines into the table using the BFILENAME function by associating the system files
exploitation with the corresponding field. Then we declare and initialize the LOB locator in the
program, the locator will be initialized with the line and the column containing the BFILE. We can then read the
BFILE with OCI or the DBMS_LOB function using the locator as a reference to the file.

Page 95 / 102 Supinfo Laboratory of Oracle Technologies

July 3, 2003 [Link]
 Advanced PL/SQL - Version 1.2

12.3 The DBMS_LOB package

12.3.1 Use of DBMS_LOB

To manipulate LOBs, we use the package provided by Oracle DBMS_LOB. This package provides routines
to access and manipulate internal and external LOBs.
In order to load the DBMS_LOB package, the DBA must connect as SYS and execute the scripts.
[Link] execute the script [Link]. Users can then be granted
privileges to use the package.
The DBMS_LOB routines do not implicitly lock the rows containing the LOB, so the user must
lock the lines containing the internal LOB by itself before calling a subprogram that requires a
writing in the value of the LOB.

12.3.2 Functions and Procedures of DBMS_LOB

The functions and procedures included in the DBMS_LOB package can be classified into two
categories: modifiers and observers. Modifiers can change the values of the LOB then
that observers only access the LOBs in read mode.

Type Name Description

Modifier APPEND Add the content of the source LOB to the LOB of
destination
Modifier COPY Copy all or part of the source LOB into the LOB
of destination
Modifier ERASE Delete all or part of the LOB
Observer LOADFROMFILE Load data from a BFILE into a LOB
internal
Modifier TRIM Reduce the LOB value to a specified size
Modifier WRITE Write data in the LOB at a location
specific
Observer GETLENGTH Retrieve the length of a LOB value
Observer INSTR Return the position in the LOB of the nth
occurrence of the model
Observer READ Read the data in a LOB, starting from the position
specified
Observer SUBSTR Return a part of the LOB starting at the
specified position
Modifier FILECLOSE Close the file
Modifier FILECLOSEALL Close all previously opened files
Observer FILEEXISTS Check if the file exists on the server
Observer FILEGETNAME Get the directory alias and the file name
Observer FILE IS OPEN Check if a file has been opened using the
BFILE locator
Modifier FILEOPEN Open a file

The functions FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME, FILEISOPEN, and FILEOPEN are
specific to BFILEs.
All functions of the DBMS_LOB package return a NULL value if any of the passed parameters is
NULL. All modifying procedures of the package raise an error if the destination for the LOB is
specified as NULL.
The positions used in the WRITE, READ, INSTR, and SUBSTR functions must always be positive.
They represent the number of bits/characters from the beginning of the LOB where the operation will take place.
By default, the value is 1, which means that any operation not specifying a value will start at the beginning of the LOB.
For BLOB and BFILE, the position is measured in bits, whereas for CLOB and NCLOB the measurement
is done in characters.

Supinfo Laboratory of Oracle Technologies Page 96 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

12.3.3 DBMS_LOB READ and WRITE

DBMS_LOB.READ
The READ procedure is used to read and return all or part (depending on the AMOUNT parameter) of a
LOB starting at the specified position.
PROCEDURE READ (
lobsrc IN BFILE|BLOB|CLOB ,
amount IN OUT BINARY_INTEGER,
offset IN INTEGER,
buffer OUT RAW|VARCHAR2 )
If the end of the LOB is reached before the specified number of bits/characters has been read, the value returned by
AMOUNT will be less than specified.
PL/SQL supports a maximum value of 3267 for RAW and VARCHAR2. It is important to ensure that one has
allocated sufficient system resources to support these buffer sizes relative to the number of
user sessions, otherwise the Oracle server will return memory errors.
BLOBs and BFILES return RAW, others return VARCHAR2.

DBMS_LOB.WRITE

The WRITE procedure is used to write all or part (depending on the AMOUNT parameter) of data.
in a LOB from a user-defined BUFFER starting at the specified position or from the
start of the LOB.
PROCEDURE WRITE (
lobdst IN OUT BLOB|CLOB,
amount IN OUT BINARY_INTEGER,
offset IN INTEGER := 1,
buffer IN RAW|VARCHAR2 ) -- RAW for BLOB
We must ensure that the bit size corresponds to the size of the data in the buffer. WRITE has none
means to check if these sizes correspond and write a number of bits equivalent to the value of AMOUNT of
buffer in the LOB.

12.3.4 Example of table creation with LOBs

LOB columns are defined using SQL data definition language (DDL) statements such as CREATE.
TABLE. The content of a LOB column is stored in the LOB segment of the database while the
the table column simply contains a reference to this specific storage area, this reference is
called locator. It is possible, in PL/SQL, to define LOB type variables that will contain, such as
for the tables, only the value of the LOB locator.

SQL> CREATE TABLE employee

2 (emp_id NUMBER
VARCHAR2(35)
4 resumes CLOB,
5 picture BLOB);

12.4 Manipulating LOBs in SQL

12.4.1 Insertion using SQL

It is possible to directly insert a value into a LOB column using variables in SQL,
PL/SQL, 3GL-SQL or OCI

INSERT INTO employee(emp_id, emp_name,

resume picture
VALUES( 7899, 'James Dean'
8 February 1931

Page 97 / 102 Supinfo Laboratory of Oracle Technologies

July 3, 2003 [Link]
 Advanced PL/SQL - Version 1.2

It is also possible to initialize a LOB but without assigning any data to it using the function
EMPTY_CLOB() or EMPTY_BLOB(). To then enter a value into this LOB, one can use an order
UPDATE.

INSERT INTO employee

emp_id resume picture)
VALUES(7898, 'Marilyn Monroe', EMPTY_CLOB(),NULL);

If NULL is used as a value for a LOB column, the LOB is not initialized. Therefore, it does not
cannot be populated using an UPDATE statement. An INSERT statement will be needed to insert a
new line in column LOB and assign a value to it at the same time.
When creating a LOB instance, the Oracle server creates and places a locator to the offline value.
the LOB in the LOB column. SQL, OCI, and other programming interfaces operate on LOBs in
using the locators.
The EMPTY_C/B/NCLOB() function can be used as a DEFAULT constraint for a column. This
allows to initialize the column with locators.

Example:

CREATE TABLE employee

(emp_id NUMBER
VARCHAR2(35)
resume CLOB default EMPTY_CLOB()
picture BLOB default EMPTY_BLOB());
Æ This command creates a table with two LOB columns that have a default value set.
by the EMPTY_CLOB/BLOB function.

12.4.2 Updating LOBs using SQL

You can update a LOB column by initializing it to another LOB value, to NULL, or by using the
function EMPTY_CLOB or EMPTY_BLOB. We can update the Lob using a variable of
substitution in SQL that can be NULL, empty, or populated.

Example:

UPDATE employee SET

Date of Birth = 1 June 1926
WHERE emp_id = 7898;
Æ This order modifies the value of resume in the EMPLOYEE table.

When a LOB is initialized to a value equal to another, a new copy of the LOB is created. These actions
do not require a SELECT FOR UPDATE statement, we must lock a row before performing an UPDATE
only when we update a part of a LOB.

12.4.3 Update using DBMS_LOB

To update a LOB, you can also use the WRITE and WRITEAPPEND functions from the package
DBMS_LOB.

Supinfo Laboratory of Oracle Technologies Page 98 / 104

[Link] March 7, 2003
 Advanced PL/SQL - Version 1.2

Example :

DECLARE
lobloc CLOB; -- will serve as a LOB locator
Died = 5 August 1962
amount NUMBER ; -- size to write
offset INTEGER; -- where to start writing
BEGIN
SELECT resume INTO lobloc
FROM employee WHERE emp_id = 7898 FOR UPDATE;
offset := DBMS_LOB.GETLENGTH(lobloc) + 2;
amount := length(text);
DBMS_LOB.WRITE(lobloc, amount, offset, text);
Died = 30 September 1955
SELECT resume INTO lobloc
FROM employee WHERE emp_id = 7899 FOR UPDATE;
amount := length(text);
DBMS_LOB.WRITEAPPEND(lobloc, amount, text);
COMMIT;
END;

In the above example, the variable LOBLOC is used as a locator and the variable AMOUNT
corresponds to the size of the text to be added. The SELECT FOR UPDATE statement locks the row and returns the
locator of the LOB for the LOB SUMMARY column. Finally, the WRITE procedure of the package is called to
write the text into the LOB value at the specified location. WRITEAPPEND adds the text to the LOB value.
To write in LOBs using DBMS_LOB.WRITE, the LOB must be initialized. If one tries
Writing a value to an uninitialized LOB, the Oracle server will return an error.

Example:

INSERT INTO employee (emp_id, emp_name, resume)

VALUES (7900, ’JOHN WAYNE’,NULL);

SQL> DECLARE assignment of value with PL/SQL

Date of Birth = 26 May 1907
9 SELECT . . . WHERE emp_id = 7900 FOR UPDATE;
15 DBMS_LOB.WRITE(lobloc, amount, offset, text);
17 END;

DECLARE value assignment with PL/SQL

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 708
ORA-06512: at line 15
Æ We insert a row with an uninitialized CLOB and then execute a script to write but
an error is returned because the LOB is not initialized.

12.4.4 Select CLOB values

It is possible to view the data from a CLOB column using a SELECT statement, but one cannot
visualize the data of a BLOB or BFILE column in a SELECT order using SQL*Plus. To do this, you need to
use a tool that can display binary information for a BLOB and appropriate software for a BFILE.

Page 99 / 102 Supinfo Laboratory of Oracle Technologies

07/03/2003 [Link]
 Advanced PL/SQL - Version 1.2

Example:

SQL> SELECT emp_id, emp_name, resume -- CLOB

2 FROM employee;
EMP_ID EMPLOYEE NAME
---------- ---------------
RESUME
--------------------------------------------------------
7899 James Dean
8 February 1931

7898 Marilyn Monroe

1 June 1926
Æ This query displays the content of the EMPLOYEE table, particularly the column of type
CLOB.

You can also display only a part of the LOB using the SUBSTR function of the DBMS_LOB package.
use is similar to the SUBSTR function in SQL.
You can also display the position of a character in a LOB using the DBMS_LOB.INSTR function. This
The function is useful for searching for characters in a LOB.

Example:

SQL> SELECT DBMS_LOB.SUBSTR(resume,5,19)

2 DBMS_LOB.INSTR(resume, '=')
3 FROM employee

DBMS_LOB.SUBSTR(RESUME,5,19) DBMS_LOB.INSTR(RESUME,'=')
---------------------------- --------------------------
February 15
June 15
Æ This query keeps only 5 characters from the CLOB and searches for the position in the CLOB of
character =.

These two functions of the DBMS_LOB package work in SQL*Plus when the columns are of type
CLOB. If the LOBs had been of type BLOB or BFILE.

12.4.5 Example of deleting LOBs

A LOB instance can be deleted using appropriate DML commands. The SQL DELETE command deletes a row.
and the value of the associated internal LOB. To prevent the row from being deleted and only the reference to the LOB
to be removed, the line must be updated by replacing the LOB column with a NULL value or a string
see thanks to the EMPTY_B/C/NCLOB() function. Replace the value of a column with a NULL or the
function EMPTY_B/C/NCLOB() is not the same. By using NULL, the value of the column will be NULL
while EMPTY_B/C/NCLOB ensures that there is nothing in the column value.
A LOB is destroyed when the row containing the LOB column is deleted, when the table is dropped or
truncated or implicitly when the LOB data is updated.

Example:

SQL> DELETE FROM employee

2 WHERE emp_name = 'Marilyn Monroe';
SQL> UPDATE employee SET
3 resume = EMPTY_CLOB()
4 WHERE emp_id =7899;
SQL> DELETE FROM lob_table_1 WHERE key = 21;
SQL> DROP TABLE lob_table_1;
SQL> TRUNCATE TABLE lob_table_1;
SQL> UPDATE lob_table_1 SET blob_col = EMPTY_BLOB()
2 WHERE key = 12;
Æ Several examples of LOB deletion.

Supinfo Laboratory of Oracle Technologies Page 100 / 104

[Link] 07/03/2003
 Advanced PL/SQL - Version 1.2

When you want to delete the file associated with a BFILE, you must use the commands provided by
the operating system.
If you want to delete a part of an internal LOB, you can use DBMS_LOB.ERASE.

12.5 Temporary LOBs

Temporary LOBs can be of type BLOB, CLOB, or NCLOB.
These LOBs are not associated with a specific table, the data is stored in the tablespace.
user's temporary. Therefore, they are not stored permanently in the database.
data. All temporary LOBs are deleted at the end of the session in which they were created, but
They can also be deleted at the end of the call by specifying this at creation.
Temporary LOBs are created using DBMS_LOB.CREATETEMPORARY.
Temporary LOBs are useful when one wants to perform transformation operations on a LOB,
Example to change a LOB from one format to another and then reflect these changes in the LOB.
permanent. A temporary LOB will be empty upon its creation and does not support functions
EMPTY_B/C/NCLOB.
To use and manipulate temporary LOBs, the DBMS_LOB package is used.

Page 101 / 102 Supinfo Oracle Technology Laboratory

March 7, 2003 [Link]