Home » SQL & PL/SQL » SQL & PL/SQL » case statement in a stored procedure (Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production)  () 1 Vote
case statement in a stored procedure [message #424757] Mon, 05 October 2009 19:37 Go to next message
cesarnz
Messages: 10
Registered: November 2008
Junior Member
hi all,
found something weird in a stored procedure which has a case statement in it. the case statement is

SELECT (CASE TABLE_NAME
	 	WHEN 'A' THEN seq_A.NEXTVAL
		WHEN 'B' THEN seq_B.NEXTVAL
		WHEN 'C' THEN seq_C.NEXTVAL
		WHEN 'D' THEN seq_D.NEXTVAL
		ELSE 0
	END)
	 INTO NEXT_VAL FROM DUAL;


when i execute this the SP from sqlplus i noticed that it increases the sequence for all the tables listed in the case statement instead of just the one table which is being called !! e.g

sql>var v_result number
sql>exec sp_isauthorized('A', :v_result)

PL/SQL procedure successfully completed.

sql>print v_result  -- or turn autoprint on


i noticed that all the sequences have increased by 1 rather than just the "seq_A".

is this a bug in oracle or some patch which i need to do ??

thanks in advance.
Re: case statement in a stored procedure [message #424766 is a reply to message #424757] Mon, 05 October 2009 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is this a bug in oracle or some patch which i need to do ??

You cannot make assumptions on how Oracle works. You may call it a bug and Oracle a feature.
However it does not (it should not) matter if Oracle increases all the sequences or only one.
Try with a multilines query.

Always post a working test case: create objects and insert statements.
Also always post the whole session you executed... how did you see that all sequences were increased?

Regards
Michel

[Updated on: Mon, 05 October 2009 23:20]

Report message to a moderator

Re: case statement in a stored procedure [message #424837 is a reply to message #424766] Tue, 06 October 2009 03:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fortunately, some of us are interested enough in this sort of thing to create our own test cases.

Here's some generic logging code, and a function that returns the value you give it and record that it was called:
create table general_log (log_id   number, log_text  varchar2(4000));

create sequence log_seq;

create or replace function log_result(p_in  varchar2) return varchar2 is
  pragma autonomous_transaction;
begin
  insert into general_log
  values (log_seq.nextval,p_in);
  
  commit;
  
  return p_in;
end;
/


As we can see from this, Oracle does not always evaluate the functions in the branches of CASE statements other tan the branch it selects:
SQL> delete general_log;

1 row deleted.

SQL> 
SQL> SELECT case v_test 
  2                 when 'A' then log_result('A')
  3                 when 'B' then log_result('B')
  4                 when 'C' then log_result('C')
  5                 when 'D' then log_result('D')
  6            end
  7  from (select 'C' v_test from dual);

CASEV_TESTWHEN'A'THENLOG_RESULT('A')WHEN'B'THENLOG_RESULT('B')WHEN'C'THENLOG_RES
--------------------------------------------------------------------------------
C

SQL> 
SQL> select * from general_log;

LOG_ID     LOG_TEXT
---------- -------------------------
        24 C


If we try a case similar to the OPs, using sequences, we get something quite different:

create sequence a_seq;
create sequence b_seq;
create sequence c_seq;
create sequence d_seq;

select * from user_sequences where sequence_name like '__SEQ';

delete general_log;

SELECT case v_test 
               when 'A' then log_result(a_seq.nextval)
               when 'B' then log_result(b_seq.nextval)
               when 'C' then log_result(c_seq.nextval)
               when 'D' then log_result(d_seq.nextval)
          end
from (select 'C' v_test from dual);

select * from general_log;

select a_seq.currval,b_seq.currval,c_seq.currval,d_seq.currval from dual;

select * from user_sequences where sequence_name like '__SEQ';


This shows only a single row being logged in the log table, but all of the sequences now have a CURRVAL attribute, meaning that they've all been selected.
If you repeat the Case Select, then you can see in User_Sequences that the last_number increases for each of the sequences, regardless of whether or not it's been selected.

I can only assume that the optimiser sees the sequence, and generates the sequence values, and them worries about the rest of the query.
Re: case statement in a stored procedure [message #424962 is a reply to message #424837] Tue, 06 October 2009 15:06 Go to previous messageGo to next message
cesarnz
Messages: 10
Registered: November 2008
Junior Member
that makes sense, thanks.
Re: case statement in a stored procedure [message #424996 is a reply to message #424837] Wed, 07 October 2009 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does indeed increment every sequences at each row, that is it executes nextval before case. Why? Because sequence must give the same value accross all the expressions for a returned row (even if you use nextval). Here's only one expression is returned but there could be several ones using some of the previous sequences and the result of the query should not depend on the order of the expressions.
drop sequence a_seq;
drop sequence b_seq;
drop sequence c_seq;
drop sequence d_seq;
create sequence a_seq nocache;
create sequence b_seq nocache;
create sequence c_seq nocache;
create sequence d_seq nocache;
SELECT v_test,
       case v_test 
          when 'A' then a_seq.nextval
          when 'B' then b_seq.nextval
          when 'C' then c_seq.nextval
          when 'D' then d_seq.nextval
       end v,
       b_seq.nextval b
from (
select 'A' v_test from dual
union all
select 'B' v_test from dual
union all
select 'C' v_test from dual
union all
select 'C' v_test from dual
);
select a_seq.currval,b_seq.currval,c_seq.currval,d_seq.currval from dual;

SQL> SELECT v_test,
  2         case v_test 
  3            when 'A' then a_seq.nextval
  4            when 'B' then b_seq.nextval
  5            when 'C' then c_seq.nextval
  6            when 'D' then d_seq.nextval
  7         end v,
  8         b_seq.nextval b
  9  from (
 10  select 'A' v_test from dual
 11  union all
 12  select 'B' v_test from dual
 13  union all
 14  select 'C' v_test from dual
 15  union all
 16  select 'C' v_test from dual
 17  );
V          V          B
- ---------- ----------
A          1          1
B          2          2
C          3          3
C          4          4

4 rows selected.

SQL> select a_seq.currval,b_seq.currval,c_seq.currval,d_seq.currval from dual;
   CURRVAL    CURRVAL    CURRVAL    CURRVAL
---------- ---------- ---------- ----------
         4          4          4          4

1 row selected.

Note if you don't want this behaviour you can hide the sequence accesses inside a function (but you then lose the property to get the same value of a sequence in all expressions, and so the result of the query depends on the order of these expressions):
create or replace function get_next(seq varchar2) return number
is
  ret number;
begin
  case seq
    when 'A' then select a_seq.nextval into ret from dual;
    when 'B' then select b_seq.nextval into ret from dual;
    when 'C' then select c_seq.nextval into ret from dual;
    when 'D' then select d_seq.nextval into ret from dual;
  end case;
  return ret;
end;
/
SELECT v_test,
       case v_test 
         when 'A' then get_next('A')
         when 'B' then get_next('B')
         when 'C' then get_next('C')
         when 'D' then get_next('D')
       end v,
       get_next('B') b
from (
select 'A' v_test from dual
union all
select 'B' v_test from dual
union all
select 'C' v_test from dual
union all
select 'C' v_test from dual
);
select a_seq.currval,b_seq.currval,c_seq.currval,d_seq.currval from dual;

SQL> SELECT v_test,
  2         case v_test 
  3           when 'A' then get_next('A')
  4           when 'B' then get_next('B')
  5           when 'C' then get_next('C')
  6           when 'D' then get_next('D')
  7         end v,
  8         get_next('B') b
  9  from (
 10  select 'A' v_test from dual
 11  union all
 12  select 'B' v_test from dual
 13  union all
 14  select 'C' v_test from dual
 15  union all
 16  select 'C' v_test from dual
 17  );
V          V          B
- ---------- ----------
A          1          1
B          2          3
C          1          4
C          2          5

4 rows selected.

SQL> select a_seq.currval,b_seq.currval,c_seq.currval,d_seq.currval from dual;
select a_seq.currval,b_seq.currval,c_seq.currval,d_seq.currval from dual
                                                 *
ERROR at line 1:
ORA-08002: sequence D_SEQ.CURRVAL is not yet defined in this session


SQL> select a_seq.currval,b_seq.currval,c_seq.currval from dual;
   CURRVAL    CURRVAL    CURRVAL
---------- ---------- ----------
         1          5          2

1 row selected.

The last but one query shows that sequence was not accessed.
The second line with v_test='B' show that you get a different value for both expression, so if you put get_next('B') expression before or after the case one, you get a different result for the query.

Regards
Michel

Re: case statement in a stored procedure [message #425007 is a reply to message #424996] Wed, 07 October 2009 02:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good demonstration.

Previous Topic: Add columns between tables
Next Topic: Sort a String and Prepare Range
Goto Forum:
  


Current Time: Thu Sep 29 10:50:32 CDT 2016

Total time taken to generate the page: 0.08716 seconds