Partnering for Success

Oracle
OraclePractice
Practice––PLSQL
PLSQLTraining
Training

11/17/20 1

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Programming in Oracle
with PL/SQL

Procedural Language Extension to SQL

11/17/20 2

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Introduction PL/SQL

• Allows using general programming tools with

SQL, for example: loops, conditions, functions,
etc.
• This allows a lot more freedom than general
SQL, and is lighter-weight than JDBC.
• We write PL/SQL code in a regular file, for
example PL.sql, and load it with @PL in the
sqlplus console.

11/17/20 3

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
PL/SQL Blocks

• PL/SQL code is built of Blocks, with a unique structure.

• There are two types of blocks in PL/SQL:
1. Anonymous Blocks: have no name (like scripts)
• can be written and executed immediately in
SQLPLUS
• can be used in a trigger
2. Named Blocks:
• Procedures
• Functions
11/17/20 4

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Anonymous Block Structure:

DECLARE (optional)
/* Here you declare the variables you will use in this
block */
BEGIN (mandatory)
/* Here you define the executable statements (what the
block DOES!)*/
EXCEPTION (optional)
/* Here you define the actions that take place if an
exception is thrown during the run of this block */
END; (mandatory)
A correct completion of a block
/
will generate the following
Always put a new line with only :message
a / at the end of a block! (This
11/17/20 PL/SQL procedure successfully 5
tells Oracle to run the block)
www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. |
completed
| PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 DECLARE
Syntax
identifier
identifier [CONSTANT]
[CONSTANT] datatype
datatype [NOT
[NOT NULL]
NULL]

[:=
[:= || DEFAULT
DEFAULT expr];
expr];
Notice that PL/SQL
Examples includes all SQL types,
…and more

Declare
Declare
birthday
birthday DATE;
DATE;
age
age NUMBER(2)
NUMBER(2) NOT
NOT NULL
NULL :=
:= 27;
27;
name
name VARCHAR2(13)
VARCHAR2(13) :=:= 'Levi';
'Levi';
magic
magic CONSTANT
CONSTANT NUMBER
NUMBER :=:= 77;
77;
valid
valid BOOLEAN
BOOLEAN NOT
NOT NULL
NULL :=
:= TRUE;
TRUE;
11/17/20 6

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Declaring Variables with the %TYPE Attribute

Examples Accessing column sname

in table Sailors

DECLARE
sname Sailors.sname%TYPE;
fav_boat VARCHAR2(30);
my_fav_boat fav_boat%TYPE := 'Pinta';
...
Accessing
another variable

11/17/20 7

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Declaring Variables with the %ROWTYPE Attribute

Declare a variable with the type of a ROW

Accessing
of a table. table
Reserves

reserves_record Reserves%ROWTYPE;

And how do we access the fields in

reserves_record?

reserves_record.sid:=9;
Reserves_record.bid:=877;

11/17/20 8

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Creating a PL/SQL Record
A record is a type of variable which we can define (like
‘struct’ in C or ‘object’ in Java)
DECLARE
DECLARE
TYPE
TYPE sailor_record_type
sailor_record_type IS IS RECORD
RECORD
(sname
(sname VARCHAR2(10),
VARCHAR2(10),
sid
sid VARCHAR2(9),
VARCHAR2(9),
age
age NUMBER(3),
NUMBER(3),
rating
rating NUMBER(3));
NUMBER(3));
sailor_record
sailor_record
sailor_record_type;
sailor_record_type;
...
...
BEGIN
BEGIN
Sailor
Sailor__record.sname:=‘peter’;
record.sname:=‘peter’;
11/17/20 9
Sailor
Sailor
www.intelligroup.com
__record.age:=45;
record.age:=45;
| Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Creating a Cursor
• We create a Cursor when we want to go over a result of
a query (like ResultSet in JDBC)
• Syntax Example:

DECLARE sailorData is a
variable that
cursor c is select * from sailors; can hold a
sailorData sailors%ROWTYPE; ROW from
the sailors
table
BEGIN
open c; Here the
fetch c into sailorData; first row of
sailors is
inserted into
sailorData
11/17/20 10

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Explicit Cursor Attributes

Obtain status information about a cursor.

Attribute Type Description

%ISOPEN Boolean Evaluates to TRUE if the cursor
is open.
%NOTFOUND Boolean Evaluates to TRUE if the most
recent fetch does not return a row.
%FOUND Boolean Evaluates to TRUE if the most
recent fetch returns a row;
complement of %NOTFOUND
%ROWCOUNT
11/17/20 Number Evaluates to the total number of 11
rows
www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | returned
| PRIVATE AND so far.
CONFIDENTIAL | INTELLIGROUP proprietary.
SELECT Statements
DECLARE
v_sname VARCHAR2(10);
v_rating NUMBER(3);
BEGIN
SELECT sname, rating
INTO v_sname, v_rating
FROM Sailors
WHERE sid = '112';
END;
/
• INTO clause is required.
• Query must return exactly one row.
• Otherwise, a NO_DATA_FOUND or
TOO_MANY_ROWS exception is thrown
11/17/20 12

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Conditional logic
:Condition :Nested conditions
If <cond> If <cond>
then <command> then
elsif <cond2> if <cond2>
then <command2> then
else <command1>
<command3> end if;
end if; else <command2>
11/17/20
end if; 13

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 IF-THEN-ELSIF Statements

