CODEWITHSUNDEEP
sqloracle-databaseselectselect-into
The Light
The Light

Reputation: 27011

How to select the value of a variable in Oracle?

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.

Upvotes: 11

Views: 55366

Answers (2)

ajmalmhd04
ajmalmhd04

Reputation: 2602

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

Upvotes: 14

Aditya Kakirde
Aditya Kakirde

Reputation: 5225

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;

Upvotes: 8

Related Questions