Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 420
The Light Avatar asked Sep 24 '13 09:09

The Light


People also ask

How do you set a variable value in Oracle?

To assign a default value to a variable, you use the assignment operator ( := ) or the DEFAULT keyword. In this example, instead of using the assignment operator := , we used the DEFAULT keyword to initialize a variable.

What is SELECT into in Oracle?

The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections. For a full description of the SELECT statement, see Oracle Database SQL Reference.

What is SELECT * from table in Oracle?

In short, it is used to convert a collection or pipelined function into a table that can be queried by a SELECT statement. Typically, the collection must be of a datatype that is defined at the database level (i.e. a datatype that was created by a create or replace type ... statement). e.g.


1 Answers

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

like image 194
ajmalmhd04 Avatar answered Oct 19 '22 14:10

ajmalmhd04