. . .
IF rating > 7 THEN
v_message := 'You are great';
ELSIF rating >= 5 THEN
v_message := 'Not bad';
ELSE
v_message := 'Pretty bad';
END IF;
. . .

11/17/20 14

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Suppose we have the following table:

create table mylog( mylog

who varchar2(30),
logon_num number who logon_num

);
Peter 3

• Want to keep track of how many John 4

times someone logged on to the
DB Moshe 2
• When running, if user is already
in table, increment logon_num.
Otherwise, insert user into table
11/17/20 15

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Solution
DECLARE
cnt NUMBER;
BEGIN
select count(*)
into cnt
from mylog
where who = user;

if cnt > 0 then

update mylog
set logon_num = logon_num + 1
where who = user;
else
insert into mylog values(user, 1);
end if;
commit;
end;
/
11/17/20 16

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
SQL Cursor
SQL cursor is automatically created after each SQL query.
It has 4 useful attributes:
SQL%ROWCOUNT Number of rows affected by the
most recent SQL statement (an
integer value).
SQL%FOUND Boolean attribute that evaluates to
TRUE if the most recent SQL
statement affects one or more rows.
SQL%NOTFOUND Boolean attribute that evaluates to
TRUE if the most recent SQL
statement does not affect any rows.
SQL%ISOPEN Always evaluates to FALSE because
PL/SQL closes implicit cursors
immediately after they are executed.
11/17/20 17

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Solution (2)

BEGIN
update mylog
set logon_num = logon_num + 1
where who = user;

if SQL%ROWCOUNT = 0 then
insert into mylog values(user, 1);
end if;
commit;
END;
/

11/17/20 18

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Loops: Simple Loop
create table number_table(
num NUMBER(10)
);

DECLARE
i number_table.num%TYPE := 1;
BEGIN
LOOP
INSERT INTO number_table
VALUES(i);
i := i + 1;
EXIT WHEN i > 10;
END LOOP;
END;
11/17/20 19

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Loops: Simple Cursor Loop

create table number_table(

num NUMBER(10)
);

DECLARE
cursor c is select * from number_table;
cVal c%ROWTYPE;
BEGIN
open c;
LOOP
fetch c into cVal;
EXIT WHEN c%NOTFOUND;
insert into doubles values(cVal.num*2);
END LOOP;
11/17/20END; 20

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Loops: FOR Loop

DECLARE
i number_table.num%TYPE;
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO number_table VALUES(i);
END LOOP;
END;

Notice that i is incremented

11/17/20
automatically 21

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Loops: For Cursor Loops
DECLARE
cursor c is select * from number_table;

BEGIN
for num_row in c loop
insert into doubles_table
values(num_row.num*2);
end loop;
END;
/

Notice that a lot is being done implicitly:

declaration of num_row, open cursor,
fetch cursor, the exit condition
11/17/20 22

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Loops: WHILE Loop

DECLARE
TEN number:=10;
i number_table.num%TYPE:=1;
BEGIN
WHILE i <= TEN LOOP
INSERT INTO number_table
VALUES(i);
i := i + 1;
END LOOP;
END;

11/17/20 23

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Printing Output

• You need to use a function in the DBMS_OUTPUT

package in order to print to the output
• If you want to see the output on the screen, you must
type the following (before starting):
set serveroutput on format wrapped size 1000000
• Then print using
– dbms_output. put_line(your_string);
– dbms_output.put(your_string);

11/17/20 24

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Input and output example
set serveroutput on format wrap size 1000000
ACCEPT high PROMPT 'Enter a number: '

