Tags
A sequence that starts at the starting point with every session that accesses the sequence…what is this good for?
Now, I could think at things like logging, where we could give every step of a session a ascending step_id, which would be stored in the log table along with the session_id to which the step belonged. Or we could have a transaction which creates a series of entries in the database and within the transaction those entries are numbered for whatever purpose. Generating Primary Keys for a Global Temporary Table would be another field where those kinds of sequences could be used.
Create a session level sequence
A session level sequence is generated using the new SESSION key word. This will restart the sequence for every session.
CREATE SEQUENCE session_seq
START WITH 1
INCREMENT BY 1
SESSION
/
Usage
Example filling a global temporary table using a session level sequence as primary key generator:
CREATE SEQUENCE session_seq
START WITH 1
INCREMENT BY 1
SESSION
/
Sequence created.
CREATE GLOBAL TEMPORARY TABLE employees_temp_table (
employee_id NUMBER DEFAULT session_seq.nextval NOT NULL
,first_name VARCHAR2(20)
,last_name VARCHAR2(25)
)
ON COMMIT PRESERVE ROWS
/
Table created.
REM ===========================================================================
REM Add rows to table
REM ===========================================================================
INSERT INTO employees_temp_table (first_name, last_name)
SELECT first_name, last_name
FROM hr.employees
WHERE rownum < 20
/
19 rows created.
REM ===========================================================================
REM Show the results
REM ===========================================================================
SELECT *
FROM employees_temp_table
/
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
1 Ellen Abel
2 Sundar Ande
3 Mozhe Atkinson
4 David Austin
5 Hermann Baer
6 Shelli Baida
7 Amit Banda
8 Elizabeth Bates
9 Sarah Bell
10 David Bernstein
11 Laura Bissot
12 Harrison Bloom
13 Alexis Bull
14 Anthony Cabrio
15 Gerald Cambrault
16 Nanette Cambrault
17 John Chen
18 Kelly Chung
19 Karen Colmenares
19 rows selected.
If another session does the same thing, the employee_id will start at 1 again.
In my opinion the changes to the DEFAULT behaviour is a big progress and I am pretty sure, that this will influence the usage of this feature.