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...

18 thoughts on “EXECUTE IMMEDIATE ‘SELECT’ does not execute anything”

JAZollars January 26, 2011 at 20:25

Laurent,
Thanks for the great website and all the good articles. I have an 8.1.7.4 and 9.2.0.8, hp-ux instances handy and ran the test on both. The same issue exists back to 8i. The SID of the connection names will show the versions.

sys@L8174> –~~~~~ 8.1.7.4 test ~~~~~
sys@L8174> @conn ‘test/***@l8174’

USERNAME INST_NAME HOST_NAME VERSION STARTED SID SERIAL#
——————– ———— ————————- ———- ——– —– ——–
test L8174 server1 8.1.7.4.0 20101111 21 12401

test@LWAREPRD> exec execute immediate ‘select 1/0 from dual connect by level create sequence s1;

Sequence created.

test@L8174> select s1.nextval from dual;

NEXTVAL
————-
1

test@L8174> exec execute immediate ‘select 1/0 from dual connect by level set timing on
test@L8174> exec execute immediate ‘select 1/0 from dual connect by level
test@L8174> select sequence_name from user_sequences;

SEQUENCE_NAME
——————————
S1

Elapsed: 00:00:00.14
test@L8174>
test@L8174>
test@L8174> begin for f in (select sequence_name n from user_sequences)
2 loop
3 execute immediate
4 ‘select ‘||f.n||’.nextval
5 from dual
6 connect by level select s1.currval from dual;

NEXTVAL
————-
1

Elapsed: 00:00:00.14

system@c9208> –~~~~~ 9.2.0.8 test ~~~~~
system@c9208> @conn test/***@c9208

USERNAME INST_NAME HOST_NAME VERSION STARTED SID SERIAL#
——————– ———— ————————- ———- ——– —– ——–
TEST c9208 server2 9.2.0.8.0 20101221 10 14667

Elapsed: 00:00:00.11

test@c9208> exec execute immediate ‘select 1/0 from dual connect by level create sequence s1;

Sequence created.

Elapsed: 00:00:00.07
test@c9208>
test@c9208>
test@c9208> select s1.nextval from dual;

NEXTVAL
————-
1

Elapsed: 00:00:00.09
test@c9208>
test@c9208> begin for f in (select sequence_name n from user_sequences)
2 loop
3 execute immediate
4 ‘select ‘||f.n||’.nextval
5 from dual
6 connect by level
test@c9208> select s1.currval from dual;

CURRVAL
————-
1

Elapsed: 00:00:00.10
Centinul January 26, 2011 at 20:44

I’m not sure if I’d call this a bug or not. Looking at a 10046/SQL trace I think shows pretty clearly what Oracle is doing. Here is an excerpt of the trace file for the first code snippet you posted.

===================== PARSING IN CURSOR #255684584 len=85 dep=0 uid=72 oct=47 lid=72 tim=24920502652 hv=1485565950 ad='3d4c6f94' sqlid='3zur921c8rvzy' BEGIN execute immediate 'select 1/0 from dual connect by level<9999999999999'; END; END OF STMT PARSE #255684584:c=0,e=2670,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=24920502649 ===================== PARSING IN CURSOR #255700156 len=51 dep=1 uid=72 oct=3 lid=72 tim=24920516374 hv=3385496215 ad='3d4c695c' sqlid='4f3hrpv4wp4nr' select 1/0 from dual connect by level<9999999999999 END OF STMT PARSE #255700156:c=0,e=13064,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1236776825,tim=24920516371 EXEC #255700156:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1236776825,tim=24920517153 STAT #255700156 id=1 cnt=0 pid=0 pos=1 obj=0 op='CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=29 us)' STAT #255700156 id=2 cnt=0 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)' CLOSE #255700156:c=0,e=289,dep=1,type=3,tim=24920517678 EXEC #255684584:c=0,e=14629,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=24920517792 PARSE #255685732:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=24920520886 EXEC #255685732:c=0,e=421,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=24920521568
*** 2011-01-26 13:34:50.095 CLOSE #255684584:c=0,e=66,dep=0,type=0,tim=24925003053

Normally you would see a PARSE, EXEC followed by a FETCH operation. However, since you do not provide any INTO clause in the first example Oracle is not performing the fetch operation. If you notice cursor #255700156 is still open at the end of the trace file.
Dani Rey January 26, 2011 at 22:40

To me the behavior makes perfect sense. As Oracle doesn’t know where to send the result set to, it doesn’t execute the query at all.

Remember, the following

begin select * from dual; end;
/

Will always cause the following error message
PLS-00428: an INTO clause is expected in this SELECT statement

As you used execute immediate, it can not produce an error at compile time.
Laurent Schneider Post authorJanuary 27, 2011 at 09:26

@dani but it does produce an error at execution time neither !
Sokrates January 27, 2011 at 10:38

“…and not documented as far as I know…..”

I do not agree:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/executeimmediate_statement.htm#LNPLS01317

states
…
Use [into_clause ] if and only if dynamic_sql_stmt returns a single row.
…
Use [bulk_collect_into_clause] if and only if dynamic_sql_stmt can return multiple rows.
…

I read that as (note the “if and only if”) : you *have to use* an into / bulk_collect_into – clause when executing a SELECT you expect to return at least one row.

(
select s1.nextval from dual where null is not null
doesn’t increase the sequence either
)
Laurent Schneider Post authorJanuary 27, 2011 at 11:12

@sokrates with the following logic?
into if one row returned
bulk collect into if it could return multiple rows

else it does not return one row nor multiple rows, so it returns implicitely zero row

a bit obscure imho
Sokrates January 27, 2011 at 13:43

@Laurent Schneider
it is a bit obscure, I agree
David Aldridge January 27, 2011 at 14:58

I would think here that the bug is the failure to throw an error when an INTO clause is not specified for a SELECT statement.

In 10.2.0.4 if you do not list enough variables in the INTO clause you get:

ORA-00932: inconsistent datatypes: expected – got –

If you list too many you get:

ORA-01007: variable not in select list

Why no error when the INTO clause is missing entirely?
Laurent Schneider Post authorJanuary 27, 2011 at 16:09

@david aldridge
Yes, throwing an error when no INTO clause is specified would be good
Sokrates January 27, 2011 at 17:33

@Centinul
that’s a pretty cool way if you simply want to parse a SELECT-statement – for example to check if it is valid !
unfortunately it’s not documented that way
Sokrates January 27, 2011 at 17:34

@Laurent Schneider
wouldn’t it be nicer they would document the fact that without INTO the statement is only parsed ?
Centinul January 27, 2011 at 18:13

@Centinul

I misspoke. It is closed (I missed the line), but it still isn’t fetched.
Laurent Schneider Post authorJanuary 28, 2011 at 11:37

@Sokrates
yes, I suggested to document this…
Dani Rey January 31, 2011 at 12:56

@Laurent Schneider
I absolutely agree with you, about the missing execution time error
AZOR October 28, 2011 at 00:28

The sequence is unhappy – but otherwise, it is logic – you do not want results and (except sequence) you can not change state of DB, by select. So why should oracle run it?

In another case you can use execute immedie with the select to “slow down” – instead of dbms_sleep session and that will not work too 🙁
Jim Gettman December 22, 2011 at 04:00

Oracle connects perfectly well to the output grid in Toad (or other display channels in other tools). but becomes ugly and stupid when an anonymous block is created with a begin and end statement. Now, the common need to interactively debug a nascent procedural query requires extra time to (1) create an output channel with a declared type, (2) direct the select result to that channel using “bulk collect into”, then (3) somehow connect that channel to the output grid. All three steps are at the “guru” level of complexity. Please, can one or more of the people with those skills give some examples for the three steps, especially the third?
Sokrates August 1, 2012 at 15:41

Laurent, I think you are wrong, you wrote: “EXECUTE IMMEDIATE ‘SELECT’ does not execute anything”
I think, it should read “EXECUTE IMMEDIATE ‘SELECT’ does not fetch anything”.

See:

[email protected] > exec execute immediate ‘select 1/0 from dual where 0 is not null’;

PL/SQL procedure successfully completed.

[email protected] > exec execute immediate ‘select 0 from dual where 1/0 is not null’;
BEGIN execute immediate ‘select 0 from dual where 1/0 is not null’; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 1

tkprof shows me ( for both statements ) 1 parse, 1 execute but zero fetches ( which explains the error for the second, but no error for the first query )
Laurent Schneider Post authorAugust 2, 2012 at 10:10

This is what Centinul traces revealed indeed

Comments are closed.