DECLARE
i number_table.num%TYPE:=1;
BEGIN
dbms_output.put_line(‘Hey, I can print from PL/SQL!!!');
WHILE i <= &high LOOP
INSERT INTO number_table
VALUES(i);
i := i + 1;
END LOOP;
END;

11/17/20 25

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Reminder- structure of a block

DECLARE (optional)
/* Here you declare the variables you will use in this
block */
BEGIN (mandatory)
/* Here you define the executable statements (what the
block DOES!)*/
EXCEPTION (optional)
/* Here you define the actions that take place if an
exception is thrown during the run of this block */
END; (mandatory)
/

11/17/20 26

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Trapping Exceptions

• Here we define the actions that should happen when

an exception is thrown.
• Example Exceptions:
– NO_DATA_FOUND
– TOO_MANY_ROWS
– ZERO_DIVIDE
• When handling an exception, consider performing a
rollback

11/17/20 27

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trapping Exceptions (contd.)

DECLARE
num_row number_table%ROWTYPE;
BEGIN
select *
into num_row
from number_table;
dbms_output.put_line(1/num_row.num);

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No data!');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('Too many!');
WHEN OTHERS THEN
11/17/20
dbms_output.put_line(‘Error’); 28
end;
www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
User-Defined Exception
DECLARE
e_number1 EXCEPTION;
cnt NUMBER;
BEGIN
select count(*)
into cnt
from number_table;

IF cnt = 1 THEN RAISE e_number1;

ELSE dbms_output.put_line(cnt);
END IF;

EXCEPTION
WHEN e_number1 THEN
dbms_output.put_line('Count = 1');
end;
11/17/20 29

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Functions and Procedures

• Up until now, our code was in an anonymous block

• It was run immediately

• It is useful to put code in a function or procedure so it can be called

several times

• Once we create a procedure or function in a Database, it will remain

until deleted (like a table).

11/17/20 30

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Creating Procedures
CREATE
CREATE [OR
[OR REPLACE]
REPLACE] PROCEDURE
PROCEDURE procedure_name
procedure_name
[(parameter1
[(parameter1 [mode1]
[mode1] datatype1,
datatype1,
parameter2
parameter2 [mode2]
[mode2] datatype2,
datatype2,
.. .. .)]
.)]
IS|AS
IS|AS
PL/SQL
PL/SQL Block;
Block;

• Modes:
– IN: procedure must be called with a value for the parameter.
Value cannot be changed
– OUT: procedure must be called with a variable for the
parameter. Changes to the parameter are seen by the user (i.e.,
call by reference)
– IN OUT: value can be sent, and changes to the parameter are
seen by the user
•11/17/20
Default Mode is: IN 31

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Calling the Procedure

declare
declare
howmany
howmany mylog.logon_num%TYPE;
mylog.logon_num%TYPE;
begin
begin
num_logged(‘John',howmany);
num_logged(‘John',howmany);
dbms_output.put_line(howmany);
dbms_output.put_line(howmany);
end;
end;
//

11/17/20 32

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Errors in a Procedure

• When creating the procedure, if there are errors in

its definition, they will not be shown
• To see the errors of a procedure called
myProcedure, type
• SHOW ERRORS PROCEDURE myProcedure
in the SQLPLUS prompt
• For functions, type
• SHOW ERRORS FUNCTION myFunction

11/17/20 33

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Creating a Function

• Almost exactly like creating a procedure, but you

supply a return type

CREATE
CREATE [OR
[OR REPLACE]
REPLACE] FUNCTION
FUNCTION
function_name
function_name
[(parameter1
[(parameter1 [mode1]
[mode1] datatype1,
datatype1,
parameter2
parameter2 [mode2]
[mode2] datatype2,
datatype2,
.. .. .)]
.)]
RETURN
RETURN datatype
datatype
IS|AS
IS|AS
PL/SQL
PL/SQL Block;
Block;

11/17/20 34

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 A Function
create
create or or replace
replace function
function
rating_message(rating
rating_message(rating IN IN NUMBER)
NUMBER)
return
return VARCHAR2
VARCHAR2 NOTE THAT YOU
AS
AS DON'T SPECIFY THE
BEGIN
BEGIN SIZE
IF
IF rating
rating >> 77 THEN
THEN
return
return 'You
'You are
are great';
great';
ELSIF
ELSIF rating
rating >=>= 55 THEN
THEN
return
return 'Not
'Not bad';
bad';
ELSE
ELSE
return
return 'Pretty
'Pretty bad';
bad';
END
END IF;
IF;
END;
END;
//
11/17/20 35

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Calling the function

declare
declare
paulRate:=9;
paulRate:=9;
Begin
Begin
dbms_output.put_line(ratingMessage(paulRate
dbms_output.put_line(ratingMessage(paulRate
));
));
end;
end;
//

11/17/20 36

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 :Creating a function

create or replace function squareFunc(num in number)

return number
is
BEGIN
return num*num;
End;
/

:Using the function

BEGIN
dbms_output.put_line(squareFunc(3.5));
END;
/
11/17/20 37

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Packages

11/17/20 38

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 :Objectives

Describe packages and list their possible components.

Create a package to group together related variables,

cursors, constants, exceptions, procedures and functions

Designate a package construct as either public or private

Invoke a package construct

Describe a use for a bodiless package.

11/17/20 39

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Overview of Packages

Packages:

• Group logically related PL/SQL types, items, and

subprograms

•Consist of two parts:

- Specification
- Body

•Cannot be invoked, parameterized or nested

•Allow the oracle server to read multiple objects

11/17/20into memory at once. 40

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 .Packages contd

• A package usually has a specification and a body,

stored separately in the database.
• Functions, Procedures, Variables can be put together
in a package
• In a package, you can allow some of the members
to be "public" and some to be "private"
• There are also many predefined Oracle packages

11/17/20 41

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 .Packages contd

When you call a packaged PL/SQL, construct for the

first time , the whole package is loaded into
.memory

Thus, later calls to constructs in the same package

.require no disk input/output(I/O)

11/17/20 42

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Package development

:You create a package in two parts

Package specification *
package body *

A package specification can exist without a package

body, but a package body cannot exist without a
.package specification

11/17/20 43

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
How to develop a package

:Basic three steps

1.write the text of the CREATE PACKAGE statement within a SQL script
file to create the package specification and run the script file. The source
code is compiled into p code and is stored with the data dictionary.

2.write the text of the CREATE PACKAGE BODY statement within a SQL
script file to create the package body and run the script file.

The source code is compiled into P code and is also stored within the
data dictionary

3. Invoke any public construct within the package from an Oracle server
environment.

11/17/20 44

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Creating the Package Specification

:Syntax
Create [or replace] package package_name
IS/AS
Public type and item declarations
Subprogram specifications
End package_name ;

The replace option drops and recreates the package

specification.

Variable declared in the package specification are initialized to

null by default

All the constructs declared in a package specification are visible

.to only those users who are granted privileges on the package
11/17/20 45

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Creating the Package Specification

:Creating a package Specification

Example:
CREATE OR REPLACE PACKAGE comm._package IS
G_comm NUMBER := 0.10; --initialized to 0.10
PROCEDURE reset_comm
(p_comm IN NUMBER);
END comm._package;
/

G_COMM is a global variable and is initialized to 0.10

RESET_COMM is a public procedure that is
implemented in the package body
In the above example, the variable G_COMM and the
procedure RESET_COMM are public constructs.
11/17/20 46

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
.Creating a package body contd

Define a function to validate the commission. The commission may not be

.greater than the highest commission among all existing employees
Define a procedure that enables you to reset and validate the prevailing
.commission

Comm._pack.sql
Procedure reset_comm(p_comm IN NUMBER)
IS
BEGIN
IF validate_comm(p_comm)
Then g_comm := p_comm; -- reset global variable
ELSE
;RAISE_ APPLICATION_ ERROR (-20210, ‘Invalid commission’)
;END IF
;END reset_comm
;End comm._package
/
11/17/20 47

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Invoking package constructs

Example 1: Invoke a function from a procedure within

the same package.

CREATE OR REPLACE PACKAGE BODY comm._package IS

.………
PROCEDURE reset_com
)p_com IN NUMBER(
IS
BEGIN
IF validate_comm(p_comm)
;THEN g_comm := p_comm
ELSE
RAISE_APPLICATION_ERROR
;) ’Invalid commission‘ ,20210-(
;END IF
;END reset_comm
;END comm._package
11/17/20 48

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Invoking package constructs

When u invoke a package procedure or function from outside the

.package, u must qualify its name with the name of the package

.Ex: 2 Invoke a package procedure from iSQL*Plus

EXECUTE comm_package.reset_comm(0.15)

Call the RESET_COMM procedure from isql*plus , making the

prevailing commission 0.15 for the user session

11/17/20 49

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Invoking package constructs

.Ex : 3 Invoke a package procedure in a different schema

EXECUTE scott.comm _package.reset_comm(0.15)

Call the RESET_COMM procedure that is located in the SCOTT

schema from isql*plus, making the prevailing commission 0.15
.for the user session

Ex: 4 Invoke a package procedure in a remote database

EXECUTE comm._package.reset_comm@ny(0.15)

Call the RESET_COMM procedure that is located in a remote

database that is determined by the database link named NY
from iSQL*PLUS, making the prevailing commission 0.15 for the
.user session

11/17/20 50

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Declaring a Bodiless package

CREATE OR REPLACE PACKAGE global_consts IS

Mile_2_kilo constant NUMBER := 1.6093;
Kilo_2_mile constant NUMBER := 0.6214;
Yard_2_meter constant NUMBER := 0.9144;
Meter-2_yard constant NUMBER := 1.0936;
END global_consts:
/
EXECUTE DBMS_OUTPUT.PUT_LINE (’20 miles = ‘||
20*global_consts.mile_2_kilo ||’ km’)

11/17/20 51

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Reference a public variable from a Stand-Alone Procedures

CREATE OR REPLACE PROCEDURE meter_to_yard

)p_meter IN NUMBER, p_yard OUT NUMBER(

IS
BEGIN
;P_yard := p_meter *global_consts.meter_2_yard
;END meter_to_yard
/
VARIABLE yard NUMBER
Execute meter_to_yard(1, :yard)

PRINT yard

11/17/20 52

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Removing Packages

To remove the package specification and the body, use

the following syntax:

DROP PACKAGE package_name;

To remove the Package body, use the following syntax:

DROP PACKAGE BODY package_name;

A package has two parts, so you can drop the whole

package or just the package body and retain the
package specification.
11/17/20 53

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Guidelines for developing packages

Construct packages for general use.

Define the package specification before the body

The package specification should contain only those constructs that

you want to be public.

Place items in the declaration part of the package body when you
must maintain them throughout a session or across transactions

Changes to the package specification require recompilation of each

referencing subprogram.

The package specification should contain as few constructs as

possible.

11/17/20 54

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 TRIGGERS

11/17/20 55

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Objective

• Create triggers
• Types of triggers
• Security issues
• Disable and enable triggers

11/17/20 56

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Triggers

• Triggers are special procedures which we want

activated when someone has performed some
action on the DB.

• For example, we might define a trigger that is

executed when someone attempts to insert a row
into a table, and the trigger checks that the
inserted data is valid.

11/17/20 57

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trigger Concepts

• Triggers are PL/SQL programs.

• Triggers execute automatically upon a triggering
statement or event
• Triggers can roll back triggering statement
• Triggers cannot be disabled without privilege
• Common used for auditing and business rule
enforcement among others.

11/17/20 58

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Trigger (contd.)

• Triggers are PL/SQL programs that automatically

fire(ie..execute) when
- A DML statement such as an update, Insert or
Delete statement occurs on a table
- A system event such as shutdown, startup or
servererror occurs
- A user event such as logon or logof occurs

11/17/20 59

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trigger Executions

• Triggers can occur Before, After or Instead of the

triggering event.

11/17/20 60

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trigger Syntax

CREATE [ or REPLACE ] Trigger [schema.] trigger

{ BEFORE/AFTER/INSTEAD OF }
{ DELETE or INSERT or UPDATE [of column [, column]…] }
ON [ Schema.] table | database | Schema [For each row |
statement } ]
[ WHEN (condition) ] ]
[DECLARE]
< local variable declarations>
begin <code>
[exception]
<exception handlers>
end;
11/17/20 61

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Row Trigger example(old)

CREATE [ or REPLACE ] Trigger after_customer

After delete on customer
for each row
begin
insert into cust_history
(cost_no, lastname, firstname)
values(:old.cust_no, :old.lastname, :old.firstname);
end;

11/17/20 62

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
: old Reference Variables

• : old column_name
- contains value before update
- null for insert trigger
- only valid in row trigger
• Cannot be modified
- use :new to change column values
• Commonly used in update or delete triggers
- e.g maintain audit trail of old values.

11/17/20 63

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
: New Reference Variables

• :NEW.column_name
- contains the value to be inserted or updated
• :NEW can be modified in a before triggers
• :NEW can be referenced in after trigger
• Commonly used in Insert and update
triggers
• :NEW is null in delete triggers.
• Only valid in row triggers.

11/17/20 64

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
: Row trigger example using :New

CREATE or REPLACE TRIGGER before_customer

before update or insert on costomer for each row

begin
/* convert character values to upper case */
:new.lastname := upper(:new.lastname);
:new.firstname := upper(:new.firstname);
end;

11/17/20 65

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trigger attributes

CREATE or REPLACE trigger before_customer

before update or insert or delete on customer for each row
begin
if inserting then
insert into cust_history
(cust_no, lastname, firstname)
values
(:new.cust_no, :new.lastname, new.firstname)
elseif updating then
update cust_history
set lastname = :new.lastname
firstname = :new.firstname
where cust_no = :new.cust_no
11/17/20 66

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trigger attributes (contd.)

elsif deleting then

begin
/* insure history is there…. */
update cust_history
set lastname = :old.lastname,
firstname = old.firstname
where cust_no = :old.cust_no
end;
end if;
end;

11/17/20 67

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Audit trigger

CREATE or REPLACE trigger audit_customer

after UPDATE or INSERT on customer for each row
begin
if inserting then
insert into class_audit
(c_user,c_date, operations, new_data)
values
(user, sysdate, ‘insert’, to_char(:new.cust_no || :new.lastname)
endif;
end;

11/17/20 68

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Statement trigger

CREATE or REPLACE trigger before_dept

before insert or update or delete on dept
begin
if to_char(sysdate, ‘day’) = ‘Sunday’
then raise_application_error(-20000, ‘no DML allowed on
Sunday’);
end if;
end;

11/17/20 69

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Instead of triggers

• Facilitate update to complex views

- so called because it executes “instead of the DML statement
Assume this view

create view manager as

select e.empno, d.deptno, d.dname, e.ename from emp e
and dept d
where d.mgrno = e.empno

11/17/20 70

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Instead of triggers

Support this update:

Update manager
set empno =2 where deptno =1;

With this trigger –

create or replace trigger manager_update
instead of update on manager for each row
begin
update department
set mgrno = :new.empno
where deptno = :new.deptno;
end;
11/17/20 71

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Autonomous Trigger

• Use PRAGMA to define routine as autonomous

CREATE or REPLACE Trigger logit

Before insert on dept
declare
PRAGMA AUTONOMOUS_TRANSACTIONS;
Begin
Insert into logit values(‘Insert into dept by ‘User’);
end;

 Trigger logs event even if triggering statement is rolled back.

 Autonomous trigger can contain commit/roll back

11/17/20 72

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Database Trigger Execution
System Events Oracle Server

Shut down Before only

Startup
After only
Servererror

User Events

Log on After only

Log off Before only

DDL
Before
Create
or
Drop
After
11/17/20
Alter 73

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Rules to Remember

• Whenever a database-level event trigger fires. Oracle opens an

autonomous transaction, fires the trigger and commits any DML in the
trigger logic independently of the existing user tranction.

• When defining logon, startup and servererror triggers, you can specify the
After context.

• When defining logoff, shutdown triggers, you can specify the Before
context.

• You cannot define After startup and before shutdown for a schema. These
apply only to database.

11/17/20 74

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
DDL trigger

•Maintain a log of all drops in Scott’ schema

CREATE or REPLACE trigger log_drop_in_scott

before drop on scott.schema
begin
Insert into drop_log
values(‘object name: || ora_dict_obj_name || ‘object type : ‘||
ora_dict_obj_type || ‘ ‘ || sysdate);
end;

11/17/20 75

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
DDL trigger

Frequently used event attributes –

• ora_dict_obj_name
• ora_dict_owner
• ora_dict_type
• ora_login_user
• ora_server_error(n)
• ora_sysevent

11/17/20 76

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Enable/Disable triggers

• Disable triggers
- Trigger still exists but is dormant.
- sql*loader and import will run faster

• Enabled triggers do not execute for inserts/updates

that may have occurred while disabled.

Alter trigger bir_customer disable;

Alter trigger bir_customer enable;
Alter table customer disable all triggers;
Alter table customer enaable all triggers;
11/17/20 77

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trigger and the Data Dictionary

• Triggers are recorded in:

- X_objects

Source code can be found in :

- X_source

Example :
Select object_name from user_objects where
object_type = ‘trigger’;

11/17/20 78

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Trigger Privileges

• You must have the appropriate privileges granted

to userid in order to create, drop, enable or disable
a trigger

• Triggers can be enabled or disabled, but not

without, ALTER ANY TRIGGER privilege

11/17/20 79

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Trigger Restrictions

• Triggers cannot be fired for a select statement

• Triggers can never be executed explicitly like

procedure or functions

• Triggers can call database procedures, functions

and packages.

11/17/20 80

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 SQL*Loader

11/17/20 81

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
SQL*Loader Features
•Load data from multiple datafiles during the same load
session.

•Load data into multiple tables during the same load

session.

•Selectively load data (you can load records based on the

records’ values).

•Manipulate the data before loading it, using SQL

functions.

•Generate unique sequential key values in specified

columns.
11/17/20 82

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
SQL*Loader Multiple Data Files
• load data
• INFILE dept.dat
• INFILE newdept.dat
• append INTO TABLE TEST_DEPT
• (dept POSITION(1:2) integer external,
• deptname POSITION(4:20) CHAR)

11/17/20 83

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
SQL*Loader Multiple Tables
• INFILE employ_dept.dat
• APPEND INTO TABLE TEST_EMP
• WHEN recid = "1"
• (recid POSITION(1:1) integer external,
• empid POSITION(2:4) integer external,
• empname POSITION(5:12) CHAR,
• sal POSITION(13:16) integer external,
• comn POSITION(17:18) integer external,
• dept POSITION(19:20) integer external)
• INTO TABLE TEST_DEPT
• WHEN recid = "2"
• (recid POSITION(1:1) integer external,
• dept POSITION(2:3) integer external,
• deptname POSITION(4:14) CHAR)

11/17/20 84

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
SQL*Loader Control File
• OPTIONS (SKIP=1)
• CHARACTERSET UTF16
• Load data
• INFILE *
• APPEND into table XXBEL.XXMTL_ITEM_SUBINV_INTERFACE
• FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '*' TRAILING NULLCOLS
• ( INVENTORY_ITEM_NAME CHAR "TRIM (:INVENTORY_ITEM_NAME)",
• ORGANIZATION_CODE CHAR "TRIM (:ORGANIZATION_CODE)",
• SECONDARY_INVENTORY CHAR "TRIM (:SECONDARY_INVENTORY)",
• INVENTORY_PLANNING_CODE INTEGER EXTERNAL "2",
• PREPROCESSING_LEAD_TIME INTEGER EXTERNAL "TRIM (:PREPROCESSING_LEAD_TIME)",
• PROCESSING_LEAD_TIME INTEGER EXTERNAL "TRIM (:PROCESSING_LEAD_TIME)",
• POSTPROCESSING_LEAD_TIME INTEGER EXTERNAL "TRIM (:POSTPROCESSING_LEAD_TIME)",
• ATTRIBUTE1 CHAR "TRIM (:ATTRIBUTE1)",
• ATTRIBUTE2 CHAR "TRIM (:ATTRIBUTE2)",
• ATTRIBUTE3 CHAR "TRIM (:ATTRIBUTE3)",
• ATTRIBUTE4 CHAR "TRIM (:ATTRIBUTE4)",
• ATTRIBUTE5 CHAR "TRIM (:ATTRIBUTE5)",
• ATTRIBUTE6 CHAR "TRIM (:ATTRIBUTE6)",
• DELETE_FLAG CHAR "TRIM (:DELETE_FLAG)",
• PROCESS_FLAG INTEGER EXTERNAL "20",
• UPLOAD_RECORD_ID sequence(max,1) )

11/17/20 85

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Invoking SQL*Loader
• SQLLDR CONTROL=foo.ctl, LOG=bar.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dis,
DISCARDMAX=5

11/17/20 86

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Invoking SQL*Loader
• SQLLDR CONTROL=foo.ctl, LOG=bar.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dis,
DISCARDMAX=5

11/17/20 87

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Call Java Stored
Procedure from
PLSQL Procedure

11/17/20 88

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Call a Java SP from PL/SQL Procedure
1. Define a Java Stored Stored procedure as below in the database.
DROP JAVA SOURCE APPS."DirList";

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED apps."DirList" AS import java.io.*;
import java.sql.*;
public class dirlist
{
public static void getlist(string directory)
throws sqlexception
{
try
{
file path = new file( directory );
string[] list = path.list();
string element;
for(int i = 0; i &lt; list.length; i++)
{
element = list[i];
#sql { insert into xx_dir_list (filename)
values (:element) };
} catch (exception e);
{
system.out.ptintln(e.getmeaasage);
}
}
} 11/17/20 89
}
www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Call a Java SP from PL/SQL Procedure
2. Call the above Java Stored Stored procedure "DirList" from the PL/SQL
Procedure as follows.

CREATE OR REPLACE PROCEDURE APPS.get_dir_list (p_directory IN

VARCHAR2)
AS
LANGUAGE JAVA
NAME 'DirList.getList( java.lang.String )';
/

11/17/20 90

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 Dynamic SQL in
PL/SQL

11/17/20 91

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Dynamic SQL

What Is Dynamic SQL?

Why Use Dynamic SQL?

11/17/20 92

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Methods for using Dynamic SQL within PL/SQL
Oracle provides two methods for using dynamic SQL within PL/SQL

Native dynamic SQL

 EXECUTE IMMEDIATE statement :
 OPEN-FOR, FETCH, and CLOSE statements : To process a multi-
row query

DBMS_SQL package

11/17/20 93

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Referencing Database Objects that Do Not Exist at Compilation

CREATE OR REPLACE PROCEDURE query_invoice(

month VARCHAR2,
year VARCHAR2) IS
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(200);
inv_num NUMBER;
inv_cust VARCHAR2(20);
inv_amt NUMBER;
BEGIN
query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year
|| ' WHERE invnum = :id';
OPEN c FOR query_str USING inv_num;
LOOP
FETCH c INTO inv_num, inv_cust, inv_amt;
EXIT WHEN c%NOTFOUND;
-- process row here
END LOOP;
CLOSE c;
END;
/
11/17/20 94

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Executing Dynamic PL/SQL Blocks

•You can use the EXECUTE IMMEDIATE statement to execute run time procedure names.

CREATE OR REPLACE PROCEDURE event_dispatcher

(event NUMBER, param NUMBER) IS
BEGIN
EXECUTE IMMEDIATE
'BEGIN
EVENT_HANDLER_' || to_char(event) || '(:1);
END;'
USING param;
END;
/

•You can use the EXECUTE IMMEDIATE statement to execute DDL Statements.

CREATE OR REPLACE PROCEDURE drop_location (loc VARCHAR2) IS

BEGIN
-- delete the employee table for location 'loc'
EXECUTE IMMEDIATE 'DROP TABLE ' || 'emp_' || loc;
END;
/
11/17/20 95

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Dynamic SQL using DBMS_SQL Package

CREATE OR REPLACE PROCEDURE create_index

(index_in IN VARCHAR2, table_in IN VARCHAR2, column_in in VARCHAR2)
IS
cursor_handle INTEGER;
feedback INTEGER;
BEGIN
/* Create a cursor to use for the dynamic SQL */
cursor_handle := DBMS_SQL.OPEN_CURSOR;

/* Construct the SQL statement and parse it in native mode. */

DBMS_SQL.PARSE
(cursor_handle,
'CREATE INDEX ' || index_in || ' ON ' || table_in ||
'( ' || column_in || ')',
DBMS_SQL.NATIVE);

/* You should always execute your DDL! */

feedback := DBMS_SQL.EXECUTE (cursor_handle);

DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END create_index;
/ 11/17/20 96

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 PL/SQL
Performance
Tuning

11/17/20 97

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Make SQL Statements as Efficient as Possible

• Make sure you have appropriate indexes

• Analyze the execution plans and performance of the SQL statements

SET AUTOTRACE ON EXPLAIN STAT

• Rewrite the SQL with query hints to avoid full table scan

• Use FORALL statement as a way to replace loops of INSERT, UPDATE,

and DELETE statements

• Use BULK COLLECT clause of the SELECT INTO statement as a

way to bring the entire result set into memory in a single operation

11/17/20 98

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Make Function Calls as Efficient as Possible

BEGIN
-- Inefficient, calls my_function for every row.
FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM
employees)
LOOP
dbms_output.put_line(item.col_alias);
END LOOP;
-- Efficient, only calls function once for each distinct value.
FOR item IN ( SELECT SQRT(department_id) col_alias FROM ( SELECT
DISTINCT department_id FROM employees) )
LOOP
dbms_output.put_line(item.col_alias);
END LOOP;
END;
/ 11/17/20 99

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Minimize Datatype Conversions

