PLSQL: VARBIABLE := SEQUENCE.NEXTVAL or SELECT SEQUENCE.NEXTVAL into VARIABLE from dual?

Question

What is difference in PL/SQL from:

CREATE OR REPLACE FUNCTION WBAR_TEST_1 RETURN NUMBER IS
  LN_TMP NUMBER;
BEGIN
  LN_TMP := SOME_SEQUENCE.NEXTVAL;
  RETURN LN_TMP;
END WBAR_TEST_1;

and

CREATE OR REPLACE FUNCTION WBAR_TEST_2 RETURN NUMBER IS
  LN_TMP NUMBER;
BEGIN
  SELECT SOME_SEQUENCE.NEXTVAL INTO LN_TMP FROM DUAL;
  RETURN LN_TMP;
END WBAR_TEST_2;

I think that second approach is only for history purposes only.

score 17 · Accepted Answer · answered Oct 25 '13 at 11:57

The first one became available with Oracle 11g, prior to that you had to SELECT seq_name.nextVal FROM dual. What is the difference? Well, the first one is easier to read in my opinion.

What is more, Tim Hall on his site wrote that, according to documentation, the first (new) approach can improve performance. He performed some tests, but the difference was marginal.

Read more on Tim Hall's site: Sequences and NEXTVAL in PL/SQL

On the other hand, as stated here About using NEXTVAL in PL/SQL - Oracle 11g, the underlying implementation of fetching the nextVal value hasn't changed, so in fact there should be no difference.

PLSQL: VARBIABLE := SEQUENCE.NEXTVAL or SELECT SEQUENCE.NEXTVAL into VARIABLE from dual?

1 Answers1