We would need to declare function deterministic even though it isn't which could cause unexpected results in other statements:
drop sequence test_seq;
create sequence test_seq;
create or replace
function get_seq_val
return varchar2
deterministic
is
v_seq_val varchar2(100);
begin
v_seq_val := test_seq.nextval;
dbms_output.put_line('get_seq_val:' || v_seq_val);
return v_seq_val;
end get_seq_val;
/
set serveroutput on
column seqval format a6
SQL> with temp as( select /*+ no_merge */
2 get_seq_val as seqval from dual)
3 select seqval,seqval from temp;
SEQVAL SEQVAL
------ ------
1 1
get_seq_val:1
SQL> with temp as( select
2 get_seq_val as seqval from dual)
3 select /*+ no_merge(temp) */ seqval,seqval from temp;
SEQVAL SEQVAL
------ ------
2 2
get_seq_val:2
SQL> with temp as( select /*+ materialize */
2 get_seq_val as seqval from dual)
3 select seqval,seqval from temp;
SEQVAL SEQVAL
------ ------
3 3
get_seq_val:3
SQL>
But we would get unexpected results in, for example, multi-row select:
SQL> with temp as( select /*+ no_merge */
2 get_seq_val as seqval from dual)
3 select deptno,seqval,seqval from temp,dept;
DEPTNO SEQVAL SEQVAL
---------- ------ ------
10 4 4
20 4 4
30 4 4
40 4 4
get_seq_val:4
SQL>
while most likely we expexted:
SQL> with temp as( select /*+ no_merge */
2 get_seq_val as seqval from dual)
3 select deptno,seqval,seqval from temp,dept;
DEPTNO SEQVAL SEQVAL
---------- ------ ------
10 4 4
20 5 5
30 6 6
40 7 7
get_seq_val:4
get_seq_val:5
get_seq_val:6
get_seq_val:7
SQL>
SY.