11

I'm new to Oracle. How can I set this variable and show its value?

declare nextId number;
begin  
        select HIBERNATE_SEQUENCE.nextval into nextId from dual;      
        select nextId from dual;
end;

It complains that an INTO clause is expected in this SELECT statement.

Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
The Light
  • 26,341
  • 62
  • 176
  • 258

2 Answers2

14

If you only wanted to know the sequence's next or current value, you could simply use sql query:

SELECT HIBERNATE_SEQUENCE.nextval FROM dual;
SELECT HIBERNATE_SEQUENCE.currval FROM dual;

As to know how to proceed in pl/sql (before 11g):

SET SERVEROUTPUT ON
DECLARE
     nextId NUMBER;
BEGIN
     SELECT HIBERNATE_SEQUENCE.nextval INTO nextId FROM dual;
     dbms_output.put_line(nextId);
END;

Since 11g: it is more simplified sequence to use in plsql as:

SET serveroutput ON
DECLARE
     nextId NUMBER := HIBERNATE_SEQUENCE.nextval;
BEGIN
     dbms_output.put_line(nextId);
END;

or simply

BEGIN
     dbms_output.put_line(HIBERNATE_SEQUENCE.nextval);
END;

More details:Click here

ajmalmhd04
  • 2,582
  • 6
  • 23
  • 41
  • +1 But since 11g you can directly access sequences in PL/SQL. You can replace line 5 with `nextID := HIBERNATE_SEQUENCE.nextval;`. – Jon Heller Sep 24 '13 at 17:47
8

In a pl/sql block, you cannot write an SQL statement like 

select nextId from dual;

That is why it is showing you an error. By the way you do not need this statement altogether. To display it as an output you should use -

DBMS_OUTPUT.PUT_LINE(nextId);

To be able to display it you need to write the below statement before the declare block -

SET SERVEROUTPUT ON;

Aditya Kakirde
  • 4,935
  • 1
  • 13
  • 10