Categories
dba plsql sql tuning

EXECUTE IMMEDIATE ‘SELECT’ does not execute anything

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing.
exec execute immediate 'select 1/0 from dual connect by level<9999999999999' PL/SQL procedure successfully completed. Elapsed: 00:00:00.00

But it is really annoying... and not documented as far as I know.

Imagine I want to increase all my sequences by 1000

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

NEXTVAL
----------
1

SQL> begin
2 for f in (select sequence_name n from user_sequences)
3 loop
4 execute immediate
5 'select '||f.n||'.nextval from dual connect by level<=1000'; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL> select s.currval from dual;

CURRVAL
----------
1

Hmm, it does not work. Does SELECT work at all? Yes when it is a SELECT INTO 🙂

SQL> drop sequence s;

Sequence dropped.

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

NEXTVAL
----------
1

SQL> declare
2 type t is table of number index by pls_integer;
3 c t;
4 begin
5 for f in (select sequence_name n from user_sequences)
6 loop
7 execute immediate
8 'select '||f.n||'.nextval from dual connect by level<=1000' 9 bulk collect into c; 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed. SQL> select s.currval from dual;

CURRVAL
----------
1001

I wonder in which version this optimization/bug was introduced...