How to select the value of a variable in Oracle?

The Light picture The Light · Sep 24, 2013 · Viewed 41.7k times · Source

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.

Answer

ajmalmhd04 picture ajmalmhd04 · Sep 24, 2013

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