pl/sql oracle how to check or how to store the current value of a sequence

Question

I am trying to show the record after insert it on the table. However my primary key is a substring with a sequence, so I cant find a way to save this value ...this is my code

SET SERVEROUTPUT ON


ACCEPT MARQUE PROMPT "Entrez la marque de la moto " 
ACCEPT ANNEE PROMPT "Entrez l annee de la moto: " 
ACCEPT PRIX PROMPT "Entrez le prix de la moto: "

DECLARE


myMarque VARCHAR2(50):='&MARQUE';
myAnnee VARCHAR2(6):='&ANNEE';
myPrix NUMBER(6,2):=&PRIX;
lecode VARCHAR2(12);

BEGIN

    IF myAnnee = 2013 THEN

        INSERT INTO MOTO (CODE, MARQUE, ANNEE, PRIX) VALUES (UPPER(SUBSTR(myMarque, 1,3)||MASEQ.nextval), myMarque, myAnnee, myPrix);
        COMMIT; 

    ELSIF myAnnee = 2014 THEN

        INSERT INTO MOTO (CODE, MARQUE, ANNEE, PRIX) VALUES (UPPER(SUBSTR(myMarque, 1,3)||MASEQ.nextval), myMarque, myAnnee, myPrix);
        COMMIT;

    ELSIF myAnnee = 2015 THEN

        INSERT INTO MOTO (CODE, MARQUE, ANNEE, PRIX) VALUES (UPPER(SUBSTR(myMarque, 1,3)||MASEQ.nextval), myMarque, myAnnee, myPrix);
        COMMIT;

    ELSIF myAnnee = 2016 THEN

        INSERT INTO MOTO (CODE, MARQUE, ANNEE, PRIX) VALUES (UPPER(SUBSTR(myMarque, 1,3)||MASEQ.nextval), myMarque, myAnnee, myPrix);
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Annee invalide SVP VERIFIEZ');
    END IF;



END;
/

In short what I need is to show the record after commit , any ideas? I tried to save the value in the variable lecode but it didnt work.

Perhaps you need last insert id http://stackoverflow.com/q/3131064/2298301? — Dhruv Saxena, Feb 07 '17 at 22:32

score 3 · Accepted Answer · answered Feb 07 '17 at 22:30

3

Use the RETURNING clause on your INSERTS, e.g.:

DECLARE
  strLast_code  MOTO.CODE%TYPE;
BEGIN
  INSERT INTO MOTO
    (CODE, MARQUE, ANNEE, PRIX)
  VALUES
    (UPPER(SUBSTR(myMarque, 1,3)||MASEQ.nextval), myMarque, myAnnee, myPrix)
  RETURNING CODE INTO strLast_code;
END;

answered Feb 07 '17 at 22:30

Bob Jarvis - Слава Україні

48,992
9
77
110

thank you it works fine. i didnt know u could do that – Natalia Fontiveros Feb 08 '17 at 02:51

score 0 · Answer 2 · edited Feb 08 '17 at 07:38

0

sequence_name.currval will give the current value of sequence.

just like sequence_name.nextval you can get sequence_name.currval

edited Feb 08 '17 at 07:38

J. Chomel

8,193
15
41
69

answered Feb 08 '17 at 02:41

Rahul Malireddy

31
8

score 0 · Answer 3 · answered Feb 08 '17 at 07:41

0

Another solution would be to select directly from the database's metadata:

select last_number from all_sequences where sequence_name='MASEQ';

answered Feb 08 '17 at 07:41

J. Chomel

8,193
15
41
69

pl/sql oracle how to check or how to store the current value of a sequence

3 Answers3