Hi Experts,
Below question came across while practicing sequence . I have a sequence which can be referenced from multiple session.
--------------------------------------------------------
-- DDL for Table TEST and TEST_CP
--------------------------------------------------------
CREATE TABLE TEST
( ID_PK NUMBER, NAME VARCHAR2(20)
);
CREATE TABLE TEST_CP
( ID_PK NUMBER, NAME VARCHAR2(20),INSERT_DATE DATE
);
CREATE sequence sno;
-- Session 1
INSERT INTO test VALUES
(sno.nextval,'Oracle'
);
--Session 2
INSERT INTO test VALUES
(sno.nextval,'Oracle'
);
--Session 1
INSERT INTO test_cp VALUES
(sno.currval,'Oracle',sysdate -- here sequence value has been update by session 2. But I have to insert value which was generated from session1( value 1 instead of 2)
);
Is there any way to get sequence value specific to particular session in pure sql without using PL/SQL and variables.
This can be achieved through PLSQL code. but I would like to know is there any way can achieve through SQL.
DECLARE
V_SEQ_VALUE NUMBER;
BEGIN
V_SEQ_VALUE := SNO.NEXTVAL;
INSERT INTO test VALUES
(V_SEQ_VALUE,'Oracle'
);
INSERT INTO test_cp VALUES
(V_SEQ_VALUE,'Oracle',sysdate
) ;
END;