9

Time flies when you’re having fun – The truth about Oracle Exception handlers (t...

 3 years ago
source link: https://mikesmithers.wordpress.com/2020/07/10/time-flies-when-youre-having-fun-the-truth-about-oracle-exception-handlers-this-week/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Time flies when you’re having fun – The truth about Oracle Exception handlers (this week)

Posted on July 10, 2020

Oracle Twitter is a bit like Poker – you tend to take notice when Aces turn up.
I mention this because I’m indebted to Jacek Gebal, Sabine Heismath and Andy Sayer, whose discussion on this topic alerted me to the fact that PL/SQL exception handling had changed in a small but important way whilst I wasn’t looking.

Just in case you’re wondering whether that opening paragraph is a prelude to a post containing gratuitous references to a certain Motorhead song, all I can say is…if you like to gamble…

How things were

Consider this procedure :

create or replace procedure deal_em( i_card in varchar2)
as
begin
if i_card in ('7', '11') then
raise_application_error(-20000, 'Snake eyes watching you');
elsif upper(i_card) = 'JOKER' then
raise_application_error(-20001, q'[Don't forget the Joker]');
elsif upper(i_card) = 'ACE OF SPADES' then
dbms_output.put_line('The only card I need !');
else
raise_application_error(-20002, q'[I'm a shade of green]');
dbms_output.put_line('Card is '||i_card);
end if;   
exception when others then
-- I'll see your application error and raise you...
raise;
end;
/

As I observed some years ago, back in the days of 11g and before PL/SQL programmers could entertain themselves for hours by playing hunt the exception…

SQL> exec deal_em('7')
BEGIN deal_em('7'); END;
*
ERROR at line 1:
ORA-20000: Snake eyes watching you
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at line 1
SQL> exec deal_em('Joker')
BEGIN deal_em('Joker'); END;
*
ERROR at line 1:
ORA-20001: Don't forget the Joker
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at line 1
SQL> exec deal_em('2')
BEGIN deal_em('2'); END;
*
ERROR at line 1:
ORA-20002: I'm a shade of green
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at line 1

Read ’em and weep, three different exceptions but all of them are reported at line 16.

From 12cR2 onward (thanks Phil Goldenberg) however, some clever people at Oracle seem to have been pushing up the ante…

SQL> exec deal_em('7')
BEGIN deal_em('7'); END;
*
ERROR at line 1:
ORA-20000: Snake eyes watching you
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at "MIKE.DEAL_EM", line 5
ORA-06512: at line 1
SQL> exec deal_em('Joker')
BEGIN deal_em('Joker'); END;
*
ERROR at line 1:
ORA-20001: Don't forget the Joker
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at "MIKE.DEAL_EM", line 7
ORA-06512: at line 1
SQL> exec deal_em('2')
BEGIN deal_em('2'); END;
*
ERROR at line 1:
ORA-20002: I'm a shade of green
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at "MIKE.DEAL_EM", line 11
ORA-06512: at line 1

Yep, right there at the bottom of the error stack is the line number from which the error originated.
There you have it, PL/SQL exceptions are now rather easier to pinpoint.
Oh, and gambling’s for fools.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK