Copyright 2000-2006 Steven Feuerstein - Page 1

OPP 2007
February 28 March 1, 2007
San Mateo Marriott
San Mateo, California
An ODTUG SP* Oracle PL/SQL
Programming Conference
*SP Seriously Practical Conference
For more information isit !!!"o#tu$"com or call %10&'(2&7'''
O)*+, -alei#osco.e
/une 18 21, 2007
Pre&conference 0an#s&on *rainin$ & /une 11 17
0ilton )aytona 2each Oceanfront 3esort
)aytona, Flori#a
WOW-Wide Open World, Wide Open Web
Copyright 2000-2006 Steven Feuerstein - Page 2
Ma4in$ the Most of
Oracle P56S75
8rror Mana$ement Features
Steven Feuerstein
PL/SQL Evangelist
Quest Software
steven.feuerstein@quest.co
m
Copyright 2000-2006 Steven Feuerstein - Page 3
Ten Years Writing Ten Books
on the Oracle PL/SQL Language
Copyright 2000-2006 Steven Feuerstein - Page 4
How to benefit most from this class

Watch, listen, ask questions.

Download the training materials and supporting scripts:

http://oracleplsqlprogramming.com/resour
ces.html

"Demo zip": all the scripts I run in my class

available at
http://oracleplsqlprogramming.com/downlo
ads/demo.zip

Use these materials as an accelerator as you venture into

new territory and need to apply new techniques.

Play games! Keep your brain resh and active by mi!ing

hard wor" with challenging games

asterind and Set !www.setgame.com"

filename9from9#emo9:i."s;l
Copyright 2000-2006 Steven Feuerstein - Page 5
Manage errors effectiel! an" consistentl!

# signiicant challenge in any programming

environment.

Ideally# errors are raised# handled#

logged and communicated in a
consistent# robust manner

$ome special issues or P%&$'% developers

$he %&'%($I)* datatype

+ow to ,ind the line on which the

error is raised-

'ommunication with non.(//S0/ host

environments
Copyright 2000-2006 Steven Feuerstein - Page 6
#chieing i"eal error management

Deine your requirements clearly

Understand P%&$'% error management

eatures and ma"e ull use o what P%&$'%
has to oer

#pply best practices.

'ompensate ,or (//S0/ wea1nesses

Single point o, de,inition: use

reusable components to ensure
consistent# robust error management
Copyright 2000-2006 Steven Feuerstein - Page 7
$efine !our re%uirements clearl!

When will errors be raised, when handled(

Do you let errors go unhandled to the

host# trap locally# or trap at the top.
most level-

)ow should errors be raised and handled(

2ill users do whatever they want or will

there be standard approaches that
everyone will ,ollow-

Useul to conceptuali*e errors into three

categories:

Deliberate# un,ortunate# une3pected

Copyright 2000-2006 Steven Feuerstein - Page 8
$ifferent t!&es of e'ce&tions

Deliberate

$he code architecture itsel, deliberately

relies on an e3ception. %3ample:
4$/5FI/%.6%$5/I*%

Unortunate

It is an error# but one that is to be

e3pected and may not even indicate a
problem. %3ample: S%/%'$ I*$) .7
*)5D8$85F)4*D

Une!pected

8 "hard" error that indicates a problem

within the application. %3ample:
(rimary 1ey loo1up raises $))58*9
:)2S
e<ec9##l9from9file"s;l
$et9ne<tline"sf
fullname".4b
fullname".4b
Copyright 2000-2006 Steven Feuerstein - Page 9
PL/SQL error management features

Deining e!ceptions

+aising e!ceptions

)anding e!ceptions

,!ceptions and D-%

