Oracle: guaranteeing unmodified database state with queries?
It's possible for a SELECT statement to modify the database, for example:


select mysequence.nextval increments a sequence
select myfunction executes a PL/SQL function which could have any sort of side effects


The last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as

ORA-14551: cannot perform a DML operation inside a query
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML


are returned:

SQL&gt; set serveroutput on
SQL&gt; create table t1 (x number);

Table created.

SQL&gt; insert into t1 values (42);

1 row created.

SQL&gt; commit;

Commit complete.

SQL&gt; select * from t1;

         X
----------
        42

SQL&gt; create or replace function f1 return varchar2 is
   pragma autonomous_transaction;
begin
   execute immediate 'truncate table t1';
   return 'Table t1 truncated';
end f1;
/
  2    3    4    5    6    7
Function created.

SQL&gt; select f1 from dual;

F1
--------------------------------------------------------------------------------
Table t1 truncated

SQL&gt; select * from t1;

no rows selected