DECLARE
n NUMBER;
c CHAR(5);
BEGIN
n := n + 15; -- converted implicitly; slow
n := n + 15.0; -- not converted; fast
c := 25; -- converted implicitly; slow
c := TO_CHAR(25); -- converted explicitly; still slow
c := '25'; -- not converted; fast
END;
/

11/17/20 100

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Improving performance by using better Data types

• Use PLS_INTEGER or BINARY_INTEGER for Integer Arithmetic

• Use BINARY_FLOAT and BINARY_DOUBLE for Floating-Point

Arithmetic

11/17/20 101

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Using the FORALL Statement

To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL

statement within a PL/SQL FORALL statement instead of a loop construct.

This FORALL statement sends all three DELETE statements to the SQL
engine at once:

CREATE TABLE employees2 AS SELECT * FROM employees;

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM employees2 WHERE department_id = depts(i);
COMMIT;
END;
/
11/17/20 102

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Compare performance of FORALL Statement

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));

CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
DECLARE
TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;
iterations CONSTANT PLS_INTEGER := 500;
t1 INTEGER; t2 INTEGER; t3 INTEGER;
BEGIN
FOR j IN 1..iterations LOOP -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;
t1 := dbms_utility.get_time;
FOR i IN 1..iterations LOOP -- use FOR loop
INSERT INTO parts1 VALUES (pnums(i), pnames(i));
END LOOP;
t2 := dbms_utility.get_time;
FORALL i IN 1..iterations -- use FORALL statement
INSERT INTO parts2 VALUES (pnums(i), pnames(i));
t3 := dbms_utility.get_time;
dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('---------------------');
dbms_output.put_line('FOR loop: ' || TO_CHAR((t2 - t1)/100));
dbms_output.put_line('FORALL: ' || TO_CHAR((t3 - t2)/100));
COMMIT;
END;
/
DROP TABLE parts1;
DROP TABLE parts2;

