CODEWITHSUNDEEP
sqloracle-databaseplsql
p.ro
p.ro

Reputation: 53

sequence on pl/sql

create sequence MYSEQ_sequence
start with 100
increment by 5;

I just created the sequence in sql. I want to create pl/sql anonymous block program unit that shows the curval, and nextval of the sequence MYSEQ. Does anybody know how to put it in pl/sql?

Upvotes: 2

Views: 2396

Answers (5)

Miracle
Miracle

Reputation: 387

DECLARE
    curr NUMBER;
    nxt NUMBER; 
BEGIN
    SELECT MYSEQ_sequence.currval, MYSEQ_sequence.nextval
    INTO curr, nxt
    FROM DUAL;
    DBMS_OUTPUT.PUT_LINE ('Current value: ' || curr || ' ' || 'Next value: ' || nxt);
END;

Upvotes: 1

hmmftg
hmmftg

Reputation: 1754

Hi I tested this one and it works:

DECLARE
   V_CUR    VARCHAR2 (32);
   V_NEXT   VARCHAR2 (32);
BEGIN
   SELECT MYSEQ_SEQUENCE.CURRVAL
     INTO V_CUR
     FROM DUAL;
   SELECT MYSEQ_SEQUENCE.NEXTVAL
     INTO V_NEXT
     FROM DUAL;

   DBMS_OUTPUT.PUT_LINE (V_CUR);
   DBMS_OUTPUT.PUT_LINE (V_NEXT);
END;

IMPORTANT: -You have to get the first value of this sequence once. -If you get both values in a single try, you'll get one number

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

CREATE SEQUENCE MYSEQ_sequence
MINVALUE 100
START WITH 100
INCREMENT BY 5;

SELECT MYSEQ_sequence.currval AS seq
FROM DUAL

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270993

A simple query will do:

SELECT MYSEQ_sequence.currval, MYSEQ_sequence.nextval
FROM DUAL

You don't even need a PL/SQL block for this.

Upvotes: 3

Jim Macaulay
Jim Macaulay

Reputation: 5167


Hi,
You can use below procedure,

CREATE OR REPLACE PROCEDURE procedure_name (sequence_name varchar2)
AS
V_NEXTVAL integer;
V_SELECT varchar2(100);
BEGIN
V_SELECT := 'SELECT'||sequence_name||'.NEXTVAL FROM DUAL';
EXECUTE IMMMEDIATE V_SELECT INTO V_NEXTVAL; 
DBMS_OUTPUT.PUTLINE('Nextval is: '||TO_CHAR(V_NEXTVAL));
END;

Upvotes: 2

Related Questions