Mysql – simulate sequence on thesql in preparation for move to oracle

This can be done in a single statement, but you have to wrap the sequence call in a function.

By calling a function in the select (as opposed to the sequence directly), you overcome the problem of getting ORA-02287 errors. However, the function will be called once for each row in A, which is not what you want.

This can be overcome by defining the function as deterministic. This allows Oracle to optimize the function so it is only executed once for each distinct parameter value passed in. To make this work in this case, you'll need to pass in ceil(x/2):

create or replace function f(p integer) return number deterministic  as
  l_retval pls_integer;
begin
  select my_seq.nextval into l_retval from dual;
  return l_retval;
end;
/

insert all 
    when mod(x,2) = 0 then
        into b (val) values (seq)
        into c (val) values (seq)
    else 
        into c (val) values (seq)
select x, f(ceil(x/2)) seq from a;


select * from b;

VAL
---
  1 
  2 

select * from c;

VAL
---
  1 
  2 
  1 
  2 

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'