4

ORA-06592 and the Case of the Happy Australians

 3 years ago
source link: https://mikesmithers.wordpress.com/2018/01/26/ora-06592-and-the-case-of-the-happy-australians/
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.

ORA-06592 and the Case of the Happy Australians

Posted on January 26, 2018

Another Ashes Tour to Australia has come and gone and the home team once again hold The Urn.
For any non-cricket fans, I should probably explain.
Every four years, England sends their Men’s and Women’s Cricket Teams to Australia on a goodwill mission.
The object of the exercise is to make Australians feel good about their country as their teams inevitably triumph.

These recently concluded contests provide the theme for the illustration of the less-than-straightforward circumstance surrounding the ORA-06592 error which follows.
When encountering this error, you’ll probably see something like

ORA-06592: CASE not found while executing CASE statement
06592. 00000 -  "CASE not found while executing CASE statement"
*Cause:    A CASE statement must either list all possible cases or have an
else clause.
*Action:   Add all missing cases or an else clause.

Despite this apparently definitive advice, you don’t always need to cover any possible case, or include an ELSE clause…

The buggy code

Say we have the following table and records…

create table ashes_big_inns (
batter varchar2(50),
team varchar2(10),
runs number,
not_out_ind varchar2(1))
/
insert into ashes_big_inns( batter, team, runs, not_out_ind)
values('STEVE SMITH', 'AUSTRALIA', 239, 'N')
/
insert into ashes_big_inns( batter, team, runs, not_out_ind)
values('ELLYSE PERRY', 'AUSTRALIA', 213, 'Y')
/
insert into ashes_big_inns( batter, team, runs, not_out_ind)
values('ALISTAIR COOK', 'ENGLAND', 244, 'Y')
/
commit;

Now, we may want to celebrate these achievements by means of the following :

set serveroutput on size unlimited
declare
message varchar2(100);
begin
for r_player in (select team from ashes_big_inns) loop
case r_player.team
when 'AUSTRALIA' then message := q'[C'mon Aussie, C'mon !]';
end case;
dbms_output.put_line(nvl(message, 'Where did that urn go ?'));
end loop;
end;
/

Things start promisingly enough (especially if you’re Australian) …

C'mon Aussie, C'mon !
C'mon Aussie, C'mon !

…before falling apart like England’s middle order…

...
Error report -
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 5
06592. 00000 -  "CASE not found while executing CASE statement"
*Cause:    A CASE statement must either list all possible cases or have an
else clause.
*Action:   Add all missing cases or an else clause.

According to this error then, a CASE statement must either list all possible cases or have an else clause…

An unsolved CASE

Let’s change things around a bit. This time, we’re going to put the case statement on the right hand side of an assignment…

set serveroutput on size unlimited
declare
message varchar2(100);
begin
for r_player in (select team from ashes_big_inns) loop
message :=
case r_player.team
when 'AUSTRALIA' then q'[C'mon Aussie, C'mon !]'
end;
dbms_output.put_line(nvl(message, 'Where did that urn go ?'));
end loop;
end;
/

The CASE statement is still lacking a case for the team being ‘ENGLAND’ as well as an ELSE clause. So when we run it we should get the error, right?

case_assign.png?w=584&h=285

Well, that was unexpected.

So, on the face of it, is this…

sql_case.png?w=584&h=159

Before we get too indignant about the seemingly blatant inaccuracy in the Oracle Error message, it’s probably worth remembering that there are two distinct processing engines at work when PL/SQL is running on an Oracle Database – SQL and PL/SQL.

Reading the Manual

According to the PL/SQL documentation for the CASE Statement :

“Without the ELSE clause, if no boolean_expression has the value TRUE, the system raises the predefined exception CASE_NOT_FOUND.”

By contrast, the SQL docs for the CASE expression say that :

“If no condition is found to be true, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. ”

Let’s take a fresh look at our original effort…

set serveroutput on size unlimited
declare
message varchar2(100);
begin
for r_player in (select team from ashes_big_inns) loop
case r_player.team
when 'AUSTRALIA' then message := q'[C'mon Aussie, C'mon !]';
end case;
dbms_output.put_line(nvl(message, 'Where did that urn go ?'));
end loop;
exception when CASE_NOT_FOUND then
dbms_output.put_line('I was expecting that !');
end;
/

Sure enough, when we run this we get…

From this, we can conclude that we’re running the PL/SQL CASE statement hence the raising of the pre-defined CASE_NOT_FOUND exception.
One other way of distinguishing between the PL/SQL CASE statement and the SQL CASE expression is the minor syntactical difference in their ending.
In PL/SQL you need to terminate the statement with END CASE. In SQL, you simply type END.

So, whilst the error message is correct in what it says about a CASE statement, the solution may well be to use a CASE expression instead.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK