ORA-06592 and the Case of the Happy Australians

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?

Well, that was unexpected.

So, on the face of it, is this…

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.

Advertisements

2 thoughts on “ORA-06592 and the Case of the Happy Australians

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.