Re: How to: retrieve Sequence.NEXTVAL to a variable?

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Fri, 13 Nov 1998 12:36:30 GMT
Message-ID: <364c278f.1206224@newshost.us.oracle.com>

On Fri, 13 Nov 1998 11:18:56 -0000, "Hugh Pendry" <hpendry_at_trinitech.co.uk> wrote:

>Does anyone know how to retrieve the next value (or current value) a
>sequence into a variable in a stored procedure. I have tried various things
>including:-
>
>function x
>(
> ...
>)
>return ...
>is
> MyVar integer
>begin
> select MySequence.nextVal into MyVar;
> ...
>end;
>
>But have had no luck getting anything to work.

You almost had it...you just need to SELECT from DUAL instead. As in:

---

SQL> set serveroutput on;
SQL> create sequence seq_foo;

Sequence created.

SQL> create procedure doit as
  2 myvar integer;
  3 begin
  4 SELECT seq_foo.NEXTVAL INTO myvar FROM DUAL;   5 DBMS_OUTPUT.PUT_LINE('The value is: ' || myvar );   6 end;
  7 /

Procedure created.

SQL> exec doit;
The value is: 1

PL/SQL procedure successfully completed.

SQL> exec doit;
The value is: 2

PL/SQL procedure successfully completed.

SQL>

---

>
>Thanks in advance
>hugh.
>
>

Thanks!

Joel

Joel R. Kallman Oracle Government, Education, & Health

Columbus, OH                             http://govt.us.oracle.com

jkallman@us.oracle.com                   http://www.oracle.com

---

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Fri Nov 13 1998 - 06:36:30 CST