The sequence does not increase unless I store the value

Question

In regular SQL, my sequence is incremented every time I call .NEXTVAL :

 SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 54 SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL; -- 55 SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL; -- 56 SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL; -- 57 SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57 (54+3, correct) 

However, in dynamic SQL, inside a PL / SQL block, it does not increase:

 SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57 BEGIN EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL'; EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL'; EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL'; END; / SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57 

... if I do not store the value in a variable:

 SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 57 (!) DECLARE FOO INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL' INTO FOO; EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL' INTO FOO; EXECUTE IMMEDIATE 'SELECT PDF_DATOS_TITULO_ID_SEQ.NEXTVAL FROM DUAL' INTO FOO; END; / SELECT PDF_DATOS_TITULO_ID_SEQ.CURRVAL FROM DUAL; -- 60 (57+3, correct) 

What is the explanation? Is this the documented behavior of EXECUTE IMMEDIATE ?

_{Before you ask, SQL must be dynamic since the variable name is a variable.}

Answer 1

when you omit or return in sentences, Oracle will analyze only sql, but not produce any of them. The docs do not explicitly state that this will happen, but the docs indicate that you should use INTO when you have 1 row, returned in bulk, if you have the ability to return multiple rows.

eg:.

 SQL> create sequence testseq; Sequence created. SQL> alter session set events '10046 trace name context forever'; Session altered. SQL> exec execute immediate 'select testseq.nextval from dual'; PL/SQL procedure successfully completed. SQL> alter session set events '10046 trace name context off'; Session altered. SQL> exit 

we see in the trace that Oracle was not worried about the FETCH stage:

 ===================== PARSING IN CURSOR #140341213531640 len=32 dep=1 uid=83 oct=3 lid=83 tim=1363260261727946 hv=956010684 ad='7ac66b58' sqlid='56jwk2hwgr45w' select testseq.nextval from dual END OF STMT PARSE #140341213531640:c=4001,e=50473,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,plh=112670795,tim=1363260261727944 EXEC #140341213531640:c=0,e=219,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=112670795,tim=1363260261728249 STAT #140341213531640 id=1 cnt=0 pid=0 pos=1 obj=79530 op='SEQUENCE TESTSEQ (cr=0 pr=0 pw=0 time=181 us)' STAT #140341213531640 id=2 cnt=0 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)' CLOSE #140341213531640:c=4001,e=24391,dep=1,type=3,tim=1363260261752736 EXEC #140341212444728:c=8002,e=75407,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1363260261752783 *** 2013-03-14 11:24:29.866 CLOSE #140341212444728:c=0,e=37,dep=0,type=0,tim=1363260269866098 ===================== 

vs

 SQL> alter session set events '10046 trace name context forever'; Session altered. SQL> var a number SQL> exec execute immediate 'select testseq.nextval from dual' into :a; PL/SQL procedure successfully completed. SQL> alter session set events '10046 trace name context off'; Session altered. 

and now:

 PARSING IN CURSOR #139830768042232 len=32 dep=1 uid=83 oct=3 lid=83 tim=1363260428931803 hv=956010684 ad='7ac66b58' sqlid='56jwk2hwgr45w' select testseq.nextval from dual END OF STMT PARSE #139830768042232:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=112670795,tim=1363260428931802 EXEC #139830768042232:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=112670795,tim=1363260428931917 FETCH #139830768042232:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=112670795,tim=1363260428931980 STAT #139830768042232 id=1 cnt=1 pid=0 pos=1 obj=79530 op='SEQUENCE TESTSEQ (cr=0 pr=0 pw=0 time=39 us)' STAT #139830768042232 id=2 cnt=1 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 #139830768042232:c=0,e=0,dep=1,type=3,tim=1363260428931980 EXEC #139830768045912:c=0,e=294,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1363260428931980 *** 2013-03-14 11:27:13.138 CLOSE #139830768045912:c=0,e=45,dep=0,type=0,tim=1363260433138490 ===================== 

FETCH is displayed. I think that, ideally, Oracle should throw an error when the user issues a choice without specifying INTO / BULK INTO.