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”

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

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

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

  4. “…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
    )

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

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

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

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

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

    sokrates@11.2.0.2 > exec execute immediate ‘select 1/0 from dual where 0 is not null';

    PL/SQL procedure successfully completed.

    sokrates@11.2.0.2 > 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 )

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>