Copyright 2000-2006 Steven Feuerstein - Page 10
Qui() Test !our e'ce&tion han"ling know*how
DECLARE
aname VARCHAR2(5);
BEGIN
BEGIN
aname := 'Justice';
DB!"#$%&$%'&$%"LINE (aname);
E(CE&%I#N
)HEN VAL$E"ERR#R
%HEN
DB!"#$%&$%'&$%"LINE ('Inne* +,-c.');
END;
DB!"#$%&$%'&$%"LINE (')/at e**-*0');
E(CE&%I#N
)HEN VAL$E"ERR#R
%HEN
DB!"#$%&$%'&$%"LINE ('#ute* +,-c.');
END;
e<c;ui:1"s;l

What do you see ater running this bloc"(

Copyright 2000-2006 Steven Feuerstein - Page 11
$efining +'ce&tions

.he ,/0,P.123 is a limited type o data.

+as ;ust two attributes: code and

message.

9ou can :8IS% and handle an

e3ception# but it cannot be passed as
an argument in a program.

4ive names to error numbers with the

,/0,P.1235131. P+#4-#.
CREA%E #R RE&LACE &R#CED$RE u12"3-*"2e1t (
2e1t"in IN em1,-4ee'2e1a*tment"i25%6&E
7 ne8sa,"in IN em1,-4ee'sa,a*45%6&E
)
I!
+u,."e**-*s E(CE&%I#N;
&RAGA E(CE&%I#N"INI% (+u,."e**-*s7 92:;<=);
Copyright 2000-2006 Steven Feuerstein - Page 12
,aising +'ce&tions

+#1$, raises the speciied e!ception by

name.

:8IS%< re.raises current e3ception.

'allable only within the e3ception
section.

+#1$,5#PP%10#.1235,++2+

'ommunicates an application speci,ic

error bac1 to a non.(//S0/ host
environment.

%rror numbers restricted to the

.=>#??? . .=>#>>> range.
Copyright 2000-2006 Steven Feuerstein - Page 13
-sing ,#.S+/#PPL.0#T.O1/+,,O,
I> :NE)'+i*t/2ate ? ADD"#N%H! (!6!DA%E7 9= @ =< @ =2)
%HEN
RAI!E"A&&LICA%I#N"ERR#R
(92AABA7 CEm1,-4ee must +e =<'D);
END I>;

0ommunicate an error number and message to a

non6P%&$'% host environment.

$he ,ollowing code ,rom a database

triggers shows a typical !and problematic"
usage o, :8IS%58((/I'8$I)*5%::)::
RAI!E"A&&LICA%I#N"ERR#R
(num +ina*4"inteEe*7 msE Fa*c/a*27
.ee1e**-*stac. +--,ean 2e3au,t >AL!E);
Copyright 2000-2006 Steven Feuerstein - Page 14
Qui(2 #n +'ce&tional Package

$o 1 create the valerr pac"age and then e!ecute the

ollowing command. What is displayed on the screen(
&ACGAGE Fa,e**
I!
>$NC%I#N
Eet RE%$RN VARCHAR2;
END Fa,e**;
!HL? E(EC$%E 1', (Fa,e**'Eet);
&ACGAGE B#D6 Fa,e**
I!
F VARCHAR2(=) := Ca+cD;
>$NC%I#N Eet RE%$RN VARCHAR2 I!
BEGIN
RE%$RN F;
END;
BEGIN
1', ('Be3-*e I s/-8 4-u F'''');
E(CE&%I#N
)HEN #%HER! %HEN
1', (C%*a11e2 t/e e**-*ID);
END Fa,e**;
alerr".4$
alerr2".4$
Copyright 2000-2006 Steven Feuerstein - Page 15
Han"ling +'ce&tions

.he ,/0,P.123 section consolidates all error

handling logic in a bloc".

@ut only traps errors raised in the e3ecutable

section o, the bloc1.

$everal useul unctions usually come into play:

S0/')D% and S0/%::

D@S54$I/I$9.F):8$5%::):5S$8'A

D@S54$I/I$9.F):8$5%::):5@8'A$:8'%

.he D7-$5,++%24 pac"age

0uic1 and easy logging o, D/ errors

.he #8.,+ $,+9,+,++2+ trigger

Instance.wide error handling

Copyright 2000-2006 Steven Feuerstein - Page 16
$BMS/-T.L.TY error functions

4et the ull error message with

D7-$5U.1%1.:.82+-#.5,++2+5$.#0K

S0/%:: might truncate the message.

4se S0/%:: went you want to obtain the

message associated with an error number.

8ind line number on which error was raised with

D7-$5U.1%1.:.82+-#.5,++2+57#0K.+#0,

Introduced in )racleB>g :elease =# this

,unction returns the ,ull stac1 o, errors with
line number in,ormation.

Formerly# this stac1 was available only i, you

let the error go unhandled.
bac4trace"s;l
Copyright 2000-2006 Steven Feuerstein - Page 17
$BMS/+,,LO3 4Oracle56g,78

#llows D-% statements to e!ecute against

all rows, even i an error occurs.

$he /)6 %::):S clause speci,ies how

logging should occur.

4se the D@S5%::/)6 pac1age to

associate a log table with D/
operations on a base table.

-uch aster than trapping errors, logging,

and then continuing&recovering.

3ote: 82+#%% with $#9, ,/0,P.123$

oers similar capabilities.
#bms9errlo$"*
Copyright 2000-2006 Steven Feuerstein - Page 18
The #9T+, S+,:+,+,,O, trigger

Provides a relatively simple way to use a

single table and single procedure or
e!ception handling in an entire instance.

Drawbac"s:

%rror must go unhandled out o, your

(//S0/ bloc1 ,or the trigger to 1ic1 in.

Does not ,ire ,or all errors !*): .C>>#

.BD>E# .BD==..."

-ost useul or non6P%&$'% ront ends

e!ecuting $'% statements directly.
afterserererror"s;l
Copyright 2000-2006 Steven Feuerstein - Page 19
+'ce&tions an" $ML

D-% statements generally are not rolled bac" when an

e!ception is raised.

$his gives you more control over your

transaction.

+ollbac"s occur with...

4nhandled e3ception ,rom the outermost

(//S0/ bloc1<

%3it ,rom autonomous transaction without

commit/rollbac1<

)ther serious errors# such as ":ollbac1

segment too small".

0orollary: error logs should rely on autonomous

transactions to avoid sharing the same transaction as
the application.

/og in,ormation is committed# while leaving

the business transaction unresolved.
lo$8i".4$
Copyright 2000-2006 Steven Feuerstein - Page 20
Best &ractices for error management

0ompensate or P%&$'% wea"nesses.

$ome general guidelines:

8void hard.coding o, error numbers

and messages.

@uild and use reusable components

,or raising# handling and logging
errors.

#pplication6level code should not contain:

:8IS%58((/I'8$I)*5%::):: donFt
leave it to the developer to decide
how to raise.

(:868 %&'%($I)*5I*I$: avoid

duplication o, error de,initions.
Copyright 2000-2006 Steven Feuerstein - Page 21
0om&ensate for PL/SQL weaknesses

.he ,/0,P.123 datatype does not allow you

to store the ull set o inormation about an
error.

2hat was the conte3t in which the error

occurred-

Diicult to ensure e!ecution o common error

handling logic.

4sually end up with lots o, repetition.

*o ",inally" section available in (//S0/ .

yet.

+estrictions on how you can speciy the error

)nly B>>> ,or application.speci,ic

errors....
Copyright 2000-2006 Steven Feuerstein - Page 22
Ob;ect*like re&resentation of an e'ce&tion

#n error is a row in the error table, with many

more attributes than simply code and
message, including:

Dynamic message !substitution

variables"

+elp message !how to recover ,rom

the problem"

#n error instance is one particular

occurrence o an error.

8ssociated with it are one or more

values that re,lect the conte3t in
which the error was raised.
Copyright 2000-2006 Steven Feuerstein - Page 23
+,$ for error "efinition tables
;#9error"er#
;#9runtime".4b
Copyright 2000-2006 Steven Feuerstein - Page 24
Har" to aoi" co"e re&etition in han"lers

=f eery #eelo.er !rites e<ce.tion han#ler co#e

on their o!n, you en# u. !ith an unmana$eable
situation"

Di,,erent logging mechanisms# no standards

,or error message te3t# inconsistent handling
o, the same errors# etc.
)HEN N#"DA%A">#$ND %HEN
IN!ER% IN%# e**,-E
VAL$E! ( !HLC#DE
7 'N- c-m1an4 3-* i2 ' JJ %#"CHAR ( F"i2 )
7 '3iK2e+t'7 !6!DA%E7 $!ER );
)HEN #%HER! %HEN
IN!ER% IN%# e**,-E
VAL$E! (!HLC#DE7 !HLERR7 '3iK2e+t'7 !6!DA%E7 $!ER );
RAI!E;
END;
Copyright 2000-2006 Steven Feuerstein - Page 25
Protot!&e e'ce&tion manager &ackage
&ACGAGE e**1.E
I!
&R#CED$RE *aise (e**"in IN &L!"IN%EGER);
&R#CED$RE *aise (e**"in in VARCHAR2);
&R#CED$RE *ec-*2"an2"st-1 (
e**"in IN &L!"IN%EGER := !HLC#DE
7msE"in IN VARCHAR2 := N$LL);
&R#CED$RE *ec-*2"an2"c-ntinue (
e**"in IN &L!"IN%EGER := !HLC#DE
7msE"in IN VARCHAR2 := N$LL);
END e**1.E;
,eneric 3aises
3ecor#
an# Sto.
3ecor#
an# Continue
err.4$".4$
Copyright 2000-2006 Steven Feuerstein - Page 26
.noking stan"ar" han"lers

.he rule: developers should only call a pre6deined

handler inside an e!ception section

a1e it easy ,or developers to write

consistent# high.quality code

$hey donFt have to ma1e decisions about the

,orm o, the log and how the process should be
stopped
E(CE&%I#N
)HEN N#"DA%A">#$ND
%HEN
e**1.E'*ec-*2"an2"c-ntinue (
!HLC#DE7
' N- c-m1an4 3-* i2 ' JJ %#"CHAR (F"i2));
)HEN #%HER!
%HEN
e**1.E'*ec-*2"an2"st-1;
END;
*he #eelo.er sim.ly
de!cribe!
the #esire# action"
Copyright 2000-2006 Steven Feuerstein - Page 27
S&ecif!ing the error
)ow should 1 speciy the application6
speciic error 1 need to raise(
; <ust use 6=>>>> all the time(
; Pic" one o those ?>>> numbers rom
6=>@@@ to 6=>>>>(
; Use any positive error number
besides ? and ?>>(
; Use error names instead o numbers(
Copyright 2000-2006 Steven Feuerstein - Page 28
#oi" har"*co"ing of *76<111 +rrors

4ive your
error numbers
names and
associate
them with
named
e!ceptions.
&ACGAGE e**nums
I!
en"Eene*a,"e**-* C#N!%AN% N$BER := 92AAAA;
eKc"Eene*a,"e**-* E(CE&%I#N;
&RAGA E(CE&%I#N"INI%
(eKc"Eene*a,"e**-*7 92AAAA);
en"must"+e"=< C#N!%AN% N$BER := 92AAA=;
eKc"must"+e"=< E(CE&%I#N;
&RAGA E(CE&%I#N"INI%
(eKc"must"+e"=<7 92AAA=);
en"sa,"t--",-8 C#N!%AN% N$BER := 92AAA2;
eKc"sa,"t--",-8 E(CE&%I#N;
&RAGA E(CE&%I#N"INI%
(eKc"sa,"t--",-8 7 92AAA2);
maK"e**-*"use2 C#N!%AN% N$BER := 92AAA2;
END e**nums; ms$info".4$
ms$info"fmb6fm<
2ut #on>t !rite this
co#e manually?
Copyright 2000-2006 Steven Feuerstein - Page 29
-sing the stan"ar" raise &rogram

+ather than have individual programmers call

+#1$,5#PP%10#.1235,++2+, simply call the
standard raise program. 7eneits:

%asier to avoid hard.codings o, numbers.

Support positive error numbersG

%etAs revisit that trigger logic using the inrastructure

elements...
&R#CED$RE Fa,i2ate"em1 (+i*t/2ate"in IN DA%E) I!
BEGIN
I> ADD"#N%H! (!6!DA%E7 =< @ =2 @ 9=) L +i*t/2ate"in
%HEN
e**1.E'*aise (e**nums'en"t--"4-unE);
END I>;
END;
@o more har#&co#e#
strin$s or numbers"
Copyright 2000-2006 Steven Feuerstein - Page 30
,aise/han"le errors b! number===or name>

.he above trigger ragment illustrates a common

problem: )ard6coding o error numbers and
messages.

0ertainly, it is better to use named constants, as in:

BEGIN
I> em1,-4ee"*1'is"t-"4-unE (:ne8'/i*e"2ate)
%HEN
RAI!E"A&&LICA%I#N"ERR#R (
92A=B57 '6-u must +e at ,east =< 4ea*s -,2I');
END I>;
BEGIN
I> em1,-4ee"*1'is"t-"4-unE (:ne8'/i*e"2ate)
%HEN
RAI!E"A&&LICA%I#N"ERR#R (
em1,-4ee"*1'en"t--"4-unE
7 em1,-4ee"*1'em"t--"4-unE);
END I>;
2ut no! !e hae a
centrali:e#
#e.en#ency"
Copyright 2000-2006 Steven Feuerstein - Page 31
,aising errors b! name

Use an error name Bliteral valueC.

$he code compiles now.

/ater# I de,ine that error in the

repository.

*o central point o, ,ailure.

Downsides: ris" o typos, runtime

notiication o Dundeined error.D
BEGIN
I> em1,-4ee"*1'is"t-"4-unE (:ne8'/i*e"2ate)
%HEN
M2"*untime'*aise"e**-* (
'E&L#6EE9%##96#$NG'
7 name="in =? 'LA!%"NAE'
7 Fa,ue="in =? :ne8',ast"name);
END I>;
7n<o
!!!";n<o"com
;#9runtime"*
Copyright 2000-2006 Steven Feuerstein - Page 32
Summar!2 an +'ce&tion Han"ling #rchitecture

-a"e sure you understand how it all wor"s

%3ception handling is tric1y stu,,

$et standards beore you start coding

ItFs not the 1ind o, thing you can easily add in

later

Use standard inrastructure components

%veryone and all programs need to handle

errors the same way

.a"e ull advantage o error management eatures.

S8H% %&'%($I)*S# D@S5%::/)6#

D@S54$I/I$9.F):8$5%::):5@8'A$:8'%...

DonAt accept the limitations o 2racleAs current

implementation.

9ou can do lots to improve the situation.

Copyright 2000-2006 Steven Feuerstein - Page 33
More PL/SQL< Please)

7ryn %lewellyn, P%&$'% Product -anager,

will hold orth at the Database 4uru %ounge
at ?=:E> P- today.

Located in the Database Demo

rounds area which is in the !est
"all at the rear# dead center and
under the area D sign
Copyright 2000-2006 Steven Feuerstein - Page 34
OPP 2007
February 28 March 1, 2007
San Mateo Marriott
San Mateo, California
An ODTUG SP* Oracle PL/SQL
Programming Conference
*SP Seriously Practical Conference
For more information isit !!!"o#tu$"com or call %10&'(2&7'''
O)*+, -alei#osco.e
/une 18 21, 2007
Pre&conference 0an#s&on *rainin$ & /une 11 17
0ilton )aytona 2each Oceanfront 3esort
)aytona, Flori#a
WOW-Wide Open World, Wide Open Web