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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With