PL/SQL CASE Statement

This is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL. Here is a simple example, which checks whether a given number is 1 or 2. You start with the word case. Then you add one or more conditions followed by the action to take if that condition is satisfied. Each condition is preceded by the word when. You finish with the words end case:

SQL> declare

  2    one_or_two number := 1;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two = 2 then

  8      dbms_output.put_line('Two');

  9    end case;

 10  end;

 11  /

One

 

PL/SQL procedure successfully completed.

 

SQL>

The next example puts the variable name after the word case. This saves you having to include it in every when line:

SQL> declare

  2    one_or_two number := 2;

  3  begin

  4    case one_or_two

  5    when 1 then dbms_output.put_line('One');

  6    when 2 then dbms_output.put_line('Two');

  7    end case;

  8  end;

  9  /

Two

 

PL/SQL procedure successfully completed.

 

SQL>

If none of the when clauses are satisfied, you get an ORA-06592:

SQL> declare

  2    one_or_two number := 3;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two = 2 then

  8      dbms_output.put_line('Two');

  9    end case;

 10  end;

 11  /

declare

*

ERROR at line 1:

ORA-06592: CASE not found while executing CASE

statement

ORA-06512: at line 4

 

SQL>

One way to avoid this is to add an else clause before the end case:

SQL> declare

  2    one_or_two number := 3;

  3  begin

  4    case one_or_two

  5    when 1 then dbms_output.put_line('One');

  6    when 2 then dbms_output.put_line('Two');

  7    else dbms_output.put_line('Try again');

  8    end case;

  9  end;

 10  /

Try again

 

PL/SQL procedure successfully completed.

 

SQL>

An alternative is to add an exception section and check for case_not_found:

SQL> declare

  2    one_or_two number := 3;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two = 2 then

  8      dbms_output.put_line('Two');

  9    end case;

 10  exception

 11    when case_not_found then

 12      dbms_output.put_line('Try again');

 13  end;

 14  /

Try again

 

PL/SQL procedure successfully completed.

 

SQL>

Once a condition has been satisfied, Oracle jumps to the end case statement, ignoring the remaining conditions:

SQL> declare

  2    one_or_two number := 1;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two < 2 then

  8      dbms_output.put_line('Less than two');

  9    end case;

 10  exception

 11    when case_not_found then

 12      dbms_output.put_line('Try again');

 13  end;

 14  /

One

 

PL/SQL procedure successfully completed.

 

SQL>

You can even use a Boolean expression after the word case, as you can see in the two examples below:

SQL> begin

  2  case 1 = 1

  3  when true then

  4    dbms_output.put_line('1 = 1');

  5  when false then

  6    dbms_output.put_line('1 != 1');

  7  end case;

  8  end;

  9  /

1 = 1

 

PL/SQL procedure successfully completed.

 

SQL> begin

  2  case 1 = 2

  3  when true then

  4    dbms_output.put_line('1 = 2');

  5  when false then

  6    dbms_output.put_line('1 != 2');

  7  end case;

  8  end;

  9  /

1 != 2

 

PL/SQL procedure successfully completed.

 

SQL>

These examples were all tested in an Oracle 11 database.

ORA-12801 in utlrp

I tried to run a utlrp in an Oracle 11.1.0.6.0 database and saw the errors below:

ORA-12801: error signaled in parallel query server P035

ORA-00018: maximum number of sessions exceeded

ORA-06512: at "SYS.UTL_RECOMP", line 629

ORA-06512: at line 4

I checked the number of sessions in v$session but there were not very many:

SQL> select count(*) from v$session;

 

  COUNT(*)

----------

        32

 

SQL>

I checked the value of the sessions parameter and it was much higher:

SQL> l

  1  select value from v$parameter

  2* where name = 'sessions'

SQL> /

 

VALUE

----------

87

 

SQL>

I took a look in the utlrp.sql and saw the following line:

@@utlprp.sql 0

I read somewhere that the parameter supplied to utlprp.sql specifies the number of parallel processes to use when doing the recompilations. If it is zero, the value is calculated as cpu_count x parallel_threads_per_cpu. I checked these two parameters:

SQL> l

  1  select name, value

  2  from v$parameter

  3  where name in

  4* ('cpu_count', 'parallel_threads_per_cpu')

SQL> /

 

NAME                           VALUE

------------------------------ ----------

cpu_count                      16

parallel_threads_per_cpu       2

 

SQL>

I traced my session and reran the utlrp.sql. When I looked in the trace file, I saw the following piece of SQL, which I have reformatted slightly:

WITH INST_CPUS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPUS

FROM GV$PARAMETER

WHERE NAME = 'cpu_count'),

INST_THREADS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPU_THREADS

FROM GV$PARAMETER

WHERE NAME = 'parallel_threads_per_cpu')

SELECT SUM((CASE WHEN CPUS <= 0 THEN 1 ELSE CPUS END)

* (CASE WHEN CPU_THREADS <= 0 THEN 1 ELSE CPU_THREADS END))

FROM INST_CPUS, INST_THREADS

WHERE INST_CPUS.INST_ID = INST_THREADS.INST_ID

So, clearly, cpu_count and parallel_threads_per_cpu are being multiplied together for some reason when you run a utlrp.sql. I changed the line above from:

@@utlprp.sql 0

to

@@utlprp.sql 1

... hoping that this would make the recompilations run one at a time. I ran the utlrp.sql again and it worked.