11/17/20 103

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Tuning PL/SQL Performance with the RETURNING Clause

• Eliminates the need to SELECT the row after an insert or update, or before a
delete. As a result, fewer network round trips, less server CPU time, fewer
cursors, and less server memory are required.

• Example, you update the salary of an employee and at the same time retrieve the
employee's name and new salary into PL/SQL variables.

PROCEDURE update_salary (emp_id NUMBER) IS

name VARCHAR2(15);
new_sal NUMBER;
BEGIN
UPDATE emp SET sal = sal * 1.1
WHERE empno = emp_id
RETURNING ename, sal INTO name, new_sal;
-- Now do computations involving name and new_sal
END;
11/17/20 104

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
 PL/SQL
Wrapping

11/17/20 105

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.
Wrapping PL/SQL Source Code

Wrapping is the process of hiding PL/SQL source code. Wrapping helps to

protect your source code from business competitors and others who might
misuse it.

Guidelines for Wrapping

oWrap only the body of a package or object type, not the specification
oWrap code only after you have finished editing it.
oWrapped code cannot be unwrapped.

You can wrap PL/SQL source code with either the wrap utility or DBMS_DDL
subprograms.

Wrap utility
wrap iname=input_file [ oname=output_file ]

DBMS_DDL.WRAP
11/17/20 106

www.intelligroup.com | Copyright © 2010 Intelligroup, Inc. | | PRIVATE AND CONFIDENTIAL | INTELLIGROUP proprietary.