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

plsql oracle11g oracle11gr2
WBAR picture WBAR · Oct 25, 2013 · Viewed 46.7k times · Source

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.

Answer

Przemyslaw Kruglej picture Przemyslaw Kruglej · Oct 25, 2013

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.

Related questions

Using bind variables with dynamic SELECT INTO clause in PL/SQL

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL. For example, I know that this is valid: CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job …

Read More
ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536

Below is the code i use to extract data from a table to a flat file. BEGIN DECLARE file_name VARCHAR2(50); file_handle utl_file.file_type; BEGIN file_name := 'table.txt'; file_handle := utl_file.fopen('SEND',file_name,…

Read More
Sleep function in ORACLE

I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function: CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP ( TIME_ IN NUMBER ) RETURN INTEGER IS BEGIN DBMS_LOCK.sleep(seconds => TIME_); …

Read More