Oracle - delete where long like

Question

How do you delete rows from a table, where a column contains a substring, but the type of that column is 'Long'. (Yes, I know I shouldn't use Long, but I'm maintaining someone else's mess).

My first attempt was:

delete from longtable 
  where search_long(rowid) like '%hello%';  

(following on from this answer)

This returns:

SQL Error: ORA-04091: table blah.longtable is mutating, trigger/function may not see it

Answer 1

I just replicated your problem and got the same error - it seems the function can't work from within a DELETE statement. The full text of the error is:

ORA-04091: table HOU.LONGTABLE is mutating, trigger/function may not see it
ORA-06512: at "TONY.SEARCH_LONG", line 4

This procedural approach will work:

begin
  for r in (select id from longtable 
            where search_long(rowid) like '%hello%')
  loop
    delete longtable where id = r.id;
  end loop;
end;